From owner-freebsd-fs@FreeBSD.ORG Thu Oct 24 12:50:35 2013 Return-Path: Delivered-To: freebsd-fs@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [8.8.178.115]) (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTP id CAE0DA25 for ; Thu, 24 Oct 2013 12:50:35 +0000 (UTC) (envelope-from daniel@digsys.bg) Received: from smtp-sofia.digsys.bg (smtp-sofia.digsys.bg [193.68.21.123]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mx1.freebsd.org (Postfix) with ESMTPS id 38E262C10 for ; Thu, 24 Oct 2013 12:50:34 +0000 (UTC) Received: from dcave.digsys.bg (dcave.digsys.bg [193.68.6.1]) (authenticated bits=0) by smtp-sofia.digsys.bg (8.14.6/8.14.6) with ESMTP id r9OCeuBR075443 (version=TLSv1/SSLv3 cipher=DHE-RSA-CAMELLIA256-SHA bits=256 verify=NO) for ; Thu, 24 Oct 2013 15:40:57 +0300 (EEST) (envelope-from daniel@digsys.bg) Message-ID: <52691558.5060306@digsys.bg> Date: Thu, 24 Oct 2013 15:40:56 +0300 From: Daniel Kalchev User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:24.0) Gecko/20100101 Thunderbird/24.0.1 MIME-Version: 1.0 To: freebsd-fs@freebsd.org Subject: Re: ZFS-FreeBSD + postgresql performance References: <20131023162545.GA19794@pcjas.obspm.fr> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-BeenThere: freebsd-fs@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: Filesystems List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 24 Oct 2013 12:50:35 -0000 On 24.10.13 15:25, krad wrote: > It is also generally better to allocate the ram to the db rather than zfs. > This reduces context switches, and the db can cache more intelligently that > the file system. However your mileage with this may vary depending on the > db in question. On a system with such a large memory pool limiting zrc to 8 > gb, maybe a good thing to do, especially if you can hook up a large l2arc. If you have large L2ARC, that will also require large ARC. Indeed, the theory is that most memory should be allocated to the PostgreSQL shared memory pool. However, my practical experience demonstrates exactly the opposite, especially when the server runs mixed load. Limiting ARC has always resulted in worse performance. In short: you need to run extensive tests. It used to be much worse, but current FreeBSD is behaving quite well without artificially limiting the ARC. > If your dbs are doing lots of writes, consider implementing raid 10 on the > pools level as its much faster for writes. By all means, if you will run PostgreSQL on the zpool avoid any other redundancy than MIRROR. You will be better having one separate MIRROR (or multiple mirror vdevs) pool for the DB and another for other data, any time. > As you have a lots of dbs, and potentially a lot of spindles, you could mix > and match the above ideas depending on the dbs profile. Have say a raidz6 > pool for you lower traffic mostly read dbs, a raid 10 pool for your heavy > write dbs, and a dedicated pool for you low latency db I have actually experimented with this, in using 15k drives for the "fast" databases and 10k drives for the "slow" databases. At the end, I discovered it was better idea to combine all the drives in a single zpool with multiple vdevs, than to do such separation -- as multiple vdevs are always going to provide you with more performance. With PostgreSQL, you want to have dedicated ZIL device. Any current SSD will do, but consider your workload -- you might be interested in an enterprise grade (more write endurance) SSD, or -- you might do what I do -- consider SSDs used as ZIL and L1ARC as consumables -- when they wear out, just put a new one. Their only role is to increase performance, no data is dependent on their long life... I also experimented with SSD-only zpools for PostrgeSQL. What to say.. for reasonably sized databases, this setup beats anything else hands down. Just make sure you have enough redundancy and backups, as any SSD can fail at any time for no obvious reason (disks too). Daniel