Rogue MySQL Process / Service

aristoworks · 11-29-2007, 08:49 PM

Greetings. I have a simple oscommerce install hosted on a MediaTemple server. Yesterday the site was running slow so we called MT and the contact informed us that a query had been running for a long time (a few days). This is why the server was being bogged down.

I've been coding for about 2 years (self-taught) and never encountered this kind of issue before. My question is:

How do I detect these long-running queries so I can establish where they are coming from? Isn't there some kind of config in MySQL that I can set to keep this type of thing from happening?

Thanks

jw

Wildhoney · 11-29-2007, 09:30 PM

I don't believe you are able to set a MySQL query time-out, unless things have changed since around 4 months ago. You can, however, run the following command to see which query is running that is draining resources:

sql Code:

SHOW FULL PROCESSLIST

FULL is an optional clause. You will also obviously need to have the privileges to see every process list, else you'll just be able to view your own. Email your web-host for further information on that side of things.

Remember though that it may not be one single query, but rather your PHP that has got stuck in a loop and executing the same query, or bunch of queries, over-and-over again.

Hope this helps you!

aristoworks · 11-30-2007, 07:44 PM

Hey Wildhoney - I got your response last night and forgot to thank you for your input.

I spit out the "FULL PROCESSLIST" but I'm still unable to decypher anything that would help me resolve the problem.

I'm pasting an example of what I'm seeing:

Code:

+--------+-------------+--------------------+----------------+---------+------+-------+-----------------------+
| Id     | User        | Host               | db             | Command | Time | State | Info                  |
+--------+-------------+--------------------+----------------+---------+------+-------+-----------------------+
| 244320 | serveradmin | xx.xx.xxx.xx:45158 | username | Sleep   | 1865 |       | NULL                  |
| 244322 | serveradmin | xx.xx.xxx.xx:45166 | username | Sleep   | 1863 |       | NULL                  |
| 244422 | serveradmin | xx.xx.xxx.xx:45605 | username | Sleep   | 1737 |       | NULL                  |
| 244424 | serveradmin | xx.xx.xxx.xx:45611 | username | Sleep   | 1736 |       | NULL                  |
| 244425 | serveradmin | xx.xx.xxx.xx:45613 | username | Sleep   | 1735 |       | NULL                  |
| 244434 | serveradmin | xx.xx.xxx.xx:45655 | username | Sleep   | 1723 |       | NULL                  |
| 244447 | serveradmin | xx.xx.xxx.xx:46067 | username | Sleep   | 1612 |       | NULL                  |
| 244458 | serveradmin | xx.xx.xxx.xx:46125 | username | Sleep   | 1597 |       | NULL                  |
| 244465 | serveradmin | xx.xx.xxx.xx:46179 | username | Sleep   | 1580 |       | NULL                  |
| 244475 | serveradmin | xx.xx.xxx.xx:46219 | username | Sleep   | 1564 |       | NULL                  |
| 244479 | serveradmin | xx.xx.xxx.xx:46326 | username | Sleep   | 1523 |       | NULL                  |
| 244487 | serveradmin | xx.xx.xxx.xx:46365 | username | Sleep   | 1506 |       | NULL                  |
| 244494 | serveradmin | xx.xx.xxx.xx:46628 | username | Sleep   | 1386 |       | NULL                  |
| 244538 | serveradmin | xx.xx.xxx.xx:47805 | username | Sleep   | 1025 |       | NULL                  |
| 244561 | serveradmin | xx.xx.xxx.xx:48593 | username | Sleep   |  785 |       | NULL                  |
| 244588 | serveradmin | xx.xx.xxx.xx:49567 | username | Sleep   |   63 |       | NULL                  |
| 244601 | serveradmin | xx.xx.xxx.xx:49884 | username | Sleep   |  304 |       | NULL                  |
| 244612 | serveradmin | xx.xx.xxx.xx:50233 | username | Sleep   |  183 |       | NULL                  |
| 244624 | serveradmin | xx.xx.xxx.xx:50634 | username | Sleep   |   63 |       | NULL                  |
| 244628 | serveradmin | localhost          | NULL           | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |
+--------+-------------+--------------------+----------------+---------+------+-------+-----------------------+
20 rows in set (0.00 sec)

Any other insights?

Thanks
jw

Wildhoney · 11-30-2007, 08:09 PM

I don't see anything in that little list. In fact all but 1 are internal MySQL queries run from, possibly, a cron. Have a look at the MySQL reference document for the SHOW PROCESSLIST command. It seems the privilege you require is the super privilege, else you will just be able to view your own thread, as seen in the above list you've shown us.

Your host no doubt has the super privilege and that's why they were able to inform you of the query. However, could you not ask them to provide further information to allow you to debug? If it's a non-stop PHP loop then it may be difficult to diagnose if it's via cron because you will never see the default memory limit error once all the memory has all been exhausted.

WinSrev · 11-30-2007, 08:29 PM

Well, if it's only you on the server then limit all connections to MySQL to localhost or 127.0.0.1 if you know that it won't be your coding otherwise do what Wildhoney said and check to see if there's any crons, etc..