Re: "--tuning" compile and runtime option (?) - Mailing list pgsql-hackers

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
RecordCreates     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
AIWrites         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
FRchain          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
RecordCreates         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
AIWrites         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
FRchain          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@catalyst.net.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