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

mysql_tzinfo_to_sql under innodb is slow

Details

    Description

      When MDEV-18778 added ENGINE=InnoDB to allow timezone to be replicated via galera the obvious extension of wrapping the inserts in a transaction was missed.

      As a result an innodb initialization in a stand alone mode takes ~27 seconds instead of 1.2 seconds (timing per MDEV-23326)

      Attachments

        Issue Links

          Activity

            danblack Daniel Black added a comment -

            bb-10.1-MDEV-23440-danielblack-mysql_tzinfo_to_sql-with-transactions pushed

            Simple patch: https://github.com/MariaDB/server/commit/23bdab05a7cf5aff5d2c6451227315bde6ef53a5

            danblack Daniel Black added a comment - bb-10.1- MDEV-23440 -danielblack-mysql_tzinfo_to_sql-with-transactions pushed Simple patch: https://github.com/MariaDB/server/commit/23bdab05a7cf5aff5d2c6451227315bde6ef53a5
            danblack Daniel Black added a comment -

            bb-10.1-MDEV-23440-danielblack-mysql_tzinfo_to_sql-with-transactions with test cases fixed now http://buildbot.askmonty.org/buildbot/grid?category=main&category=experimental&category=galera&category=connectors&branch=bb-10.1-MDEV-23440-danielblack-mysql_tzinfo_to_sql-with-transactions

            10.1-85bd5314c56 before changes

            [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ cat ../innodb_transactional.sql 
             
             
            create database if not exists innodb;
            use innodb;
             
            create table time_zone                 like mysql.time_zone                ;
            create table time_zone_leap_second     like mysql.time_zone_leap_second    ;
            create table time_zone_name            like mysql.time_zone_name           ;
            create table time_zone_transition      like mysql.time_zone_transition     ;
            create table time_zone_transition_type like mysql.time_zone_transition_type;
             
            alter table time_zone                 engine=innodb;
            alter table time_zone_leap_second     engine=innodb;
            alter table time_zone_name            engine=innodb;
            alter table time_zone_transition      engine=innodb;
            alter table time_zone_transition_type engine=innodb;
             
            [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ client/mysql -u root -S /tmp/s.sock  < ../innodb_transactional.sql
            [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ time sql/mysql_tzinfo_to_sql  /usr/share/zoneinfo/ | client/mysql -u root -S /tmp/s.sock  innodb
            Warning: Unable to load '/usr/share/zoneinfo//leapseconds' as time zone. Skipping it.
            Warning: Unable to load '/usr/share/zoneinfo//tzdata.zi' as time zone. Skipping it.
             
            real	0m26.552s
            user	0m0.890s
            sys	0m0.727s
            

            bb-10.1-MDEV-23440-danielblack-mysql_tzinfo_to_sql-with-transactions

             
            [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ client/mysql -u root -S /tmp/s.sock  -e "drop database innodb; shutdown"
            [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ client/mysql -u root -S /tmp/s.sock  < ../innodb_transactional.sql
            [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ time sql/mysql_tzinfo_to_sql  /usr/share/zoneinfo/ | client/mysql -u root -S /tmp/s.sock  innodb
            Warning: Unable to load '/usr/share/zoneinfo//leapseconds' as time zone. Skipping it.
            Warning: Unable to load '/usr/share/zoneinfo//tzdata.zi' as time zone. Skipping it.
             
            real	0m1.849s
            user	0m0.216s
            sys	0m0.127s
            

            danblack Daniel Black added a comment - bb-10.1- MDEV-23440 -danielblack-mysql_tzinfo_to_sql-with-transactions with test cases fixed now http://buildbot.askmonty.org/buildbot/grid?category=main&category=experimental&category=galera&category=connectors&branch=bb-10.1-MDEV-23440-danielblack-mysql_tzinfo_to_sql-with-transactions 10.1-85bd5314c56 before changes [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ cat ../innodb_transactional.sql     create database if not exists innodb; use innodb;   create table time_zone like mysql.time_zone ; create table time_zone_leap_second like mysql.time_zone_leap_second ; create table time_zone_name like mysql.time_zone_name ; create table time_zone_transition like mysql.time_zone_transition ; create table time_zone_transition_type like mysql.time_zone_transition_type;   alter table time_zone engine=innodb; alter table time_zone_leap_second engine=innodb; alter table time_zone_name engine=innodb; alter table time_zone_transition engine=innodb; alter table time_zone_transition_type engine=innodb;   [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ client/mysql -u root -S /tmp/s.sock < ../innodb_transactional.sql [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | client/mysql -u root -S /tmp/s.sock innodb Warning: Unable to load '/usr/share/zoneinfo//leapseconds' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo//tzdata.zi' as time zone. Skipping it.   real 0m26.552s user 0m0.890s sys 0m0.727s bb-10.1-MDEV-23440-danielblack-mysql_tzinfo_to_sql-with-transactions   [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ client/mysql -u root -S /tmp/s.sock -e "drop database innodb; shutdown" [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ client/mysql -u root -S /tmp/s.sock < ../innodb_transactional.sql [dan@grit build-mariadb-server-10.1-RelWithDebugInfo]$ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | client/mysql -u root -S /tmp/s.sock innodb Warning: Unable to load '/usr/share/zoneinfo//leapseconds' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo//tzdata.zi' as time zone. Skipping it.   real 0m1.849s user 0m0.216s sys 0m0.127s
            danblack Daniel Black added a comment - - edited A few more benchmarks with this patch applied: https://jira.mariadb.org/browse/MDEV-20555?focusedCommentId=162428&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-162428
            danblack Daniel Black added a comment -

            before /after output checks

             $ history | tail -n 10
             2614  sql/mysql_tzinfo_to_sql --skip-write-binlog > /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt
             2615  sql/mysql_tzinfo_to_sql --skip-write-binlog /usr/share/zoneinfo/ > /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt
             2616  cp sql/mysql_tzinfo_to_sql /tmp
             2617  cmake --build .
             2618  sql/mysql_tzinfo_to_sql --skip-write-binlog /usr/share/zoneinfo/ > /tmp/mysql_tzinfo_to_sql-MDEV-23440--skip-write-binlog.txt
             2619  sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ > /tmp/mysql_tzinfo_to_sql-MDEV-23440-basic.txt
             2621  diff -u  /tmp/mysql_tzinfo_to_sql-basic.txt /tmp/mysql_tzinfo_to_sql-MDEV-23440-basic.txt
             2622  diff -u  /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt /tmp/mysql_tzinfo_to_sql-MDEV-23440--skip-write-binlog.txt 
            

            results of output check before/after patch

            $ diff -u  /tmp/mysql_tzinfo_to_sql-basic.txt /tmp/mysql_tzinfo_to_sql-MDEV-23440-basic.txt
            --- /tmp/mysql_tzinfo_to_sql-basic.txt	2020-08-11 16:24:29.171950052 +1000
            +++ /tmp/mysql_tzinfo_to_sql-MDEV-23440-basic.txt	2020-08-11 16:27:58.112286396 +1000
            @@ -11,6 +11,7 @@
             TRUNCATE TABLE time_zone_name;
             TRUNCATE TABLE time_zone_transition;
             TRUNCATE TABLE time_zone_transition_type;
            +START TRANSACTION;
             INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
             SET @time_zone_id= LAST_INSERT_ID();
             INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id);
            @@ -142081,6 +142082,7 @@
             INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
              (@time_zone_id, 0, 0, 0, 'UTC')
             ;
            +COMMIT;
             ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
             ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
             \d |
             
            $ diff -u  /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt /tmp/mysql_tzinfo_to_sql-MDEV-23440--skip-write-binlog.txt 
            --- /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt	2020-08-11 16:25:19.976265142 +1000
            +++ /tmp/mysql_tzinfo_to_sql-MDEV-23440--skip-write-binlog.txt	2020-08-11 16:27:27.476086092 +1000
            @@ -5,6 +5,7 @@
             TRUNCATE TABLE time_zone_name;
             TRUNCATE TABLE time_zone_transition;
             TRUNCATE TABLE time_zone_transition_type;
            +START TRANSACTION;
             INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
             SET @time_zone_id= LAST_INSERT_ID();
             INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id);
            @@ -142075,5 +142076,6 @@
             INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
              (@time_zone_id, 0, 0, 0, 'UTC')
             ;
            +COMMIT;
             ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
             ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
            

            danblack Daniel Black added a comment - before /after output checks $ history | tail -n 10 2614 sql/mysql_tzinfo_to_sql --skip-write-binlog > /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt 2615 sql/mysql_tzinfo_to_sql --skip-write-binlog /usr/share/zoneinfo/ > /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt 2616 cp sql/mysql_tzinfo_to_sql /tmp 2617 cmake --build . 2618 sql/mysql_tzinfo_to_sql --skip-write-binlog /usr/share/zoneinfo/ > /tmp/mysql_tzinfo_to_sql-MDEV-23440--skip-write-binlog.txt 2619 sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ > /tmp/mysql_tzinfo_to_sql-MDEV-23440-basic.txt 2621 diff -u /tmp/mysql_tzinfo_to_sql-basic.txt /tmp/mysql_tzinfo_to_sql-MDEV-23440-basic.txt 2622 diff -u /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt /tmp/mysql_tzinfo_to_sql-MDEV-23440--skip-write-binlog.txt results of output check before/after patch $ diff -u /tmp/mysql_tzinfo_to_sql-basic.txt /tmp/mysql_tzinfo_to_sql-MDEV-23440-basic.txt --- /tmp/mysql_tzinfo_to_sql-basic.txt 2020-08-11 16:24:29.171950052 +1000 +++ /tmp/mysql_tzinfo_to_sql-MDEV-23440-basic.txt 2020-08-11 16:27:58.112286396 +1000 @@ -11,6 +11,7 @@ TRUNCATE TABLE time_zone_name; TRUNCATE TABLE time_zone_transition; TRUNCATE TABLE time_zone_transition_type; +START TRANSACTION; INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); SET @time_zone_id= LAST_INSERT_ID(); INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id); @@ -142081,6 +142082,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (@time_zone_id, 0, 0, 0, 'UTC') ; +COMMIT; ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time; ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id; \d |   $ diff -u /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt /tmp/mysql_tzinfo_to_sql-MDEV-23440--skip-write-binlog.txt --- /tmp/mysql_tzinfo_to_sql--skip-write-binlog.txt 2020-08-11 16:25:19.976265142 +1000 +++ /tmp/mysql_tzinfo_to_sql-MDEV-23440--skip-write-binlog.txt 2020-08-11 16:27:27.476086092 +1000 @@ -5,6 +5,7 @@ TRUNCATE TABLE time_zone_name; TRUNCATE TABLE time_zone_transition; TRUNCATE TABLE time_zone_transition_type; +START TRANSACTION; INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); SET @time_zone_id= LAST_INSERT_ID(); INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id); @@ -142075,5 +142076,6 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (@time_zone_id, 0, 0, 0, 'UTC') ; +COMMIT; ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time; ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;

            People

              danblack Daniel Black
              danblack Daniel Black
              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.