Recommended LXD mariadb container configuration?

1

Is there a recommended way to configure a mariadb container on LXD, in order to avoid disk i/o bottlenecks? What do you do?

I have a host with a mariadb zfs container which runs various mariadb databases that other containers are using (web applications). I have two disks with mirrored zpool configuration. Lately, the load on the host goes sky high (~ 30 or more) with high wait times. iostats shows 100% disk utilization and iotop shows mysqld is responsible. I read about tuning mariadb on ZFS, and I already setup these mariadb configuration parameters:
skip-innodb_doublewrite
innodb_use_native_aio=0
innodb_use_atomic_writes=0

These seem to help, but I occasionally still get 100% disk usage and high uptime load times.

I also read that it’s best to use separate zfs datasets for the Innodb table data, with recordsize=16K and for the Innodb log files, with recordsize=128K. How would I set these record sizes with LXD? Is it worth it to attach and configure two additional zfs devices to the container (for data and logfiles) with these recordsizes? Could I just set recordsize=16K for the whole container?

Should I move the mariadb container to an ext4 filesystem (with dir LXD storage)? If I did that, I would lose snapshots.

2

This is how I ended up setting a mariadb container, for use by other containers:
Create two zfs filesystems on the host, with record sizes 16K and 128K, as recommended.
Assuming that these two filesystems are z/mysql/data and z/mysql/log, I created a mariadb profile:

config:
  raw.idmap: |
    uid 111 111
    gid 115 115
description: ""
devices:
  data:
    path: /var/share/mysql/data
    source: /z/mysql/data
    type: disk
  log:
    path: /var/share/mysql/log
    source: /z/mysql/log
    type: disk
name: mariadb
used_by: []

The uid/gid is the mysql uid/gid in a Ubuntu 18.04 container with mariadb installed.

  • Attach this profile to the mariadb container.
    Prepare the mariadb container:

  • service mysql stop

  • verify that the owner:group of /var/share/mysql/data and /var/share/mysql/log are mysql:mysql

  • rsync -av /var/lib/mysql/ /var/share/mysql/data/

  • mv /var/share/mysql/data/ib_logfile* /var/share/mysql/log/

  • Append this to /etc/mysql/my.cnf:

    [mysqld]
    skip-innodb_doublewrite
    innodb_use_native_aio=0
    innodb_use_atomic_writes=0

    datadir = /var/share/mysql/data
    innodb_log_group_home_dir = /var/share/mysql/log
    bind-address = 0.0.0.0

  • service mysql start

  • Verify that the correct filesystems are used by creating a database and checking that it is created in /var/share/mysql/data/

For snapshots, use zfs snapshots of the two filesystems (which can be done recursively on their parent filesystem). To make sure the snapshots have consistent data, use something like this:
echo "flush tables with read lock;" | ssh mariadb.lxd sudo mysql | sudo zfs snapshot -r z/mysql@{name}

3

I suspect the issue is due to MariaDB tuning (or lack thereof). Suggest you download and run the MySQL tuner perl script and make sure the DB is properly tuned. https://github.com/major/MySQLTuner-perl

4

Here is how I did it:

lxc storage volume create data nextcloud-data
zfs set recordsize=16k ssd/lxd/custom/database-data
lxc storage volume attach default database-data database /var/lib/mysql-data

lxc storage volume create data nextcloud-logs
zfs set recordsize=128k ssd/lxd/custom/database-logs
lxc storage volume attach default database-data database /var/lib/mysql-logs
5

Hi @Lox,

In your pasted code, you seem to mix ‘nextcloud-data’ and ‘database-data’, and the same with the logs. Is that a mistake?

I am trying to correlate the creation of the LXC storage volume and where you then set the ZFS record size.

I am a bit fresh to this stuff, by the way, so apologies if the answer is obvious.

Cheers,
Whytey