It’s really helpful to know what queries are choking a database. To enable the mysql
slow query log add these lines to the [mysqld]
configuration section of your my.cnf
or mysql
configuration file and restart mysqld:
slow_query_log=1
slow_query_log_file=/var/log/mysql_slow_queries/dbserver1.log
long_query_time=10
This will log queries that take longer than 10 seconds to the specified file. After you collect some of the logs, work with the application developers to see if some of the queries can be optimized, using more efficient sql, adding indices, etc.