| Bug #96233 | MySQL Questions per second is decreasing with higher number of table partitions | ||
|---|---|---|---|
| Submitted: | 17 Jul 2019 13:28 | Modified: | 19 Jul 2019 12:47 |
| Reporter: | Uday Sitaram | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S4 (Feature request) |
| Version: | 5.7.26 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | High Disk IO, MySQL Performance, partitions, slow | ||
[17 Jul 2019 13:54]
MySQL Verification Team
Hi Mr. Sitaram, Thank you for your bug report. First, this is not a bug , but a feature request. This is because you are asking for the improvement in our Optimiser plan choice. Before continuing, could you please send us the output from EXPLAIN EXTENDED for both queries. While, doing that, please try omitting " AND RAND() < 0.01 " from your query.
[17 Jul 2019 23:10]
Dongchan Sung
Hi Sinisa, I have question. :-) Is this not the case when mysql have already selected index that executed the query? If so, I think that there is no need to turn around in the execution plan situation though it is data to access anyway. Best Regards, Chan.
[18 Jul 2019 12:59]
MySQL Verification Team
HI Sung, This is not so much about index choice, but about a need to count rows in each partition. Also, we shall know more after we get that EXPLAIN ........
[19 Jul 2019 6:26]
Uday Sitaram
Sinisa, Here are respective explain plans. tbl1 has 12 partitions while tbl2 has just 3 for the same dataset. ============================= mysql> explain extended SELECT * FROM tbl1 WHERE COL01 > 'value from Query1' AND RAND() < 0.01 ORDER BY PAR_DATE DESC LIMIT 10; +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | tbl1 | PF_201801,PF_201802,PF_201803,PF_201804,PF_201805,PF_201806,PF_201807,PF_201808,PF_201809,PF_201810,PF_201811,PF_201812,PF_201813,PF_201814,PF_201815,PF_201816 | range | PRIMARY | PRIMARY | 66 | NULL | 1 | 100.00 | Using where; Using filesort | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain extended SELECT * FROM tbl2 WHERE COL01 > 'value from Query1' AND RAND() < 0.01 ORDER BY PAR_DATE DESC LIMIT 10; +----+-------------+--------+-------------------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+-------------------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | tbl2 | PF_201801,PF_201802,PF_201803 | range | PRIMARY | PRIMARY | 66 | NULL | 1 | 100.00 | Using where; Using filesort | +----+-------------+--------+-------------------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ 1 row in set, 2 warnings (0.00 sec) =============================
[19 Jul 2019 12:26]
MySQL Verification Team
Hi Mr. Sitaram, Everything is much more clear now. However, we would like to see those two warnings !!!
[19 Jul 2019 12:27]
MySQL Verification Team
Hi, One more thing. Can you please ANALYZE both tables before running the queries .....
[19 Jul 2019 12:43]
Uday Sitaram
Sinisa, I have re run those queries preceding an ANALYZE table operation. Those warnings are general and expected. I am attaching the details in the attachment. Please have a look at it.
[19 Jul 2019 12:44]
Uday Sitaram
warnings information along with explain after analyze
Attachment: 96233_details_July19th.txt (text/plain), 9.07 KiB.
[19 Jul 2019 12:47]
MySQL Verification Team
Hello Mr. Sitaram, Thank you for your feedback. We are verifying this report as a very useful feature request !!!!! Thanks ....
Description: When stress testing on a partitioned table, it is observed that the overall performance is impacted by the number of table partitions. I used the same dataset loaded into two identical tables which are differing in partition range definition. > Table1 has 12 partitions & > Table2 has 3 partitions. Below are the benchmarking results when tested random read IO using the tool mysqlslap. # Case1: Table with 12 partitions Queries per second: 2200 QPS Total time to run: 80 seconds Total no.of queries run: ~176K # Case1: Table with 3 partitions Queries per second: 3100 QPS Total time to run: 80 seconds Total no.of queries run: ~258K When we looked into the thread states using pt-pmp tool, it is observed that MySQL is estimating records in each partition which is resulting more pages to be read and more disk IO if the buffer pool had to fetch them from the disk. This is increasing with the number of partitions. ========================== 1 ha_innopart::records_in_range,int,,int,,::??,test_quick_select,JOIN::estimate_rowcount,JOIN::make_join_plan,JOIN::optimize,st_select_lex::optimize,handle_query,::??,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone 1 ::??,ha_innopart::records_in_range,int,,int,,::??,test_quick_select,JOIN::estimate_rowcount,JOIN::make_join_plan,JOIN::optimize,st_select_lex::optimize,handle_query,::??,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone 1 ::??,buf_page_get_gen,btr_cur_search_to_nth_level,::??,ha_innopart::records_in_range,int,,int,,::??,test_quick_select,JOIN::estimate_rowcount,JOIN::make_join_plan,JOIN::optimize,st_select_lex::optimize,handle_query,::??,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone 1 btr_cur_latch_leaves,btr_cur_open_at_index_side_func,::??,ha_innopart::records_in_range,int,,int,,::??,test_quick_select,JOIN::estimate_rowcount,JOIN::make_join_plan,JOIN::optimize,st_select_lex::optimize,handle_query,::??,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone ========================== What's wrong: As per the explain plan, the query is fetching its results by scanning the PRIMARY KEY and don't need to scan pages from each partition for estimating the number of records. How to repeat: 1. Create tbl1 and tbl2 using below schema CREATE TABLE `<tbl1/tbl2>` ( `COL01` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `PAR_DATE` datetime NOT NULL, `COL02` decimal(10,0) NOT NULL, `COL03` decimal(10,0) NOT NULL, `TMST` timestamp NOT NULL, `COL42` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `APLY_TMST` timestamp(3) NOT NULL, `APLY_TMST2` timestamp(3) NOT NULL, PRIMARY KEY (`COL01`,`PAR_DATE`,`COL02`,`COL03`), KEY `ix01` (`PAR_DATE`), KEY `ix02` (`COL42`), KEY `ix03` (`APLY_TMST2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; 2. Run DDL on tbl1 and tbl2 so that tbl1 will have monthly partitions(12 partitions) and tbl2 will have a partition for 3 months(4 partitions total) on PAR_DATE field. 3. Load some test data(5M records) using mysql_random_data_loader into these tables. 4. Run a stress test with the mysqlslap utility using below query which randomly reads 10 records. Query1: SELECT MIN(COL01) FROM tbl1; ---> use this in the below query. Query to use in mysqlslap: SELECT * FROM tab2m1 WHERE COL01 > 'Aaron Adams' AND RAND() < 0.01 ORDER BY PAR_DATE DESC LIMIT 10;" --create-schema=DB260326 --concurrency=10 --iterations=10 --number-of-queries=4000 ; mysqlslap command for tbl1: # mysqlslap --user=root --password=xxxxxxx --host=127.0.0.1 --port=3307 --query="SELECT * FROM tbl1 WHERE COL01 > 'value from Query1' AND RAND() < 0.01 ORDER BY PAR_DATE DESC LIMIT 10;" --create-schema=testdb --concurrency=10 --iterations=10 --number-of-queries=10000 ; mysqlslap command for tbl2: # mysqlslap --user=root --password=xxxxxxx --host=127.0.0.1 --port=3307 --query="SELECT * FROM tbl1 WHERE COL01 > 'value from Query1' AND RAND() < 0.01 ORDER BY PAR_DATE DESC LIMIT 10;" --create-schema=testdb --concurrency=10 --iterations=10 --number-of-queries=10000 ; Monitor disk IO and total execution time on the server. In my case, I se Suggested fix: This behavior of estimating records in each partition should be cut off the executin flow.