Date: Wed, 23 Nov 2016 16:34:01 +0100 From: =?UTF-8?Q?Morgan_Wesstr=c3=b6m?= <freebsd-database@pp.dyndns.biz> To: freebsd-database@freebsd.org Subject: Re: Need some further understanding of MariaDB/MySQL on ZFS Message-ID: <5835B6E9.9000004@pp.dyndns.biz> In-Reply-To: <583554EE.8010304@quip.cz> References: <5834C395.5080305@pp.dyndns.biz> <583554EE.8010304@quip.cz>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?5835B6E9.9000004>