Re: "--tuning" compile and runtime option (?)

Bruce Momjian wrote:
>
> Well, again, I will write a performance tuning article this month, which
> hopefyully will help people.
>
> My recommendation on shared memory is that if you have a machine that is
> going to be used only for PostgreSQL, the shared memory should be
> increased to the point where you are not seeing any swap page-ins during
> normal use. I know you have the kernel buffer cache for all unused
> memory, but those pages are copied in and out of the PostgreSQL buffer
> cache for processing, which can be an expensive operation.
>
> Now how do you automate something to increase shared memory until there
> are no page swap-ins under normal use. I think the administrator will
> have to be involved because a script has no idea what a normal load
> looks like. The best we could do is to monitor swap-ins as part of the
> running server and report to the administrator that there is extra
> memory around that could be used for shared memory.

Brilliant. Thanks for that - it's exactly the sort of information / statistics
stuff that it is useful to know.

I use Progress RDBMS on a few sites. On a Progress database I get this sort of
information which can help me tune things:

Activity - Sampled at 04/11/01 12:32 for 892:23:25.

Event Total Per Sec Event Total Per Sec
Commits 50518 0.0 Undos 24 0.0
Record Updates 72407 0.0 Record Reads 121294681 37.7
Record Creates 37065 0.0 Record Deletes 19807 0.0
DB Writes 25720 0.0 DB Reads 1551040 0.4
BI Writes 14701 0.0 BI Reads 14534 0.0
AI Writes 0 0.0
Record Locks 645952 0.2 Record Waits 0 0.0
Checkpoints 62 0.0 Buffers Flushed 13102 0.0

Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 0 % Writes by BIW 0 % Writes by AIW 0 %
Buffer Hits 16 %
DB Size 96 MB BI Size 3192 K AI Size 0 K
FR chain 0 blocks RM chain 1 blocks
Shared Memory 29864 K Segments 1

8 Servers, 7 Users (0 Local, 7 Remote, 0 Batch),0 Apws

Or, for a more reasonable length of sample:

Activity - Sampled at 04/11/01 12:42 for 0:09:26.

Event Total Per Sec Event Total Per Sec
Commits 14 0.0 Undos 0 0.0
Record Updates 7 0.0 Record Reads 90488 159.8
Record Creates 1 0.0 Record Deletes 0 0.0
DB Writes 38 0.0 DB Reads 1636 2.8
BI Writes 5 0.0 BI Reads 0 0.0
AI Writes 0 0.0
Record Locks 69 0.1 Record Waits 0 0.0
Checkpoints 0 0.0 Buffers Flushed 0 0.0

Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 0 % Writes by BIW 0 % Writes by AIW 0 %
Buffer Hits 99 %
DB Size 96 MB BI Size 3192 K AI Size 0 K
FR chain 0 blocks RM chain 1 blocks
Shared Memory 29864 K Segments 1

8 Servers, 9 Users (0 Local, 9 Remote, 0 Batch),0 Apws

I find this is quite a straightforward and useful set of statistics. Just having
this sort of functionality easily available gets me used to the sorts of numbers I
can expect in different hardware environments. It is then simple to conduct basic
tuning by running reports (or other operations) and seeing the sorts of numbers you
get for the sample period.

Of course Progress has a bunch more stuff you can tune, including separate processes
for asynchronously writing database pages, or their after-image and before-image
files. I don't have any databases that get that arcane though, hence the APW, BIW
and AIW statistics are zero above.

Regards,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267