帮助了解 MySQL 进程列表描述/含义
谁能告诉我下面的信息是什么意思或说了什么？我对 MySQL 不太熟悉。此信息来自我的托管提供商。我特别感兴趣的是命令和时间注释的含义或告诉他们的内容。
查看当前的 mysql 进程列表，我们注意到以下与您的帐户相关的线程：
| 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 |
以下是有关该情况的一些更详细的信息：
我最近遇到了与我的共享托管提供商有关我的 MySQL 使用情况的情况。参考中的页面已经运行了近两年，没有出现任何问题，直到两天前，他们声称该页面占用了过多的资源，给服务器带来了负载，导致其不稳定。我们的网站流量和统计数据在过去 9 个月甚至更长时间内保持不变。
他们从其中拖出一些信息后为我提供了一些信息，但我的问题是：
- 这是否过度使用资源？
- 为什么2年后突然就成为一个问题了？
- 即使在过去 2.5 天禁用该页面后，MySQL 上的资源使用率仍然保持不变或上升，那么问题是否真的与我的页面或其他内容有关？
- 进程列表告诉我什么？
- 我可以做什么来解决这个问题？
以下是他们提供的一些信息：
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
您的帐户的问题在于 MySQL 使用率过高。其他帐户的平均使用量为 MySQL 的 0.2%，而您的帐户则消耗了 1% 以上。
查看当前的 mysql 进程列表，我们注意到以下与您的帐户相关的线程：
+ 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 |
请确保您的帐户同时有不超过 2-3 个并发 mysql 连接，并且您继续优化连接池的连接字符串;还要确保所有连接在使用后都正确终止，并且没有连接处于“空闲打开”状态。
我检查了过去 7 天的资源使用情况，这就是 MySQL 的情况。奇怪的是，自从该页面不再活跃且在过去 2.5 天内不再活跃以来，使用量有所上升或保持不变。
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
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:
- is this excessive resource usage?
- why after 2 years would it be an issue all of a sudden?
- 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?
- what does the process list tell me?
- 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
好的，回答您的问题：
我认为这是否“过度”资源使用取决于您与托管公司的协议。我会回顾一下。但老实说，如果我的主机认为 1% 的 CPU 使用率过高，我会寻找另一台主机。与 4 个睡眠连接相同。当然，我更习惯于专用托管（我想说，我使用的机器上的 MySQL 的 CPU 使用率过高，远远超过 200%，而过多的空闲连接可能会达到数百个）。但即便如此，您使用的一台便宜机器的成本还不到 1%，所以。考虑一下价值 1000 美元的 i7 机器上的最大 CPU 使用率为 400% 或 800%（取决于超线程）。
对于为什么它可能突然改变，唯一想到的就是检查正确的索引。也许您的数据一直在缓慢增长？也有可能它没有突然改变，但你的托管公司刚刚注意到“嘿，这个服务器超载了，让我们调查一下”。
显然，如果关闭页面没有改变负载，那么它就不会导致负载。但我确信您已经知道这一点。
进程列表告诉您有 4 个休眠（空闲）连接，以及它们空闲了多长时间。
要减少空闲连接数，请更改 PHP/Apache 配置。我相信
mysql_pconnect每个 Apache 进程都拥有一个连接。或者，切换到mysql_connect。唯一真正的编程答案是检查索引、对查询进行 EXPLAIN 等，这是正常的 MySQL 优化内容。我怀疑，真正的答案是找到一台在您使用 20 美分/月的计算能力时不会抱怨的主机。
Ok, to answer your questions:
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).
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".
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.
The process list tells you you have 4 sleeping (idle) connections, and how long they've been idle for.
To decrease the number of idle connections, change your PHP/Apache configuration. I believe
mysql_pconnectholds one connection per Apache process. Alternatively, switch tomysql_connect.The only real programming answers are to check your indices, do
EXPLAINon 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.您也可以获得更好的信息 mysql processlist
如果您是， 使用 mysql 客户端或说 phpmyadmin 您实际上可以看到确定查询时间的过程查看哪些查询需要很长时间才能执行。
you can get better information mysql processlist
also if you are using a mysql client or say phpmyadmin you can actually see the processes to determine your query time and see which queries take long time to execute.