First run is very slow with MariaDB

[neilbags]

Using the docker-compose MariaDB examples with no modification, the web container can't connect to MariaDB for 5-10 minutes. Then creating the admin account and logging in takes about another minute. Everything is very fast thereafter.

I've tried this on 3 different machines with different distros and sunk hours into trying to figure out what is wrong. Is anyone else experiencing this?

[J0WI]

NextCloud takes some time to initialize all tables. The individual performance depends on many factors, so it's hard to guess what your bottleneck is.

[neilbags]

Its MySQL specific. No issues at all with Postgres

[J0WI]

Is it Docker specific?

[neilbags]

Yes

[J0WI]

I wasn't able to reproduce this so far. Could you post some details about your Docker network, version numbers, logs etc.? You may also try to replace MariaDB with MySQL to compare their performance. This would mean that the issue exists in https://github.com/docker-library/mariadb.

[neilbags]

Thanks for looking at this.

Ive tried on three different machines - debian, ubuntu and fedora and have tried MySQL instead of mariadb. All my systems are btrfs which is the only common link I can think of. I suppose it could be network related but I'm not having issues with anything else.

[J0WI]

Are your containers connected over host, bridge or --link?
You may have a look at the btrfs-driver documentation. There are still some limitations. As far as I know overlay2 is the recommend driver:

In certain circumstances, overlay2 may perform better than btrfs as well.

See also https://coreos.com/os/docs/latest/btrfs-troubleshooting.html#disable-copy-on-write

[neilbags]

I'm using the docker-compose examples provided by this project, so that would be --link

And I mean the host filesystem is btrfs. I haven't actually changed anything in docker, just run docker-compose -d up

[infomaniac50]

There is something about MariaDB that is making it populate the time zone tables. This shouldn't be running automatically, because there is a script for that. https://mariadb.com/kb/en/library/mysql_tzinfo_to_sql/

When I run SHOW processlist;, I get a bunch of queries like

INSERT INTO time_zone
INSERT INTO time_zone_name
INSERT INTO time_zone_transition
INSERT INTO time_zone_transition_type

[infomaniac50]

It tried this on MySQL 5.7 without --transaction-isolation=READ-COMMITTED and it booted fairly quickly. My host filesystem is also BTRFS.

I'm guessing there are different defaults for MariaDB and MySQL.

[infomaniac50]

I just tested this on a normal docker install and MariaDB took 5 minutes to initialize the time_zone tables. The backing filesystem is ext4.

The fstab entry looks like this: /var/lib/docker ext4 rw,noatime,data=ordered

[J0WI]

Seems to be a duplicate of MariaDB/mariadb-docker#262
Can you confirm that hosts with NVME SSD are not affected?

[infomaniac50]

The only Docker machine I have uses a SATA SSD. My two machines with NVMe SSDs run Windows 10.

I'm not sure if Docker on Windows can properly test this. The thing I worry about is messing up access times. VirtualBox abstracts away some of the latency when doing defragmentation. I don't know if that would have any effect on MariaDB.

[infomaniac50]

I tested this again on NVMe. Docker is running on an Ubuntu virtual machine. Ubuntu is 18.04.3 LTS and Docker is 18.06.1-ce. I created the virtual disk drive using the fixed size option. The host is running Windows 10.

Using mariadb:latest the first boot takes around 1 minute. Windows Task Manager reports disk usage no higher than 5MB/s. The HDD LED is also blinking very visibly. Putting a large strain on the NVMe drive will make the LED turn on persistently.

MariaDB is doing something weird on the first boot. It's not using anything near the maximum IO capacity for this drive. I don't know if VirtualBox or Docker abstracts any of this away from MariaDB.

[infomaniac50]

The docker-entrypoint script is causing slowness on first boot. It's loading time zone info by default, so my first comment was correct.

See also: https://github.com/docker-library/mariadb/blob/42559604de17559919e34cdeb4772bac8215101d/10.4/docker-entrypoint.sh#L122

If you want MariaDB to initialize quickly then add MYSQL_INITDB_SKIP_TZINFO=true to your environment variables. Actually any non-zero length string will work. e.g. MYSQL_INITDB_SKIP_TZINFO=1 or MYSQL_INITDB_SKIP_TZINFO=moar_speed

See also: MariaDB/mariadb-docker#261

Note: By skipping time zone setup, you lose access to that information. I don't know if NextCloud uses this information. So it's best to be cautious.

With the time zone database disabled, MariaDB initializes in 9 seconds. This is in contrast with it enabled, taking 1 minute on a Docker VM on NVMe and taking 5 minutes on native Docker on a SATA SSD.

[J0WI]

Databases do not perform well on CoW filesystems. Maybe that's also the case for your Docker VM? I don't know what VirtualBox uses by default.

[infomaniac50]

VirtualBox runs on top of the host operating system. In my case that would be Windows 10. The guest OS is Ubuntu 18.04.3 and the filesystem is ext4.

The disk file is one large 10GB contiguous block. I made the disk file a fixed size so NTFS wouldn't fragment it. From the VMs point of view it should operate just like a regular block device.

To get MariaDB to boot faster you have to add MYSQL_INITDB_SKIP_TZINFO to your environment variables. @J0WI Can you confirm whether or not NextCloud uses the time zone tables?

[J0WI]

Closing as duplicate MariaDB/mariadb-docker#262