PostgreSQL with ZFS on Linux

On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion
<sl@thestrangefactory.com>wrote:

Hello,

Since ZFS on Linux (http://zfsonlinux.org/) has been declared production
ready last March (v0.6.1), I am curious if anyone is using it with
PostgreSQL on production servers (either main or backup) and if so, what is
their experience so far ?

Thank you,

Sébastien

FYI, a recent (Sept. 2013) presentation I found about using ZoL in
production (albeit, not with PostgreSQL) and the current status of the
project:

http://lanyrd.com/2013/linuxcon-north-america/scqmfb/

http://www.unix-experience.fr/2013/2451/

FreeBSD is also a very mature platform for ZFS/postgresql.

On 16/01/2014 11:57, Sébastien Lorion wrote:

On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion <sl@thestrangefactory.com <mailto:sl@thestrangefactory.com>> wrote:

Hello,

Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or
backup) and if so, what is their experience so far ?

Thank you,

Sébastien

FYI, a recent (Sept. 2013) presentation I found about using ZoL in production (albeit, not with PostgreSQL) and the current status of the project:

http://lanyrd.com/2013/linuxcon-north-america/scqmfb/

--
Achilleas Mantzios
Head of IT DEV
IT DEPT
Dynacom Tankers Mgmt

On 16 January 2014 12:09, Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:

http://www.unix-experience.fr/2013/2451/

FreeBSD is also a very mature platform for ZFS/postgresql.

More mature than on Linux even, as far as I know. If I had to choose
an OS to use ZFS with, I'd go with
either FreeBSD or Solaris. That said, I am biased to FreeBSD anyway;
the only Linux installation that I
own is the one in my Android phone, while I own several FreeBSD systems.

On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion <sl@thestrangefactory.com>
wrote:

Hello,

