Need some further understanding of MariaDB/MySQL on ZFS
Morgan Wesström
freebsd-database at pp.dyndns.biz
Wed Nov 23 15:58:33 UTC 2016
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
More information about the freebsd-database
mailing list