Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.3, 10.4.13
Description
From https://github.com/docker-library/mariadb/issues/262.
The reason for the blocker is the number of significant name projects mentioning that issue in the reason they are disabling time zone loading in their product. From openstack, nextcloud. ibis, and these are only the ones mentioning the issue on github. From the performance data below, it used to be quick in 10.3. Its now slow enough to abort startup in many production scenarios for container deployment.
(as of time 10.5.4 corresponds to 10.5)
from attached script:
MariaDB | Plain basic time | without tz init |
---|---|---|
10.5 | 0m21.094s | 0m4.283s |
10.4. {13} | 0m22.557s | 0m4.553s |
10.4.13 | 0m22.343s | |
10.4.12 | 0m22.548s | |
10.4.11 | 0m32.815s | |
10.4.10 | 0m33.566s | |
10.4.8 | 0m29.068s | |
10.4.7 | 0m19.067s | |
10.4.6 | 0m18.089s | |
10.4.5 | 0m18.859s | |
10.4.4 | 0m20.296s | |
10.4.3 | 0m20.071s | |
10.3 | 0m6.305s | 0m4.559s |
10.2 | 0m6.289s | 0m5.533s |
10.1 | 0m14.059s | 0m12.289s |
this isn't a factor of the mysql_tzinfo_to_sql script::
10.5.5-ubsan build |
[dan@grit build-mariadb-server-10.5-ubsan]$ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/s.sock mysql
|
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 0m21.056s
|
user 0m0.365s
|
sys 0m0.204s
|
|
|
(reinitialised the datadir and still using a 10.5 server)
|
|build-mariadb-server-10.2-debug]$ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/s.sock mysql
|
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 0m20.688s
|
user 0m0.283s
|
sys 0m0.202s
|
|
scripts/m*dumpslow /tmp/slow.log | grep Time= | grep -v Time=0.0
|
|
Reading mysql slow query log from /tmp/slow.log
|
Count: 1 Time=3.93s (3s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=120290.0 (120290), dan[dan]@localhost
|
Count: 1 Time=0.32s (0s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=9581.0 (9581), dan[dan]@localhost
|
10.2.33-debug server version |
time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/s.sock -u root mysql
|
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.
|
time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/s.sock -u root mysql
|
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 0m2.199s
|
user 0m0.203s
|
sys 0m0.087s
|
|
$ scripts/m*dumpslow /tmp/slow.log | grep Time= | grep -v Time=0.0
|
|
Reading mysql slow query log from /tmp/slow.log
|
|
Count: 1 Time=0.24s (0s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=120290.0 (120290), root[root]@localhost
|
10.2 |
CREATE TABLE `time_zone` (
|
`Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
|
PRIMARY KEY (`Time_zone_id`)
|
) ENGINE=MyISAM AUTO_INCREMENT=1784 DEFAULT CHARSET=utf8 COMMENT='Time zones'
|
10.5 |
CREATE TABLE `time_zone` (
|
`Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
|
PRIMARY KEY (`Time_zone_id`)
|
) ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Time zones'
|
Attachments
Issue Links
- causes
-
MDEV-28263 mariadb-tzinfo-to-sql binlog, embedded, and performance fixes
- Closed
- is blocked by
-
MDEV-24268 mtr tests for mysql_install_db
- Open
- relates to
-
MDEV-18778 mysql_tzinfo_to_sql does not work correctly in MariaDB Galera
- Closed
-
MDEV-20555 Transactional Aria tables are slower than MyISAM on inserts.
- Closed
-
MDEV-21209 mysql_tzinfo_to_sql's Galera checks do not work
- Closed
-
MDEV-23440 mysql_tzinfo_to_sql under innodb is slow
- Closed
-
MDEV-27608 speed up Docker Library Initialization (timezones)
- Closed
-
MDEV-27074 container start-up to take a long time
- Closed
- links to
overflayfs fresh initialization
[dan@grit ~]$ fusermount -u datadir
[dan@grit ~]$ rm -rf datadir*/*
[dan@grit ~]$ fuse-overlayfs -o lowerdir=~//datadir_lower -o upperdir=~/datadir_upper -o workdir=~/datadir_work ~/datadir
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ time scripts/mysql_install_db --no-defaults --srcdir=${OLDPWD} --builddir=${PWD} --datadir=/home/dan/datadir --log-bin=/home/dan/datadir/mysqlbin --verbose
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ sql/mysqld --no-defaults --skip-networking --datadir=/home/dan/datadir --log-bin=/home/dan/datadir/mysqlbin --socket /tmp/s.sock --lc-messages-dir=${PWD}/sql/share --verbose
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ client/mysql -S /tmp/s.sock < ../aria_non_transactional.sql
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ cat ../aria_non_transactional.sql
create database if not exists aria_non_transactional;
use aria_non_transactional;
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 transactional=0;
alter table time_zone_leap_second transactional=0;
alter table time_zone_name transactional=0;
alter table time_zone_transition transactional=0;
alter table time_zone_transition_type transactional=0;
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ > /dev/null
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 0m0.029s
user 0m0.021s
sys 0m0.007s
overlayfs aria T=1
$ sudo //usr/share/bcc/tools/biolatency -F -Q & PID=$! ; sleep 4; time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | client/mysql -S /tmp/s.sock mysql ; sudo kill -INT $PID
[2] 61204
Tracing block device I/O... Hit Ctrl-C to end.
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 0m20.554s
user 0m0.769s
sys 0m0.537s
flags = Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 1 | |
256 -> 511 : 0 | |
512 -> 1023 : 50 |****************************************|
1024 -> 2047 : 0 | |
2048 -> 4095 : 5 |**** |
flags = Background-Priority-Metadata-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 1 |*** |
64 -> 127 : 0 | |
128 -> 255 : 11 |****************************************|
256 -> 511 : 0 | |
512 -> 1023 : 7 |************************* |
1024 -> 2047 : 0 | |
2048 -> 4095 : 7 |************************* |
4096 -> 8191 : 10 |************************************ |
flags = Background-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 1 |****************************************|
64 -> 127 : 0 | |
128 -> 255 : 1 |****************************************|
256 -> 511 : 0 | |
512 -> 1023 : 1 |****************************************|
1024 -> 2047 : 0 | |
2048 -> 4095 : 1 |****************************************|
flags = NoMerge-Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 0 | |
256 -> 511 : 4 |****************************************|
512 -> 1023 : 2 |******************** |
1024 -> 2047 : 2 |******************** |
flags = Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 181 |* |
16 -> 31 : 628 |*** |
32 -> 63 : 5592 |******************************* |
64 -> 127 : 1963 |*********** |
128 -> 255 : 13 | |
256 -> 511 : 1 | |
512 -> 1023 : 7017 |****************************************|
1024 -> 2047 : 11 | |
2048 -> 4095 : 40 | |
4096 -> 8191 : 6 | |
flags = ForcedUnitAccess-Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 0 | |
1024 -> 2047 : 1287 |****************************************|
2048 -> 4095 : 2 | |
4096 -> 8191 : 3 | |
overlayfs aria T=0
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ sudo //usr/share/bcc/tools/biolatency -F -Q & PID=$! ; sleep 4; time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | client/mysql -S /tmp/s.sock aria_non_transactional ; sudo kill -INT $PID
[2] 61246
Tracing block device I/O... Hit Ctrl-C to end.
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.106s
user 0m0.129s
sys 0m0.063s
flags = Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 1 |** |
16 -> 31 : 3 |******** |
32 -> 63 : 6 |**************** |
64 -> 127 : 7 |****************** |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 15 |****************************************|
1024 -> 2047 : 2 |***** |
2048 -> 4095 : 2 |***** |
4096 -> 8191 : 2 |***** |
flags = NoMerge-Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 1 |************* |
256 -> 511 : 3 |****************************************|
512 -> 1023 : 1 |************* |
1024 -> 2047 : 1 |************* |
2048 -> 4095 : 1 |************* |
flags = ForcedUnitAccess-Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 0 | |
1024 -> 2047 : 13 |****************************************|
change to non-overlayfs
[dan@grit ~]$ fusermount -u datadir
[dan@grit ~]$ rm -rf datadir*/*
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ time scripts/mysql_install_db --no-defaults --srcdir=${OLDPWD} --builddir=${PWD} --datadir=/home/dan/datadir --log-bin=/home/dan/datadir/mysqlbin --verbose
Installing MariaDB/MySQL system tables in '/home/dan/datadir' ...
2020-08-10 14:42:25 0 [Note] /home/dan/repos/build-mariadb-server-10.4-RelWithDebugInfo/sql/mysqld (mysqld 10.4.14-MariaDB-log) starting as process 61953 ...
..
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ sql/mysqld --no-defaults --skip-networking --datadir=/home/dan/datadir --log-bin=/home/dan/datadir/mysqlbin --socket /tmp/s.sock --lc-messages-dir=${PWD}/sql/share --verbose
2020-08-10 14:42:35 0 [Note] sql/mysqld (mysqld 10.4.14-MariaDB-log) starting as process 61988 ...
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ client/mysql -S /tmp/s.sock < ../aria_non_transactional.sql
non-overlayfs aria T=1
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ sudo //usr/share/bcc/tools/biolatency -F -Q & PID=$! ; sleep 4; time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | client/mysql -S /tmp/s.sock mysql ; sudo kill -INT $PID
[3] 62063
Tracing block device I/O... Hit Ctrl-C to end.
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 0m17.315s
user 0m0.597s
sys 0m0.493s
flags = Background-Priority-Metadata-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 13 |**************************** |
64 -> 127 : 14 |******************************* |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 18 |****************************************|
flags = Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 22 | |
16 -> 31 : 1058 |****** |
32 -> 63 : 4999 |**************************** |
64 -> 127 : 2271 |************ |
128 -> 255 : 14 | |
256 -> 511 : 5 | |
512 -> 1023 : 6998 |****************************************|
1024 -> 2047 : 18 | |
2048 -> 4095 : 48 | |
4096 -> 8191 : 4 | |
flags = Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 1 |* |
64 -> 127 : 19 |******************* |
128 -> 255 : 39 |****************************************|
256 -> 511 : 1 |* |
512 -> 1023 : 4 |**** |
flags = NoMerge-Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 3 |*************** |
256 -> 511 : 8 |****************************************|
512 -> 1023 : 8 |****************************************|
1024 -> 2047 : 3 |*************** |
flags = Background-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 1 |******************** |
64 -> 127 : 0 | |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 2 |****************************************|
flags = ForcedUnitAccess-Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 0 | |
1024 -> 2047 : 1286 |****************************************|
2048 -> 4095 : 2 | |
4096 -> 8191 : 1 | |
non-overlayfs aria T=0
[dan@grit build-mariadb-server-10.4-RelWithDebugInfo]$ sudo //usr/share/bcc/tools/biolatency -F -Q & PID=$! ; sleep 4; time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | client/mysql -S /tmp/s.sock aria_non_transactional ; sudo kill -INT $PID
[4] 62082
Tracing block device I/O... Hit Ctrl-C to end.
[3] Done sudo //usr/share/bcc/tools/biolatency -F -Q
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 0m0.888s
user 0m0.133s
sys 0m0.073s
flags = NoMerge-Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 3 |******************** |
256 -> 511 : 6 |****************************************|
512 -> 1023 : 6 |****************************************|
1024 -> 2047 : 1 |****** |
2048 -> 4095 : 2 |************* |
flags = Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 1 |***** |
256 -> 511 : 1 |***** |
512 -> 1023 : 0 | |
1024 -> 2047 : 0 | |
2048 -> 4095 : 0 | |
4096 -> 8191 : 8 |****************************************|
flags = Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 2 |***** |
32 -> 63 : 8 |********************** |
64 -> 127 : 9 |************************* |
128 -> 255 : 1 |** |
256 -> 511 : 3 |******** |
512 -> 1023 : 14 |****************************************|
1024 -> 2047 : 1 |** |
2048 -> 4095 : 4 |*********** |
4096 -> 8191 : 1 |** |
flags = ForcedUnitAccess-Sync-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 0 | |
1024 -> 2047 : 11 |****************************************|
flags = Background-Priority-Metadata-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 0 | |
1024 -> 2047 : 0 | |
2048 -> 4095 : 0 | |
4096 -> 8191 : 7 |****************************************|
flags = Background-Write
usecs : count distribution
0 -> 1 : 0 | |
2 -> 3 : 0 | |
4 -> 7 : 0 | |
8 -> 15 : 0 | |
16 -> 31 : 0 | |
32 -> 63 : 0 | |
64 -> 127 : 0 | |
128 -> 255 : 0 | |
256 -> 511 : 0 | |
512 -> 1023 : 0 | |
1024 -> 2047 : 0 | |
2048 -> 4095 : 0 | |
4096 -> 8191 : 1 |****************************************|
Summary of above times:
Aria load time of timezones (seconds)
mysql_tzinfo_to_sql stats
$ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | grep ^INSERT | wc -l
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.
6991
for i in $( client/mysql -S /tmp/s.sock -ABNe "show tables like 'time_%'" mysql); do echo -n "$i: "; client/mysql -S /tmp/s.sock -ABNe "select count(*) from mysql.$i;"; done
time_zone: 1783
time_zone_leap_second: 0
time_zone_name: 1783
time_zone_transition: 120290
time_zone_transition_type: 9581
So ~7k inserts and 2 ALTER TABLE time_zone_transition* ORDER BY requires 15k Sync Writes with TRANSACTION=1. Comments monty?