Single sqlserver jail+ZFS for other jails, but how?

[kalleboy]

Greetings.

I have few jails running nginx on my FreeBSD 13.1 host. I'd like to create another jail for their SQL database needs.

I'd like to create ZFS dataset for this purpose (for MySQL jail) but should I create it on host? If the dataset is created within the host, could the Jail access it? Is it possible to point it within a Jail? My jails are under /jails folder in root. Or it should be created within the jail?

It's like:
/jails/web1 - 10.10.10.2
/jails/web2 - 10.10.10.3
/jails/web3 - 10.10.10.4
/jails/sqlserver

 

[SirDice]

There are different ways to solve this. One way is to create the filesystem on the host and use nullfs(5) to mount it on the jail. If you have ZFS you can create a dataset and use zfs-jail(8) to assign it to a jail. Then you can mount it directly in a jail. Which one to use depends on your use case, how you want to keep track of the data.

 

[kalleboy]

Thanks a lot SirDice , nullfs helped a lot, have been reading about it after your post. Works fine now.

I'm confused on mountpoints/folders/recordsizes right now.

When all those 3 datasets below, are created&mounted into a single folder; /jail/sqlsrv/, do they still have their own "recordsize" kept? (128k recordsize for the folder /jails/sqlsrv/srv, but 16k recordsize for the folder /jails/sqlsrv/db BUT actually they're inside the same folder which is; /jail/sqlsrv/ , that confuses me) Or that's a wrong way doing it?

zfs create -o mountpoint=/jails/sqlsrv/srv -o atime=off -o compression=lz4 -o primarycache=metadata -o recordsize=128k zroot/sqlsrv
zfs create -o mountpoint=/jails/sqlsrv/db -o logbias=throughput -o recordsize=16k zroot/sqlsrv/db
zfs create -o mountpoint=/jails/sqlsrv/logs -o compress=gzip1 -o primarycache=none zroot/sqlsrv/logs

Thanks.

 

[SirDice]

Each dataset can have its own recordsize. It doesn't matter where or in what order they are mounted, the recordsize is set on the dataset, not the mountpoint. The order of the datasets does matter, zdata/somedir would inherit the recordsize from zdata, unless you specifically set the recordsize on zdata/somedir.

In your case zroot/sqlsrv/logs inherits the (128K) recordsize from zroot/sqlsrv.

 

[rootbert]

as a side note, you should really avoid compress=gzip1 and use zstd-1 instead

 

[kalleboy]

SirDice thanks a lot for such a great info. Much appreciated.

Hi rootbert zstd-1 for mysql log files? Is there any specific reason for this? Many thanks.

 

[rootbert]

you can use any compression from zstd-1 to zstd-19 where zstd-19 offers the slowest but best compression. In general, zstd is much faster (~4x) than gzip and also offers better compression rates. If speed is your most important concern then lz4 is your best option.

 

[kalleboy]

Thanks a lot, rootbert . Noted.

So, in my case, "datadir=" of mariadb should be "/jails/sqlsrv/db" or "/jails/sqlsrv/"?

 

[SirDice]

So, in my case, "datadir=" of mariadb should be "/jails/sqlsrv/db" or "/jails/sqlsrv/"?

The db directory. Note that the MySQL/MariaDB 'log' directory isn't for traditional 'text' based logging, it's used for binlogs.

Here's a nice article about tuning MySQL (most of it applies to MariaDB too) and ZFS: https://shatteredsilicon.net/blog/2020/06/05/mysql-mariadb-innodb-on-zfs/

 

[kalleboy]

Great article, full of details and technical background SirDice thanks a lot. Bookmarked it.

And regarding the "log" directory of mine, it's defined as (under MariaDB's server.cf file):

innodb_log_group_home_dir = /jails/sqlsrv/logs

but I guess I'll actually going to have "skip-log-bin" too, as I don't have Replication (no master/slave servers - just a single jail sql server for wordpress), and for recovery purpose, I nightly take backups to an external drive. Under these circumstances, is it safe to disable Binary Logging?

So I assume "innodb_log_group_home_dir" points it correctly, right?

Edit: Wait, it seems log-bin is not enabled by the default, already.

root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_bin';
| log_bin | OFF |