Need some further understanding of MariaDB/MySQL on ZFS

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Need some further understanding of MariaDB/MySQL on ZFS

Morgan Wesström-2
Hi list, first time poster here but long time FreeBSD user.

I'm migrating my FreeBSD based home router to FreeBSD 11 and ZFS on
root. This also includes moving my MariaDB databases to ZFS. I've
studied the wiki guide as well as several other sources on this subject
and I understand the complex interaction between ZFS and the internal
InnoDB and MyISAM database structures.

I've created the following datasets:

zroot/var/db/mysql
recordsize=8K
mount point /var/db/mysql

zroot/var/db/mysql/innodb
recordsize=16K
mount point /var/db/mysql/innodb

zroot/var/db/mysql/innodb/logs
recordsize=128K
mount point /var/db/mysql/innodb/logs

My my.cnf contains (among others) these lines to move InnoDB databases
to the correct folders:

innodb_data_home_dir = /var/db/mysql/innodb
innodb_log_group_home_dir = /var/db/mysql/innodb/logs

When I start the server the first time the InnoDB folders are correctly
populated with some default files like innodb/ibdata1 and
innodb/logs/ib_logfile0. But as soon as I create a new database (foo)
with InnoDB tables, the server creates a /var/db/mysql/foo folder and
populates it with ibd and frm files whereas I would've expected it to
create it as /var/db/mysql/innodb/foo to inherit the correct 16K recordsize.

Is this correct? Shouldn't it be created under /var/db/mysql/innodb?
Have I missed some vital configuration option?

Kind Regards
Morgan Wesström

_______________________________________________
[hidden email] mailing list
https://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: Need some further understanding of MariaDB/MySQL on ZFS

Miroslav Lachman
Morgan Wesström wrote on 2016/11/22 23:15:

> innodb_data_home_dir = /var/db/mysql/innodb
> innodb_log_group_home_dir = /var/db/mysql/innodb/logs
>
> When I start the server the first time the InnoDB folders are correctly
> populated with some default files like innodb/ibdata1 and
> innodb/logs/ib_logfile0. But as soon as I create a new database (foo)
> with InnoDB tables, the server creates a /var/db/mysql/foo folder and
> populates it with ibd and frm files whereas I would've expected it to
> create it as /var/db/mysql/innodb/foo to inherit the correct 16K
> recordsize.
>
> Is this correct? Shouldn't it be created under /var/db/mysql/innodb?
> Have I missed some vital configuration option?

I don't think it works the way you are expecting.

If you have innodb_file_per_table Off, then all InnoDB / XtraDB tables
are stored in ib_data file(s) in innodb_data_home_dir = /var/db/mysql/innodb
But some metadata (frm files) are stored in /var/db/mysql/databasename/

If you have innodb_file_per_table On, then all tables data are stored in
/var/db/mysql/databasename/ and only internal InnoDB data are stored in
/var/db/mysql/innodb/ib_data

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_data_home_dir

The common part of the directory path for all InnoDB data files in the
*system tablespace*. This setting _does not affect the location of
file-per-table tablespaces when innodb_file_per_table is enabled_. The
default value is the MySQL data directory. If you specify the value as
an empty string, you can use absolute file paths in innodb_data_file_path.

Miroslav Lachman


_______________________________________________
[hidden email] mailing list
https://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: Need some further understanding of MariaDB/MySQL on ZFS

Miroslav Lachman
Miroslav Lachman wrote on 2016/11/23 09:35:

> If you have innodb_file_per_table On, then all tables data are stored in
> /var/db/mysql/databasename/ and only internal InnoDB data are stored in
> /var/db/mysql/innodb/ib_data
>
> https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_data_home_dir
>
>
> The common part of the directory path for all InnoDB data files in the
> *system tablespace*. This setting _does not affect the location of
> file-per-table tablespaces when innodb_file_per_table is enabled_. The
> default value is the MySQL data directory. If you specify the value as
> an empty string, you can use absolute file paths in innodb_data_file_path.

