MySQL query suddenly slow down

Question

(MySQL Version 5.5.58)

I have two tables articles and comments. articles has 1K rows, while comments has 100K rows:

CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` text COLLATE utf8_unicode_ci,
  `type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` mediumtext COLLATE utf8_unicode_ci,
  `result` varchar(255) COLLATE utf8_unicode_ci,
  `article_id` int(11) DEFAULT '0',
  `user_id` int(11) DEFAULT '0',
  `spec_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_comments_on_result` (`result`),
  KEY `index_comments_on_user_id` (`user_id`),
  KEY `index_comments_on_article_id` (`article_id`),
  KEY `index_comments_on_spec_id` (`spec_id`),
)

I have a "select" query from those tables:

SELECT articles.*,
       count(DISTINCT CASE
                          WHEN s.result = 'OK' THEN s.user_id
                      END) user_ok,
       count(DISTINCT s.user_id) user_cnt,
       count(CASE
                 WHEN s.result = 'OK' THEN 1
             END) com_ok,
       count(s.id) com_cnt,
       bit_or(s.result = 'OK'
              AND s.user_id = 1) cur_user_ok,
       bit_or(s.user_id = 1) cur_user_all
FROM articles
LEFT JOIN comments s ON s.article_id = articles.id AND s.spec_id IS NULL
GROUP BY articles.id
ORDER BY articles.id ASC

This query will finish in ~100ms normally.

However, after 2~3 days of uptime, the query time will suddenly increase to about 200 seconds. This will usually last 4~6 hours before it gets back to its normal state (~100ms). If I restart MySQL in the period, there is a chance (about 50%) to solve the slowdown (temporary).

I have a backup routine run 3 times a day, which mysqldump all databases. I noticed that the moment the query time increases is almost always after the backup routine finished. But if I disable the backup routine, it still occurs.

The memory usage (RSS reported by ps) always stayed at about 500MB, and didn't seems to increase over time or differ in the slowdown period. Yet Handler_read_next increases a lot in the slowdown period: normally it is about 80K, but during the period it is 100M. SHOW PROFILES indicates that most of the time are spent in "Copying to tmp table".

Here's my my.cnf settings:

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
key_buffer_size = 256M
table_open_cache = 2000
sort_buffer_size = 4M
read_buffer_size = 1M
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
tmp_table_size = 128M
max_heap_table_size = 512M
query_cache_size = 0
query_cache_type = 0

What are the possible reasons?

Answer 1

The InnoDB buffer pool is essentially a huge cache. (A variant of LRU - 'Least Recently Used'). If your working set data fits into that cache, then SELECT queries will usually be relatively fast. On the other hand, if the working set data doesn't fit into that cache, then MySQL will have to retrieve some of the data from disk (or whichever storage medium is used), and this is significantly slower.

Running mysqldump can bring huge amounts of otherwise unused data into the buffer pool, and at the same time the (potentially useful) data that is already there will be evicted and flushed to disk.

There are ways to avoid or minimise this problem:

1. You can manipulate system variables like innodb_old_blocks_time (increase this - 1000 = 1 second) and innodb_old_blocks_pct (default is 37 - allowed range is from 5 to 95, set a smaller value to evict data from mysqldump and similar faster). Both these variables are dynamic, so they can be given special values just before you run mysqldump, and then restored to the original values once it has completed. For details, see Making the Buffer Pool Scan Resistant.
2. With MySQL 5.6+ (or MariaDB 10.0+) it's also possible to run a special command to dump the buffer pool contents to disk, and to load the contents back from disk into the buffer pool again later. (See MySQL Dumping and Reloading the InnoDB Buffer Pool | mysqlserverteam.com.) This way you can still use mysqldump or other tools that "pollute" the buffer pool and then restore it afterwards.
3. A way to prevent that running backup is unintentionally evicting your working set data at all would be to replace your mysqldump backup method with Percona Xtrabackup or another physical backup tool that doesn't access the InnoDB buffer pool as such. Physical backup methods are also faster, and can be less disruptive than mysqldump. The disadvantage is that you'll need the exact same MySQL version and configuration on the system where the backup is restored.

Answer 2

Suggestions for your my.cnf or my.ini [mysqld] section from data available at this time.

key_buffer=16M  to be removed, an old deprecated variable.
key_buffer_size=4M  # from 256M  less than 1M used in uptime of 2712 seconds
innodb_buffer_pool_size=1G  # from 2G - for ~250M of data at this time
tmp_table_size=40M  # from 128M for 1% of RAM
max_heap_table_size=40M  # from 512M - should ALWAYS = tmp_table_size
read_buffer_size=128K  # from 1M for default and your handler_read_next count will stabilize
max_connections=32  # from 151 since max_used_connections was 6
thread_cache_size=48  # from 8 - threads are slammed at 8 when mysqldump running

1. Please keep your innodb_buffer_pool_instances at 2 to avoid mutex contention
2. Copy your existing my.cnf-ini in case you need to get back to it.
3. I would make all these changes, stop services/shutdown/restart will all these changes. Please stay in touch.
4. After you have 3 weekdays of uptime, please start New Question with current
5. my.cnf-ini, SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES for new analysis.
6. Would like to see htop, ulimit -a and iostat -x when time permits.

Answer 3

Let's work on improving the query. Notice how the current formulation hauls around multiple copies of articles.* while doing the grouping? Let's avoid that.

SELECT a.*, s.*
    FROM ( SELECT article_id,
                  counts, bitors, etc
              FROM comments
              WHERE spec_id IS NULL
              GROUP BY article_id
         ) AS s
    JOIN articles AS a  ON a.id = s.article_id
    ORDER BY a.id

Caveat: Because of LEFT and spec_id IS NULL, I am not sure that this reformulation provides the identical results.