Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-10-02 Thread Jim Nasby
On 10/1/15 8:04 PM, Joseph Kregloh wrote: In either case you are still "bottlenecked" by the speed of the write from RAM to the zpool. Now for a small database with not many writes a ZIL would be awesome. But on a write heavy database you will be acknowledging more writes because of the ZIL that

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-10-01 Thread Joseph Kregloh
On Wed, Sep 30, 2015 at 5:12 PM, Benjamin Smith wrote: > On Wednesday, September 30, 2015 09:58:08 PM Tomas Vondra wrote: > > On 09/30/2015 07:33 PM, Benjamin Smith wrote: > > > On Wednesday, September 30, 2015 02:22:31 PM Tomas Vondra wrote: > > >> I think this really

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-10-01 Thread Jim Nasby
On 10/1/15 8:50 AM, Joseph Kregloh wrote: In my testing with pgbench I actually saw a decrease in performance with a ZIL enabled. I ended up just keeping the L2ARC and dropping the. ZIL will not provide you with any speed boost as a database. On a NAS with NFS shared for example, a ZIL would

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-10-01 Thread Joseph Kregloh
On Thu, Oct 1, 2015 at 5:51 PM, Jim Nasby wrote: > On 10/1/15 8:50 AM, Joseph Kregloh wrote: > >> In my testing with pgbench I actually saw a decrease in performance with >> a ZIL enabled. I ended up just keeping the L2ARC and dropping the. ZIL >> will not provide you

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Benjamin Smith
On Wednesday, September 30, 2015 02:22:31 PM Tomas Vondra wrote: > I think this really depends on the workload - if you have a lot of > random writes, CoW filesystems will perform significantly worse than > e.g. EXT4 or XFS, even on SSD. I'd be curious about the information you have that leads

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Tomas Vondra
On 09/30/2015 07:33 PM, Benjamin Smith wrote: On Wednesday, September 30, 2015 02:22:31 PM Tomas Vondra wrote: I think this really depends on the workload - if you have a lot of random writes, CoW filesystems will perform significantly worse than e.g. EXT4 or XFS, even on SSD. I'd be

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Benjamin Smith
On Wednesday, September 30, 2015 09:58:08 PM Tomas Vondra wrote: > On 09/30/2015 07:33 PM, Benjamin Smith wrote: > > On Wednesday, September 30, 2015 02:22:31 PM Tomas Vondra wrote: > >> I think this really depends on the workload - if you have a lot of > >> random writes, CoW filesystems will

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Tomas Vondra
On 09/30/2015 03:45 PM, Patric Bechtel wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Tomas, Tomas Vondra schrieb am 30.09.2015 um 14:01: Hi, ... I've also done a few runs with compression, but that reduces the performance a bit (understandably). I'm somewhat surprised by

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Keith Fiske
On Tue, Sep 29, 2015 at 1:01 PM, Benjamin Smith wrote: > Does anybody here have any recommendations for using PostgreSQL 9.4 > (latest) > with ZFS? > > We've been running both on ZFS/CentOS 6 with excellent results, and are > considering putting the two together. In particular,

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Patric Bechtel
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Tomas, Tomas Vondra schrieb am 30.09.2015 um 14:01: > Hi, > > On 09/30/2015 12:21 AM, Patric Bechtel wrote: >> -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 >> >> Hi Benjamin, >> >> if you're using compression, forget about that. You need to

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Tomas Vondra
Hi, On 09/30/2015 12:21 AM, Patric Bechtel wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Benjamin, if you're using compression, forget about that. You need to synchronize the ashift value to the internal rowsize of you SSD, that's it. Make sure your SSD doesn't lie to you regarding

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Tomas Vondra
Hi, On 09/29/2015 07:01 PM, Benjamin Smith wrote: Does anybody here have any recommendations for using PostgreSQL 9.4 (latest)with ZFS? I think this really depends on the workload - if you have a lot of random writes, CoW filesystems will perform significantly worse than e.g. EXT4 or XFS,

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Benjamin Smith
On Wednesday, September 30, 2015 03:49:44 PM Keith Fiske wrote: > We've run postgres on ZFS for years with great success (first on > OpenSolaris, now on OmniOS, and I personally run it on FreeBSD). The > snapshotting feature makes upgrades on large clusters much less scary > (snapshot and revert

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-30 Thread Keith Fiske
On Wed, Sep 30, 2015 at 4:58 PM, Benjamin Smith wrote: > On Wednesday, September 30, 2015 03:49:44 PM Keith Fiske wrote: > > We've run postgres on ZFS for years with great success (first on > > OpenSolaris, now on OmniOS, and I personally run it on FreeBSD). The > > snapshotting

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-29 Thread Patric Bechtel
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Benjamin, if you're using compression, forget about that. You need to synchronize the ashift value to the internal rowsize of you SSD, that's it. Make sure your SSD doesn't lie to you regarding writing blocks and their respective order. In that

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-29 Thread Jov
I use pg with zfs on freebsd,it work great.I test zol 1 years ago,it will crash the os on load. Some note for pg on freebsd from my experience: 1.if you use compression,8k recordsize make the compression ratio poor.I reach 7.x with gzip using default record size(128k if I remember) while get 2.x

[GENERAL] Postgresql 9.4 and ZFS?

2015-09-29 Thread Benjamin Smith
Does anybody here have any recommendations for using PostgreSQL 9.4 (latest) with ZFS? We've been running both on ZFS/CentOS 6 with excellent results, and are considering putting the two together. In particular, the CoW nature (and subsequent fragmentation/thrashing) of ZFS becomes largely

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-29 Thread John R Pierce
On 9/29/2015 10:01 AM, Benjamin Smith wrote: Does anybody here have any recommendations for using PostgreSQL 9.4 (latest) with ZFS? For databases, I've always used mirrored pools, not raidz*. put pgdata in its own zfs file system in your zpool. on that dedicated zfs, set the blocksize to

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-29 Thread John R Pierce
On 9/29/2015 1:08 PM, Benjamin Smith wrote: put pgdata in its own zfs file system in your zpool. on that dedicated >zfs, set the blocksize to 8k. Based on my reading here, that would be -o ashift=13 ? HowDoesZFSonLinuxHandleAdvacedFormatDrives EG: 2^13 = 8192 sorry, I meant recordsize.

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-29 Thread Larry Rosenman
On Tue, Sep 29, 2015 at 01:08:20PM -0700, Benjamin Smith wrote: > On Tuesday, September 29, 2015 10:35:28 AM John R Pierce wrote: > > On 9/29/2015 10:01 AM, Benjamin Smith wrote: > > > Does anybody here have any recommendations for using PostgreSQL 9.4 > > > (latest) with ZFS? > > > > For

Re: [GENERAL] Postgresql 9.4 and ZFS?

2015-09-29 Thread Benjamin Smith
On Tuesday, September 29, 2015 10:35:28 AM John R Pierce wrote: > On 9/29/2015 10:01 AM, Benjamin Smith wrote: > > Does anybody here have any recommendations for using PostgreSQL 9.4 > > (latest) with ZFS? > > For databases, I've always used mirrored pools, not raidz*. > put pgdata in its own