Re: Urgent: Tuning strategies? - Mailing list pgsql-general

On Tue, 25 Jun 2002, Markus Wollny wrote:

> Its a postgresql-7.2.1-installation under SuSE 7.3 on a 1GB RAM,
> 4xPIII550MHz Xeon machine with ~30MB storage on RAID 5 (3 disks),
> filesystem is ext3.

I assume you mean 30 GB of storage, not 30 MB. You don't say what kind
of RAID you have. Software? Hardware? IDE drives? SCSI drives? If IDE,
does each drive have its own controller? What drives are you using?

Anyway, if you're doing a lot of updates, you definitely want to get the
log file on to a separate disk, and one that's preferably not RAID-5.
(RAID-5 writes tend to be very slow.) Consider adding a mirrored pair of
disks just for the log file.

> fsync = false

You don't like your data? If not, you should ditch the RAID-5 as
well, and use a striped (RAID-0) pair for data and the other disk
for logs. Otherwise you should turn on fsync, since the RAID is
otherwise not giving you much useful protection should the database
crash.

> max_connections = 256
> shared_buffers = 56320

That's a lot of shared buffers: 450 MB worth. Since the OS is also
doing caching, you're pretty much maximizing your changes that a
block will be cached both in the shared buffers and in the OS buffer
cache. Reducing your shared buffers to a few thousand might increase
your cache hit rate.

> sort_mem = 64336

This is probably a bit high, since a back-end actually uses more than 3x
the amount of memory specified in sort_mem when it does a sort. (This
is due to the way memory is counted--this is more accurate in 7.3, I
think.) So this will let a backend grow to 200 MB or more when sorting,
which may drive it into swap, which will then slow down your sort,
rather than speeding it up. I generally use 16-32 MB for sort_mem. Note
you can always increase it for a particular connection using the SET
command if you're rebuilding indexes or whatever.

> Mem:  1029400K av, 1023660K used,    5740K free,  0K shrd,  2932K buff
> Swap: 2097136K av,  459800K used, 1637336K free           699220K cached

Ouch! You are being hosed over big time; you should never, ever see any
significant swapping in a database server. (The I/O is supposed to be
there for the database; don't waste it on moving programs in and out
of memory!) Start cranking down memory limits all over the place until
swapping goes away. Add more memory if you have to. First place to start
is with the shared_buffers and sort_mem.

> A complete dump.sql of the database-installation is roughly 300MB in

So it sounds like you don't have much data. It sounds like you can
easily fit into 10 GB, and have plenty of room to grow to many
times your current size. If you're doing a lot of updates, and you
don't anticipate really growing into that 30 GB, drop the third
volume of your RAID-5 and mirror the other pair of disks, for 10
GB of storage. Writes will be much faster.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC