Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16413

test performance of distinct range queries

Details

    Description

      Check the claims in this blog

      Attachments

        Issue Links

          Activity

            axel Axel Schwenke added a comment -

            utf8_unicode_ci

            # data set 03 -> mariadb-10.3.9
            # data set 06 -> mysql-8.0.11
            # data set 07 -> mariadb-10.4 baseline
            # data set 08 -> mariadb-10.4 with inline collate
             
            # read-only (short ranges)
            #thd    03      06      07      08
            1       6212.1  6744.6  5911.8  7197.8
            2       11072   13233   10696   13491
            4       21491   25630   21763   25607
            8       38310   45858   39494   45109
            16      72045   91982   69576   84109
            32      94114   129141  91978   109944
            64      89896   129005  89959   104324
            128     90026   129264  89056   108373
            256     92578   128756  91488   107215
            

            most busy function in mysqld:

            12.56%  [.] my_uca_strnxfrm_onelevel_no_contractions_utf8mb3
            

            call graph:

            -   12.03%    11.86%  [.] my_uca_strnxfrm_onelevel_no_contractions_utf8mb3
               - my_uca_strnxfrm_onelevel_no_contractions_utf8mb3
                  - 99.81% my_uca_strnxfrm_no_contractions_utf8mb3
                       _ZN12Field_string11sort_stringEPhj
                       _ZL12make_sortkeyP10Sort_paramPhS1_
                       _Z8filesortP3THDP5TABLEP8FilesortP16Filesort_trackerP4JOINy
                       _Z17create_sort_indexP3THDP4JOINP13st_join_tableP8Filesort
                       _ZN13st_join_table10sort_tableEv
                       _Z21join_init_read_recordP13st_join_table
                       _ZN7AGGR_OP8end_sendEv
                       _Z24sub_select_postjoin_aggrP4JOINP13st_join_tableb
                       _ZN4JOIN10exec_innerEv
                       _ZN4JOIN4execEv
            

            axel Axel Schwenke added a comment - utf8_unicode_ci # data set 03 -> mariadb-10.3.9 # data set 06 -> mysql-8.0.11 # data set 07 -> mariadb-10.4 baseline # data set 08 -> mariadb-10.4 with inline collate   # read-only (short ranges) #thd 03 06 07 08 1 6212.1 6744.6 5911.8 7197.8 2 11072 13233 10696 13491 4 21491 25630 21763 25607 8 38310 45858 39494 45109 16 72045 91982 69576 84109 32 94114 129141 91978 109944 64 89896 129005 89959 104324 128 90026 129264 89056 108373 256 92578 128756 91488 107215 most busy function in mysqld: 12.56% [.] my_uca_strnxfrm_onelevel_no_contractions_utf8mb3 call graph: - 12.03% 11.86% [.] my_uca_strnxfrm_onelevel_no_contractions_utf8mb3 - my_uca_strnxfrm_onelevel_no_contractions_utf8mb3 - 99.81% my_uca_strnxfrm_no_contractions_utf8mb3 _ZN12Field_string11sort_stringEPhj _ZL12make_sortkeyP10Sort_paramPhS1_ _Z8filesortP3THDP5TABLEP8FilesortP16Filesort_trackerP4JOINy _Z17create_sort_indexP3THDP4JOINP13st_join_tableP8Filesort _ZN13st_join_table10sort_tableEv _Z21join_init_read_recordP13st_join_table _ZN7AGGR_OP8end_sendEv _Z24sub_select_postjoin_aggrP4JOINP13st_join_tableb _ZN4JOIN10exec_innerEv _ZN4JOIN4execEv
            axel Axel Schwenke added a comment -

            utf8mb4_unicode_ci

            # data set 03 -> mariadb-10.3.9
            # data set 06 -> mysql-8.0.11
            # data set 07 -> mariadb-10.4 baseline
            # data set 08 -> mariadb-10.4 with inline collate
             
            # read-only (short ranges)
            #thd    03      06      07      08
            1       5622.1  7309.9  5608.0  6822.8
            2       10188   14859   10637   12787
            4       20166   27410   19464   24159
            8       34266   50028   34302   41361
            16      64133   97444   62909   75192
            32      89883   136156  85741   103783
            64      83062   137810  83564   100442
            128     82443   138099  82641   101763
            256     85764   137945  85181   104029
            

            most busy function in mysqld:

            16.91%  [.] my_uca_strnxfrm_onelevel_no_contractions_utf8mb4
            

            call graph:

            -   17.62%    17.11%  [.] my_uca_strnxfrm_onelevel_no_contractions_utf8mb4
               - my_uca_strnxfrm_onelevel_no_contractions_utf8mb4
                  - 99.60% my_uca_strnxfrm_no_contractions_utf8mb4
                       _ZN12Field_string11sort_stringEPhj
                       _ZL12make_sortkeyP10Sort_paramPhS1_
                       _Z8filesortP3THDP5TABLEP8FilesortP16Filesort_trackerP4JOINy
                       _Z17create_sort_indexP3THDP4JOINP13st_join_tableP8Filesort
                       _ZN13st_join_table10sort_tableEv
                       _Z21join_init_read_recordP13st_join_table
                       _ZN7AGGR_OP8end_sendEv
                       _Z24sub_select_postjoin_aggrP4JOINP13st_join_tableb
                       _ZN4JOIN10exec_innerEv
                       _ZN4JOIN4execEv
            ...
            

            axel Axel Schwenke added a comment - utf8mb4_unicode_ci # data set 03 -> mariadb-10.3.9 # data set 06 -> mysql-8.0.11 # data set 07 -> mariadb-10.4 baseline # data set 08 -> mariadb-10.4 with inline collate   # read-only (short ranges) #thd 03 06 07 08 1 5622.1 7309.9 5608.0 6822.8 2 10188 14859 10637 12787 4 20166 27410 19464 24159 8 34266 50028 34302 41361 16 64133 97444 62909 75192 32 89883 136156 85741 103783 64 83062 137810 83564 100442 128 82443 138099 82641 101763 256 85764 137945 85181 104029 most busy function in mysqld: 16.91% [.] my_uca_strnxfrm_onelevel_no_contractions_utf8mb4 call graph: - 17.62% 17.11% [.] my_uca_strnxfrm_onelevel_no_contractions_utf8mb4 - my_uca_strnxfrm_onelevel_no_contractions_utf8mb4 - 99.60% my_uca_strnxfrm_no_contractions_utf8mb4 _ZN12Field_string11sort_stringEPhj _ZL12make_sortkeyP10Sort_paramPhS1_ _Z8filesortP3THDP5TABLEP8FilesortP16Filesort_trackerP4JOINy _Z17create_sort_indexP3THDP4JOINP13st_join_tableP8Filesort _ZN13st_join_table10sort_tableEv _Z21join_init_read_recordP13st_join_table _ZN7AGGR_OP8end_sendEv _Z24sub_select_postjoin_aggrP4JOINP13st_join_tableb _ZN4JOIN10exec_innerEv _ZN4JOIN4execEv ...
            axel Axel Schwenke added a comment -

            utf8mb4_unicode_ci, removed the ORDER BY clause from the query to eliminate the filesort.

            # data set 03 -> mariadb-10.3.9
            # data set 06 -> mysql-8.0.11
            # data set 07 -> mariadb-10.4 baseline
            # data set 08 -> mariadb-10.4 with inline collate
            # data set 09 -> mariadb-10.4 bar
             
            # read-only (short ranges)
            #thd    03      06      07      08      09
            1       8250.3  8152.8  8003.8  8403.1  8469.8
            2       16582   16159   15965   15926   16721
            4       30780   31393   28467   30442   31813
            8       52279   56888   51344   52947   54583
            16      96950   109789  95596   97491   95948
            32      138664  160343  143985  149344  149047
            64      128624  160171  127250  133061  132593
            128     127300  159873  122495  129590  129175
            256     127458  159356  126651  132277  131171
            

            axel Axel Schwenke added a comment - utf8mb4_unicode_ci, removed the ORDER BY clause from the query to eliminate the filesort. # data set 03 -> mariadb-10.3.9 # data set 06 -> mysql-8.0.11 # data set 07 -> mariadb-10.4 baseline # data set 08 -> mariadb-10.4 with inline collate # data set 09 -> mariadb-10.4 bar   # read-only (short ranges) #thd 03 06 07 08 09 1 8250.3 8152.8 8003.8 8403.1 8469.8 2 16582 16159 15965 15926 16721 4 30780 31393 28467 30442 31813 8 52279 56888 51344 52947 54583 16 96950 109789 95596 97491 95948 32 138664 160343 143985 149344 149047 64 128624 160171 127250 133061 132593 128 127300 159873 122495 129590 129175 256 127458 159356 126651 132277 131171
            bar Alexander Barkov added a comment - - edited

            Repeatable with this script:

            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (pk SERIAL, field CHAR(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
            INSERT INTO t1 (field) VALUES ('a'),('b'),('c'),('d');
            INSERT t1 (field)
            WITH  RECURSIVE int_seq AS (
              SELECT 1 AS val
              UNION ALL
              SELECT val + 1
              FROM int_seq
              WHERE val < 1000
            ) SELECT 'a' FROM int_seq;
             
            DROP PROCEDURE IF EXISTS p1;
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE a INT DEFAULT 100000;
              WHILE (a > 0)
              DO
                SELECT DISTINCT field INTO @a FROM t1 WHERE pk BETWEEN 1 AND 11 ORDER BY field LIMIT 1;
                SET a=a-1;
              END WHILE;
            END;
            $$
            DELIMITER ;
            CALL p1;
            

            • (11.62 sec) – MariaDB-10.4 before MDEV-17474
            • (9.25 sec) – MariaDB-10.4 after MDEV-17474
            • (8.30 sec) – MySQL-8.0

            Note, if I remove the ORDER BY, the statistics looks as follows:

            • (5.62 sec) – MariaDB-10.4 before MDEV-17474
            • (5.54 sec) – MariaDB-10.4 after MDEV-17474
            • (6.40 sec) – MySQL-8.0
            bar Alexander Barkov added a comment - - edited Repeatable with this script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (pk SERIAL, field CHAR (120) CHARACTER SET utf8 COLLATE utf8_unicode_ci); INSERT INTO t1 (field) VALUES ( 'a' ),( 'b' ),( 'c' ),( 'd' ); INSERT t1 (field) WITH RECURSIVE int_seq AS ( SELECT 1 AS val UNION ALL SELECT val + 1 FROM int_seq WHERE val < 1000 ) SELECT 'a' FROM int_seq;   DROP PROCEDURE IF EXISTS p1; DELIMITER $$ CREATE PROCEDURE p1() BEGIN DECLARE a INT DEFAULT 100000; WHILE (a > 0) DO SELECT DISTINCT field INTO @a FROM t1 WHERE pk BETWEEN 1 AND 11 ORDER BY field LIMIT 1; SET a=a-1; END WHILE; END ; $$ DELIMITER ; CALL p1; (11.62 sec) – MariaDB-10.4 before MDEV-17474 (9.25 sec) – MariaDB-10.4 after MDEV-17474 (8.30 sec) – MySQL-8.0 Note, if I remove the ORDER BY, the statistics looks as follows: (5.62 sec) – MariaDB-10.4 before MDEV-17474 (5.54 sec) – MariaDB-10.4 after MDEV-17474 (6.40 sec) – MySQL-8.0
            bar Alexander Barkov added a comment - - edited

            With VARCHAR(120), instead of CHAR(120), MariaDB is faster on ORDER-BY queries (even before MDEV-17474 changes)

            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (pk SERIAL, field VARCHAR(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
            INSERT INTO t1 (field) VALUES ('a'),('b'),('c'),('d');
            INSERT t1 (field)
            WITH  RECURSIVE int_seq AS (
              SELECT 1 AS val
              UNION ALL
              SELECT val + 1
              FROM int_seq
              WHERE val < 1000
            ) SELECT 'a' FROM int_seq;
             
            DROP PROCEDURE IF EXISTS p1;
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE a INT DEFAULT 100000;
              WHILE (a > 0)
              DO
                SELECT DISTINCT field INTO @a FROM t1 WHERE pk BETWEEN 1 AND 11 ORDER BY field LIMIT 1;
                SET a=a-1;
              END WHILE;
            END;
            $$
            DELIMITER ;
            CALL p1;
            

            • (6.82 sec) – MariaDB-10.4 before MDEV-17474
            • (6.65 sec) – MariaDB-10.4 after MDEV-17474
            • (8.22 sec) – MySQL-8.0
            bar Alexander Barkov added a comment - - edited With VARCHAR(120), instead of CHAR(120), MariaDB is faster on ORDER-BY queries (even before MDEV-17474 changes) DROP TABLE IF EXISTS t1; CREATE TABLE t1 (pk SERIAL, field VARCHAR (120) CHARACTER SET utf8 COLLATE utf8_unicode_ci); INSERT INTO t1 (field) VALUES ( 'a' ),( 'b' ),( 'c' ),( 'd' ); INSERT t1 (field) WITH RECURSIVE int_seq AS ( SELECT 1 AS val UNION ALL SELECT val + 1 FROM int_seq WHERE val < 1000 ) SELECT 'a' FROM int_seq;   DROP PROCEDURE IF EXISTS p1; DELIMITER $$ CREATE PROCEDURE p1() BEGIN DECLARE a INT DEFAULT 100000; WHILE (a > 0) DO SELECT DISTINCT field INTO @a FROM t1 WHERE pk BETWEEN 1 AND 11 ORDER BY field LIMIT 1; SET a=a-1; END WHILE; END ; $$ DELIMITER ; CALL p1; (6.82 sec) – MariaDB-10.4 before MDEV-17474 (6.65 sec) – MariaDB-10.4 after MDEV-17474 (8.22 sec) – MySQL-8.0

            People

              axel Axel Schwenke
              axel Axel Schwenke
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.