From owner-freebsd-database@freebsd.org  Wed Nov 23 08:50:20 2016
Return-Path: <owner-freebsd-database@freebsd.org>
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 A2C98C51BD4
 for <freebsd-database@mailman.ysv.freebsd.org>;
 Wed, 23 Nov 2016 08:50:20 +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 66DBA9D7
 for <freebsd-database@freebsd.org>; Wed, 23 Nov 2016 08:50:19 +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 49FA728451;
 Wed, 23 Nov 2016 09:50:18 +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 38F3028462;
 Wed, 23 Nov 2016 09:50:17 +0100 (CET)
Subject: Re: Need some further understanding of MariaDB/MySQL on ZFS
To: =?UTF-8?Q?Morgan_Wesstr=c3=b6m?= <freebsd-database@pp.dyndns.biz>,
 freebsd-database@freebsd.org
References: <5834C395.5080305@pp.dyndns.biz> <583554EE.8010304@quip.cz>
From: Miroslav Lachman <000.fbsd@quip.cz>
Message-ID: <58355849.6070501@quip.cz>
Date: Wed, 23 Nov 2016 09:50:17 +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: <583554EE.8010304@quip.cz>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-BeenThere: freebsd-database@freebsd.org
X-Mailman-Version: 2.1.23
Precedence: list
List-Id: Database use and development under FreeBSD
 <freebsd-database.freebsd.org>
List-Unsubscribe: <https://lists.freebsd.org/mailman/options/freebsd-database>, 
 <mailto:freebsd-database-request@freebsd.org?subject=unsubscribe>
List-Archive: <http://lists.freebsd.org/pipermail/freebsd-database/>
List-Post: <mailto:freebsd-database@freebsd.org>
List-Help: <mailto:freebsd-database-request@freebsd.org?subject=help>
List-Subscribe: <https://lists.freebsd.org/mailman/listinfo/freebsd-database>, 
 <mailto:freebsd-database-request@freebsd.org?subject=subscribe>
X-List-Received-Date: Wed, 23 Nov 2016 08:50:20 -0000

Miroslav Lachman wrote on 2016/11/23 09:35:

> 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.

Just to say more - if you need file-per-table, then you can use 16K for 
/var/db/mysql too (and then you don't need separate /var/db/mysql/innodb)

zroot/var/db/mysql
recordsize=16K
mount point /var/db/mysql

* optional
zroot/var/db/mysql/innodb
recordsize=16K
mount point /var/db/mysql/innodb

zroot/var/db/mysql/innodb/logs
recordsize=128K
mount point /var/db/mysql/innodb/logs


Or you can use your setup and set innodb_file_per_table = off in you my.cnf.

Miroslav Lachman