MySQL is an important service in the Web-Hosting industry. Almost 80% of websites on the Internet are database driven. In a shared hosting environment, the availability of this service is critical.
Many times, I have come across the problem of some particular database query taking more time to execute or using more resources. Eventually the result is high load on the server. This can be due to a sequential query to select a particular value from a large table which is not optimized.
A simple approach can be adopted to find the query and table involved. To do this you can enable slow query logging.
How to enable slow query logging?
The MySQL server’s main configuration file is /etc/my.cnf. Open this file and add the following line below the section “[mysqld]“, then save and quit.
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5Create the slow query log file using the following command. Make sure MySQL user can write to this file.
touch /var/log/mysql-slow.log
chown mysql.mysql /var/log/mysql-slow.logFollow it up with a restart of the MySQL daemon. If you check the slow query log file, you can find all the queries that take more than 5 secs to run. Once you find the query, you can inform the customer(to whom the database belongs to) to optimize his database and query.
The destructive way
Until the customer optimizes his query and database, the situation of high resource usage can prevail. You can setup a script(given below) to find out the query that takes more time to run, say more than 100 seconds and kill that mysql query. 100 seconds is a safe value. Usually no query takes more than 10 seconds to run. The script will also help to keep the load caused from mysql to a minimum. You can also make use of the testmysql.txt file to find out which query is causing the problem.
#!/bin/bash
mysqladmin proc stat | grep -v Uptime > /root/testmysql.txt;
for i in $(cat /root/testmysql.txt | awk ‘{print $12}’ | grep ^[0-9])
do
echo $i;
if [ $i -gt 100 ] ; then
id=$(grep $i /root/testmysql.txt | awk ‘{print $2}’ | grep ^[0-9]);
mysqladmin kill $id;
fi
done
mysqladmin proc stat | grep -v Uptime > /root/testmysql.txt;
for i in $(cat /root/testmysql.txt | awk ‘{print $12}’ | grep ^[0-9])
do
echo $i;
if [ $i -gt 100 ] ; then
id=$(grep $i /root/testmysql.txt | awk ‘{print $2}’ | grep ^[0-9]);
mysqladmin kill $id;
fi
done
Setup a cron to execute the script every 2 or 5 minutes. This script will only terminate the mysql query and not the mysql service running on the server.
This way, you can make sure that your mysql server resource is available to all customers.
