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))

    XMLWordPrintable

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

        1. perf-g-no-children-aria.txt
          3.67 MB
          Daniel Black
        2. pod-mariadb-init.sh
          0.7 kB
          Daniel Black

        Issue Links

          Activity

            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.