From owner-freebsd-database@freebsd.org Wed Nov 23 08:36:10 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 3D50EC517CC for ; Wed, 23 Nov 2016 08:36:10 +0000 (UTC) (envelope-from 000.fbsd@quip.cz) Received: from elsa.codelab.cz (elsa.codelab.cz [94.124.105.4]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 02A74197 for ; Wed, 23 Nov 2016 08:36:09 +0000 (UTC) (envelope-from 000.fbsd@quip.cz) Received: from elsa.codelab.cz (localhost [127.0.0.1]) by elsa.codelab.cz (Postfix) with ESMTP id B18CC28450; Wed, 23 Nov 2016 09:36:00 +0100 (CET) Received: from illbsd.quip.test (ip-86-49-16-209.net.upcbroadband.cz [86.49.16.209]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by elsa.codelab.cz (Postfix) with ESMTPSA id C053B28460; Wed, 23 Nov 2016 09:35:58 +0100 (CET) Subject: Re: Need some further understanding of MariaDB/MySQL on ZFS To: =?UTF-8?Q?Morgan_Wesstr=c3=b6m?= , freebsd-database@freebsd.org References: <5834C395.5080305@pp.dyndns.biz> From: Miroslav Lachman <000.fbsd@quip.cz> Message-ID: <583554EE.8010304@quip.cz> Date: Wed, 23 Nov 2016 09:35:58 +0100 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:42.0) Gecko/20100101 Firefox/42.0 SeaMonkey/2.39 MIME-Version: 1.0 In-Reply-To: <5834C395.5080305@pp.dyndns.biz> 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 08:36:10 -0000 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