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

aria TRANSACTIONAL=1 significantly slow on timezone intialisation (was: time zone initialision significantly slower in 10.4 compared to 10.3 (myisam))

Details

    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

          Activity

            danblack Daniel Black added a comment -

            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)

              T=1 T=0
            Overlays 20.554 1.106
            ext4 17.315 0.888

            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?

            danblack Daniel Black added a comment - 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)   T=1 T=0 Overlays 20.554 1.106 ext4 17.315 0.888 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 ?
            danblack Daniel Black added a comment -

            With mysql_tzinfo_to_sql that is patched actually uses innodb and start transaction and COMMIT

            non-overlayfs innodb 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 innodb ; sudo kill -INT $PID
            [6] 69433
            Tracing block device I/O... Hit Ctrl-C to end.
            [5]   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	0m1.229s
            user	0m0.059s
            sys	0m0.027s
             
             
            flags = Sync-Write
                 usecs               : count     distribution
                     0 -> 1          : 0        |                                        |
                     2 -> 3          : 0        |                                        |
                     4 -> 7          : 0        |                                        |
                     8 -> 15         : 4        |                                        |
                    16 -> 31         : 86       |**************                          |
                    32 -> 63         : 130      |*********************                   |
                    64 -> 127        : 89       |**************                          |
                   128 -> 255        : 9        |*                                       |
                   256 -> 511        : 2        |                                        |
                   512 -> 1023       : 239      |****************************************|
                  1024 -> 2047       : 7        |*                                       |
                  2048 -> 4095       : 1        |                                        |
                  4096 -> 8191       : 2        |                                        |
             
            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        : 17       |****************************************|
                   128 -> 255        : 8        |******************                      |
             
            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       : 233      |****************************************|
             
            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        : 3        |*****************                       |
                   512 -> 1023       : 5        |****************************            |
                  1024 -> 2047       : 7        |****************************************|
             
            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        : 3        |****************************************|
             
            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        : 1        |****************************************|
            

            Not quite the 0m0.864s that MyISAM was however 0m1.229s is much better than Aria (T=1) and not significantly worse thatn Aria (T=0, 0.888s)

            danblack Daniel Black added a comment - With mysql_tzinfo_to_sql that is patched actually uses innodb and start transaction and COMMIT non-overlayfs innodb 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 innodb ; sudo kill -INT $PID [6] 69433 Tracing block device I/O... Hit Ctrl-C to end. [5] 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 0m1.229s user 0m0.059s sys 0m0.027s     flags = Sync-Write usecs : count distribution 0 -> 1 : 0 | | 2 -> 3 : 0 | | 4 -> 7 : 0 | | 8 -> 15 : 4 | | 16 -> 31 : 86 |************** | 32 -> 63 : 130 |********************* | 64 -> 127 : 89 |************** | 128 -> 255 : 9 |* | 256 -> 511 : 2 | | 512 -> 1023 : 239 |****************************************| 1024 -> 2047 : 7 |* | 2048 -> 4095 : 1 | | 4096 -> 8191 : 2 | |   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 : 17 |****************************************| 128 -> 255 : 8 |****************** |   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 : 233 |****************************************|   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 : 3 |***************** | 512 -> 1023 : 5 |**************************** | 1024 -> 2047 : 7 |****************************************|   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 : 3 |****************************************|   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 : 1 |****************************************| Not quite the 0m0.864s that MyISAM was however 0m1.229s is much better than Aria (T=1) and not significantly worse thatn Aria (T=0, 0.888s)
            danblack Daniel Black added a comment - - edited

            $ sql/mysql_tzinfo_to_sql   /usr/share/zoneinfo/  | grep '^[A-Z]' | grep -v ^SET  |  uniq -c --check-chars=5
                  1 IF (select count(*) from information_schema.global_variables where
                  4 ALTER TABLE time_zone ENGINE=InnoDB;
                  1 END IF|
                  4 TRUNCATE TABLE time_zone;
                  1 START TRANSACTION;
            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 INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
                  1 COMMIT;
                  2 ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
                  1 IF (select count(*) from information_schema.global_variables where
                  4 ALTER TABLE time_zone ENGINE=Aria;
                  1 END IF|
            

            10.5.(pre)6-5eff7c022608af69c5f3591326e2be5132033977

            bpftrace -e 'tracepoint:syscalls:sys_enter_fdatasync { @start[args->fd] = nsecs; @fd = args->fd} tracepoint:syscalls:sys_exit_fdatasync { @us[ustack, @fd] = hist((nsecs - @start[@fd]) / 1000); delete(@start[@fd])  } ' -p 331087
             
            ...
            @us[
                fdatasync+59
                translog_sync_files+228
                translog_flush+1282
                ma_commit+80
                ha_maria::external_lock(THD*, int)+591
                handler::ha_external_lock(THD*, int)+220
                mysql_unlock_tables(THD*, st_mysql_lock*, bool)+394
                close_thread_tables(THD*)+357
                mysql_execute_command(THD*)+3116
                mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool)+495
                dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool)+7596
                do_command(THD*)+272
                do_handle_one_connection(CONNECT*, bool)+1025
                handle_one_connection+93
                pfs_spawn_thread+322
                start_thread+226
            , 5]: 
            [512, 1K)           5697 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@|
            [1K, 2K)              15 |                                                    |
            [2K, 4K)            1255 |@@@@@@@@@@@                                         |
            [4K, 8K)              23 |                                                    |
            [8K, 16K)              1 |                                                    |
             
            # ls -la /proc/331087/fd/5
            lrwx------. 1 dan dan 64 Aug 13 16:00 /proc/331087/fd/5 -> /home/dan/datadir/MDEV-20555_test/aria_log.00000001
            

            So 6991 calls to fdatasync corresponding to number of insert statements.

            danblack Daniel Black added a comment - - edited $ sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | grep '^[A-Z]' | grep -v ^SET | uniq -c --check-chars=5 1 IF (select count(*) from information_schema.global_variables where 4 ALTER TABLE time_zone ENGINE=InnoDB; 1 END IF| 4 TRUNCATE TABLE time_zone; 1 START TRANSACTION; 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 INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); 1 COMMIT; 2 ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time; 1 IF (select count(*) from information_schema.global_variables where 4 ALTER TABLE time_zone ENGINE=Aria; 1 END IF| 10.5.(pre)6-5eff7c022608af69c5f3591326e2be5132033977 bpftrace -e 'tracepoint:syscalls:sys_enter_fdatasync { @start[args->fd] = nsecs; @fd = args->fd} tracepoint:syscalls:sys_exit_fdatasync { @us[ustack, @fd] = hist((nsecs - @start[@fd]) / 1000); delete(@start[@fd]) } ' -p 331087   ... @us[ fdatasync+59 translog_sync_files+228 translog_flush+1282 ma_commit+80 ha_maria::external_lock(THD*, int)+591 handler::ha_external_lock(THD*, int)+220 mysql_unlock_tables(THD*, st_mysql_lock*, bool)+394 close_thread_tables(THD*)+357 mysql_execute_command(THD*)+3116 mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool)+495 dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool)+7596 do_command(THD*)+272 do_handle_one_connection(CONNECT*, bool)+1025 handle_one_connection+93 pfs_spawn_thread+322 start_thread+226 , 5]: [512, 1K) 5697 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@| [1K, 2K) 15 | | [2K, 4K) 1255 |@@@@@@@@@@@ | [4K, 8K) 23 | | [8K, 16K) 1 | |   # ls -la /proc/331087/fd/5 lrwx------. 1 dan dan 64 Aug 13 16:00 /proc/331087/fd/5 -> /home/dan/datadir/MDEV-20555_test/aria_log.00000001 So 6991 calls to fdatasync corresponding to number of insert statements.
            danblack Daniel Black added a comment -

            robertbindar can I please get a review of bb-10.2-danielblack-MDEV-23326-mysql_tzinfo_to_sql being part 1 of the solution here.

            This is targeted as 10.2 as the building of containers as the container scripts are easier to maintain if they don't need version dependant scripts.

            The end goal is to merge this into functionality of mysql_install_db like in the test branch https://buildbot.mariadb.org/#/grid?branch=bb-10.2-danielblack-MDEV-23326-mysql_tzinfo_to_sql. We don't want binary logs enabled nor is accessing galera status available in mysqld --bootstrap.

            danblack Daniel Black added a comment - robertbindar can I please get a review of bb-10.2-danielblack- MDEV-23326 -mysql_tzinfo_to_sql being part 1 of the solution here. This is targeted as 10.2 as the building of containers as the container scripts are easier to maintain if they don't need version dependant scripts. The end goal is to merge this into functionality of mysql_install_db like in the test branch https://buildbot.mariadb.org/#/grid?branch=bb-10.2-danielblack-MDEV-23326-mysql_tzinfo_to_sql . We don't want binary logs enabled nor is accessing galera status available in mysqld --bootstrap.
            danblack Daniel Black added a comment -

            monty https://github.com/MariaDB/server/pull/1962 is the latest code. The bb-10.2 can be ignored.

            danblack Daniel Black added a comment - monty https://github.com/MariaDB/server/pull/1962 is the latest code. The bb-10.2 can be ignored.

            People

              danblack Daniel Black
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.