ZFS and Postgresql - WASRe: Best OS for Postgres 8.2

Hello Ian,

I have done some testing with postgresql and ZFS on Solaris 10 11/06.
While I work for Sun, I dont claim to be a ZFS expert (for that matter
not even Solaris or PostgreSQL).

Lets first look at the scenarios of how postgresql can be deployed on
Solaris
First the Solaris Options
1. UFS with default setup (which is buffered file system)
2. UFS with forcedirectio option (or unbuffered file system)
3. ZFS by default (128K recordsize with checksum but no compression)
4. ZFS with Compression (Default compression using LZ* algorithm .. now
even a gzip algorithm is supported)

(For simplicity I am not considering RAID levels here since that
increases the number of scenarios quite a bit and also skipping Solaris
Volume Manager - legacy volume management capabilities in Solaris)

Now for the postgresql.conf options
a. wal_sync_method set to default - maps to opendatasync
b. wal_sync_method set to fdatasync

(assuming checkpoint_segments and wal_buffers are high already)

(This are my tests results based on the way I used the workload and
your mileage will vary)
So with this type of configurations I found the following
1a. Default UFS with default wal_sync_method - Sucks for me mostly
using pgbench or EAStress type workloads
1b. Default UFS with fdatasync - works well specially increasing
segmapsize from default 12% to higher values
2a ForcedirectIO with default wal_sync_method - works well but then is
limited to hardware disk performances
(In a way good to have RAID controller with big Write cache for
it.. One advantage is lower system cpu utilization)
2b Didn't see huge difference from 2a in this case
3a It was better than 1a but still limited
3b It was better even than 3a and 1b but cpu utilization seemed higher
4a - Didn't test this out
4b - Hard to say since in my case since I wasnt disk bound (per se) but
CPU bound. The compression helps when number of IOs to the disk are high
and it helps to cut it down at the cost of CPU cycles

Overall ZFS seems to improve performance with PostgreSQL on Solaris 10
with a bit increased system times compared to UFS.
(So the final results depends on the metrics that you are measuring the
performance :-) ) (ZFS engineers are constantly improving the
performance and I have seen the improvements from Solaris 10 1/06
release to my current setup)

Of course I haven't compared against any other OS.. If someone has
already done that I would be interested in knowing the results.

Now comes the thing that I am still exploring
* Do we do checksum in WAL ? I guess we do .. Which means that we are
now doing double checksumming on the data. One in ZFS and one in
postgresql. ZFS does allow checksumming to be turned off (but on new
blocks allocated). But of course the philosophy is where should it be
done (ZFS or PostgreSQL). ZFS checksumming gives ability to correct the
data on the bad checksum if you use mirror devices. PostgreSQL doesnt
give that ability and in case of an error would fail. ( I dont know the
exact behavior of postgresql when it would encounter a failed checksum)

Hope this helps.

Regards,
Jignesh

李彦 Ian Li wrote:
> In #postgresql on freenode, somebody ever mentioned that ZFS from
> Solaris helps a lot to the performance of pgsql, so dose anyone have
> information about that?
>
> Steve Atkins wrote:
>>
>> On May 7, 2007, at 2:55 PM, David Levy wrote:
>>
>>> Hi,
>>>
>>> I am about to order a new server for my Postgres cluster. I will
>>> probably get a Dual Xeon Quad Core instead of my current Dual Xeon.
>>> Which OS would you recommend to optimize Postgres behaviour (i/o
>>> access, multithreading, etc) ?
>>>
>>> I am hesitating between Fedora Core 6, CentOS and Debian. Can anyone
>>> help with this ?
>>
>> Well, all three you mention are much the same, just with a different
>> badge on the box, as far as performance is concerned. They're all
>> going to be a moderately recent Linux kernel, with your choice
>> of filesystems, so any choice between them is going to be driven
>> more by available staff and support or personal preference.
>>
>> I'd probably go CentOS 5 over Fedora just because Fedora doesn't
>> get supported for very long - more of an issue with a dedicated
>> database box with a long lifespan than your typical desktop or
>> interchangeable webserver.
>>
>> I might also look at Solaris 10, though. I've yet to play with it
>> much, but it
>> seems nice, and I suspect it might manage 8 cores better than current
>> Linux setups.
>>
>> Cheers,
>> Steve
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>
>
> Regards
>
> Ian
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq