MySQL 5.7 simple count query sometimes fast and sometimes very slow
Posted by:
Li Steven
Date: December 23, 2019 02:10AM
【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?
Subject
Views
Written By
Posted
MySQL 5.7 simple count query sometimes fast and sometimes very slow
1229
December 23, 2019 02:10AM
450
January 15, 2020 11:55AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.