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

Hi!

> -----Ursprüngliche Nachricht-----
> Von: Curt Sampson [mailto:cjs@cynic.net]
> Gesendet: Dienstag, 25. Juni 2002 13:53
> An: Markus Wollny
> Cc: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] Urgent: Tuning strategies?
>
>
> 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?

Yes, sorry, 30GB storage, hardware-RAID5, smart-array-controller 64MB
cache and 5 (not 3) 18GB scsi-disks.

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

As far as I can see, logfile isn't a problem on debug_level 0 (which I
always set for production state) because there's hardly anything to
write except on vacuum analyze (which usually only takes place when
hardly anybody does anything on the system anyway).

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

There's an UPS connected to the server, ext3 als fs, backups are
performed each day, it's a frontend-db anyway, so except community-wise
we wouldn't even loose one day's updates in the worst case scenario, so
I don't see much point in not setting fsync to false for faster updates.
And community-data's not that valuable to us, we can without doubt risk
loosing one day's updates.

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

Okay, I'll try that - I just followed recommendations like "you just
cannot have enough shared buffers". Seems to be wrong, though. Thanks
for that hint.

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

Ah, that might be one big issue - I often noticed several backends
growing far beyond 200MB size, which got me worried...

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

There's not really that many updates or inserts, it's mostly selects.
But I'll have to consider this right enough.

Thank you very much for your help! It's hard finding some of the correct
settings for one's own scenario without any beforehand experience :)

So thanks a lot :)