MDEV-23326: Aria significantly slow on timezone intialisation

The --skip-write-binary-log added to mysql_tzinfo_to_sql in
MDEV-18778 was only effective if galera was enabled on the server.
This is because it tied together three concepts under one option:
1. binary logging
2. wsrep replication, and
3. using innodb as a transitional table type.

Change 1: small change in help option to reflect this.

To solve the performance problem with Aria tables, LOCK TABLES WRITE
is used to eliminate the need to fdatasync until the UNLOCK TABLES.

If galera isn't enabled, then we also want to use the LOCK TABLE WRITE
mechanism.

The START TRANSACTION added in MDEV-23440 needed to be moved to
before LOCK TABLES otherwise it would cancel their effect.

TRUNCATE TABLE statements also need to be before the LOCK TABLES.

When changing back from InnoDB to Aria, include the ORDER BY that
was originally there in 6aaccbcbf790 and matching the final ALTER
TABLE in the timezonedir branch.

Running: mariadb-tzinfo-to-sql --skip-write-binlog /usr/share/zoneinfo
now generates 16 Aria_transaction_log_syncs from 7053.

1 files changed, 268 insertions, 17 deletions

diff --git a/mysql-test/main/mysql_tzinfo_to_sql_symlink.result b/mysql-test/main/mysql_tzinfo_to_sql_symlink.result
index a7426327ecf..9456b5515e3 100644
--- a/mysql-test/main/mysql_tzinfo_to_sql_symlink.result
+++ b/mysql-test/main/mysql_tzinfo_to_sql_symlink.result
@@ -1,3 +1,14 @@
+use mysql;
+RENAME TABLE time_zone TO time_zone_orig,
+time_zone_name TO time_zone_name_orig,
+time_zone_transition TO time_zone_transition_orig,
+time_zone_transition_type TO time_zone_transition_type_orig,
+time_zone_leap_second TO time_zone_leap_second_orig;
+CREATE TABLE time_zone LIKE time_zone_orig;
+CREATE TABLE time_zone_name LIKE time_zone_name_orig;
+CREATE TABLE time_zone_transition LIKE time_zone_transition_orig;
+CREATE TABLE time_zone_transition_type LIKE time_zone_transition_type_orig;
+CREATE TABLE time_zone_leap_second LIKE time_zone_leap_second_orig;
 #
 # MDEV-5226 mysql_tzinfo_to_sql errors with tzdata 2013f and above
 #
@@ -9,13 +20,18 @@ ALTER TABLE time_zone ENGINE=InnoDB;
 ALTER TABLE time_zone_name ENGINE=InnoDB;
 ALTER TABLE time_zone_transition ENGINE=InnoDB;
 ALTER TABLE time_zone_transition_type ENGINE=InnoDB;
-END IF|
-\d ;
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
 TRUNCATE TABLE time_zone_transition;
 TRUNCATE TABLE time_zone_transition_type;
 START TRANSACTION;
+ELSE
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+END IF|
+\d ;
 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 ('GMT', @time_zone_id);
@@ -33,6 +49,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset,
 Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it.
 Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/ignored.tab' as time zone. Skipping it.
 Warning: Skipping directory 'MYSQLTEST_VARDIR/zoneinfo/posix/posix': to avoid infinite symlink recursion.
+UNLOCK TABLES;
 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;
@@ -41,11 +58,26 @@ IF (select count(*) from information_schema.global_variables where
 variable_name='wsrep_on' and variable_value='ON') = 1 THEN
 ALTER TABLE time_zone ENGINE=Aria;
 ALTER TABLE time_zone_name ENGINE=Aria;
-ALTER TABLE time_zone_transition ENGINE=Aria;
-ALTER TABLE time_zone_transition_type ENGINE=Aria;
+ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time;
+ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id;
 END IF|
 \d ;
-# Silent run
+SELECT COUNT(*) FROM time_zone;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_name;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition_type;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_leap_second;
+COUNT(*)
+0
+# Run on zoneinfo directory
 \d |
 IF (select count(*) from information_schema.global_variables where
 variable_name='wsrep_on' and variable_value='ON') = 1 THEN
@@ -53,13 +85,18 @@ ALTER TABLE time_zone ENGINE=InnoDB;
 ALTER TABLE time_zone_name ENGINE=InnoDB;
 ALTER TABLE time_zone_transition ENGINE=InnoDB;
 ALTER TABLE time_zone_transition_type ENGINE=InnoDB;
-END IF|
-\d ;
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
 TRUNCATE TABLE time_zone_transition;
 TRUNCATE TABLE time_zone_transition_type;
 START TRANSACTION;
