Thread: Performance problems with Postgresql/ZFS/Non-global zones on Solaris?

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
>

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

[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

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

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