ZFS zfs for mysql - separate data and logs? what logs? where are these logs?

rwv37

rwv37

I'm about to set up mysql on a machine, and I know that in the past I've read about setting up specifically tweaked dedicated zfs datasets for it. I've never done that for mysql before, but I figure now if ever is the time to try it out. So, I searched for info about this to refresh my memory. Everything I found so far suggests setting up separate datasets for mysql's data and for its logs, with different tweaks on each (this matches with my memories of having read about it in the past). But it looks to me like both the data and the logs are (by default) in /var/db/mysql? The data being in a separate subdirectory per db, but the logs just being splayed out along with various other stuff in the main directory?

Obviously I can set up individual datasets for each database, and mount them to subdirectories of /var/db/mysql, and could do that even while giving them different zfs tweaks than the apparent log directory, i.e. their parent path, i.e. /var/db/mysql itself. But I feel like I might be misunderstanding. For one thing, there are a bunch of things directly in that directory other than logs. For another, the suggestions I've seen, if they say anything specific about the actual location of the logs at all, show them as being in their own dedicated directory.

Are the logs in /var/db/mysql really "the" logs, i.e. those for which everything I've seen recommends setting up a different zfs dataset with different tweaks for?
 
SirDice said:

MySQL :: MySQL 8.0 Reference Manual :: 7.4.4 The Binary Log

dev.mysql.com dev.mysql.com
Click to expand...

Yes, those are among the logs (and other things) that are directly in /var/db/mysql. So I'm sorry if I'm being dense, but I still feel like I may be misunderstanding. I'll try to be more clear about what I'm wondering: Is it the recommended zfs + mysql configuration on FreeBSD supposed to be "zfs dataset for /var/db/mysql, tweaked as per instructions on how to tweak a zfs dataset for use with mysql logging" plus "individual zfs datasets per database subdirectory of /var/db/mysql tweaked as per the other instructions on how to tweak a zfs dataset for use with mysql data"?
 
Thank you!

What was (and to an extent still is) confusing me is that (by default) log-bin is just mysql-bin, so all those files are just directly in /var/db/mysql/, not in any subdirectory (or an entirely different directory). For example, right now on one machine where I have mysql set up with (essentially) the default my.cnf, there are files /var/db/mysql/mysql-bin.000284, /var/db/mysql/mysql-bin.000285, /var/db/mysql/mysql-bin.000286, etc. right there alongside the directories /var/db/mysql/somedatabaseofmine, /var/db/mysql/someotherdatabaseofmine, etc.

Also, the binary logs are not the only files that are directly in /var/db/mysql by default (e.g. a bunch of certificate/key files), nor are they even the only files in there that are (apparently) "logs". There are at least three different variables in my.cnf.sample that are all set by default to /var/db/mysql (datadir, innodb_data_home_dir, and innodb_log_group_home_dir). And that's not to mention this log-bin setting, which (since it's just a relative path in the default) winds up sticking things in that same directory by default. It's not yet clear to me which type or types of "logs" should be set up differently in zfs.

And there are other possible "log" settings (like slow_query_log_file and log_error) that aren't in my.cnf.sample at all, with that lack seeming to result in those things going into /var/db/mysql/ directly, too.

