From owner-freebsd-database@freebsd.org Wed Nov 23 15:58:33 2016 Return-Path: Delivered-To: freebsd-database@mailman.ysv.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by mailman.ysv.freebsd.org (Postfix) with ESMTP id 52170C519A7 for ; Wed, 23 Nov 2016 15:58:33 +0000 (UTC) (envelope-from freebsd-database@pp.dyndns.biz) Received: from smtprelay-b11.telenor.se (smtprelay-b11.telenor.se [62.127.194.20]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 0CB749C6 for ; Wed, 23 Nov 2016 15:58:32 +0000 (UTC) (envelope-from freebsd-database@pp.dyndns.biz) Received: from ipb4.telenor.se (ipb4.telenor.se [195.54.127.167]) by smtprelay-b11.telenor.se (Postfix) with ESMTP id B0D23EA555 for ; Wed, 23 Nov 2016 16:34:22 +0100 (CET) X-SENDER-IP: [85.226.59.81] X-LISTENER: [smtp.bredband.net] X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: A2CqEQC/tjVYEFE74lVeHAEFAQsBgzkBAQEBAR9YgQKkWpRzggcohXkCghtCEgECAQEBAQEBAQYBAQEBAQECN0WEaQEBBCMPAQUeIhELGAICBRYLAgIJAwIBAgEbDAoUEwgBAYhtAQmudIIpi2YBCwEfBYELgSaIaIQzFkyCOIJdBZpPhkeUQIYqjWqEDCUJbiMLC4VdPTQBAYhDAQEB X-IPAS-Result: A2CqEQC/tjVYEFE74lVeHAEFAQsBgzkBAQEBAR9YgQKkWpRzggcohXkCghtCEgECAQEBAQEBAQYBAQEBAQECN0WEaQEBBCMPAQUeIhELGAICBRYLAgIJAwIBAgEbDAoUEwgBAYhtAQmudIIpi2YBCwEfBYELgSaIaIQzFkyCOIJdBZpPhkeUQIYqjWqEDCUJbiMLC4VdPTQBAYhDAQEB X-IronPort-AV: E=Sophos;i="5.31,538,1473112800"; d="scan'208";a="614069857" Received: from c-513be255.107-1-64736c12.cust.bredbandsbolaget.se (HELO gatekeeper.pp.dyndns.biz) ([85.226.59.81]) by ipb4.telenor.se with ESMTP; 23 Nov 2016 16:34:04 +0100 Received: from [192.168.69.70] ([192.168.69.70]) by gatekeeper.pp.dyndns.biz (8.15.2/8.15.2) with ESMTPS id uANFY1Nm028988 (version=TLSv1.2 cipher=DHE-RSA-AES128-SHA bits=128 verify=NO) for ; Wed, 23 Nov 2016 16:34:03 +0100 (CET) (envelope-from freebsd-database@pp.dyndns.biz) Subject: Re: Need some further understanding of MariaDB/MySQL on ZFS To: freebsd-database@freebsd.org References: <5834C395.5080305@pp.dyndns.biz> <583554EE.8010304@quip.cz> From: =?UTF-8?Q?Morgan_Wesstr=c3=b6m?= Message-ID: <5835B6E9.9000004@pp.dyndns.biz> Date: Wed, 23 Nov 2016 16:34:01 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.6.0 MIME-Version: 1.0 In-Reply-To: <583554EE.8010304@quip.cz> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 23 Nov 2016 15:58:33 -0000 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