Search code examples
mysqlperformancecount

MySQL 5.7 simple count query sometimes fast and sometimes very slow

【1】Here is a simple count query:

SELECT SQL_NO_CACHE count(*) FROM fees WHERE 1 = 1 AND flag = 0 AND bmonth = '201906';

【2】Query result:

+----------+
| count(*) |
+----------+
| 1021863 |
+----------+
1 row in set, 1 warning (34.77 sec)

【3】Sometimes is（most times）:

+----------+
| count(*) |
+----------+
| 1021863 |
+----------+
1 row in set, 1 warning (1.89 sec)

【4】After a few minutes, it becomes very slow like the first result show.

【5】The query will use the below index, and uk_customer contains the column bmonth:

+----+-------------+----------------------+------------+------+--------------------------------------------+----------------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------------+------------+------+--------------------------------------------+----------------+---------+-------+---------+----------+-------------+
| 1 | SIMPLE | fees | NULL | ref | uk_warehouse,uk,uk_calcu | uk_customer | 4 | const | 2028586 | 10.00 | Using where |
+----+-------------+----------------------+------------+------+--------------------------------------------+----------------+---------+-------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

=======================================================================

【6】The memory for server is 5G, and the buffer pool size is 4G. And the index size:

+--------------------------------------------------+---------------------------+------------+--------------+
| Table_Name | Index_Name | Page_Count | Size_in_MB |
+--------------------------------------------------+---------------------------+------------+--------------+
| `test`.`fees` | PRIMARY | 46503 | 567.04021835 |
| `test`.`fees` | uk_customer | 2521 | 34.86496449 |
| `test`.`fees` | uk_calcu | 3178 | 30.72235107 |

【7】The innodb settings as below:

innodb_page_size = 16384
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2048
innodb_lock_wait_timeout = 5
lock_wait_timeout=5
innodb_io_capacity = 1000
innodb_io_capacity_max = 6000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
#innodb_undo_logs = 128
innodb_undo_tablespaces = 2
innodb_flush_neighbors = 0
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16777216
#innodb_max_undo_log_size = 2147483648
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 16
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 256k
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_file_per_table = 1

Current buffer usage:

+---------------------------------------+---------------+
| Variable_name | Value |
+---------------------------------------+---------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 753136482 |
| Innodb_buffer_pool_read_ahead_evicted | 185375 |
| Innodb_buffer_pool_read_requests | 1355375133341 |
| Innodb_buffer_pool_reads | 2418853408 |
+---------------------------------------+---------------+
5 rows in set (0.00 sec)

Pages usage:

+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_data | 246447 |
| Innodb_buffer_pool_pages_dirty | 2738 |
| Innodb_buffer_pool_pages_flushed | 84827986 |
| Innodb_buffer_pool_pages_free | 3982 |
| Innodb_buffer_pool_pages_misc | 11699 |
| Innodb_buffer_pool_pages_total | 262128 |
+----------------------------------+----------+
6 rows in set (0.00 sec)

======================================================================= 【8】But it works well and very fast in slave server, all are almost 2+ seconds.

Please help, how to tuning the performance?

Solution

  • That is the effect of "caching". InnoDB has essentially one main cache, the "buffer_pool", whose size is controlled by innodb_buffer_pool_size. How big is the total dataset?

    Further details:

    1. With nothing cached in the buffer_pool, stuff has to be read from disk, hence taking, say, 35 seconds.
    2. If you run the query again soon, no I/O is needed because the stuff is still in cache. Hence 2 seconds.
    3. If there is a gap of time before you run it again, other data comes into the buffer_pool, bumping out blocks (perhaps all blocks) of your query. Not it is back to 35 seconds.

    Cures:

    • Don't run "big" queries. Counting a million rows involves perhaps 100MB of cache. I can't say what item 3's big queries are.
    • Better indexing. For that one query, INDEX(flag, bmonth) is optimal and requires less data, thereby speeding up the non-cached (35s) version. Also, by being smaller, its data is less likely to be bumped out of cache.
    • Avoid swapping. 4GB buffer_pool in 5GB of RAM is possibly leading to swapping. This is terrible for performance in MySQL. I strongly recommend you drop the buffer_pool_size to 3G.
    • More RAM (and bigger buffer_pool).
    • Locate the other 'big' queries and work on making the use less data.
    • Question the need for an exact count that is on the order of a million. This involves a UI change and perhaps ways of "estimating" the count.
    • Summary tables -- Every night tally up how many happened for the previous day; keep this in a summary table, and run a much faster query against that. (I can discuss this a lot further.)

    The buffer and page usage metrics are 'counters' that need to be divided by Uptime to get "per second". For further analysis: http://mysql.rjweb.org/doc.php/mysql_analysis

    Summary tables: http://mysql.rjweb.org/doc.php/summarytables