Обсуждение: Shared_buffers and kernel parameters, tuning

After days of searching and testing I have come up with this way of
configuring our postgresql 7.2 db.
I have not yet increased my shared_buffer as high as suggested below on our
prod machine (24-7 high traffic), but after testing on our dev machines
this allows at least the databse to start up. It is very difficult to test
the actual performance since there is a hugh difference in traffic on dev
and prod.
Here is what we have:

We have a high traffic system with a database described as followed:

4 pentium 3 633 cpu's
3753456 kB RAM (3.5 Gb)
Red Hat Linux 7.2
postgresql 7.2


What I like to do is:

/proc/sys/kernel/sem=250 32000 100 500 (after advise from forum/docs)
/proc/sys/kernel/shmmax=1921769472 (RAM / 2 * 1024 - this
piece of math I got from some of oracle's support pages (ooopps) actually )

/proc/sys/kernel/shmall=1921769472 (RAM / 2 * 1024)

in postgresql.conf:

shared_buffers = 117248 (shmmax / 2 / 1024 / 8 ) This I got from this forum.


Does this sound right or am I totally out of bounds here? I have, as said
before done this on our dev macine ( a lot smaller machine ), but it would be nice with some feedback ..

Thanx in advance for response.

/rune

"Lending, Rune" <rune.lending@inpoc.com> writes:
> in postgresql.conf:
> shared_buffers = 117248 (shmmax / 2 / 1024 / 8 ) This I got from this forum.

This is a factor of 10, maybe a factor of 100, more than you need.
It's usually better to let the kernel manage memory.  See the
pgsql-performance archives (whenever they're back up :-().

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 05 June 2003 01:10, Lending, Rune wrote:
> It is very difficult to test
> the actual performance since there is a hugh difference in traffic on dev
> and prod.

I would resolve this problem as well. How can you know any of your changes
won't cause severe problems?

Using your favorite scripting language, and your knowledge of what statements
are being executed, you should devise some test processes that mimic the
statements being run on production. Then, have a computer (or several, if
need be) launch a whole bunch of processes that run these statements against
the dev server. It isn't too hard to generate appropriate traffic on the dev
server. And you will be very pleased with the ability to experiment with more
load and various settings. Plus, you can measure how long each statement
takes to run, and track how indexes and variations on statements will affect
performance.

I think this is fundamental to any performance tweaks. If you have no way to
control the traffic to the server, and you have no way of measuring the
performance, how can you tell if what you did helped or hurt?

- --
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE+4QlSWgwF3QvpWNwRApJxAKC1XU/h2ypAcCf0ewJU+xuD14powQCgqMOT
LKUWPrHPEugf5r7C+kP3eSM=
=XkNs
-----END PGP SIGNATURE-----