----- Original Message ----
Greg,
Thanks for the reply. Unfortunately, the project I'm working is trying to provide "database-as-a-service" functionality, so I can't really tune the DB since the application/load will vary by customer (and the whole idea was to abstract all the low-level tuning parameters from the customer because we aren't expecting "power" users).
Bob
From: Greg Smith <gsmith@xxxxxxxxxxxxx>
To: jiniusatwork-postgresql@xxxxxxxxx
Cc: pgsql-general@xxxxxxxxxxxxxx
Sent: Sunday, February 3, 2008 8:43:28 PM
Subject: Re: Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
On Thu, 31 Jan 2008, jiniusatwork-postgresql@xxxxxxxxx wrote:
> I haven't done any tuning as of yet. I'm running with the default
> settings produced by initdb.
The default settings are junk and the disk pattern will change once
they're set correctly, so tuning ZFS first and then PostgreSQL is probably
backwards. You may return to tuning the database again after ZFS, but for
the first shot I'd start with a somewhat tuned DB server and then play
with the filesystem.
Put the major postgresql.conf parameters in the right
ballpark--shared_buffers, effective_cache_size, and a large setting for
checkpoint_segments since I think you mentioned a write-heavy benchmark.
You should do your own experiments with wal_sync_method, I haven't seen
any tests that are really definitive on the best setting there for S10+ZFS
and it kind of depends on the underlying hardware--try both open_datasync
and fdatasync.
To: jiniusatwork-postgresql@xxxxxxxxx
Cc: pgsql-general@xxxxxxxxxxxxxx
Sent: Sunday, February 3, 2008 8:43:28 PM
Subject: Re: Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
On Thu, 31 Jan 2008, jiniusatwork-postgresql@xxxxxxxxx wrote:
> I haven't done any tuning as of yet. I'm running with the default
> settings produced by initdb.
The default settings are junk and the disk pattern will change once
they're set correctly, so tuning ZFS first and then PostgreSQL is probably
backwards. You may return to tuning the database again after ZFS, but for
the first shot I'd start with a somewhat tuned DB server and then play
with the filesystem.
Put the major postgresql.conf parameters in the right
ballpark--shared_buffers, effective_cache_size, and a large setting for
checkpoint_segments since I think you mentioned a write-heavy benchmark.
You should do your own experiments with wal_sync_method, I haven't seen
any tests that are really definitive on the best setting there for S10+ZFS
and it kind of depends on the underlying hardware--try both open_datasync
and fdatasync.
Greg,
Thanks for the reply. Unfortunately, the project I'm working is trying to provide "database-as-a-service" functionality, so I can't really tune the DB since the application/load will vary by customer (and the whole idea was to abstract all the low-level tuning parameters from the customer because we aren't expecting "power" users).
Bob
