MySQL is one of the best open-source RDBMS being used for developing various web-based software applications. But mostly the server faces a high CPU load of MySQL that causes website down and slowness.
Check MySQL CPU usage with top command.
Reduce MySQL CPU usage
- Optimize database queries
Long-running queries may cause high MySQL CPU usage. To find out long-running queries by enabling slow query log and update these queries. Slow queries can affect database performance and overall server performance. The slow query log feature in MySQL enables you to log queries that exceed a predefined time limit.
In order to enable query log, login to MySQL
# mysql -u root -p
tail the log and find out which queries are taking too much time
# tail -f /var/lib/mysql/hostname-slow.log
After finding queries, update the queries and fix the issue, then disable query log.
SET GLOBAL slow_query_log = 'OFF';
2. Enable InnoDB
Enabling InnoDB helps to handle a high number of concurrent connections. Log in to Mysql and type command PROCESSLIST and if you see a lot of queries are in the locked state, it means a lot of queries are put on hold because MyISAM tables are handling other transactions. To fix the problem, you can convert tables to InnoDB engines from MyISAM.
First enable InnoDB engine. Login in to mysql and type:
Search for InnoDB and check whether is it enabled or not, if it is enabled then quit.
If it is disabled open config file and comment skip-innodb line as bellow:
then restart mysql service.
# /etc/init.d/mysql restart
3. Persistent connection enabling
If your server has a single application which receives thousands of connections per hour, enable persistent MySQL connection to improve performance. This will not work if the server is a shared server.
4. Checking leap second bug
Servers running with an old Linux kernel that uses time servers can cause high MySQL load. Reset the time if you have an old Linux kernel.
date -s “$(date)”.
5. High traffic
There is a possibility to increase MySQL load if you have a high traffic website. DDOS attacks on the server lead to high MySQL usage. Try DDOS reducing mechanisms.
Hope this article helps to reduce your MYSQL load issue.