From owner-freebsd-database@freebsd.org Tue Nov 22 22:44:54 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 4E076C4FF36 for ; Tue, 22 Nov 2016 22:44:54 +0000 (UTC) (envelope-from freebsd-database@pp.dyndns.biz) Received: from smtprelay-h31.telenor.se (smtprelay-h31.telenor.se [213.150.131.4]) (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 10D1A10C9 for ; Tue, 22 Nov 2016 22:44:53 +0000 (UTC) (envelope-from freebsd-database@pp.dyndns.biz) Received: from ipb2.telenor.se (ipb2.telenor.se [195.54.127.165]) by smtprelay-h31.telenor.se (Postfix) with ESMTP id A70F3D62F for ; Tue, 22 Nov 2016 23:15:52 +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: A2BMOQDbwjRYEFE74lVdHAEBFgEBBQEBgx0OAQEBAQEfgRA6hwOzO4EdGIgiQxEBAgEBAQEBAQEGAQEBAQEBAjdFhRIPAQUeIjYCBRYLAgsDAgECARsMCicIAQGIbQGfd498gimMA4ELgSaNGmKCOIJdBZpOgUmZOYYnkXI0aCIKCzKCWAyCQD2IQAEBAQ X-IPAS-Result: A2BMOQDbwjRYEFE74lVdHAEBFgEBBQEBgx0OAQEBAQEfgRA6hwOzO4EdGIgiQxEBAgEBAQEBAQEGAQEBAQEBAjdFhRIPAQUeIjYCBRYLAgsDAgECARsMCicIAQGIbQGfd498gimMA4ELgSaNGmKCOIJdBZpOgUmZOYYnkXI0aCIKCzKCWAyCQD2IQAEBAQ X-IronPort-AV: E=Sophos;i="5.31,682,1473112800"; d="scan'208";a="867167747" Received: from c-513be255.107-1-64736c12.cust.bredbandsbolaget.se (HELO gatekeeper.pp.dyndns.biz) ([85.226.59.81]) by ipb2.telenor.se with ESMTP; 22 Nov 2016 23:15:51 +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 uAMMFnmh023805 (version=TLSv1.2 cipher=DHE-RSA-AES128-SHA bits=128 verify=NO) for ; Tue, 22 Nov 2016 23:15:50 +0100 (CET) (envelope-from freebsd-database@pp.dyndns.biz) To: freebsd-database@freebsd.org From: =?UTF-8?Q?Morgan_Wesstr=c3=b6m?= Subject: Need some further understanding of MariaDB/MySQL on ZFS Message-ID: <5834C395.5080305@pp.dyndns.biz> Date: Tue, 22 Nov 2016 23:15:49 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.6.0 MIME-Version: 1.0 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: Tue, 22 Nov 2016 22:44:54 -0000 Hi list, first time poster here but long time FreeBSD user. I'm migrating my FreeBSD based home router to FreeBSD 11 and ZFS on root. This also includes moving my MariaDB databases to ZFS. I've studied the wiki guide as well as several other sources on this subject and I understand the complex interaction between ZFS and the internal InnoDB and MyISAM database structures. I've created the following datasets: zroot/var/db/mysql recordsize=8K mount point /var/db/mysql 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 My my.cnf contains (among others) these lines to move InnoDB databases to the correct folders: 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? Kind Regards Morgan Wesström