[MDEV-23440] mysql_tzinfo_to_sql under innodb is slow Created: 2020-08-10  Updated: 2022-01-11  Resolved: 2020-08-15

Status: Closed
Project: MariaDB Server
Component/s: Time zones
Affects Version/s: 10.1.42
Fix Version/s: 10.1.48, 10.2.35, 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-18778 mysql_tzinfo_to_sql does not work cor... Closed
relates to MDEV-23326 aria TRANSACTIONAL=1 significantly sl... Closed

 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)

 Comments   
Comment by Daniel Black [ 2020-08-10 ]

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

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

Comment by Daniel Black [ 2020-08-10 ]

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

Comment by Daniel Black [ 2020-08-11 ]

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

Comment by Daniel Black [ 2020-08-11 ]

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;

Generated at Thu Feb 08 09:22:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.