Linux – Mysql hangs up the server. How to avoid this

apache-2.2bottlenecklinuxMySQLperformance

Sometimes my website 'goes down' due to Mysql consuming all the resources, but I think it does not stop creating more httpd processes. Is that possible? If I restart apache, these processes disappear and I can see the CPU consumption by the mysql process go down slowly, and then the site is online again.

I'm having trouble finding the bottleneck anyway. The site has a lot of cronjobs (php scripts), and each one generates logs, which include the total execution time.

So my first thought was to try to find the script that took a lot of time, but that was not possible because it takes only one of them slowing the mysql server, to have all of them increasing their execution time.

Any advice?

Where can I learn how to find the bottlenecks?

Note: I'm on CentOS, apache 2, php 5.3, and mysql 5

Best Answer

You should start with the slow query log: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

You may also want to check to see if max_used_connections is higher than max_connections:

SHOW VARIABLES LIKE 'max\_connections';
SHOW STATUS LIKE 'max\_used\_connections';
Related Topic