title: mySQL using 100% CPU
body: <p>I have a PHP application that is running on a LAMP stack. This application makes an API call back to the server via javascript to get more data to display on the screen every second. When there are a number of users using it at the same time, say 80, mySQL slams the CPU to 100% until the application is finished.</p>
  <p>What am I using:</p>
  <ul>
  <li>mySQL 5.7.31</li>
  <li>Ubuntu 18.04</li>
  </ul>
  <p>Running on an EC2 instance of size m5.xlarge</p>
  <ul>
  <li>4 vCPU</li>
  <li>16G RAM</li>
  <li>network BW up to 10Gbps</li>
  </ul>
  <p>I used advice from percona about tweaking the mySQL parameters, they say most of 5.7's have great defaults expect a couple that are dependent on your hardware, so my mySQL config looks like this</p>
  <p>mysqld.cnf</p>
  <pre><code>[mysqld_safe]
  socket          = /var/run/mysqld/mysqld.sock
  nice            = 0
  default-character-set=utf8
  
  [mysqld]
  #
  # * Basic Settings
  #
  user            = mysql
  pid-file        = /var/run/mysqld/mysqld.pid
  socket          = /var/run/mysqld/mysqld.sock
  port            = 3306
  basedir         = /usr
  datadir         = /var/lib/mysql
  tmpdir          = /tmp
  lc-messages-dir = /usr/share/mysql
  skip-external-locking
  character-set-client-handshake = false #force encoding to uft8
  character-set-server=utf8
  collation-server=utf8_general_ci
  sql_mode = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
  
  bind-address            = 0.0.0.0
  key_buffer_size         = 16M
  max_allowed_packet      = 16M
  thread_stack            = 192K
  thread_cache_size       = 8
  myisam-recover-options  = BACKUP
  query_cache_limit       = 1M
  query_cache_size        = 256M
  
  log_error = /var/log/mysql/error.log
  
  expire_logs_days        = 10
  max_binlog_size   = 100M
  #binlog_do_db           = include_database_name
  #binlog_ignore_db       = include_database_name
  #
  # * InnoDB
  #
  # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
  # Read the manual for more InnoDB related options. There are many!
  #
  
  innodb_buffer_pool_size = 11G # (adjust value here, 50%-70% of total RAM)
  innodb_log_file_size = 256M
  innodb_flush_log_at_trx_commit = 1
  innodb_flush_method = O_DIRECT
  </code></pre>
  <h2>Percona Monitoring and Management</h2>
  <p>I am also running Percona Monitoring and Management which gives me a great view as to what is going on.</p>
  <p>So when I have the 100% CPU this is what I have determined</p>
  <ol>
  <li><p>CPU is 100%, and in user space - this is because my innoDB buffer pool size is so large all the data is in memory so HDD is not being hit therefore no IO
  <a href="https://i.stack.imgur.com/q7irg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/q7irg.png" alt="enter image description here" /></a></p>
  </li>
  <li><p>Max number of connections is not being hit - 100 connections out of 150 is being used for the duration
  <a href="https://i.stack.imgur.com/kJZO5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kJZO5.png" alt="enter image description here" /></a></p>
  </li>
  <li><p>Slow query log shows nothing in there
  <a href="https://i.stack.imgur.com/B0X6H.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/B0X6H.png" alt="enter image description here" /></a></p>
  </li>
  <li><p>Top counters seem to be com_select
  <a href="https://i.stack.imgur.com/q7O9W.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/q7O9W.png" alt="enter image description here" /></a></p>
  </li>
  <li><p>And top handlers read_next and read_rnd_next
  <a href="https://i.stack.imgur.com/RI2w1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RI2w1.png" alt="enter image description here" /></a></p>
  </li>
  <li><p>The query cache shows nothing is being cached
  <a href="https://i.stack.imgur.com/iQvQI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iQvQI.png" alt="enter image description here" /></a></p>
  </li>
  </ol>
  <p>So this points to a query that is causing this issue. PMM has a nice Query Analytics to see the queries causing issues, this is what it shows
  <a href="https://i.stack.imgur.com/8Zntv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8Zntv.png" alt="enter image description here" /></a></p>
  <p>So the top 2 queries are the culprits. Reading a lot online everyone points to indexes as the most common cause of the CPU load, but these tables have indexes. So here are the 2 queries and table definitions and indexes for each and the explain statements show they are using the indexes as well?</p>
  <h2>Query 1</h2>
  <pre><code>SELECT
    `tick`,
    VALUE
  FROM
    `stored_path_data`
  WHERE
    `stored_path_ID` = ?
    AND `tick` &lt;= ?
    AND `tick` &gt;= ?
  ORDER BY
    `tick`
  </code></pre>
  <pre><code>mysql&gt; explain stored_path_data;
  +----------------+-------------------------------+------+-----+---------+----------------+
  | Field          | Type                          | Null | Key | Default | Extra          |
  +----------------+-------------------------------+------+-----+---------+----------------+
  | ID             | int(11)                       | NO   | PRI | NULL    | auto_increment |
  | stored_path_ID | int(11)                       | NO   | MUL | NULL    |                |
  | tick           | int(11)                       | NO   | MUL | NULL    |                |
  | value          | decimal(18,7)                 | NO   |     | NULL    |                |
  | type           | enum('interpolated','manual') | NO   |     | NULL    |                |
  +----------------+-------------------------------+------+-----+---------+----------------+
  5 rows in set (0.00 sec)
  mysql&gt; show indexes from stored_path_data;
  +------------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table            | Non_unique | Key_name | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +------------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | stored_path_data |          0 | PRIMARY  |            1 | ID             | A         |      316875 |     NULL | NULL   |      | BTREE      |         |               |
  | stored_path_data |          0 | compound |            1 | stored_path_ID | A         |         997 |     NULL | NULL   |      | BTREE      |         |               |
  | stored_path_data |          0 | compound |            2 | tick           | A         |      316875 |     NULL | NULL   |      | BTREE      |         |               |
  | stored_path_data |          1 | tick     |            1 | tick           | A         |        1771 |     NULL | NULL   |      | BTREE      |         |               |
  +------------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  mysql&gt; explain SELECT   tick,value FROM stored_path_data WHERE stored_path_ID = 4887   AND `tick` &lt;= 240   AND `tick` &gt;= 0 ORDER BY   `tick`;
  +----+-------------+------------------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
  | id | select_type | table            | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                              |
  +----+-------------+------------------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
  |  1 | SIMPLE      | stored_path_data | NULL       | range | compound,tick | tick | 4       | NULL |    1 |   100.00 | Using index condition; Using where |
  +----+-------------+------------------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
  1 row in set, 1 warning (0.00 sec)
  </code></pre>
  <h2>Query 2</h2>
  <pre><code>SELECT
    `spd`.`stored_path_ID`,
    `spd`.`value`
  FROM
    (
      SELECT
        `stored_path_ID`,
        MAX (`tick`) AS `max_tick`
      FROM
        `stored_path_data`
      WHERE
        `stored_path_ID` IN (...)
        AND `tick` &lt;= ?
      GROUP BY
        `stored_path_ID`
    ) AS `temp`
    INNER JOIN `stored_path_data` AS `spd` ON `temp`.`stored_path_ID` = `spd`.`stored_path_ID`
  WHERE
    `spd`.`tick` = `temp`.`max_tick`
  </code></pre>
  <pre><code>mysql&gt; explain SELECT   `spd`.`stored_path_ID`,   `spd`.`value` FROM   (     SELECT       `stored_path_ID`,       MAX (`tick`) AS `max_tick`     FROM       `stored_path_data`     WHERE       `stored_path_ID` IN (4883,4884,4885,4886,4887)       AND `tick` &lt;= 240     GROUP BY       `stored_path_ID`   ) AS `temp`   INNER JOIN `stored_path_data` AS `spd` ON `temp`.`stored_path_ID` = `spd`.`stored_path_ID` WHERE   `spd`.`tick` = `temp`.`max_tick`;
  +----+-------------+------------------+------------+-------+---------------+-------------+---------+---------------------------------------------------+------+----------+--------------------------+
  | id | select_type | table            | partitions | type  | possible_keys | key         | key_len | ref                                               | rows | filtered | Extra                    |
  +----+-------------+------------------+------------+-------+---------------+-------------+---------+---------------------------------------------------+------+----------+--------------------------+
  |  1 | PRIMARY     | spd              | NULL       | ALL   | compound,tick | NULL        | NULL    | NULL                                              |    1 |   100.00 | NULL                     |
  |  1 | PRIMARY     | &lt;derived2&gt;       | NULL       | ref   | &lt;auto_key0&gt;   | &lt;auto_key0&gt; | 9       | tradingsim.spd.stored_path_ID,tradingsim.spd.tick |    2 |   100.00 | Using index              |
  |  2 | DERIVED     | stored_path_data | NULL       | index | compound,tick | compound    | 8       | NULL                                              |    1 |   100.00 | Using where; Using index |
  +----+-------------+------------------+------------+-------+---------------+-------------+---------+---------------------------------------------------+------+----------+--------------------------+
  3 rows in set, 1 warning (0.00 sec)
  </code></pre>
  <p>Same table so indexes are the same. Above also includes the explains on each query.</p>
  <p>There are 2 things i noticed with these queries.</p>
  <ol>
  <li>Query 1 is using a range, but has compound index on tick and stored_path_ID</li>
  <li>Query 2 is using a temp table - I have tried improving the query without the temp table and it helps a little but CPU is still slammed at 100%</li>
  </ol>
  <h2>mySQLTuner</h2>
  <p>I then ran mysqltuner <a href="https://github.com/major/MySQLTuner-perl" rel="nofollow noreferrer">https://github.com/major/MySQLTuner-perl</a> and this is the advice it gave</p>
  <pre><code>...
  -------- Recommendations ---------------------------------------------------------------------------
  General recommendations:
      Add some space to /snap/amazon-ssm-agent/2012 mountpoint.
      Add some space to /snap/core/10126 mountpoint.
      Add some space to /snap/core/10185 mountpoint.
      Cleanup files from /snap/amazon-ssm-agent/2012 mountpoint or reformat you filesystem.
      Cleanup files from /snap/core/10126 mountpoint or reformat you filesystem.
      Cleanup files from /snap/core/10185 mountpoint or reformat you filesystem.
      setup swappiness lower or equals to 10
      setup Max running number events greater than 1M
      Check all table collations are identical for all tables in tradingsim database.
      Limit charset for column to one charset if possible for tradingsim database.
      Limit collations for column to one collation if possible for tradingsim database.
      ALTER TABLE `tradingsim`.`instances` MODIFY `name` CHAR(0) NOT NULL;
      ALTER TABLE `tradingsim`.`instances` MODIFY `date_display_format` CHAR(0);
      ALTER TABLE `tradingsim`.`instruments` MODIFY `instrument_group_ID` CHAR(0);
      ALTER TABLE `tradingsim`.`news` MODIFY `title` TINYTEXT NOT NULL;
      ALTER TABLE `tradingsim`.`news` MODIFY `body` TEXT NOT NULL;
      ALTER TABLE `tradingsim`.`persons` MODIFY `secondname` VARCHAR(10) NOT NULL;
      ALTER TABLE `tradingsim`.`persons` MODIFY `second_email` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `work_phone` CHAR(0) NOT NULL;
      ALTER TABLE `tradingsim`.`persons` MODIFY `mobile_phone` CHAR(0) NOT NULL;
      ALTER TABLE `tradingsim`.`persons` MODIFY `home_phone` CHAR(0) NOT NULL;
      ALTER TABLE `tradingsim`.`persons` MODIFY `username` VARCHAR(15) NOT NULL;
      ALTER TABLE `tradingsim`.`persons` MODIFY `photo_url` CHAR(0) NOT NULL;
      ALTER TABLE `tradingsim`.`persons` MODIFY `email_type` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `fax_number` CHAR(0) NOT NULL;
      ALTER TABLE `tradingsim`.`persons` MODIFY `mts_priority` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `silent_login_group_ID` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `marketing_feedback` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `person_type` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `left_company` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `immutable_ID` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `media_server_ID` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `jobtitle` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `rdr_training_requirements` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `rdr_qualifications_correct` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `rdr_study_qualifications_correct` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `har` CHAR(0);
      ALTER TABLE `tradingsim`.`persons` MODIFY `personal_email` CHAR(0);
      ALTER TABLE `tradingsim`.`stored_path_data` MODIFY `ID` MEDIUMINT(7) UNSIGNED NOT NULL;
      ALTER TABLE `tradingsim`.`stored_path_data` MODIFY `value` DECIMAL(18, 7) NOT NULL;
      ALTER TABLE `tradingsim`.`trader_responses` MODIFY `instance_ID` CHAR(0);
      Remove unused indexes.
      Restrict Host for 'simulations'@% to simulations@SpecificDNSorIp
      UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='simulations' AND host ='%'; FLUSH PRIVILEGES;
      MySQL was started within the last 24 hours - recommendations may be inaccurate
      Reduce your overall MySQL memory footprint for system stability
      Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
      We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
               See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
               (specially the conclusions at the bottom of the page).
      Increase table_open_cache gradually to avoid file descriptor limits
      Read this before increasing table_open_cache over 64: 
      Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
      This is MyISAM only table_cache scalability problem, InnoDB not affected.
      See more details here: https://bugs.mysql.com/bug.php?id=49177
      This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
      Beware that open_files_limit (5000) variable
      should be greater than table_open_cache (2000)
      Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 
  Variables to adjust:
      vm.swappiness &lt;= 10 (echo 10 &gt; /proc/sys/vm/swappiness)
      fs.aio-max-nr &gt; 1M (echo 1048576 &gt; /proc/sys/fs/aio-max-nr)
      query_cache_size (=0)
      query_cache_type (=0)
      query_cache_limit (&gt; 1M, or use smaller result sets)
      join_buffer_size (&gt; 256.0K, or always use indexes with JOINs)
      table_open_cache (&gt; 2000)
      innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
      innodb_buffer_pool_instances(=11)
  </code></pre>
  <p>I tried those adjustments but still no luck.</p>
  <p>The only last thing I can think of is the following</p>
  <ol>
  <li>Using a cache - memcached or redis</li>
  <li>Moving mySQL off the server and onto something like RDS where I can up hardware but thats expensive</li>
  </ol>
  <p>Can anyone help suggest what I could do in this situation, am totally stumped!! I didnt think that 100 connections a second was that big a deal. Could I be hitting table lock issues? though this is what the stats showed me about that</p>
  <p><a href="https://i.stack.imgur.com/kKtze.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kKtze.png" alt="enter image description here" /></a></p>
  <p>Any help here would be greatly appreciated.</p>
  <h2>EDIT</h2>
  <p>I have found this really interesting article about max connections and scaling with mySQL - <a href="https://mysqlserverteam.com/mysql-connection-handling-and-scaling/" rel="nofollow noreferrer">https://mysqlserverteam.com/mysql-connection-handling-and-scaling/</a></p>
  <p>And if you go down to the bottom of the page to the summary the item I think that is relevent to my situation is</p>
  <blockquote>
  <p>Rule of thumb: Max number of connections = 4 times available CPU cores</p>
  </blockquote>
  <p>So based on my max usage of 100 max connections this means I should aim for a server with 25 CPU cores OR re-architect the platform. I think that is where this may be headed. I am going to load test a server of this size and see how that fairs.</p>
  <h2>Edit 2</h2>
  <pre><code>mysql&gt; SHOW TABLE STATUS WHERE NAME = 'stored_path_data';
  +------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
  | Name             | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
  +------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
  | stored_path_data | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |   4194304 |        5084417 | 2020-10-29 06:11:01 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
  +------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
  1 row in set (0.00 sec)
  </code></pre>
  <h2>Conclusions</h2>
  <p>Just to help people if they come to this looking for answers (and dont want to read through all the comments), @RickJames came out with the solution to this problem. It did end up being indexes, but something called a covering index, which I didnt know existed, so creating the index and then running ANALYZE TABLE fixed my issue.</p>
  <pre><code>CREATE INDEX covering ON stored_path_data(stored_path_ID, tick, value);
  ANALYZE TABLE stored_path_data;
  </code></pre>
  <p>I tried my suggestion above of increasing CPUs and ran 90 concurrent users on 36 CPU EC2 instance and its a complete overkill, where before the index all 36 CPUs were maxed out to 100%. I will be reducing my hardware to something more suitable for the application, but once again thanks @RickJames for your help</p>