Almost all open source web applications use MySQL as its database. It’s easy to use and supported by all hosting providers. However, many websites often face performance issues due to MySQL high CPU usage.
This is primarily caused by fragmentation in database tables, increased traffic to the database, complex SQL queries or an increase in database size. Now, these issues do not crop up one fine day. It builds up gradually over a period of time, exerting an increasingly heavy load on Memory, CPU and I/O.
Sadly, many webmasters do not become aware of a MySQL bottleneck until the web traffic drops or the site crashes right in the middle of peak hours.
At Bobcares, we help webmasters avoid MySQL high CPU issues by continually monitoring database performance metrics, and optimizing MySQL settings and tables if there’s a performance issue. In the server admin world, this is known as Preventive Server Maintenance.
Our experts monitor customer servers round the clock. When we detect an anomaly, like slow MySQL response times, we immediately login to the server, kill the abusive connection, and restore normal operating speeds.
But, we do not stop at that. We audit MySQL settings to see WHY a response issue happened in the first place, and then optimize the tables and tweak MySQL settings so that it won’t happen again.
[ Making your MySQL crash proof doesn’t have to be hard, or costly. Our Server Maintenance plans start at $25/month. ]
Today, we’ll take a look at how we react to a MySQL high CPU situation, and what we do to prevent its recurrence.
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:
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.
[ Click here to know how to fix a MySQL high memory usage situation. ]
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)”.
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 talk to you.
The post How to fix MySQL high CPU usage appeared first on Bobcares.