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