Since ZFS on Linux (http://zfsonlinux.org/) has been declared production
ready last March (v0.6.1), I am curious if anyone is using it with
PostgreSQL on production servers (either main or backup) and if so, what is
their experience so far ?

Thank you,

Sébastien

I do not consider ZFS an ideal file-system for databases. I'm not an
expert on ZFS, but there are two
features in ZFS that I think particularly make it less suitable for
database use.

One reason is that ZFS, as I understand it, is a log-structured
file-system. That means that changes to files always
go to the end of the file-system. If that file is a large frequently
updated database table, records are going to be far
apart and in fairly random order. That could seriously hurt performance.

Secondly, with ZFS you need to reserve a significant amount of memory
for the ZIL. That is memory that is
not available to your database.

Don't take my word for it, but I think the above points are worth
investigating as is finding some file-system bench-
marks where ZFS gets compared to, for example, UFS2 (FreeBSD), Ext4fs (Linux).
Of course, the other side of the coin is ZFS's excellent flexibility.

Cheers,

Alban Hertroys
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Jan 16, 2014 at 3:22 AM, Sébastien Lorion
<sl@thestrangefactory.com>wrote:

Hello,

Since ZFS on Linux (http://zfsonlinux.org/) has been declared production
ready last March (v0.6.1), I am curious if anyone is using it with
PostgreSQL on production servers (either main or backup) and if so, what is
their experience so far ?

Thank you,

Sébastien

I would highly recommend you proceed with caution and lots of testing. If
you look back in the archives of a week or so ago on this list, I posted
with memory issues. After several helpful suggestions, we found the root
cause to be postgresql coupled with ZFS on Linux. Basically, as I
understand it (I may be a bit off), the ZFS Linux port has a middle layer
to translate and emulate the Solaris kernel. Because of this, it isn't as
efficient at memory mangement as a more native port like FreeBSD or Solaris
itself. It is also prone to memory bugs/leaks with large rsync or file
copy operations. Couple this with a memory hungry database and you can
start having issues.

In our case, we went from a server with 4 GB of RAM on a hardware RAID 10
and Postgresql 8.3 to a system with 32 GB of RAM, ZFS and Postgresql 9.3.
Once we started pushing people to the new server and it got under load,
postgres started being unable to allocate new RAM when it needed it. This
was with same number of clients and same databases as the old 4 GB system,
so it seemed crazy. With some great help of some of the people on this
list, we started looking into what was going on and basically found that
ZFS had taken 24 GB of RAM, about 18 of which was not cached/shared RAM but
"real" RAM it was holding onto and wouldn't let go of.

We have since moved to FreeBSD with ZFS and Postgres 9.3 (everything else
the same) and performance has been awesome (as we were expecting out of the
new server). If you really want ZFS, I would highly recommend looking into
FreeBSD (Postgresql works great on it) or if you want to stick with Linux,
look into mdadm with LVM or some other filesystem solution.

A few helpful things we found:

Some notes on ZFS on Linux of things to watch out for:

http://utcc.utoronto.ca/~cks/space/blog/linux/ZFSonLinuxWeakAreas

A great guide to figuring out how much RAM postgresql is actually using
(assuming you run into RAM issues):

http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/

Preston

Hi,

If you really want ZFS, I would highly recommend looking into
FreeBSD (Postgresql works great on it) or if you want to stick with Linux,
look into mdadm with LVM or some other filesystem solution.

If you want to use ZFS because of its features, take a look at btrfs.
It provides a lot of the stuff supported by ZFS with usually better
performance on linux - and since the last few kernel revisions it is
finally in a state where I would dare to use it in production.

If you need highest performance, don't use a copy-on-write filesystem
like ZFS or btrfs, stick to ext4 or XFS ;)

Regards, Clemens

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Jan 16, 2014 at 6:09 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:

http://www.unix-experience.fr/2013/2451/

FreeBSD is also a very mature platform for ZFS/postgresql.

It is more mature than Linux for sure, but still not up to par with Solaris
for some features. See this comment for example:
http://www.dslreports.com/forum/r27269032-. I do like FreeBSD very much,
but it seems to me that if I want to use ZFS to its fullest, I should go
with OpenIndiana.

In the benchmark you link, ZFS+opti (orange line, physical machine
benchmark) performs well because it has sync=disabled, which is risky to
say the least ...

On Thu, Jan 16, 2014 at 6:34 AM, Alban Hertroys <haramrae@gmail.com> wrote:

On 16 January 2014 12:09, Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:

http://www.unix-experience.fr/2013/2451/

FreeBSD is also a very mature platform for ZFS/postgresql.

More mature than on Linux even, as far as I know. If I had to choose
an OS to use ZFS with, I'd go with
either FreeBSD or Solaris. That said, I am biased to FreeBSD anyway;
the only Linux installation that I
own is the one in my Android phone, while I own several FreeBSD systems.

On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion <

sl@thestrangefactory.com>

wrote:

Hello,

Since ZFS on Linux (http://zfsonlinux.org/) has been declared

production

ready last March (v0.6.1), I am curious if anyone is using it with
PostgreSQL on production servers (either main or backup) and if so,

what is

their experience so far ?

Thank you,

Sébastien

I do not consider ZFS an ideal file-system for databases. I'm not an
expert on ZFS, but there are two
features in ZFS that I think particularly make it less suitable for
database use.

One reason is that ZFS, as I understand it, is a log-structured
file-system. That means that changes to files always
go to the end of the file-system. If that file is a large frequently
updated database table, records are going to be far
apart and in fairly random order. That could seriously hurt performance.

Secondly, with ZFS you need to reserve a significant amount of memory
for the ZIL. That is memory that is
not available to your database.

Don't take my word for it, but I think the above points are worth
investigating as is finding some file-system bench-
marks where ZFS gets compared to, for example, UFS2 (FreeBSD), Ext4fs
(Linux).
Of course, the other side of the coin is ZFS's excellent flexibility.

Cheers,

Alban Hertroys
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Those are very good points, but from my own tests and recent TPC benchmarks
I saw on the net (sorry, don't have the links anymore), using SSD makes
them not/less an issue. As you say, ZFS flexibility and ease of maintenance
trumps many cards. Also, something worth pointing out and which may be
counter-intuitive is that using ZFS compression can actually speed things
up: http://citusdata.com/blog/64-zfs-compression

Sébastien

On Thu, Jan 16, 2014 at 4:42 PM, Clemens Eisserer <linuxhippy@gmail.com>wrote:

Hi,

If you really want ZFS, I would highly recommend looking into
FreeBSD (Postgresql works great on it) or if you want to stick with

Linux,

look into mdadm with LVM or some other filesystem solution.

If you want to use ZFS because of its features, take a look at btrfs.
It provides a lot of the stuff supported by ZFS with usually better
performance on linux - and since the last few kernel revisions it is
finally in a state where I would dare to use it in production.

If you need highest performance, don't use a copy-on-write filesystem
like ZFS or btrfs, stick to ext4 or XFS ;)

Regards, Clemens

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Do you have any personal experience with BTRFS for a couple of weeks in
production or any official statement/case study ? On the FAQ, it says it is
still experimental (https://btrfs.wiki.kernel.org/index.php/FAQ), though it
may just be outdated. There is also these two links that would make me very
cautious (as I am with ZFS on Linux, mind you):

http://www.anchor.com.au/blog/2013/04/the-btrfs-backup-experiment/
http://arstechnica.com/civis/viewtopic.php?f=16&amp;t=1221177

Sébastien

On Thu, Jan 16, 2014 at 11:14 PM, Sébastien Lorion <sl@thestrangefactory.com

wrote:

On Thu, Jan 16, 2014 at 4:42 PM, Clemens Eisserer <linuxhippy@gmail.com>wrote:

Hi,

If you really want ZFS, I would highly recommend looking into
FreeBSD (Postgresql works great on it) or if you want to stick with

Linux,

look into mdadm with LVM or some other filesystem solution.

If you want to use ZFS because of its features, take a look at btrfs.
It provides a lot of the stuff supported by ZFS with usually better
performance on linux - and since the last few kernel revisions it is
finally in a state where I would dare to use it in production.

If you need highest performance, don't use a copy-on-write filesystem
like ZFS or btrfs, stick to ext4 or XFS ;)

Regards, Clemens

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Do you have any personal experience with BTRFS for a couple of weeks in
production or any official statement/case study ? On the FAQ, it says it is
still experimental (https://btrfs.wiki.kernel.org/index.php/FAQ), though
it may just be outdated. There is also these two links that would make me
very cautious (as I am with ZFS on Linux, mind you):

http://www.anchor.com.au/blog/2013/04/the-btrfs-backup-experiment/
http://arstechnica.com/civis/viewtopic.php?f=16&amp;t=1221177

We're looking at it for LedgerSMB hosting (with PostgreSQL, we are

currently using XFS). So far we are liking what we are seeing. We
wouldn't be using it for PostgreSQL backups, but the general sense is that
the developers are very, very conservative about making guarantees of
stability and so far we haven't seen any indication that "experimental"
means anything other than "developers nervous about calling it stable."

This being said, we aren't very far into our evaluation yet and our view
could change.

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

On Fri, Jan 17, 2014 at 2:29 AM, Chris Travers <chris.travers@gmail.com>wrote:

On Thu, Jan 16, 2014 at 11:14 PM, Sébastien Lorion <
sl@thestrangefactory.com> wrote:

On Thu, Jan 16, 2014 at 4:42 PM, Clemens Eisserer <linuxhippy@gmail.com>wrote:

Hi,

If you really want ZFS, I would highly recommend looking into
FreeBSD (Postgresql works great on it) or if you want to stick with

Linux,

look into mdadm with LVM or some other filesystem solution.

If you want to use ZFS because of its features, take a look at btrfs.
It provides a lot of the stuff supported by ZFS with usually better
performance on linux - and since the last few kernel revisions it is
finally in a state where I would dare to use it in production.

If you need highest performance, don't use a copy-on-write filesystem
like ZFS or btrfs, stick to ext4 or XFS ;)

Regards, Clemens

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Do you have any personal experience with BTRFS for a couple of weeks in
production or any official statement/case study ? On the FAQ, it says it is
still experimental (https://btrfs.wiki.kernel.org/index.php/FAQ), though
it may just be outdated. There is also these two links that would make me
very cautious (as I am with ZFS on Linux, mind you):

http://www.anchor.com.au/blog/2013/04/the-btrfs-backup-experiment/
http://arstechnica.com/civis/viewtopic.php?f=16&amp;t=1221177

We're looking at it for LedgerSMB hosting (with PostgreSQL, we are

currently using XFS). So far we are liking what we are seeing. We
wouldn't be using it for PostgreSQL backups, but the general sense is that
the developers are very, very conservative about making guarantees of
stability and so far we haven't seen any indication that "experimental"
means anything other than "developers nervous about calling it stable."

This being said, we aren't very far into our evaluation yet and our view
could change.

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

Thank you Chris, that incites me at looking at btrfs more closely.

Sébastien