Sub optimal performance with default setting of Postgresql with FreeBSD 9.1 on ZFS

[Patrick Dung]

Hi,

Updated information in this post.

I have installed Postgresql 9.2.2 (complied by gcc) in FreeBSD 9.1 i386.
The pgsql base directory is in a ZFS dataset.

I
have noticed the performance is sub-optimal, but I know the default
setting should be the most safest one to be use (without possible data
corruption/loss).

a) I use OTRS ticketing system version 3.1, the backend is PostgreSQL.
The user interactive response is not slow (switching web pages or create a change).

b) There is a benchmark in the support module of OTRS.
It tested insert,update,select and delete performance.
The response time is slow (>10 sec), except select.

I have done some research on web, with below settings (just one change, not both), the performance returned to normal:

1) Disabled sync in the pgsql dataset in ZFS
zfs set sync=disabled mydata/pgsql
or
2) In
postgresql.conf, set synchronous_commit from on to off

I know the above setting would lead to data loss (e.g.power goes off), any comments?

PS:
1) I have tried to use primarycache/secondarycache=metadata/none, it do not seem to help.

2)
I have tried the default setting on Linux too:
RHEL 6.3, ext4, stock postgresql 8.x, OTRS 3.1.
The web site is responsive and the benchmark result is more or less the same as FreeBSD with the 'sync' turned off.

3)
For FreeBSD, same setting with Postgresql on UFS:
The performance is between ZFS (default, sync enabled) and ZFS (sync disabled).

Thanks,
Patrick

--- On Mon, 1/7/13, Patrick Dung <***@yahoo.com.hk> wrote:

From: Patrick Dung <***@yahoo.com.hk>
Subject: Sub optimal performance with default setting of Postgresql with FreeBSD 9.1 on ZFS
To: pgsql-***@postgresql.org
Date: Monday, January 7, 2013, 11:32 PM

Hi,

I have installed Postgresql 9.2.2 (complied by gcc) in FreeBSD 9.1 i386.
The pgsql base directory is in a ZFS dataset.

I have noticed the performance is sub-optimal, but I know the default setting should be the most safest one to be use (without possible data corruption/loss).

a) I use OTRS ticketing system, the backend is PostgreSQL.
The user interactive response is not slow (switching web pages or create a change).

b) There is a benchmark in the support module of OTRS.
It tested insert,update,select and delete performance.
The response time is slow (>10 sec), except select.

I have done some research on web, with below settings (just one change, not both), the performance returned to normal:

1) Disabled sync in the pgsql dataset in ZFS
zfs set sync=disabled mydata/pgsql
or
2) In
postgresql.conf, set synchronous_commit from on to off

I know the above setting would lead to data loss (e.g.power goes off), any comments?

PS:
1) I have tried to use primarycache/secondarycache=metadata/none, it do not seem to help.

2)
I have tried the default setting on Linux too:
RHEL 6.3, stock postgresql 8.x, OTRS 3.1.
The web site is responsive and the benchmark result is more or less the same as FreeBSD with the 'sync' turned off.

Thanks,
Patrick

[k***@rice.edu]

Hi Patrick,

You really need a flash ZIL with ZFS to handle syncs effectively. Setting
the sync_commit to off is the best you can do without it. Not that that
is bad, we do that here as well.

Regards,
Ken

Post by Patrick Dung
Hi,
Updated information in this post.
I have installed Postgresql 9.2.2 (complied by gcc) in FreeBSD 9.1 i386.
The pgsql base directory is in a ZFS dataset.
I
have noticed the performance is sub-optimal, but I know the default
setting should be the most safest one to be use (without possible data
corruption/loss).
a) I use OTRS ticketing system version 3.1, the backend is PostgreSQL.
The user interactive response is not slow (switching web pages or create a change).
b) There is a benchmark in the support module of OTRS.
It tested insert,update,select and delete performance.
The response time is slow (>10 sec), except select.
1) Disabled sync in the pgsql dataset in ZFS
zfs set sync=disabled mydata/pgsql
or
2) In
postgresql.conf, set synchronous_commit from on to off
I know the above setting would lead to data loss (e.g.power goes off), any comments?
1) I have tried to use primarycache/secondarycache=metadata/none, it do not seem to help.
2)
RHEL 6.3, ext4, stock postgresql 8.x, OTRS 3.1.
The web site is responsive and the benchmark result is more or less the same as FreeBSD with the 'sync' turned off.
3)
The performance is between ZFS (default, sync enabled) and ZFS (sync disabled).
Thanks,
Patrick
Subject: Sub optimal performance with default setting of Postgresql with FreeBSD 9.1 on ZFS
Date: Monday, January 7, 2013, 11:32 PM
Hi,
I have installed Postgresql 9.2.2 (complied by gcc) in FreeBSD 9.1 i386.
The pgsql base directory is in a ZFS dataset.
I have noticed the performance is sub-optimal, but I know the default setting should be the most safest one to be use (without possible data corruption/loss).
a) I use OTRS ticketing system, the backend is PostgreSQL.
The user interactive response is not slow (switching web pages or create a change).
b) There is a benchmark in the support module of OTRS.
It tested insert,update,select and delete performance.
The response time is slow (>10 sec), except select.
1) Disabled sync in the pgsql dataset in ZFS
zfs set sync=disabled mydata/pgsql
or
2) In
postgresql.conf, set synchronous_commit from on to off
I know the above setting would lead to data loss (e.g.power goes off), any comments?
1) I have tried to use primarycache/secondarycache=metadata/none, it do not seem to help.
2)
RHEL 6.3, stock postgresql 8.x, OTRS 3.1.
The web site is responsive and the benchmark result is more or less the same as FreeBSD with the 'sync' turned off.
Thanks,
Patrick

