CODEWITHSUNDEEP
mysqlperformancecount
stefli
stefli

Reputation: 33

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?

Upvotes: 0

Views: 607

Answers (4)

stefli
stefli

Reputation: 33

@Wilson Hauck The create table result as below: Create Table: CREATE TABLE fees ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'DELETED', uid bigint(20) NOT NULL DEFAULT '0' COMMENT 'DELETED', bmonth int(11) NOT NULL DEFAULT '201501' COMMENT 'DELETED', bno varchar(32) DEFAULT '' COMMENT 'DELETED', fees_no varchar(32) NOT NULL DEFAULT '' COMMENT 'DELETED', cur_time datetime DEFAULT NULL COMMENT 'DELETED', cusid varchar(32) DEFAULT '' COMMENT 'DELETED', cusname varchar(32) DEFAULT '' COMMENT 'DELETED', wcode varchar(32) DEFAULT '' COMMENT 'DELETED', wname varchar(32) DEFAULT '' COMMENT 'DELETED', product_id varchar(32) DEFAULT '' COMMENT 'DELETED', product_name varchar(300) DEFAULT '' COMMENT 'DELETED', splace_code varchar(32) DEFAULT '' COMMENT 'DELETED', splace varchar(32) DEFAULT '' COMMENT 'DELETED', batch_code varchar(32) DEFAULT '' COMMENT 'DELETED', product_date timestamp NULL DEFAULT NULL COMMENT 'DELETED', expiry_date date DEFAULT NULL COMMENT 'DELETED', in_time timestamp NULL DEFAULT NULL COMMENT 'DELETED', temperature_type varchar(4) DEFAULT '' COMMENT 'DELETED', temperature_name varchar(4) DEFAULT '' COMMENT 'DELETED', total_weight decimal(16,6) DEFAULT '0.000000' COMMENT 'DELETED', total_volume decimal(16,6) DEFAULT '0.000000' COMMENT 'DELETED', total_qty decimal(16,6) DEFAULT '0.000000' COMMENT 'DELETED', subject_code varchar(32) DEFAULT '' COMMENT 'DELETED', subject_name varchar(32) DEFAULT '' COMMENT 'DELETED', charge_unit varchar(32) DEFAULT '' COMMENT 'DELETED', charge_unit_type varchar(32) DEFAULT '' COMMENT 'DELETED', charge_num decimal(16,6) DEFAULT '0.000000' COMMENT 'DELETED', amount decimal(16,6) DEFAULT '0.000000' COMMENT 'DELETED', derate_amount decimal(16,6) DEFAULT '0.000000' COMMENT 'DELETED', discount_amount decimal(16,6) DEFAULT '0.000000' COMMENT 'DELETED', rule_no varchar(32) DEFAULT '' COMMENT 'DELETED', is_calculated varchar(4) DEFAULT '' COMMENT 'DELETED', calcu_msg varchar(128) DEFAULT '' COMMENT 'DELETED', calculate_time timestamp NULL DEFAULT NULL COMMENT 'DELETED', unit_price decimal(16,6) DEFAULT '0.000000' COMMENT 'DELETED', first_num decimal(16,6) DEFAULT NULL COMMENT 'DELETED', first_price decimal(16,6) DEFAULT NULL COMMENT 'DELETED', continue_num decimal(16,6) DEFAULT NULL COMMENT 'DELETED', continue_price decimal(16,6) DEFAULT NULL COMMENT 'DELETED', contract_attr varchar(4) DEFAULT '' COMMENT 'DELETED', creator varchar(32) DEFAULT '' COMMENT 'DELETED', create_time timestamp NULL DEFAULT NULL COMMENT 'DELETED', write_time timestamp NULL DEFAULT NULL COMMENT 'DELETED', last_modifier_id varchar(32) DEFAULT '' COMMENT 'DELETED', last_modifier varchar(32) DEFAULT '' COMMENT 'DELETED', last_modify_time timestamp NULL DEFAULT NULL COMMENT 'DELETED', del_flag tinyint(4) DEFAULT '0' COMMENT 'DELETED', remark varchar(128) DEFAULT '' COMMENT 'DELETED', version int(11) NOT NULL DEFAULT '1' COMMENT 'DELETED', bstatus smallint(6) NOT NULL DEFAULT '0' COMMENT 'DELETED', PRIMARY KEY (id), UNIQUE KEY uq_uid (uid), KEY idx_cur_time (cur_time), KEY idx_fees_no (fees_no), KEY uk_warehouse (bmonth,wcode), KEY uk_customer (bmonth,cusid), KEY uk_calcu (bmonth,is_calculated) ) ENGINE=InnoDB AUTO_INCREMENT=13910020 DEFAULT CHARSET=utf8 COMMENT='DELETED'

Upvotes: 0

stefli
stefli

Reputation: 33

Business requirements are: Find all matched data and display in the UI by pagination. Among them, the total number of data sets needs to be displayed in the UI too. Is ther any good solution to match the requirement?

@Strawberry Currently, there is no index covering the flag and bmonth, but system used another index, that might not the most right one.I'll create one later. And you are right, the type of bmonth is int(11), so it's a mistake,i'll remove the single quote.

@WilsonHauck The read buffer variables as below: +----------------------+--------+ | Variable_name | Value | +----------------------+--------+ | read_buffer_size | 131072 | | read_rnd_buffer_size | 131072 | +----------------------+--------+ cat /proc/cpuinfo | grep "physical id" | sort | uniq | wc -l 1 cat /proc/cpuinfo | grep "processor" | sort | uniq | wc -l 4

Upvotes: 0

stefli
stefli

Reputation: 33

The memory of server: free -m total used free shared buffers cached Mem: 8001 7864 136 0 145 2188 -/+ buffers/cache: 5530 2470 Swap: 0 0 0 The memory used of mysqld: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
10920 mysql 20 0 6999m 5.1g 7292 S 48.9 65.6 53260:31 mysqld

Upvotes: 0

Rick James
Rick James

Reputation: 142433

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

Upvotes: 1

Related Questions