How to fix MySQL high CPU usage
Majority web applications are database driven. MySQL is a popular database server for open source applications, but we’ve seen that it can cause performance issues in the long run.
MySQL databases grow in size, and tables get fragmented over time. This contributes to MySQL load spikes. So, protecting a server from MySQL high CPU issues requires close monitoring and periodic optimization.
In Bobcares’ Server Management services, there are 3 key operations that we do to keep MySQL server stable:
1. Initial server optimization
When we setup a server or during initial server optimization task in the servers manage, we perform custom tweaking of the MySQL server to tune them best for the customer’s business.
After analyzing the website types and traffic, we tweak the key settings in MySQL servers that helps to ensure optimal resource usage – such as the connection limits, buffer size and query cache size, etc.,
2. 24/7 monitoring & emergency rescue
A vulnerability in MySQL server is usually revealed when there’s a spike in server traffic. So, it is important to keep a close watch on the server metrics round the clock.
Our experts monitor MySQL health 24/7, which helps us to detect a spike in the CPU usage or abusive processes promptly and to fix them before it crashes the entire server.
3. Follow-up audit & optimization
While many server administrators handle high load with a service restart, those are just temporary fixes. If proper investigation and tweaking is not done, the load spike can happen again.
At Bobcares, we do a follow-up investigation to audit the MySQL settings to see WHY an issue happened in the first place, and then optimize the tables and tweak MySQL settings so that it won’t happen again.
Today, we’ll take a look at how we react to a MySQL high CPU situation, and what we do to prevent its recurrence.
[ You don’t have to be a MySQL expert to keep your sites fast and stable. Our server experts would help you to maintain your MySQL server stable. ]
How to detect MySQL high CPU usage
Very often people link high server load to high CPU usage. That is not always true. Server load can go high due to bottlenecks in any resource. This can include memory, disk I/O, network or CPU.
The top reason for MySQL induced server load is due to memory or I/O exhaustion. If it is CPU bottleneck, the output of top would look like this:
top - 8:31:15 up 23 days, 3:33, 1 user, load average: 22.31, 19.72, 25.02 Tasks: 81 total, 5 running, 76 sleeping, 0 stopped, 0 zombie Cpu(s): 87.8%us, 0.3%sy, 0.0%ni, 11.2%id, 0.0%wa, 0.2%hi, 0.5%si, 0.0%st Mem: 2975920k total, 1018219k used, 1957701k free, 287408k buffers
If it is I/O induced bottleneck, the %wa (called wait average) will have the highest CPU%. In contrast, if it is a memory induced load, the “free” memory would be limited to just a few MBs.
Fixing MySQL high CPU
If the server load is indeed related to high CPU usage, we’ve found the following fixes to be useful:
- Enable InnoDB to handle high number of concurrent connections – Check MySQL “PROCESSLIST”, and if you see a lot of queries in “LOCK” status, it means a lot queries are put on hold because MyISAM tables are handling other transactions. To fix this convert those tables into InnoDB engine which supports row-level locking.
- Enable persistent connections – If you have only a single application that receives thousands of connections per hour, enabling persistent MySQL connections can improve performance. If the server has multiple applications (like a shared web hosting server) this may not work.
- Block abusive processes – When a website is under attack (like DoS, comment spamming, etc.), an abnormally high number of connections could be established in a short time. Use the “PROCESSLIST” in MySQL to identify the top users, and block access to the abusive connections.
- Optimize database queries – Some web applications are known to use complex queries to display site information. These queries can take a long time to execute, and cause CPU load. Get the list of such queries from the “slow query log” and reduce the number of joins and other table manipulations under a single query.
- Check for “leap second bug” – On July 1st 2015, a leap second was added to standard UTC time. In servers running old Linux kernel versions, and which uses time servers, this is seen to cause MySQL high load. If you have an old Linux kernel, try resetting the time using the command date -s “$(date)”.
[ Don’t lose your customers to a slow server. Our expert server admin team is available 24/7 with instant solutions for all your server issues.]
How to prevent MySQL high CPU usage
Server traffic changes and databases grow over time. MySQL high CPU issues can be prevented to a large extent if the database server is audited and tuned for performance.
- MySQL performance tuning – MySQL uses various buffers and cache systems to execute queries. As the volume and complexity of database queries change, the server settings need to be adjusted for optimum performance. There are various tools such as mysqltuner to identify any settings that need adjustment.
- Security audit and hardening – Spamming and DoS attacks can easily overwhelm a database server. Implement web application firewalls such as ModSecurity, and DoS firewalls such as ModEvasive to prevent attackers from affecting server uptime.
- Implementing load balancing – As the server traffic grows it might be required to split the load into multiple servers. MySQL can be configured for master-master and master-slave replication which allows queries to be served from any server in a cluster.
- Optimizing database queries – If web applications are poorly coded, no amount of database optimization will fix the server load. Monitor MySQL’s “slow query log” and reduce the number of JOINs to make the database faster.
- Using high performance alternatives such as Percona – MySQL has many memory/CPU bottlenecks due to the way it process queries. MySQL spin-offs such as MariaDB and Percona resolves these issues, and helps achieve better stability.
Bobcares helps business websites of all sizes achieve world-class performance and uptime, using tried and tested website architectures. If you’d like to know how to make your website more reliable, we’d be happy to assist to you.