PostgreSQL on ZFS

[alp]

Hello. I'm going to deploy new database server. Has someone tuned PostgreSQL on ZFS? It's interesting, what is the preferred value for wal_sync_method? Has someone turned full_page_writes off?

 

[vermaden]

I remember that its recommended to use recordsize=4k for pools prepared for PostgreSQL DB.

For x86 systems, when the db_block_size and the recordsize are aligned to the system page size of 4k, then it is better to set zfs_immediate_write_sz to a little less than 4096, as 4000.

Also check this one:
http://solarisinternals.com/wiki/index.php/ZFS_for_Databases

 

[alp]

I remember that its recommended to use recordsize=4k for pools prepared for PostgreSQL DB.

It seems it should be 8k (default DB page size) for data files.

Also check this one:
http://solarisinternals.com/wiki/index.php/ZFS_for_Databases

Yes, I've read this article , it was useful. However, almost all links for "PostgreSQL ZFS" discuss Solaris installations. And I'd like to hear some FreeBSD-specific notes. For example, long ago it was recommended to set wal_sync_method to open_sync. Is it still true for modern FreeBSD installations? How does ZFS influences this decision?

 

[vermaden]

I do not have DB experience on ZFS, so wait for others to speak

 

[AndyUKG]

Yes, I've read this article , it was useful. However, almost all links for "PostgreSQL ZFS" discuss Solaris installations. And I'd like to hear some FreeBSD-specific notes. For example, long ago it was recommended to set wal_sync_method to open_sync. Is it still true for modern FreeBSD installations? How does ZFS influences this decision?

I don't know anything specific to FreeBSD or PostgreSQL, but the option you mention seems to relate to DirectIO. ZFS doesn't support DirectIO, but you can achieve some of the same effects/benefits by setting the ARC cache to meta-data only. I've seen this discussed in relation to MySQL but I guess it will apply to other SQL database systems. This will prevent both the database engine and the OS caching the same data...

thanks Andy.