+ELSE
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+END IF|
+\d ;
 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 ('GMT', @time_zone_id);
@@ -74,6 +111,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset,
  (@time_zone_id, 0, 0, 0, 'GMT')
 ;
 Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it.
+UNLOCK TABLES;
 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;
@@ -82,10 +120,78 @@ IF (select count(*) from information_schema.global_variables where
 variable_name='wsrep_on' and variable_value='ON') = 1 THEN
 ALTER TABLE time_zone ENGINE=Aria;
 ALTER TABLE time_zone_name ENGINE=Aria;
-ALTER TABLE time_zone_transition ENGINE=Aria;
-ALTER TABLE time_zone_transition_type ENGINE=Aria;
+ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time;
+ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id;
 END IF|
 \d ;
+SELECT COUNT(*) FROM time_zone;
+COUNT(*)
+2
+SELECT COUNT(*) FROM time_zone_name;
+COUNT(*)
+2
+SELECT COUNT(*) FROM time_zone_transition;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition_type;
+COUNT(*)
+2
+SELECT COUNT(*) FROM time_zone_leap_second;
+COUNT(*)
+0
+#
+# Run on zoneinfo directory --skip-write-binlog
+#
+set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?');
+prepare set_wsrep_write_binlog from @prep1;
+set @toggle=0; execute set_wsrep_write_binlog using @toggle;
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+LOCK TABLES time_zone WRITE,
+  time_zone_leap_second WRITE,
+  time_zone_name WRITE,
+  time_zone_transition WRITE,
+  time_zone_transition_type WRITE;
+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 ('GMT', @time_zone_id);
+INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
+ (@time_zone_id, 0, 0, 0, 'GMT')
+;
+Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/garbage' as time zone. Skipping it.
+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 ('posix/GMT', @time_zone_id);
+INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
+ (@time_zone_id, 0, 0, 0, 'GMT')
+;
+Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it.
+UNLOCK TABLES;
+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;
+SELECT COUNT(*) FROM time_zone;
+COUNT(*)
+2
+SELECT COUNT(*) FROM time_zone_name;
+COUNT(*)
+2
+SELECT COUNT(*) FROM time_zone_transition;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition_type;
+COUNT(*)
+2
+SELECT COUNT(*) FROM time_zone_leap_second;
+COUNT(*)
+0
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+TRUNCATE TABLE time_zone_leap_second;
 #
 # Testing with explicit timezonefile
 #
@@ -96,6 +202,10 @@ ALTER TABLE time_zone ENGINE=InnoDB;
 ALTER TABLE time_zone_name ENGINE=InnoDB;
 ALTER TABLE time_zone_transition ENGINE=InnoDB;
 ALTER TABLE time_zone_transition_type ENGINE=InnoDB;
+SELECT 'skip truncate tables';
+START TRANSACTION;
+ELSE
+SELECT 'skip truncate tables';
 END IF|
 \d ;
 INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
@@ -104,15 +214,79 @@ INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('XXX', @time_zone_id);
 INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
  (@time_zone_id, 0, 0, 0, 'GMT')
 ;
+UNLOCK TABLES;
+COMMIT;
 \d |
 IF (select count(*) from information_schema.global_variables where
 variable_name='wsrep_on' and variable_value='ON') = 1 THEN
 ALTER TABLE time_zone ENGINE=Aria;
 ALTER TABLE time_zone_name ENGINE=Aria;
-ALTER TABLE time_zone_transition ENGINE=Aria;
-ALTER TABLE time_zone_transition_type ENGINE=Aria;
+ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time;
+ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id;
 END IF|
 \d ;
+skip truncate tables
+skip truncate tables
+SELECT COUNT(*) FROM time_zone;
+COUNT(*)
+1
+SELECT COUNT(*) FROM time_zone_name;
+COUNT(*)
+1
+SELECT COUNT(*) FROM time_zone_transition;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition_type;
+COUNT(*)
+1
+SELECT COUNT(*) FROM time_zone_leap_second;
+COUNT(*)
+0
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+TRUNCATE TABLE time_zone_leap_second;
+#
+# Testing with explicit timezonefile --skip-write-binlog
+#
+set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?');
+prepare set_wsrep_write_binlog from @prep1;
+set @toggle=0; execute set_wsrep_write_binlog using @toggle;
+SELECT 'skip truncate tables';
+LOCK TABLES time_zone WRITE,
+  time_zone_leap_second WRITE,
+  time_zone_name WRITE,
+  time_zone_transition WRITE,
+  time_zone_transition_type WRITE;
+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 ('XXX', @time_zone_id);
+INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
+ (@time_zone_id, 0, 0, 0, 'GMT')
+;
+UNLOCK TABLES;
+COMMIT;
+SELECT COUNT(*) FROM time_zone;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_name;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition_type;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_leap_second;
+COUNT(*)
+0
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+TRUNCATE TABLE time_zone_leap_second;
 #
 # Testing --leap
 #
