Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id E45621A8BC81 for ; Fri, 27 Jul 2012 02:25:01 -0300 (ADT) Received: from moutng.kundenserver.de ([212.227.17.10]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Sud2n-00010i-W0 for pgsql-performance@postgresql.org; Fri, 27 Jul 2012 05:25:00 +0000 Received: from [192.168.2.118] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mreu4) with ESMTP (Nemesis) id 0MfNr4-1T9ZQ13IHC-00OofM; Fri, 27 Jul 2012 07:24:44 +0200 Message-ID: <5012261A.8040007@2ndQuadrant.com> Date: Fri, 27 Jul 2012 01:24:42 -0400 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.24) Gecko/20111120 Icedove/3.1.16 MIME-Version: 1.0 To: Laszlo Nagy CC: pgsql-performance@postgresql.org Subject: Re: ZFS vs. UFS References: <500E9A3B.3070609@shopzeus.com> In-Reply-To: <500E9A3B.3070609@shopzeus.com> Content-Type: multipart/alternative; boundary="------------020600070505020006010301" X-Provags-ID: V02:K0:wexq4yJ4USnt93MFaDuXKjGLXZ3o0cExkRHqYs11HCa WBWkwVfrKWwhD6V3rPyKSz/bQpvPDycTJEytCW24lMRpLe1K/G 8DfSy5WsLPJBr0SccHCm9T8B3REQ/mHL+yDrSuXVRT5OW5L9CQ oxG5POo3QC/PX3thaD1MkhMWiZs9OyQubXuZadfd52kfzrf2As M/sHjQ9kPaf1sSWjAxd6AxL7MvIzHOxQrteQlBrUotCVFP24g5 jUXAIn7cZyZD/aSBwrYG79t0j+cMAM3pLP8cOskrD5IzwqSGRD iC3Ufvh60QOk4Qoc7hBS9SAu+cjig4LvqORxBzffxelOIS6G/s J/hq7mDfCAtGFuNtV2zk= X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/344 X-Sequence-Number: 47525 This is a multi-part message in MIME format. --------------020600070505020006010301 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 07/24/2012 08:51 AM, Laszlo Nagy wrote: > Under FreeBSD 9, what filesystem should I use for PostgreSQL? (Dell > PowerEdge 2900, 24G mem, 10x2T SATA2 disk, Intel RAID controller.) When Intel RAID controller is that? All of the ones on the motherboard are pretty much useless if that's what you have. Those are slower than software RAID and it's going to add driver issues you could otherwise avoid. Better to connect the drives to the non-RAID ports or configure the controller in JBOD mode first. Using one of the better RAID controllers, one of Dell's good PERC models for example, is one of the biggest hardware upgrades you could make to this server. If your database is mostly read traffic, it won't matter very much. Write-heavy loads really benefit from a good RAID controller's write cache. > * ZFS is journaled, and it is more independent of the hardware. So > if the computer goes wrong, I can move the zfs array to a > different server. > * UFS is not journaled. Also I have to rely on the RAID card to > build the RAID array. If there is a hw problem with it, then I > won't be able to recover the data easily. > You should be able to get UFS working with a software mirror and journaling using gstripe/gmirror or vinum. It doesn't matter that much for PostgreSQL though. The data writes are journaled by the database, and it tries to sync data to disk after updating metadata too. There are plenty of PostgreSQL installs on FreeBSD/UFS that work fine. ZFS needs more RAM and has higher CPU overhead than UFS does. It's a heavier filesystem all around than UFS is. Your server is fast enough that you should be able to afford it though, and the feature set is nice. In addition to the RAID setup being simple to handle, having checksums on your data is a good safety feature for PostgreSQL. ZFS will heavily use server RAM for caching by default, much more so than UFS. Make sure you check into that, and leave enough RAM for the database to run too. (Doing *some* caching that way is good for Postgres; you just don't want *all* the memory to be used for that) Moving disks to another server is a very low probability fix for a broken system. The disks are a likely place for the actual failure to happen at in the first place. I like to think more in terms of "how can I create a real-time replica of this data?" to protect databases, and the standby server for that doesn't need to be an expensive system. That said, there is no reason to set things up so that they only work with that Intel RAID controller, given that it's not a very good piece of hardware anyway. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com --------------020600070505020006010301 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 07/24/2012 08:51 AM, Laszlo Nagy wrote:
Under FreeBSD 9, what filesystem should I use for PostgreSQL? (Dell PowerEdge 2900, 24G mem, 10x2T SATA2 disk, Intel RAID controller.)

When Intel RAID controller is that?  All of the ones on the motherboard are pretty much useless if that's what you have.  Those are slower than software RAID and it's going to add driver issues you could otherwise avoid.  Better to connect the drives to the non-RAID ports or configure the controller in JBOD mode first.

Using one of the better RAID controllers, one of Dell's good PERC models for example, is one of the biggest hardware upgrades you could make to this server.  If your database is mostly read traffic, it won't matter very much.  Write-heavy loads really benefit from a good RAID controller's write cache.

  • ZFS is journaled, and it is more independent of the hardware. So if the computer goes wrong, I can move the zfs array to a different server.
  • UFS is not journaled. Also I have to rely on the RAID card to build the RAID array. If there is a hw problem with it, then I won't be able to recover the data easily.

You should be able to get UFS working with a software mirror and journaling using gstripe/gmirror or vinum.  It doesn't matter that much for PostgreSQL though.  The data writes are journaled by the database, and it tries to sync data to disk after updating metadata too.  There are plenty of PostgreSQL installs on FreeBSD/UFS that work fine.

ZFS needs more RAM and has higher CPU overhead than UFS does.  It's a heavier filesystem all around than UFS is.  Your server is fast enough that you should be able to afford it though, and the feature set is nice.  In addition to the RAID setup being simple to handle, having checksums on your data is a good safety feature for PostgreSQL. 

ZFS will heavily use server RAM for caching by default, much more so than UFS.  Make sure you check into that, and leave enough RAM for the database to run too.  (Doing *some* caching that way is good for Postgres; you just don't want *all* the memory to be used for that)

Moving disks to another server is a very low probability fix for a broken system.  The disks are a likely place for the actual failure to happen at in the first place.  I like to think more in terms of "how can I create a real-time replica of this data?" to protect databases, and the standby server for that doesn't need to be an expensive system.  That said, there is no reason to set things up so that they only work with that Intel RAID controller, given that it's not a very good piece of hardware anyway.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
--------------020600070505020006010301--