From owner-freebsd-database@freebsd.org Wed Nov 23 16:45:02 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 944EEC51905 for ; Wed, 23 Nov 2016 16:45:02 +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 57DE718D9 for ; Wed, 23 Nov 2016 16:45:01 +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 B11162846D; Wed, 23 Nov 2016 17:44:58 +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 DBB7E28450; Wed, 23 Nov 2016 17:44:56 +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> <583554EE.8010304@quip.cz> <5835B6E9.9000004@pp.dyndns.biz> From: Miroslav Lachman <000.fbsd@quip.cz> Message-ID: <5835C788.6090601@quip.cz> Date: Wed, 23 Nov 2016 17:44:56 +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: <5835B6E9.9000004@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 16:45:02 -0000 Morgan Wesström wrote on 2016/11/23 16:34: > 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 yes, the default value was changed from Off to On > 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'll take a look on this and prepare some update. > 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. innodb_file_per_table = off means that all tables from all databases are in one big file. It has some advantages - faster access, less opened file descriptors... but some disadvantage too - ibdata file can grow but will not shrink if you delete some data from tables / drop tables. It can be a big problem on huge databases with a lot of writes (UPDATE, DELETE, INSERT). We converted few of our DB servers to innodb_file_per_table = On few years ago. innodb_file_per_table = On is better for our type of workload on almost all our machines but your workload can be different and you can benefit from innodb_file_per_table = Off. So you need to test or guess what is better for your setup. Good luck Miroslav Lachman