-
Notifications
You must be signed in to change notification settings - Fork 442
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
MySQL init process hangs when using image mariadb 10.1.42, 10.2.27, 10.3.18, 10.4.8 #262
Comments
I can reproduce it hanging, with the log output missing the line from the entrypoint Related to or maybe duplicate of #261 $ docker run -d --rm --name mariadb -e MYSQL_ROOT_PASSWORD=pass mariadb:10.3.18
d4a3b782337dd471738e7d6a054d60f6c7f268448f9095785b40288ef810d7b5
$ time grep -iq "ready for connections" <(docker logs -f mariadb 2>&1)
real 0m22.382s
user 0m0.000s
sys 0m0.007s diff -y 10.3.17 10.3.18$ docker run -d --rm --name mariadb-18 -e MYSQL_ROOT_PASSWORD=pass mariadb:10.3.18
db458b3f567abb6e70d778bd361e57471af5322ecb57e8f77f17341c38d19cc5
$ docker run -d --rm --name mariadb-17 -e MYSQL_ROOT_PASSWORD=pass mariadb:10.3.17
10c01a8b60179fb7e017726fc8340691f6ffc4c91eef84deabb216e6b0991c5a
$ diff -y <(docker logs mariadb-17 2>&1) <(docker logs mariadb-18 2>&1)
Initializing database Initializing database
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands To do so, start the server, then issue the following commands
'/usr/bin/mysqladmin' -u root password 'new-password' '/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h password 'new-password' '/usr/bin/mysqladmin' -u root -h password 'new-password'
Alternatively you can run: Alternatively you can run:
'/usr/bin/mysql_secure_installation' '/usr/bin/mysql_secure_installation'
which will also give you the option of removing the test which will also give you the option of removing the test
databases and anonymous user created by default. This is databases and anonymous user created by default. This is
strongly recommended for production servers. strongly recommended for production servers.
See the MariaDB Knowledgebase at http://mariadb.com/kb or the See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions. MySQL manual for more instructions.
Please report any problems at http://mariadb.org/jira Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://m The latest information about MariaDB is available at http://m
You can find additional information about the MySQL part at: You can find additional information about the MySQL part at:
http://dev.mysql.com http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community: Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/ https://mariadb.org/get-involved/
Database initialized Database initialized
MySQL init process in progress... MySQL init process in progress...
2019-09-20 17:07:09 0 [Note] mysqld (mysqld 10.3.17-MariaDB-1 | 2019-09-20 17:06:54 0 [Note] mysqld (mysqld 10.3.18-MariaDB-1
2019-09-20 17:07:09 0 [Note] InnoDB: Using Linux native AIO | 2019-09-20 17:06:54 0 [Note] InnoDB: Using Linux native AIO
2019-09-20 17:07:09 0 [Note] InnoDB: Mutexes and rw_locks use | 2019-09-20 17:06:54 0 [Note] InnoDB: Mutexes and rw_locks use
2019-09-20 17:07:09 0 [Note] InnoDB: Uses event mutexes | 2019-09-20 17:06:54 0 [Note] InnoDB: Uses event mutexes
2019-09-20 17:07:09 0 [Note] InnoDB: Compressed tables use zl | 2019-09-20 17:06:54 0 [Note] InnoDB: Compressed tables use zl
2019-09-20 17:07:09 0 [Note] InnoDB: Number of pools: 1 | 2019-09-20 17:06:54 0 [Note] InnoDB: Number of pools: 1
2019-09-20 17:07:09 0 [Note] InnoDB: Using SSE2 crc32 instruc | 2019-09-20 17:06:54 0 [Note] InnoDB: Using SSE2 crc32 instruc
2019-09-20 17:07:09 0 [Note] InnoDB: Initializing buffer pool | 2019-09-20 17:06:54 0 [Note] InnoDB: Initializing buffer pool
2019-09-20 17:07:09 0 [Note] InnoDB: Completed initialization | 2019-09-20 17:06:54 0 [Note] InnoDB: Completed initialization
2019-09-20 17:07:09 0 [Note] InnoDB: If the mysqld execution | 2019-09-20 17:06:54 0 [Note] InnoDB: If the mysqld execution
MySQL init process in progress... MySQL init process in progress...
MySQL init process in progress... MySQL init process in progress...
2019-09-20 17:07:11 0 [Note] InnoDB: 128 out of 128 rollback | 2019-09-20 17:06:57 0 [Note] InnoDB: 128 out of 128 rollback
2019-09-20 17:07:11 0 [Note] InnoDB: Creating shared tablespa | 2019-09-20 17:06:57 0 [Note] InnoDB: Creating shared tablespa
2019-09-20 17:07:11 0 [Note] InnoDB: Setting file './ibtmp1' | 2019-09-20 17:06:57 0 [Note] InnoDB: Setting file './ibtmp1'
2019-09-20 17:07:11 0 [Note] InnoDB: File './ibtmp1' size is | 2019-09-20 17:06:57 0 [Note] InnoDB: File './ibtmp1' size is
2019-09-20 17:07:11 0 [Note] InnoDB: 10.3.17 started; log seq | 2019-09-20 17:06:57 0 [Note] InnoDB: Waiting for purge to sta
2019-09-20 17:07:11 0 [Note] Plugin 'FEEDBACK' is disabled. | 2019-09-20 17:06:57 0 [Note] InnoDB: 10.3.18 started; log seq
2019-09-20 17:07:11 0 [Note] InnoDB: Loading buffer pool(s) f | 2019-09-20 17:06:57 0 [Note] InnoDB: Loading buffer pool(s) f
2019-09-20 17:07:12 0 [Warning] 'user' entry 'root@10c01a8b60 | 2019-09-20 17:06:57 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-09-20 17:07:12 0 [Warning] 'user' entry '@10c01a8b6017' | 2019-09-20 17:06:57 0 [Note] InnoDB: Buffer pool(s) load comp
2019-09-20 17:07:12 0 [Warning] 'proxies_priv' entry '@% root | 2019-09-20 17:06:57 0 [Warning] 'user' entry 'root@db458b3f56
2019-09-20 17:07:12 0 [Note] InnoDB: Buffer pool(s) load comp | 2019-09-20 17:06:57 0 [Warning] 'user' entry '@db458b3f567a'
2019-09-20 17:07:12 0 [Note] Reading of all Master_info entri | 2019-09-20 17:06:57 0 [Warning] 'proxies_priv' entry '@% root
2019-09-20 17:07:12 0 [Note] Added new Master_info '' to hash | 2019-09-20 17:06:57 0 [Note] Reading of all Master_info entri
2019-09-20 17:07:12 0 [Note] mysqld: ready for connections. | 2019-09-20 17:06:57 0 [Note] Added new Master_info '' to hash
Version: '10.3.17-MariaDB-1:10.3.17+maria~bionic' socket: '/ | 2019-09-20 17:06:57 0 [Note] mysqld: ready for connections.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.lis | Version: '10.3.18-MariaDB-1:10.3.18+maria~bionic' socket: '/
2019-09-20 17:07:14 10 [Warning] 'proxies_priv' entry '@% roo <
<
2019-09-20 17:07:14 0 [Note] mysqld (initiated by: unknown): <
2019-09-20 17:07:14 0 [Note] Event Scheduler: Purging the que <
2019-09-20 17:07:14 0 [Note] InnoDB: FTS optimize thread exit <
2019-09-20 17:07:14 0 [Note] InnoDB: Starting shutdown... <
2019-09-20 17:07:14 0 [Note] InnoDB: Dumping buffer pool(s) t <
2019-09-20 17:07:14 0 [Note] InnoDB: Buffer pool(s) dump comp <
2019-09-20 17:07:16 0 [Note] InnoDB: Shutdown completed; log <
2019-09-20 17:07:16 0 [Note] InnoDB: Removed temporary tables <
2019-09-20 17:07:16 0 [Note] mysqld: Shutdown complete <
<
<
MySQL init process done. Ready for start up. <
<
2019-09-20 17:07:16 0 [Note] mysqld (mysqld 10.3.17-MariaDB-1 <
2019-09-20 17:07:16 0 [Note] InnoDB: Using Linux native AIO <
2019-09-20 17:07:16 0 [Note] InnoDB: Mutexes and rw_locks use <
2019-09-20 17:07:16 0 [Note] InnoDB: Uses event mutexes <
2019-09-20 17:07:16 0 [Note] InnoDB: Compressed tables use zl <
2019-09-20 17:07:16 0 [Note] InnoDB: Number of pools: 1 <
2019-09-20 17:07:16 0 [Note] InnoDB: Using SSE2 crc32 instruc <
2019-09-20 17:07:16 0 [Note] InnoDB: Initializing buffer pool <
2019-09-20 17:07:16 0 [Note] InnoDB: Completed initialization <
2019-09-20 17:07:16 0 [Note] InnoDB: If the mysqld execution <
2019-09-20 17:07:17 0 [Note] InnoDB: 128 out of 128 rollback <
2019-09-20 17:07:17 0 [Note] InnoDB: Creating shared tablespa <
2019-09-20 17:07:17 0 [Note] InnoDB: Setting file './ibtmp1' <
2019-09-20 17:07:17 0 [Note] InnoDB: File './ibtmp1' size is <
2019-09-20 17:07:17 0 [Note] InnoDB: 10.3.17 started; log seq <
2019-09-20 17:07:17 0 [Note] InnoDB: Loading buffer pool(s) f <
2019-09-20 17:07:17 0 [Note] Plugin 'FEEDBACK' is disabled. <
2019-09-20 17:07:17 0 [Note] InnoDB: Buffer pool(s) load comp <
2019-09-20 17:07:17 0 [Note] Server socket created on IP: ':: <
2019-09-20 17:07:17 0 [Warning] 'proxies_priv' entry '@% root <
2019-09-20 17:07:17 0 [Note] Reading of all Master_info entri <
2019-09-20 17:07:17 0 [Note] Added new Master_info '' to hash <
2019-09-20 17:07:17 0 [Note] mysqld: ready for connections. <
Version: '10.3.17-MariaDB-1:10.3.17+maria~bionic' socket: '/ < |
Over 25 minutes later it still hasn't initialized $ docker exec mariadb-18 ps aux
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 1 0.0 0.0 18508 3216 ? Ss 17:06 0:00 /bin/bash /usr/local/bin/docker-entrypoint.sh mysqld
mysql 109 6.2 0.8 2252904 95980 ? Sl 17:06 1:35 mysqld --skip-networking --socket=/var/run/mysqld/mysqld.sock
mysql 152 0.0 0.0 10324 2496 ? S 17:06 0:00 mysql_tzinfo_to_sql /usr/share/zoneinfo
mysql 153 0.0 0.0 13560 1116 ? S 17:06 0:00 sed s/Local time zone must be set--see zic manual page/FCTY/
mysql 154 0.0 0.0 41200 8428 ? S 17:06 0:00 mysql --protocol=socket -uroot -hlocalhost --socket=/var/run/mysqld/mysqld.sock mysql
root 583 0.0 0.0 34400 2780 ? Rs 17:32 0:00 ps aux
$ docker exec --privileged mariadb-18 netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name What it looks like on an initialized 10.3.17: $ docker exec mariadb-17 ps aux
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 1 0.1 0.7 1924884 86576 ? Ssl 17:06 0:02 mysqld
root 577 0.0 0.0 34400 2892 ? Rs 17:32 0:00 ps aux
$ docker exec --privileged mariadb-17 netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp6 0 0 :::3306 :::* LISTEN 1/mysqld One that I had running in the background just finished after 40 minutes $ docker logs mariadb
Initializing database
. . .
Database initialized
MySQL init process in progress...
2019-09-20 17:00:22 0 [Note] mysqld (mysqld 10.3.18-MariaDB-1:10.3.18+maria~bionic) starting as process 109 ...
. . .
2019-09-20 17:00:26 0 [Note] mysqld: ready for connections.
Version: '10.3.18-MariaDB-1:10.3.18+maria~bionic' socket: '/var/run/mysqld/mysqld.sock' port: 0 mariadb.org binary distribution
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
2019-09-20 17:39:34 10 [Warning] 'proxies_priv' entry '@% root@d4a3b782337d' ignored in --skip-name-resolve mode.
. . .
2019-09-20 17:39:44 0 [Note] mysqld: ready for connections.
Version: '10.3.18-MariaDB-1:10.3.18+maria~bionic' socket: '/var/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
2019-09-20 17:39:44 0 [Note] InnoDB: Buffer pool(s) load completed at 190920 17:39:44 |
Running tests from docker-library/official-images#6627 (comment) $ git clone --depth 1 https://github.com/docker-library/official-images.git
Cloning into 'official-images'...
remote: Enumerating objects: 1061, done.
remote: Counting objects: 100% (1061/1061), done.
remote: Compressing objects: 100% (878/878), done.
remote: Total 1061 (delta 63), reused 810 (delta 43), pack-reused 0
Receiving objects: 100% (1061/1061), 1.03 MiB | 0 bytes/s, done.
Resolving deltas: 100% (63/63), done.
Checking connectivity... done.
$ cd official-images/
$ git filter-branch --prune-empty --subdirectory-filter test HEAD
Rewrite bf242b5d33c5bda0e0d0d91d04675942bd9eb179 (1/1) (0 seconds passed, remaining 0 predicted)
Ref 'refs/heads/master' was rewritten
$ ls
clean.sh* config.sh README.md retry.sh* run.sh* tests/
$ ./run.sh mariadb:10.3.17
testing mariadb:10.3.17
'utc' [1/7]...passed
'cve-2014--shellshock' [2/7]...passed
'no-hard-coded-passwords' [3/7]...passed
'override-cmd' [4/7]...passed
'mysql-basics' [5/7]......passed
'mysql-initdb' [6/7]......passed
'mysql-log-bin' [7/7]......passed
$ ./run.sh mariadb:10.3.18
testing mariadb:10.3.18
'utc' [1/7]...passed
'cve-2014--shellshock' [2/7]...passed
'no-hard-coded-passwords' [3/7]...passed
'override-cmd' [4/7]...passed
'mysql-basics' [5/7]................................mariadb:10.3.18 failed to accept connections in a reasonable amount of time!
++ docker logs e2e3e52741d5a3df7265e3536e99b397d0d201573f8320375a8ee4bd22742259
Initializing database
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h password 'new-password'
Alternatively you can run:
'/usr/bin/mysql_secure_installation'
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
Database initialized
MySQL init process in progress...
2019-09-20 18:11:18 0 [Note] mysqld (mysqld 10.3.18-MariaDB-1:10.3.18+maria~bionic) starting as process 109 ...
2019-09-20 18:11:18 0 [Note] InnoDB: Using Linux native AIO
2019-09-20 18:11:18 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-09-20 18:11:18 0 [Note] InnoDB: Uses event mutexes
2019-09-20 18:11:18 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-09-20 18:11:18 0 [Note] InnoDB: Number of pools: 1
2019-09-20 18:11:18 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-09-20 18:11:18 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
2019-09-20 18:11:18 0 [Note] InnoDB: Completed initialization of buffer pool
2019-09-20 18:11:18 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
MySQL init process in progress...
2019-09-20 18:11:20 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-09-20 18:11:20 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-09-20 18:11:20 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-20 18:11:20 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
MySQL init process in progress...
2019-09-20 18:11:20 0 [Note] InnoDB: 10.3.18 started; log sequence number 1631163; transaction id 21
2019-09-20 18:11:20 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-09-20 18:11:20 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-09-20 18:11:20 0 [Note] InnoDB: Buffer pool(s) load completed at 190920 18:11:20
2019-09-20 18:11:20 0 [Warning] 'user' entry 'root@e2e3e52741d5' ignored in --skip-name-resolve mode.
2019-09-20 18:11:20 0 [Warning] 'user' entry '@e2e3e52741d5' ignored in --skip-name-resolve mode.
2019-09-20 18:11:20 0 [Warning] 'proxies_priv' entry '@% root@e2e3e52741d5' ignored in --skip-name-resolve mode.
2019-09-20 18:11:21 0 [Note] Reading of all Master_info entries succeeded
2019-09-20 18:11:21 0 [Note] Added new Master_info '' to hash table
2019-09-20 18:11:21 0 [Note] mysqld: ready for connections.
Version: '10.3.18-MariaDB-1:10.3.18+maria~bionic' socket: '/var/run/mysqld/mysqld.sock' port: 0 mariadb.org binary distribution
++ eval 'echo '\''SELECT 1'\'' | mysql'
+++ echo 'SELECT 1'
+++ mysql
+++ docker run --rm -i --link mysql-container-19063-24904:mysql --entrypoint mysql -e 'MYSQL_PWD=my cool mysql password' mariadb:10.3.18 -hmysql -u0123456789012345 --silent 'my cool mysql database'
ERROR 2002 (HY000): Can't connect to MySQL server on 'mysql' (115)
failed
'mysql-initdb' [6/7]................................mariadb:10.3.18 failed to accept connections in a reasonable amount of time!
. . .
'mysql-log-bin' [7/7]................................mariadb:10.3.18 failed to accept connections in a reasonable amount of time!
. . . |
I think we're hitting this too, for us it manifests an intermittent issue in the CI, which doesn't happen if we pin to 10.1 (which is what I guess we'll do for a bit). |
i'm also hitting this issue. after intial DB initialization, it seems to hang before running the running the /docker-entrypoint-initdb.d/ scripts and root account is not yet configured .
while this was running for a few minutes i notice it was handling the tzinfo data.
|
see #261 (comment), this seems to be related to https://jira.mariadb.org/browse/MDEV-18778 using MYSQL_INITDB_SKIP_TZINFO=1 seems to workaround, but will not have TZINFO data in the database. |
I think both mariadb devs as well as the mariadb docker devs could prevent this in the future my running CI. And don't label mariadb as 'latest' while it is unstable/not workable. This could be prevented I think. I hope you all agree. (I do not want to blame.. but just an open discussion regarding prevention). |
@Danger89, we do run tests that start and connect to the server on every PR here via TravisCI and before it goes to be built for Docker Hub (like docker-library/official-images#6627 (comment)), but this issue was not encountered. The reason the test machine from my build test comment did not see the error is that the hard drive is an NVME SSD and I assume the Travis servers must also be SSD since we have only been able to reproduce the error on a spinning disk. See the |
@yosifkit So to conclude you miss test coverage of hard-drives (spinning disks). |
Yep, we don't have any CI providers which offer that (it's kind of a weird
ask from their perspective; most folks likely want to go the other
direction).
|
hi there, we are experiencing this issue on SSD Storage. (IOPS r 7500/w 5000) |
7500/5000 is still very slow compared to a local nvme ssd that is around 680,000/360,000 iops (https://cloud.google.com/compute/docs/disks/performance#type_comparison) |
#261 is related |
This is now hitting in the latest 10.1 image too, so we're now using the TZ workaround, |
So what is the story here? Is this a confirmed bug? Will this be fixed in a new version of mariadb? Or will a new docker image be published? |
Can someone look into this? Edit: Sad smiley here. |
This is an upstream issue and not something we can fix in the image. The cause is https://jira.mariadb.org/browse/MDEV-18778 and I don't see a new issue linking to it, so it probably still needs to be reported to them. The multiple workarounds are:
|
The same with version 1.5.1. |
I can confirm that this issue still happens on v. 10.4.12 and not in v. 10.3.22 From the possible workarounds i will choose sticking with a working version. Is 10.3.22 the recommended working version then? or is it any of 10.2.27, 10.3.18, 10.4.8? |
Well 10.4.8 still has this same bug according to #261 so I guess I'll stick with 10.3.18 then |
Adding |
* Update trove from branch 'master' - Merge "Fix the slow start of mariadb container" - Fix the slow start of mariadb container Adding '-e MYSQL_INITDB_SKIP_TZINFO=1' when starting database container. Ref: MariaDB/mariadb-docker#262 Change-Id: I3398557700dd76ec831e441ba074032fd4b67cca
Adding '-e MYSQL_INITDB_SKIP_TZINFO=1' when starting database container. Ref: MariaDB/mariadb-docker#262 Change-Id: I3398557700dd76ec831e441ba074032fd4b67cca
I wish someone on here could have put where the file is on the docker mariadb or what command to run? If I run mysql_tzinfo_to_sql on the Mariadb Docker container it just complains that the command doens't exist. Can anyone please help with where or what or when or how that command is to be entered to stop the timezone issue?
Where do you add it to???? |
That's the syntax for adding an environment variable via |
…ases. Appears to be related to MariaDB/mariadb-docker#262.
https://jira.mariadb.org/browse/MDEV-23326 created on the 10.3 -> 10.4 performance drop. |
MariaDB-10.4 defaulted to Aria for system tables. This introduced crash safety under the name of "transactional" that was not previously in MyISAM. The Aria implementation of checkpointing incurs significant penality on fuse-overlayfs that occurs significantly in container environments, especially those without a /var/lib/mysql volume. We work around this penality by disabling the crash safety of timezone tables for the period of timezone initialization. Analysis and timings are in https://jira.mariadb.org/browse/MDEV-23326 and local tests show that 10.4 is only 0.8 seconds slower than 10.3 on startup (6.8 seconds total). Version specific comments are used to ensure that ALTER TABLE statements aren't run on < 10.4 server versions. closes MariaDB#262
MariaDB-10.4 defaulted to Aria for system tables. This introduced crash safety under the name of "transactional" that was not previously in MyISAM. The Aria implementation of checkpointing incurs significant penality on fuse-overlayfs that occurs significantly in container environments, especially those without a /var/lib/mysql volume. We work around this penality by disabling the crash safety of timezone tables for the period of timezone initialization. Analysis and timings are in https://jira.mariadb.org/browse/MDEV-23326 and local tests show that 10.4 is only 0.8 seconds slower than 10.3 on startup (6.8 seconds total). Version specific comments are used to ensure that ALTER TABLE statements aren't run on < 10.4 server versions. closes MariaDB#262
All, to summarise a long issue that you have all endured: Timezone initialization speed has been corrected in: MariaDB 10.1.44, 10.2.31, 10.3.22 because of https://jira.mariadb.org/browse/MDEV-21209 As you all can understand, having multiple issues at the same time can make it easy to loose sight of what is/isn't fixed. On behalf of the MariaDB Foundation I apologise that this upstream issue lingered so long. The docker library team of @tianon and @yosifkit have provided you with an excellent expertise in packaging MariaDB containers for you. To grab the attention of MariaDB developers on issues that are beyond the realm of the container initialization please report those to MariaDB directly https://mariadb.com/kb/en/mariadb-community-bug-reporting/. Retesting the latest versions released yesterday: Start times (Samsung SSD 970 EVO Plus NVME M.2):
Many of you encountered issues under slow disk. So here's the same results on my 5400 rpm Western Digital Green WD40EZRX-00SPEB0:
ref: test script While slower hardware still incurs a relatively high penalty in 10.4+ it is still slow with regard to time zone initialisation, I hope these improvements make it usable if you desire its functionality. Upstream issues continue to remain open to further improve performance of initialization particularly in the 10.4+ If you are encountering any problems regarding unacceptably slow initialization from the versions above or newer onwards please create a new issue. note: @w7089 your galera issue might be solved by some variant of #253. If it is still present in a version above or later than, please create a new issue. |
Aria's crash safety is its key feature over MyISAM. It achieves this crash safety by performing a fdatasync to after each SQL statement. As non-transactional storage, transactional SQL statements are typically ignored. We change that in this commit by only performing the fdataysnc based commit on autocommit statements. When a SQL statement is in a transaction we defer the fdatasync to the COMMIT. The one line that this patches, setting MARIA_HA.autocommit=0 unconditionally, has no external to maria impacts. The impacts within Aria are: * _ma_block_get_status where MARIA_HA gains the same flags as concurrent insert * ha_maria::external_lock, where ma_commit() is skipped if autocommit==0, and MARIA_HA is cleared. An example of the performance gains that can be acheived with this are explified by mysql_tzinfo_to_sql loading into the Aria based timezone system tables. With MDEV-23440, 6991 insert are wrapped in a transaction. Aria mysql_tzinfo_to_sql slowness caused many docker container users to suffer unaccecptable start up time where they usually disabled the initialization (MariaDB/mariadb-docker#262). Currently the work around is to make Aria table TRANSACTIONAL=0 during the load. Before this patch the time taken to load the timezones on some fast NVME, SSD is: $ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ |client/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 0m16.850s user 0m0.881s sys 0m0.592s With this patch applied: $ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ |client/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 0m1.390s user 0m0.171s sys 0m0.087s On slower hardware like Western Digital Green, 5400 rpm, WDC WD40EZRX-00SPEB0 model the load time is: $ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ |client/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 1m14.200s user 0m0.855s sys 0m0.623s With this patch: $ time sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/ |client/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 0m2.826s user 0m0.176s sys 0m0.091s Reference, SQL performed by this invokation (without SET statements): $ 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| Row counts: $ 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
Should hopefully fix dokku#86, as per MariaDB/mariadb-docker#262 (comment).
I am using
mariadb:10.3
, and there seems to be an issue with the current version (10.3 == 10.3.18 == sha256:17a2a44fb692a7a4b244b104de0f286b51b29083b794ae964e3ed5aab8259358), where the MySQL init process is stuck when running a "fresh" docker (I mean, with no existing database in a volume).For example, with 10.3.18 the output looks like this:
Whereas, with 10.3.17, the output looks like this:
Here is the full output when using 10.3.18:
The text was updated successfully, but these errors were encountered: