How to fix MySQL high CPU usage?

MySQL is pretty popular among open source web apps, but it is prone to performance issues if not appropriately maintained.
Performance issues happen essentially through table fragmentation, unoptimized memory settings, and more. All this can result in MySQL high CPU usage and application errors.

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 help 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 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 the follow-up audit and re-optimization are 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.


How to detect MySQL high CPU usage

To troubleshoot CPU issues, we can use several tools, such as top or pidstat (requires the sysstat package). In the following example, we will use pidstat. The tool has an option (-t) that changes its view from process (the default) to threads, where it shows the associated threads within a given process. We can use it to find out which thread is consuming the most CPU in our server. Adding the -p parameter along with the mysql process id so the tool only shows MySQL threads, making it easier for us to troubleshoot. The last parameter (1) is to display one sample per second:

The command is pidstat -t -p <mysqld_pid> 1:

shell> pidstat -t -p 31258 1
03:31:06 PM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
[...]
03:31:07 PM 10014         -     32039    5.00    1.00    0.00    6.00    22  |__mysqld
03:31:07 PM 10014         -     32040    5.00    1.00    0.00    6.00    23  |__mysqld
03:31:07 PM 10014         -     32042    6.00    1.00    0.00    7.00     8  |__mysqld
03:31:07 PM 10014         -     32047    5.00    1.00    0.00    6.00     6  |__mysqld
03:31:07 PM 10014         -     32048    5.00    1.00    0.00    6.00    15  |__mysqld
03:31:07 PM 10014         -     32049    5.00    1.00    0.00    6.00    14  |__mysqld
03:31:07 PM 10014         -     32052    5.00    1.00    0.00    6.00    14  |__mysqld
03:31:07 PM 10014         -     32053   94.00    0.00    0.00   94.00     9  |__mysqld
03:31:07 PM 10014         -     32055    4.00    1.00    0.00    5.00    10  |__mysqld
03:31:07 PM 10014         -      4275    5.00    1.00    0.00    6.00    10  |__mysqld
03:31:07 PM 10014         -      4276    5.00    1.00    0.00    6.00     7  |__mysqld
03:31:07 PM 10014         -      4277    6.00    1.00    0.00    7.00    15  |__mysqld
03:31:07 PM 10014         -      4278    5.00    1.00    0.00    6.00    18  |__mysqld
03:31:07 PM 10014         -      4279    5.00    1.00    0.00    6.00    10  |__mysqld
03:31:07 PM 10014         -      4280    5.00    1.00    0.00    6.00    12  |__mysqld
03:31:07 PM 10014         -      4281    5.00    1.00    0.00    6.00    11  |__mysqld
03:31:07 PM 10014         -      4282    4.00    1.00    0.00    5.00     2  |__mysqld
03:31:07 PM 10014         -     35261    0.00    0.00    0.00    0.00     4  |__mysqld

 

We can see that thread 32053 is consuming the most CPU. Using that information, we can log into the database and use the following query to find out which MySQL Thread is the culprit:

mysql > select * from performance_schema.threads where THREAD_OS_ID = 32053 \G
*************************** 1. row ***************************
          THREAD_ID: 686
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 590
   PROCESSLIST_USER: msandbox
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select * from test.joinit where b = 'a a eveniet ut.'
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 32053
1 row in set (0.00 sec)

 

The high CPU consumption comes from a query in the server table, executed by the user msandbox from localhost in the database test. Using this information, we can troubleshoot and check the execution plan with the EXPLAIN command to see if there is any room for improvement.

 

mysql > explain select * from test.server where b = 'a a eveniet ut.' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: joinit
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7170836
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

 

 Here, the index was missing!

 mysql > alter table test.joinit add index (b) ;
Query OK, 0 rows affected (15.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

Once we have created the index, now we can no longer see ant CPU spikes:

shell> pidstat -t -p 31258 1
03:37:53 PM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
[...]
03:37:54 PM 10014         -     32039   25.00    6.00    0.00   31.00     0  |__mysqld
03:37:54 PM 10014         -     32040   25.00    5.00    0.00   30.00    21  |__mysqld
03:37:54 PM 10014         -     32042   25.00    6.00    0.00   31.00    20  |__mysqld
03:37:54 PM 10014         -     32047   25.00    4.00    0.00   29.00    23  |__mysqld
03:37:54 PM 10014         -     32048   25.00    7.00    0.00   32.00    22  |__mysqld
03:37:54 PM 10014         -     32049   23.00    6.00    0.00   29.00     4  |__mysqld
03:37:54 PM 10014         -     32052   23.00    7.00    0.00   30.00    14  |__mysqld
03:37:54 PM 10014         -     32053   10.00    2.00    0.00   12.00    11  |__mysqld
03:37:54 PM 10014         -     32055   24.00    6.00    0.00   30.00     1  |__mysqld
03:37:54 PM 10014         -      4275   25.00    6.00    0.00   31.00     7  |__mysqld
03:37:54 PM 10014         -      4276   25.00    6.00    0.00   31.00     1  |__mysqld
03:37:54 PM 10014         -      4277   24.00    5.00    0.00   29.00    14  |__mysqld
03:37:54 PM 10014         -      4278   24.00    6.00    0.00   30.00     9  |__mysqld
03:37:54 PM 10014         -      4279   25.00    5.00    0.00   30.00     6  |__mysqld
03:37:54 PM 10014         -      4280   26.00    5.00    0.00   31.00    14  |__mysqld
03:37:54 PM 10014         -      4281   24.00    6.00    0.00   30.00    10  |__mysqld
03:37:54 PM 10014         -      4282   25.00    6.00    0.00   31.00    10  |__mysqld
03:37:54 PM 10014         -     35261    0.00    0.00    0.00    0.00     4  |__mysqld
03:37:54 PM 10014         -     36153    0.00    0.00    0.00    0.00     5  |__mysqld

 

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 the high number of concurrent connections – Check MySQL “PROCESSLIST”, and if you see a lot of queries in “LOCK” status, it means a lot of queries are put on hold because MyISAM tables are handling other transactions. To fix this convert those tables into the 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 a 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)”.

 

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 processes queries. MySQL spin-offs such as MariaDB and Percona resolves these issues and helps achieve better stability.


If you are facing the same issue then get choose any of the packages under  hire an experts.

Was this answer helpful? 0 Users Found This Useful (0 Votes)