httpd and mysql long running process

[filth]

Hi all

Recently (last couple of weeks) I have found that mysql and apache have been eating my server resources pretty badly. When I execute a top command I find that 1 or the other has an extremely long running process. For example the following is taken from a top command 5 minutes ago:-

24256 mysql 15 0 101m 6704 2472 S 5.3 0.9 11:53.69 mysqld

As you can see it has been running for almost 12 hours. (and consuming a lot of memory). When I try the 'watch mysqladmin proc' command there was only 2 running and nothing looked out of ordinary. The longest I have seen so far is a process running for 23 hours.

Has anyone any idea what could cause this? Is mysql simply not closing a dormant connection?

As soon as I restart the service the load of the server returns to a very good level but before the commands they can be anywhere between 4 and 6 on a single core server.

 

[cPanelKenneth]

When your server is under this load, execute the following:

# mysqladmin processlist

It will display the queries are running. The important column is the State column. If the entry for the first query is Locked then it will block the other queries that need to access the same table(s).

A quick way of determining if locks are your problem is to execute:

# mysqladmin debug

and check the output in the /var/lib/mysql/*.err log. It will display a list of locks.

Locks may or may not be your particular issue. Please consider reading the MySQL manual, particularly the sections pertaining to optimization and administration. The manual contains valuable insight into determining what exactly is causing issues with your server.