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

[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

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

As it turns out, I think the ZFS-on-RAID setup I had is the problem[1]the suggestions made previously helped a bit, but not nearly enough to overcome the 50% drop originally noted.. 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

----- Original Message ----
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.

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 abstract all 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