Обсуждение: Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
От
jiniusatwork-postgresql@yahoo.com
Дата:
In the hopes that someone has already blazed this trail ... I'm running Postgresql (v8.1.10) on Solaris 10 (Sparc) from within a non-global zone. I originally had the database "storage"in the non-global zone (e.g. /var/local/pgsql/data on a UFS filesystem) and was getting performance of "X" (e.g.from a TPC-like application: http://www.tpc.org). I then wanted to try relocating the database storage from the zone(UFS filesystem) over to a ZFS-based filesystem (where I could do things like set quotas, etc.). When I do this, I getroughly half the performance (X/2) I did on the UFS system. I did try to run some low level I/O tests (i.e. http://www.iozone.org/)to see if there was a filesystem performance difference, but there doesn't seem to be any betweenthe UFS and ZFS numbers I'm seeing. So, I was hoping someone might have already tried this type of setup and can provide some suggestions for helping boost theZFS performance numbers (and save me a truss debugging session). Are there any known issues with using Postgresql and ZFS? Things I have already tried: - setting the record size of the ZFS filesystem to be 8K (vs 128K ) default -- no noticeable performance difference - addind the ZFS filesystem as a loopback (i.e. lofs) filesystem in the non-global zone -- no noticeable difference - adding the ZFS filesystem as a dataset filesystem in the non-global zone -- no noticeable difference Bob
Bob, First, Which exact version of S10 are you using ? In general, I have seen having logs and data on different pools helps. Also, let us know your wal tunning parameters like commit_delay, fsync. If you are using S10u4, setting commit_delay to a non zero number should help get better performance. Rgds Mayuresh jiniusatwork-postgresql@yahoo.com wrote: > In the hopes that someone has already blazed this trail ... > > > I'm running Postgresql (v8.1.10) on Solaris 10 (Sparc) from within a non-global zone. I originally had the database "storage"in the non-global zone (e.g. /var/local/pgsql/data on a UFS filesystem) and was getting performance of "X" (e.g.from a TPC-like application: http://www.tpc.org). I then wanted to try relocating the database storage from the zone(UFS filesystem) over to a ZFS-based filesystem (where I could do things like set quotas, etc.). When I do this, I getroughly half the performance (X/2) I did on the UFS system. I did try to run some low level I/O tests (i.e. http://www.iozone.org/)to see if there was a filesystem performance difference, but there doesn't seem to be any betweenthe UFS and ZFS numbers I'm seeing. > > So, I was hoping someone might have already tried this type of setup and can provide some suggestions for helping boostthe ZFS performance numbers (and save me a truss debugging session). > > Are there any known issues with using Postgresql and ZFS? > > Things I have already tried: > > - setting the record size of the ZFS filesystem to be 8K (vs 128K ) default -- no noticeable performance difference > > - addind the ZFS filesystem as a loopback (i.e. lofs) filesystem in the non-global zone -- no noticeable difference > > - adding the ZFS filesystem as a dataset filesystem in the non-global zone -- no noticeable difference > > Bob > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Re: Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
От
jiniusatwork-postgresql@yahoo.com
Дата:
Mayuresh, comments in-lined, below ... ----- Original Message ---- > From: Mayuresh Nirhali <Mayuresh.Nirhali@Sun.COM> > To: jiniusatwork-postgresql@yahoo.com > Cc: pgsql-general@postgresql.org > Sent: Thursday, January 31, 2008 6:23:23 AM > Subject: Re: [GENERAL] Performance problems with Postgresql/ZFS/Non-global zones on Solaris? > > Bob, > > First, > Which > exact > version > of > S10 > are > you > using > ? > more /etc/release Solaris 10 8/07 s10s_u4wos_12b SPARC Copyright 2007 Sun Microsystems, Inc. All Rights Reserved. Use is subject to license terms. Assembled 16 August 2007 > pkginfo SUNWpostgr-server system SUNWpostgr-server The programs needed to create and run a PostgreSQL 8.1.10 server > > In > general, > I > have > seen > having > logs > and > data > on > different > pools > helps. > Also, > let > us > know > your > wal > tunning > parameters > like > commit_delay, > fsync. > If > you > are > using > S10u4, > setting > commit_delay > to > a > non > zero > number > should > help > get > better > performance. > Thanks for the info. I'll give it a shot and let you know. Bob
Re: Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
От
jiniusatwork-postgresql@yahoo.com
Дата:
[Following up on my own message.] > Also, > > > let > > > us > > > know > > > your > > > wal > > > tunning > > > parameters > > > like > > > commit_delay, > > > fsync. I haven't done any tuning as of yet. I'm running with the default settings produced by initdb. Bob
On Thursday 31 January 2008 07:08, jiniusatwork-postgresql@yahoo.com wrote: > [Following up on my own message.] > > > Also, > > > let > > > > us > > > > know > > > > your > > > > wal > > > > tunning > > > > parameters > > > > like > > > > commit_delay, > > > > fsync. > > I haven't done any tuning as of yet. I'm running with the default settings > produced by initdb. > Don't even bother trying to tune zfs untill after you've tuned postgres, otherwise your wasting your time. -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Re: Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
От
jiniusatwork-postgresql@yahoo.com
Дата:
Don't even bother trying to tune zfs untill after you've tuned postgres,
otherwise your wasting your time.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
otherwise your wasting your time.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
As it turns out, I think the ZFS-on-RAID setup I had is the problem[1]. After running some more I/O tests with "fsync" turned on, I noticed that filesystem "writes" were about 400% slower. Running PostgreSQL without "fsync" made the application run about 1.5 times faster on ZFS versus UFS. So, now it's back to the drawing board to figure out if I can make the synchronous writes faster.
Bob
[1] the suggestions made previously helped a bit, but not nearly enough to overcome the 50% drop originally noted.
On Thu, 31 Jan 2008, jiniusatwork-postgresql@yahoo.com 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 Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Re: Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
От
jiniusatwork-postgresql@yahoo.com
Дата:
----- 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@gregsmith.com>
To: jiniusatwork-postgresql@yahoo.com
Cc: pgsql-general@postgresql.org
Sent: Sunday, February 3, 2008 8:43:28 PM
Subject: Re: [GENERAL] Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
On Thu, 31 Jan 2008, jiniusatwork-postgresql@yahoo.com 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@yahoo.com
Cc: pgsql-general@postgresql.org
Sent: Sunday, February 3, 2008 8:43:28 PM
Subject: Re: [GENERAL] Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
On Thu, 31 Jan 2008, jiniusatwork-postgresql@yahoo.com 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
jiniusatwork-postgresql@yahoo.com wrote: > 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 abstractall the low-level tuning parameters from the customer because we aren't expecting "power" users). Tune for your hardware, ram, disc array and config. Not for specific workload. There is no way the initdb defaults can be sane for any production setup. -- Best regards, Hannes Dorbath