Re: Slow Query / Check Point Segments

John R Pierce wrote:
> I know the database has a lot of write volume overall, and its only
> one of several databases running in different zones on the server. I
> know nothing about the SAN, I suspect its a EMC Symmetrix of some
> sort. Probably a generation or two behind latest. The operations
> people are used to running large oracle databases.

One thing you might try is making the PostgreSQL install act more like
an Oracle one in terms of how it does writes. By default, PostgreSQL
does its WAL writes by writing and then calling a sync method. On
Solaris, you should be able to safely change this in the postgresql.conf
file to be:

wal_sync_method=open_datasync

Which I don't think is the default (you can confirm with "show
wal_sync_method;" via psql on your database). That will use O_DSYNC
writes, which are more like how Oracle approaches this and therefore
potentially a better tuned path for your install.

More on this subject, including idea for further tweaking

http://www.postgresql.org/docs/8.4/static/runtime-config-wal.html
http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm

From what you've shown and described, I'm not sure what other
PostgreSQL tuning you might do to improve the specific symptoms you're
seeing. The particular issue you've got I'd normally start attacking on
the filesystem and hardware side of things. There's a reason why many
people avoid SANs in this context, there's a lot of ways you can screw
up in this particular aspect of their performance relative to what you
get with direct attached storage, and it's hard to tell which you've run
into when a problem does pop up.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com