Re: Best filesystem for a high load db

Currently I use FreeBSD 10 with ZFS filesystem for our Production database.
Speed wise it's fine, i'm sure other filesystems could be faster, even
though we have never compared it with other filesystems. The reason we do
ZFS is to take advantage of the data compression and snapshots. It is very
easy to generate a new slave just by copying the filesystem to another
machine. Having different compression for tablespaces that don't get
accessed as much, or tablespaces on faster disks. Doing big data migrations
or pushes we are able to rollback if something fails. Also when upgrading
to a newer version of Postgres, just take a snapshot and upgrade that.

Same with database backups. We issue a pg_start_backup(), take a few
snapshots, issue pg_stop_backup(). Then ship the entire filesystem to a
different machine and that's your backup.

One thing I am pushing to do is using SSDs for the ZIL and L2ARC. This
would allow for a pretty nice boost in speed.

-Joseph

On Wed, Nov 26, 2014 at 9:50 AM, Andy Colson <andy(at)squeakycode(dot)net> wrote:

> On 11/26/2014 4:16 AM, Maila Fatticcioni wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On 11/25/2014 05:54 PM, Bill Moran wrote:
>>
>>> On Tue, 25 Nov 2014 17:27:18 +0100 Christoph Berg <cb(at)df7cb(dot)de>
>>> wrote:
>>>
>>> Re: Bill Moran 2014-11-25
>>>> <20141125111630(dot)d05d58a9eb083c7cf80ed9f8(at)potentialtech(dot)com>
>>>>
>>>>> Anything with a journal is a performance problem. PostgreSQL
>>>>> effectivly does its own journalling with the WAL logs. That's
>>>>> not to say that there's no value to crash recovery to having a
>>>>> journalling filesystem, but it's just to say that our
>>>>> experience showed journaling filesystems to be slower. That
>>>>> rules out ext4, unless you disable the journal. I seem to
>>>>> remember ext4 with journalling disabled being one of the faster
>>>>> filesystems, but I could be remembering wrong.
>>>>>
>>>>
>>>> If you are using a non-journalling FS, you'll be waiting for a
>>>> full fsck after a system crash. Not sure that's an improvement.
>>>>
>>>
>>> It's an improvement if: a) You're investing in high-quality
>>> hardware, so the chance of a system crash is very low. b) The
>>> database is replicated, so your plan in the event of a primary
>>> crash is to fail over to the backup anyway.
>>>
>>> If both of those are in place (as they were at my previous job)
>>> then the time it takes to fsck isn't an issue, and taking action
>>> that causes the database to run faster when nothing is wrong can be
>>> considered.
>>>
>>> Obviously, the OP needs to assess the specific needs of the product
>>> in question. Your point is very valid, and I'm glad you brought it
>>> up (as a lot of people forget about it) but sometimes it's not the
>>> most important factor.
>>>
>>>
>> Thank you a lot to have shared with me your experiences.
>> Indeed we will have two servers in cluster with high quality hardware
>> so a fsck restore shouldn't be a big problem.
>> I will analize the xfs option as well and then I will decide.
>>
>> Thank you again,
>> Maila Fatticcioni
>>
>>
> Also, if you do some timings, please share it with us, it'd be nice to
> have some more data points.
>
> -Andy
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>