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