@@ -123,6 +297,10 @@ ALTER TABLE time_zone ENGINE=InnoDB;
 ALTER TABLE time_zone_name ENGINE=InnoDB;
 ALTER TABLE time_zone_transition ENGINE=InnoDB;
 ALTER TABLE time_zone_transition_type ENGINE=InnoDB;
+SELECT 'skip truncate tables';
+START TRANSACTION;
+ELSE
+SELECT 'skip truncate tables';
 END IF|
 \d ;
 \d |
@@ -139,15 +317,72 @@ ALTER TABLE time_zone_leap_second ENGINE=Aria;
 END IF|
 \d ;
 ALTER TABLE time_zone_leap_second ORDER BY Transition_time;
+UNLOCK TABLES;
+COMMIT;
 \d |
 IF (select count(*) from information_schema.global_variables where
 variable_name='wsrep_on' and variable_value='ON') = 1 THEN
 ALTER TABLE time_zone ENGINE=Aria;
 ALTER TABLE time_zone_name ENGINE=Aria;
-ALTER TABLE time_zone_transition ENGINE=Aria;
-ALTER TABLE time_zone_transition_type ENGINE=Aria;
+ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time;
+ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id;
 END IF|
 \d ;
+skip truncate tables
+skip truncate tables
+SELECT COUNT(*) FROM time_zone;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_name;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition_type;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_leap_second;
+COUNT(*)
+0
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+TRUNCATE TABLE time_zone_leap_second;
+#
+# Testing --skip-write-binlog --leap
+#
+set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?');
+prepare set_wsrep_write_binlog from @prep1;
+set @toggle=0; execute set_wsrep_write_binlog using @toggle;
+SELECT 'skip truncate tables';
+LOCK TABLES time_zone WRITE,
+  time_zone_leap_second WRITE,
+  time_zone_name WRITE,
+  time_zone_transition WRITE,
+  time_zone_transition_type WRITE;
+TRUNCATE TABLE time_zone_leap_second;
+ALTER TABLE time_zone_leap_second ORDER BY Transition_time;
+UNLOCK TABLES;
+COMMIT;
+skip truncate tables
+skip truncate tables
+SELECT COUNT(*) FROM time_zone;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_name;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_transition_type;
+COUNT(*)
+0
+SELECT COUNT(*) FROM time_zone_leap_second;
+COUNT(*)
+0
 #
 # MDEV-6236 - [PATCH] mysql_tzinfo_to_sql may produce invalid SQL
 #
@@ -158,13 +393,19 @@ ALTER TABLE time_zone ENGINE=InnoDB;
 ALTER TABLE time_zone_name ENGINE=InnoDB;
 ALTER TABLE time_zone_transition ENGINE=InnoDB;
 ALTER TABLE time_zone_transition_type ENGINE=InnoDB;
-END IF|
-\d ;
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
 TRUNCATE TABLE time_zone_transition;
 TRUNCATE TABLE time_zone_transition_type;
 START TRANSACTION;
+ELSE
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+END IF|
+\d ;
+UNLOCK TABLES;
 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;
@@ -173,7 +414,17 @@ IF (select count(*) from information_schema.global_variables where
 variable_name='wsrep_on' and variable_value='ON') = 1 THEN
 ALTER TABLE time_zone ENGINE=Aria;
 ALTER TABLE time_zone_name ENGINE=Aria;
-ALTER TABLE time_zone_transition ENGINE=Aria;
-ALTER TABLE time_zone_transition_type ENGINE=Aria;
+ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time;
+ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id;
 END IF|
 \d ;
+DROP TABLE time_zone;
+DROP TABLE time_zone_name;
+DROP TABLE time_zone_transition;
+DROP TABLE time_zone_transition_type;
+DROP TABLE time_zone_leap_second;
+RENAME TABLE time_zone_orig TO time_zone,
+time_zone_name_orig TO time_zone_name,
+time_zone_transition_orig TO time_zone_transition,
+time_zone_transition_type_orig TO time_zone_transition_type,
+time_zone_leap_second_orig TO time_zone_leap_second;