Just to say more - if you need file-per-table, then you can use 16K for
/var/db/mysql too (and then you don't need separate /var/db/mysql/innodb)

zroot/var/db/mysql
recordsize=16K
mount point /var/db/mysql

* optional
zroot/var/db/mysql/innodb
recordsize=16K
mount point /var/db/mysql/innodb

zroot/var/db/mysql/innodb/logs
recordsize=128K
mount point /var/db/mysql/innodb/logs


Or you can use your setup and set innodb_file_per_table = off in you my.cnf.

Miroslav Lachman

_______________________________________________
[hidden email] mailing list
https://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: Need some further understanding of MariaDB/MySQL on ZFS

Morgan Wesström-2
In reply to this post by Miroslav Lachman
On 2016-11-23 09:35, Miroslav Lachman wrote:

> Morgan Wesström wrote on 2016/11/22 23:15:
>
>> innodb_data_home_dir = /var/db/mysql/innodb
>> innodb_log_group_home_dir = /var/db/mysql/innodb/logs
>>
>> When I start the server the first time the InnoDB folders are correctly
>> populated with some default files like innodb/ibdata1 and
>> innodb/logs/ib_logfile0. But as soon as I create a new database (foo)
>> with InnoDB tables, the server creates a /var/db/mysql/foo folder and
>> populates it with ibd and frm files whereas I would've expected it to
>> create it as /var/db/mysql/innodb/foo to inherit the correct 16K
>> recordsize.
>>
>> Is this correct? Shouldn't it be created under /var/db/mysql/innodb?
>> Have I missed some vital configuration option?
>
> I don't think it works the way you are expecting.
>
> If you have innodb_file_per_table Off, then all InnoDB / XtraDB tables
> are stored in ib_data file(s) in innodb_data_home_dir =
> /var/db/mysql/innodb
> But some metadata (frm files) are stored in /var/db/mysql/databasename/
>
> If you have innodb_file_per_table On, then all tables data are stored in
> /var/db/mysql/databasename/ and only internal InnoDB data are stored in
> /var/db/mysql/innodb/ib_data
>

Thank you Miroslav, this was the vital piece of information I needed. It
seems MySQL changed the default value of innodb_file_per_table from 0 to
1 with version 5.6.
https://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

The parameter isn't mentioned in any of the online ZFS/MySQL wikis or
blogs that I've found, not even FreeBSD's own wiki. I assume this change
could interfere with people running production systems on ZFS.
Personally I'm reluctant to change default values unless I have a very
good reason to do so, so I will try to find a way to adapt to it. Your
suggestion to put the whole of /var/db/mysql on a 16K recordsize (except
for the logs directory) is probably the way to go. I just need to delve
a little bit further into the subject before I make up my mind. Thank
you again for pointing me in the right direction.

Morgan
_______________________________________________
[hidden email] mailing list
https://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: Need some further understanding of MariaDB/MySQL on ZFS

Miroslav Lachman
Morgan Wesström wrote on 2016/11/23 16:34:

> Thank you Miroslav, this was the vital piece of information I needed. It
> seems MySQL changed the default value of innodb_file_per_table from 0 to
> 1 with version 5.6.
> https://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

yes, the default value was changed from Off to On

> The parameter isn't mentioned in any of the online ZFS/MySQL wikis or
> blogs that I've found, not even FreeBSD's own wiki.

I'll take a look on this and prepare some update.

> I assume this change
> could interfere with people running production systems on ZFS.
> Personally I'm reluctant to change default values unless I have a very
> good reason to do so, so I will try to find a way to adapt to it. Your
> suggestion to put the whole of /var/db/mysql on a 16K recordsize (except
> for the logs directory) is probably the way to go. I just need to delve
> a little bit further into the subject before I make up my mind. Thank
> you again for pointing me in the right direction.

innodb_file_per_table = off means that all tables from all databases are
in one big file. It has some advantages - faster access, less opened
file descriptors... but some disadvantage too - ibdata file can grow but
will not shrink if you delete some data from tables / drop tables. It
can be a big problem on huge databases with a lot of writes (UPDATE,
DELETE, INSERT).
We converted few of our DB servers to innodb_file_per_table = On few
years ago.

innodb_file_per_table = On is better for our type of workload on almost
all our machines but your workload can be different and you can benefit
from innodb_file_per_table = Off.
So you need to test or guess what is better for your setup.

Good luck

Miroslav Lachman
_______________________________________________
[hidden email] mailing list
https://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: Need some further understanding of MariaDB/MySQL on ZFS

Miroslav Lachman
In reply to this post by Morgan Wesström-2
Morgan Wesström wrote on 2016/11/23 16:34:

> Thank you Miroslav, this was the vital piece of information I needed. It
> seems MySQL changed the default value of innodb_file_per_table from 0 to
> 1 with version 5.6.
> https://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html
>
> The parameter isn't mentioned in any of the online ZFS/MySQL wikis or
> blogs that I've found, not even FreeBSD's own wiki. I assume this change
> could interfere with people running production systems on ZFS.

I added note to FreeBSD ZFS Tuning page:
https://wiki.freebsd.org/ZFSTuningGuide

Miroslav Lachman
_______________________________________________
[hidden email] mailing list
https://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"