Seems like it might be good (from the point of view of easing creation of zfs data sets for other databases, at least, to add in a /var/db/mysql/data level? That way I can just inherit from it when setting up a new zfs dataset for a new database. I guess I should try to figure out which of the three (or possibly more) options that are set by default to /var/db/mysql is the one (well, hopefully "the" one) that results in the db subdirectories being created there, change that to /var/db/mysql/data, and tweak the zfs settings for the dataset for that one directory as recommended for mysql data on zfs?

Since I'm still confused regarding exactly what counts as a "log" for these purposes, and moreover what to do with all the "other" stuff (not to mention how to do it), I'm thinking that maybe at least for now it would be best if I were only to worry about setting up those actual database subdirectories specially? Leaving everything else as it is by default, both from the mysql point of view (i.e. like my.cnf.sample, just dump pretty much everything directly in /var/db/mysql) and from the zfs point of view (i.e. do the recommended zfs tweaks for mysql data on the database subdirectories, but just use the zfs defaults for logs and everything else).
 
rwv37 said:
What was (and to an extent still is) confusing me is that (by default) log-bin is just mysql-bin, so all those files are just directly in /var/db/mysql/, not in any subdirectory (or an entirely different directory).
Click to expand...
The default location for binary log files and the binary log index file is the data directory. You can use the --log-bin option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory.
Click to expand...
Granted, it's a little further down the text.

rwv37 said:
And there are other possible "log" settings (like slow_query_log_file and log_error) that aren't in my.cnf.sample at all, with that lack seeming to result in those things going into /var/db/mysql/ directly, too.
Click to expand...
The thing you're missing is when MySQL/MariaDB talks about splitting data and logs they're actually referring to binlogs. It's truly confusing. What's being referred here are the 'traditional' text based logs, for errors or slow queries. Older versions (on FreeBSD) used to log those to the DB "base" directory (/var/db/mysql), nowadays it's more common to save those to /var/log/mysql/ for example. That brings it more in line with logging from other services.

rwv37 said:
Since I'm still confused regarding exactly what counts as a "log" for these purposes, and moreover what to do with all the "other" stuff (not to mention how to do it), I'm thinking that maybe at least for now it would be best if I were only to worry about setting up those actual database subdirectories specially?
Click to expand...
Yeah, I honestly have stopped worrying about splitting them up. Just have the /var/db/mysql/ dataset contain everything. I do apply some of the caching settings and MySQL/MariaDB tweaks, just skip the splitting of data and (bin)logs.

Most of the performance gain will be gotten from properly configuring buffer_pool and the various caches within MySQL/MariaDB itself. If possible the whole database should comfortably sit in RAM any way.
 
SirDice said:
Granted, it's a little further down the text.
Click to expand...
To (try to) be clear, I wasn't saying I was confused by the idea that they could be there, or could be elsewhere. The root of my confusion is that, by default, mysql on FreeBSD just dumps pretty much everything, log or not (not to mention seemingly entirely different types of logs or not) into /var/db/mysql. So the various recommendations on the web that say to apply certain zfs tweaks to "the log directory", which they refer to as if it's totally standard, don't really correspond to anything that's reflected by FreeBSD's defaults.

Anyway, thanks again for your help. I think I have a firmer grasp on the situation now.
 
rwv37 said:
There are at least three different variables in my.cnf.sample that are all set by default to /var/db/mysql (datadir, innodb_data_home_dir, and innodb_log_group_home_dir).

(...)

I guess I should try to figure out which of the three (or possibly more) options that are set by default to /var/db/mysql is the one (well, hopefully "the" one) that results in the db subdirectories being created there
Click to expand...

For the benefit of anyone who finds this thread in the future: The answer seems to be "none of them". At least not with the default install on FreeBSD.
  • datadir: Has no effect at all, as far as I could see. I guess it probably has some effect, but it certainly didn't cause anything to be created in the specified directory. Everything that by default would be created in /var/db/mysql was still created in /var/db/mysql, and nothing at all was created in datadir.
  • innodb_data_home_dir: Created a couple ib* files (I think ibdata1 and ibtmp1 in the specified directory, and nothing else. Everything else that by default would be created in /var/db/mysql was still created in /var/db/mysql, and nothing besides those couple ib* files was created in innodb_data_home_dir.
  • innodb_log_group_home_dir: I didn't actually try this one, as its name makes it seem totally unrelated to the db directories.
However, I finally got it working at least close enough to what I want: I just made my.cnf be a totally unmodified copy of my.cnf.sample (in which all three of those settings are /var/db/mysql, and (thanks to a post literally from 2009) instead set something in /etc/rc.conf: mysql_dbdir="/stuff/mysqldbs". After doing that, almost everything (including the db subdirectories, which are what I really wanted elsewhere) was created in the specified directory, and almost nothing was created in /var/db/mysql (despite what my.cnf had to say).

I guess I'm almost certainly missing something here, but the default mysql setup on FreeBSD seems like a mess, frankly.
 
I’ve not had any trouble with the default file locations until the last few years when it became necessary to have at least the data directory on an encrypted file system.

The defaults are defaults - they will work fine UNLESS you want to do something more specific or follow something on the web.
 
Maybe also of interest for ZFS Database tuning: Tuning OpenZFS by Allan Jude and Michael Lucas; Usenix 2016.
However, regarding MySQL directories and SirDice's remark "Older versions [of MySQL/MariaDB] (on FreeBSD) used to log ...", I don't know how things have changed over time.

For completeness, there's also relevant info (o.a. database section) at the OpenZFS website: Workload Tuning - Database workloads.
 
rwv37 said:
datadir: Has no effect at all, as far as I could see. I guess it probably has some effect, but it certainly didn't cause anything to be created in the specified directory. Everything that by default would be created in /var/db/mysql was still created in /var/db/mysql, and nothing at all was created in datadir.
Click to expand...
That's due to the way the rc(8) script starts the service. The command line --datadir overrules whatever is set in my.cnf. If you want/need to change it you need to set mysql_dbdir in rc.conf.

Code: 
# mysql_dbdir (str):    Default to "/var/db/mysql"
#                       Base database directory.
{...}

command_args="-c -f /usr/local/bin/mysqld_safe ${mysql_extra} --basedir=/usr/local --datadir=${mysql_dbdir} --pid-file=${pidfile} --user=${mysql_user} ${mysql_args}  "
 
SirDice said:
Yeah, I honestly have stopped worrying about splitting them up. Just have the /var/db/mysql/ dataset contain everything. I do apply some of the caching settings and MySQL/MariaDB tweaks, just skip the splitting of data and (bin)logs.
Click to expand...
and how do you set different recordsize then? You keep all 16K? If you do, thanks
 
This is the simplest procedure I came up so far, I wrote the steps in a simple script called mysql-zfs-data-and-logs.sh, this is the story:
sh: 
#!/bin/sh

#
# Run this script via sudo or as root.
#
# default recordsize is 128k, we change it only for /var/db/mysql/innodb
# where the databases life (see note in https://wiki.freebsd.org/ZFSTuningGuide#MySQL):
#
# % zfs get recordsize
#
# NAME                                        PROPERTY    VALUE    SOURCE
# zroot/mysql                                 recordsize  128K     local
# zroot/mysql/innodb                          recordsize  16K      local
#
# compression can be on, which defaults to lz4, you are likely to be able
# to decompress the data much faster than your SSD can read the compressed data

# cp -a /usr/local/etc/mysql/my.cnf.sample /usr/local/etc/mysql/my.cnf
mkdir -p /var/log/mysql

sysrc mysql_enable="YES"
sysrc mysql_dbdir="/var/db/mysql/innodb"
sysrc mysql_args="--innodb-doublewrite=0 --log-error=/var/log/mysql/mysqld.err --innodb-log-group-home-dir=/var/db/mysql/log --general-log-file=/var/db/mysql/log/general.log --log-bin=/var/db/mysql/log/mysql-bin --relay-log=/var/db/mysql/log/relay-log --slow-query-log-file=/var/db/mysql/log/slow.log"

zfs create zroot/mysql        # placeholder for default values
zfs create zroot/mysql/innodb    # /var/db/mysql/innodb (recordsize=16k)
zfs create zroot/mysql/log    # /var/db/mysql/log (inherits recordsize=128k)

zfs set mountpoint=none compression=on recordsize=128k atime=off primarycache=metadata zroot/mysql
zfs set mountpoint=/var/db/mysql/innodb recordsize=16k zroot/mysql/innodb
zfs set mountpoint=/var/db/mysql/log zroot/mysql/log

pkg install -y mysql84-server

chown -R mysql:mysql /var/db/mysql/log /var/log/mysql

# After you run this script we can start the server:
#
# % sudo service mysql-server start
#
# it will fail because we need first to move some files:
#
# % sudo mv /var/db/mysql/innodb/\#innodb_redo /var/db/mysql/log
#
# Start mysql-server again, should be fine now
 
You must log in or register to reply here.
Back
Top