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.

# top

Reduce MySQL CPU usage

  1. 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

and

SET GLOBAL slow_query_log = 'ON';
var/lib/mysql/hostname-slow.log.

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:

show engines;

Search for InnoDB and check whether is it enabled or not, if it is enabled then quit.

quit;

If it is disabled open config file and comment skip-innodb line as bellow:

#skip-innodb
/etc/my.cnf

then restart mysql service.

# /etc/init.d/mysql restart
ALTER TABLE table_name ENGINE=InnoDB;
to convert MyISAM to InnoDB

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.

Conclusion

Hope this article helps to reduce your MYSQL load issue.