帮助MySQL进程列表描述/含义

Question

Can anyone tell me what the information below means or says? I'm not real familiar with MySQL. This info is coming from my hosting provider. I'm specifically interested in what the command and time comment means or is telling them.

Looking into the current mysql process list, we have noticed following threads related to your account:

| Id | User  | Host | db | Command | Time | State | Info |

| 4655267 | xyz | localhost | xyz | Sleep   | 111  |     
| 4655961 | xyz | localhost | xyz | Sleep   | 8    |   
| 4654241 | xyz | localhost | xyz | Sleep   | 126  |     
| 4655961 | xyz | localhost | xyz | Sleep   | 38   |     

Here's some more detailed info on the situation:

I've recently run into a situation with my shared hosting provider regarding my MySQL usage. The page in reference has been up and running for nearly 2 years without issue until two days ago when they claimed it was utilizing excessive resources and putting load on server, making it unstable. Our site traffic and stats have been the same for the last 9 months and probably longer.

They've provide me with some information after dragging it out of them, but my questions are:

1. is this excessive resource usage?
2. why after 2 years would it be an issue all of a sudden?
3. even after the page was disabled for the last 2.5 days the resource usage on MySQL either remained the same or went up, so is the issue really related to my page or something else?
4. what does the process list tell me?
5. what might I do to resolve this issue?

Here's some info they have provided:

CPU: 0.06 % 
Memory: 0.17 % 
Mysql: 1.2 % 

Top Process   %CPU 3.0   /usr/php4/bin/php / specific file name 
Top Process   %CPU 1.5   /usr/php4/bin/php / specific file name 

The issue with your account is of high MySQL usage. The average usage of other account is 0.2% of MySQL whereas your account is consuming more than 1%.

Looking into the current mysql process list, we have noticed following threads related to your account:

+ Id | User  | Host | db | Command | Time | State | Info |

| 4655267 | xyz | localhost | xyz | Sleep   | 111  |     
| 4655961 | xyz | localhost | xyz | Sleep   | 8    |   
| 4654241 | xyz | localhost | xyz | Sleep   | 126  |     
| 4655961 | xyz | localhost | xyz | Sleep   | 38   |

Please make sure that your account is having not more than 2-3 simultaneous mysql connections at a single time and you continue to optimize your connection strings for connection pooling; also make sure that all of the connections are properly terminated after their utilization and no connections are left as "idle-open".

I've checked the resource usage over the last seven days and this is what it says for MySQL. Strange that the usage has gone up or remained the same since the page is no longer active and hasn't been for the last 2.5 days.

1/21/11 : Number of MySQL procs (average) - 1.19 
1/20/11 : Number of MySQL procs (average) - 1.24 
1/19/11 : Number of MySQL procs (average) - 1.20 
1/18/11 : Number of MySQL procs (average) - 1.18 
1/17/11 : Number of MySQL procs (average) - 1.13 
1/16/11 : Number of MySQL procs (average) - 1.08 
1/15/11 : Number of MySQL procs (average) - 1.09

Answer 1

Ok, to answer your questions:

1. Whether this is "excessive" resource usage would, I think, depend on your agreement with your hosting company. I'd review that. But honestly, if my host considered 1% CPU usage excessive, I'd find another host. Same with 4 sleeping connections. Of course, I'm more used to dedicated hosting (where I'd say excessive CPU usage is well over 200% for MySQL on the machines I use, and excessive idle connections would be more like several hundred). But even so, you're using less than 1% of one cheap machine, so. Consider that max CPU usage on a $1k i7 machine is either 400 or 800% (depending on hyperthreading).

2. For why it may have suddenly changed, the only thing that comes to mind is to check for proper indices. Maybe your data has been slowly growing? Its also possible that it hasn't suddenly changed, but your hosting company just noticed "hey, this server is overloaded, let's look into it".

3. Well, obviously, if turning off the page didn't change the load, then it wasn't causing the load. But I'm sure you already knew this.

4. The process list tells you you have 4 sleeping (idle) connections, and how long they've been idle for.

5. To decrease the number of idle connections, change your PHP/Apache configuration. I believe mysql_pconnect holds one connection per Apache process. Alternatively, switch to mysql_connect.

The only real programming answers are to check your indices, do EXPLAIN on your queries, etc., the normal MySQL optimization stuff. The real answer, I suspect, is to find a host that doesn't complain when you use 20¢/mo of computing power.