Select Page

How to fix MySQL high CPU usage

How to fix MySQL high CPU usage

A majority of 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 fragment over time. This contributes to MySQL load spikes. So, protecting a server from MySQL high CPU issues requires close monitoring and periodic optimization.

Keep your server stable. Hire a server admin.

Here at Bobcares, our Dedicated Support Engineers maintain thousands of web and MySQL servers of our customers all over the world.

We function as the tech team of these companies, and keep their servers stable through proactive maintenance and 24/7 monitoring.

Here are the 3 key operations that we perform to keep MySQL servers stable:

1. Initial server optimization 

When a customer signs up, our engineers audit the server performance and optimize the MySQL server settings to fix any pre-existing resource bottlenecks.

After analyzing the website type and traffic, we tweak the key settings in MySQL servers that helps to ensure optimal resource usage. This includes connection limits, buffer size and query cache size, and more.

2. 24/7 monitoring & emergency rescue

Comment spamming, brute forcing, bot attack or even a legitimate increase in traffic can cause high MySQL load. Which is why it is important to monitor server metrics 24/7.

Server admins from Bobcares monitor MySQL health 24/7. It helps us to detect a spike in the CPU usage or abusive program immediately and fix it before it crashes the entire server.

3. Follow-up audit & optimization

Databases grow over time, and the load on it changes when website traffic change. That is why follow-up audit and re-optimization is critical to maintain MySQL server stability.

A core job of our Dedicated Server Administrators is to audit customer servers regularly and find out performance bottlenecks before they happen, and fix it before any customers are affected.

Today, we’ll take a look at how our Server Admins 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. Let us help you. Our experts will keep your servers fast, secure and rock solid. ]

 

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.

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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. Let us help you. Our experts will monitor & maintain your servers 24/7 so that it remains lightning fast.]

 

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.

 

How to get started

MySQL can be a bit thick to understand. It takes patience and a lot of experience to know what solution works where.

You can either jump head first into MySQL docs and become an expert (in about 2-3 years), or you can get your server fixed (and keep it fixed) by hiring an administrator.

And no, it doesn’t have to be costly. You can hire an admin for as low as $12.99/hour.

Or if you want one time quick assistance, click here to submit a support request.

 

 


PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SEE SERVER ADMIN PLANS

2 Comments

  1. Thank you *very much* for your advice. Came home today, the first day of January 2017, to find my mariadb process taking up about 40% CPU … with no database queries, should have been totally idle. As my server (Centos 7) is running on an Amazon ‘T2’ instance which relies on never running out of ‘CPU credit’ – which for my machine means only using an average of 20% CPU – this was a concern. Another half day of this and I would have been sunk.

    I read your article, not thinking I was going to find anything to help … saw your item #5 … ran the ‘date’ command … and CPU has dropped to zero.

    THANK YOU!

    Reply
  2. mysqld was consumming the entire cpu up to 1377% as my server has 14 cores, I resolved this by indexing all keywords used in WHERE clause and increasing query_cache_limit to 1024M &
    query_cache_size to 2048M

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Bobcares
Bobcares is a server management company that helps businesses deliver uninterrupted and secure online services. Our engineers manage close to 51,500 servers that include virtualized servers, cloud infrastructure, physical server clusters, and more.
MORE ABOUT BOBCARES

Privacy Preference Center

    Necessary

    Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

    PHPSESSID - Preserves user session state across page requests.

    gdpr[consent_types] - Used to store user consents.

    gdpr[allowed_cookies] - Used to store user allowed cookies.

    PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
    PHPSESSID
    WHMCSpKDlPzh2chML

    Statistics

    Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

    _ga - Preserves user session state across page requests.

    _gat - Used by Google Analytics to throttle request rate

    _gid - Registers a unique ID that is used to generate statistical data on how you use the website.

    smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

    _ga, _gat, _gid
    _ga, _gat, _gid
    smartlookCookie

    Marketing

    Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

    IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

    test_cookie - Used to check if the user's browser supports cookies.

    1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

    NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

    DV - Google ad personalisation

    IDE, test_cookie, 1P_JAR, NID, DV, NID
    IDE, test_cookie
    1P_JAR, NID, DV