1

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

HappyDeveloper
  • 315
  • 1
  • 3
  • 7

1 Answers1

1

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';
xofer
  • 3,052
  • 12
  • 19
  • Should max_user_connections be higher or lower than max_connections? I have set both to 100000, because I was getting errors from scripts that were failing to connect to the DB. – HappyDeveloper Sep 07 '11 at 01:44
  • I was suggesting that you examine the values to see if the server hit its limit ([max_used_connections](http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Max_used_connections) >= [max_connections](http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_connections)). For setting the max_connections, have a look at [Calculating Maximum Connections for MySQL Server](http://mysqlhacker.com/kabir/performance/calculating-maximum-connections-for-mysql-server.html) – xofer Sep 07 '11 at 02:19