SQL performance in LXD container

#1

Hey there,

so i started my LXD journey a couple of days ago and after getting familiar with the command line tools to manage my containers, i started to put it into practice on Ubuntu LTS 18.04.4 LTS and lxd version 3.03.
As i created a seperate mariadb-server container and configured it, so that i can connect remotely (aka from another container) to it, i noticed that the connection to the sql-server from another container was quite slow. Occasionally the service that i set all this up for, wasn`t able to connect at all to the database.

Eventually, i was able to find out, that the slow connection seemed to be DNS related so i disabled mysqls name resolution (skip-name-resolve) and created a database user with an ip adress as its hosts part instead of a hostname. After that the connection tests i did with mysqlclient were significantly faster and the problems were gone.

That made me curious and maybe thats a little bit of a vague question but judging from your experience which is certainly more than i have, is it maybe not a good idea to “containerize” mysql?
Or to put it differently, do i have to be aware of any particular tuning that has to be done so that mysql can run competitively fast in a container as compared to running “on metal”?

Is there something i can do to improve dns performance inside my containers or can you explain what might be the issue here? Is there something like an established “best practice” when it comes to using mysql inside a container?

#2

Your SQL server should work just fine. You need to focus on this DNS-related issue.
Can you post instructions on how to replicate a minimal setup that demonstrates the issue that you are facing?

#3

@simos

I ll try to give you an acurate description of my setup. This post will be a little bit longer, i am sorry for that.

*IPv6 is disabled on the host system by editing the grub file. (ipv6.disable=1)

Container c1-sql

  • Image: Ubuntu 18.04 LTS amd64 (8c4e87e53c02)
  • apt-get update & upgrade inside the container
  • mariadb-server 10.4 installed
    IPv4: 10.10.10.10
    IPv6: disabled

Container c2-seafile
Image: Ubuntu 18.04 LTS amd64 (8c4e87e53c02)

  • apt-get update & upgrade inside the container
  • seafile-pro server installed
    IPv4: 10.10.10.20
    IPv6 disabled

The default profile is attached to both containers and therefor to lxdbr0.
lxdbr0 configuation is as follows:

ipv4.address: 10.10.10.1/24
ipv4.nat: “true”
ipv6.address: none
description: “”
name: lxdbr0
type: bridge

I ve assigned static ip adresses for both containers by using
config device override $container eth0 ipv4.address=10.x.x.x

Scenario 1: (DNS enabled)
Ok, so i created my databases and a mysql user and granted this user all permissions on the seafile databases. In this scenario the sql user was created as username@c2-seafile.lxd.
Reverse DNS in my.cnf was left untouched (enabled by default) I also bound 0.0.0.0 to mysql so that i can connect to the database server from another container.
After i ve set up seafile and running the the seafile start script it will give me the following error message:

Failed to get database connection: Failed to connect to MySQL: Lost connection to MySQL server at ‘handshake: reading inital communication packet’, system error: 110.

If i try again immedaitely after, the script starts without any errors.
I can reproducve this by rebooting the hosts and repeating the steps i just described.

Scenario 2: (DNS disabled)
Same as in scenario 1 only this time i disabled dns lookups in mysql by adding “skip-name-resolve”
in the mysqld section of the my.cnf configuration file.
After that i dropped the database user (user@containername.lxd) from scenario 1 and instead created
username@10.10.10.20, so replacing the host part with an ip adress. After that i reassigned the privileges
for the seafile databases to this new user and made a test again.
Even after several cycles of rebooting and running the script, i wasn`t able to reproduce th error messager anymore.

This may seem like the cause for this problem lies within seafile and thats what i was thinking first but after trying to connect to my sql database remotely with
mysql -h 10.10.10.20 -u seafile -p"whatever" $database

i noticed a few seconds delay until the connection was established when dns resolving is active.
When i did this with “skip-name-resolve” in the config file and systemctl restart mysql, the connection came up almost instantely.

Sure, i got this working after some of trial & error but i am curious if i just made a configuration mistake or missed something and i am sure the dns resolving should work just fine inside lxd containers and that the delay i was experiencing isn`t supposed to be normal.

Thank you for taking the time simon. :slight_smile:

edit:
Of course i understand that this is difficult to reproduce for you, by using seafile as an example but you can easily reproduce this with the mysql command.
(e.g mysql -h 10.10.10.20 -u testuser -p"whatever" testdb)
With the DNS resolver enabled (mariadb default settings) it takes about 5 seconds until i get to the mysql prompt, with “skip-name-resolve” set in the configuration file, there is almost no delay whatsoever.

#4

Putting aside the specific network problem that prompted this post, I’ve been using mariadb in containers for a long time now and I will continue doing so, even if performance suffers somewhat. You can do some benchmarks and decide for yourself. I determined that mariadb on a zfs container was perhaps twice as slow as mariadb directly on ext3, but the ability to have snapshots and isolation from other applications is well worth the performance hit, for me.

After noticing that my mariadb container was reaching a limit on i/o, I moved the /var/lib/mysql to an attached zfs device with a 16k recordsize and the mariadb log to a zfs device with a 128k recordsize, as recommended in various places that I consulted. My benchmarks showed that this indeed performed better, but since I also did other changes to my setup, I never found out if my original performance problem was caused by incorrect filesystem tuning or something else.

#5

@votsalo
this is interesting.
When you say “twice as slow” did this also happen if you installed mariadb on a zfs pool but instead of running it inside a container, doing so on the host system directly?
Did you (just for comparision) tried it with btrfs as well?
Was this an zfs pool made out of ssd(s) or regular hdd(s)?

Can you maybe link me those informations where you found out whats considered “best practice” when installling mysql inside a container thats configured to use zfs?

Lots of questions ^^
Thank you.

#6

The recommendations were for running mariadb/mysql on zfs, not specifically on containers:
http://www.open-zfs.org/wiki/Performance_tuning#InnoDB

I have published some of my benchmarks here, but they were of limited scope (just loading a database, no heavy network operations):


(November 2018, last comment).
I used a variety of magnetic disks and network disks, whatever was available on the systems I tested.
These were without tuning the recordsize.

#7

If you are interested, I added some figures about MariaDB performance in LXD versus on host, and on EXT4 versus BTRFS, here: Weird Myqsl slowness depending on version and FS