Weird Myqsl slowness depending on version and FS

1

Hi,

Exploring the possibilities of LXD (3.23 via snap under Debian 10.3), I faced unexpected real-slowness of MariaDB depending on the environment.

All the container below are raw install, with then mariadb-server and mariadb-client installed, and then I run this test (on a Ryzen 3700x, on SSD, with 32GB ram, kernel is 4.19 on both Debian 9 and 10):
mysqlslap --no-defaults --user=root --password --host=localhost --auto-generate-sql --verbose --concurrency=50 --iterations=10
Results below show number of seconds to run all queries.

Average Min Max Environment
0.063 0.057 0.080 Host (Debian 10, mariadb 10.3.22, ext4)
0.039 0.032 0.085 Host (Debian 10, mariadb 10.3.22, btrfs)
0.053 0.036 0.109 CT 1 (Debian 9, mariadb 10.1.44, btrfs)
4.691 1.320 9.896 CT 1 (Debian 9, mariadb 10.3.2, btrfs)
0.044 0.027 0.106 CT 1 (Debian 9, mariadb 10.3.2, btrfs but /var/lib/mysql bind-mounted by LXD on ext4)
14.295 2.236 36.621 CT 2 (Debian 10, mariadb 10.3.22, btrfs)
0.043 0.029 0.121 CT 2 (Debian 10, mariadb 10.3.22, btrfs but /var/lib/mysql bind-mounted by LXD on ext4)

So

  • MariaDB 10.3 on host performs equally well on BRTFS and EXT4.
  • MariaDB 10.3 in container under BTRFS without tuning is very very slow. I expected that copy on write would involve penalty, but not with that order of magnitude. Is that normal?
  • But what’s weird was that MariaDB 10.1 in container under BTRFS is quick, as quick as with EXT4.
    Any advice? Should I rather open an issue on BTRFS bugtracker or on MariaDB bugtracker (which one?)?

Your opinion is welcome!

2

I have tried this on Ubuntu 18.04, LXD 3.23, ZFS on Linux 5.3.

$ lxc launch ubuntu:18.04 mysql --ephemeral
$ lxc ubuntu mysql
ubuntu@mysql:~$ sudo apt update
ubuntu@mysql:~$ sudo apt install -y mysql-server
ubuntu@mysql:~$ sudo mysqlslap --user=root --password --host=localhost --auto-generate-sql --verbose --concurrency=50 --iterations=10
Enter password: 
Benchmark
	Average number of seconds to run all queries: 0.052 seconds
	Minimum number of seconds to run all queries: 0.038 seconds
	Maximum number of seconds to run all queries: 0.075 seconds
	Number of clients running queries: 50
	Average number of queries per client: 0
ubuntu@mysql:~$

Which looks good, because the times are sub-0.1second.

I repeated the same with mariadb, and the times were still sub-0.1second. The command was changed to the following, as I was getting some likely irrelevant error (unknown variable ‘default-character-set=utf8mb4’).

ubuntu@mariadb:~$ sudo mysqlslap --no-defaults --user=root --password --host=localhost --auto-generate-sql --verbose --concurrency=50 --iterations=10
3

Thanks, @simos . Could/woud you try with a Debian CT ?

[Edit] I tested like @simos with a Ubuntu 18.04 container. Result is blazing fast, with average of 0.043 seconds.
So the issue lies in a combination of Debian 10 container + BTRFS + LXD
[Edit2] Conclusions above were wrong:

  • Ubuntu 18.04 installs MariaDB 10.1.44, same version as in Debian 9 and same speed
  • If I install the more recent 10.3.22 version of MariaDB on Ubuntu 18.04, I get again very slow speed, on average 12s.

So the conclusion is that MariaDB >10.1 on BTRFS in LXD is slow

For info, MariaDB 10.1 will end of life in October 2020.

4

I tried with lxc launch images:debian/10 debian and still got sub 0.1s times, with mariadb-server-10.3.

5

Thank you Simeon to have tested Debian as well. I’m lost where to search. I saw nothing in logs (mysl, syslog, dmesg) of both host and container. I can of course choose the easy way of using one of the running combinations from above, but well, I’m curious about this. Any suggestion?

[Update] Adding more verbose to the mysqlslap command (-vvv below) shows that each and any sql statement is actually slow: create database, insert of a single row…

mysqlslap --no-defaults --user=root --password --host=localhost --auto-generate-sql --concurrency=50 --iterations=10 -vvv

6

You would need to check with mysql-server as well. By doing so, you will have an indication whether it is an issue restricted to the SQL server software.

It would be good if you could get someone that has LXD on btrfs on Debian to test for you. It is important to check whether this issue is somehow restricted to your btrfs setup. If someone is reading this and have LXD on Debian and use a btrfs storage pool, they can easily perform the test.

Note that it is likely that this issue is not a LXD issue. Further testing will show what is going on.

There are tools that can give insight in what happens on your system. One of them is sysdig, at https://sysdig.com/opensource/ You can compare between a good case (older version of mariadb) and a bad case (newer version of mariadb).

7

Thanks a lot Simeon for the idea - and also by the way for your blog, I learned a couple of interesting tips on it!

On a raw debian 10.3 server running btrfs on root (on hard disk, not SSD), with LXD 3.23 also, I get an average of 0.5-0.7 seconds on mariadb 10.3 in a debian 10 container. So, yes, this is my main server which is faulty… I just have to find what!