Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5783

MySQL Queries per second is decreasing with higher number of table partitions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 5.7.26-29, 8.0.16-7
    • Fix Version/s: 5.7.28-31, 8.0.18-9
    • Component/s: None
    • Environment:

      MySQL 5.7.26

      Ubuntu 18.04

      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 a couple of records.

      Query1:

      SELECT MIN(COL01) FROM tbl1; ---> use this in the below query.

      sample 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 ;

      sample 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.
       
      NOTE: Having the smaller buffer will make the result much deviating from each other as they involve fetching them from disk.
       
      Suggested fix:
      This behavior of estimating records in each partition should be cut off the execution flow.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            uday.varagani Uday Varagani (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 4 days, 5 hours, 30 minutes
                4d 5h 30m

                  Smart Checklist