[Patrick Dung]

Hi Ken,

Thanks for reply.
After researching, I get more understanding with the importance of the ZIL
http://constantin.glez.de/blog/2010/07/solaris-zfs-synchronous-writes-and-zil-explained
So the performance issue is the ZIL...

BTW, using a standard UFS+software update is still slower than Linux or ZFS without sync.

Best regards,
Patrick

--- On Tue, 1/8/13, ***@rice.edu <***@rice.edu> wrote:

From: ***@rice.edu <***@rice.edu>
Subject: Re: [PERFORM] Sub optimal performance with default setting of Postgresql with FreeBSD 9.1 on ZFS
To: "Patrick Dung" <***@yahoo.com.hk>
Cc: pgsql-***@postgresql.org
Date: Tuesday, January 8, 2013, 2:28 AM

Hi Patrick,

You really need a flash ZIL with ZFS to handle syncs effectively. Setting
the sync_commit to off is the best you can do without it. Not that that
is bad, we do that here as well.

Regards,
Ken

Post by Patrick Dung
Hi,
Updated information in this post.
I have installed Postgresql 9.2.2 (complied by gcc) in FreeBSD 9.1 i386.
The pgsql base directory is in a ZFS dataset.
I
  have noticed the performance is sub-optimal, but I know the default
setting should be the most safest one to be use (without possible data
corruption/loss).
a) I use OTRS ticketing system version 3.1, the backend is PostgreSQL.
The user interactive response is not slow (switching web pages or create a change).
b) There is a benchmark in the support module of OTRS.
It tested insert,update,select and delete performance.
The response time is slow (>10 sec), except select.
1) Disabled sync in the pgsql dataset in ZFS
zfs set sync=disabled mydata/pgsql
or
2) In
  postgresql.conf, set synchronous_commit from on to off
I know the above setting would lead to data loss (e.g.power goes off), any comments?
1) I have tried to use primarycache/secondarycache=metadata/none, it do not seem to help.
2)
RHEL 6.3, ext4, stock postgresql 8.x, OTRS 3.1.
The web site is responsive and the benchmark result is more or less the same as FreeBSD with the 'sync' turned off.
3)
The performance is between ZFS (default, sync enabled) and ZFS (sync disabled).
Thanks,
Patrick
Subject: Sub optimal performance with default setting of Postgresql with FreeBSD 9.1 on ZFS
Date: Monday, January 7, 2013, 11:32 PM
Hi,
I have installed Postgresql 9.2.2 (complied by gcc) in FreeBSD 9.1 i386.
The pgsql base directory is in a ZFS dataset.
I have noticed the performance is sub-optimal, but I know the default setting should be the most safest one to be use (without possible data corruption/loss).
a) I use OTRS ticketing system, the backend is PostgreSQL.
The user interactive response is not slow (switching web pages or create a change).
b) There is a benchmark in the support module of OTRS.
It tested insert,update,select and delete performance.
The response time is slow (>10 sec), except select.
1) Disabled sync in the pgsql dataset in ZFS
zfs set sync=disabled mydata/pgsql
or
2) In
  postgresql.conf, set synchronous_commit from on to off
I know the above setting would lead to data loss (e.g.power goes off), any comments?
1) I have tried to use primarycache/secondarycache=metadata/none, it do not seem to help.
2)
RHEL 6.3, stock postgresql 8.x, OTRS 3.1.
The web site is responsive and the benchmark result is more or less the same as FreeBSD with the 'sync' turned off.
Thanks,
Patrick