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 work well. ZIL is more for data protection than anything. I run in Production FreeBSD 10.1 with an NVMe mirror for L2ARC, the rest of the storage is spinning drives. With a combination of filesystem compressions. For example, archival tablespaces and the log folder are on gzip compression on an external array. Faster stuff like the xlog are lz4 and on an internal array.
I'm not a ZFS expert, but my understanding is that a ZIL *that has lower latency than main storage* can be a performance win. This is similar to the idea of giving pg_xlog it's own dedicated volume so that it's not competing with all the other IO traffic every time you do a COMMIT.
Recent versions of Postgres go to a lot of trouble to make fsync as painless as possible, so a ZIL might not help much in many cases. Where it could still help is if you're running synchronous_commit = true and you consistently get lower latency on the ZIL than on the vdev's; that will make every COMMIT run faster.
(BTW, this is all based on the assumption that ZFS treats fsync as a synchronous request.)
-- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
