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

[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]

separate data and logs? what logs? where are these logs?

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

dev.mysql.com

 

[rwv37]

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

dev.mysql.com

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"?

 

[SirDice]

Last time I did this I created a dataset for /var/db/mysql and separate dataset for /var/db/mysql_binlog, they would need to be separate datasets because of the recordsize difference. And set log-bin and relay_log to /var/db/mysql_binlog.

Then a few other settings based on this: https://shatteredsilicon.net/mysql-mariadb-innodb-on-zfs/ and https://wiki.freebsd.org/ZFSTuningGuide#MySQL

 

[rwv37]

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).

 

[SirDice]

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).

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.

Granted, it's a little further down the text.

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.

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.

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?

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.

 

[rwv37]

Granted, it's a little further down the text.

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]

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

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.

 

[richardtoohey2]

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.

 

[Erichans]

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.

 

[SirDice]

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.

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.

# 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}  "