DB Tuning Notes for comment...

The notes below are the results of various tuning issues experienced 
recently on a large database (several GB) that has many tables and a high 
transient data flow (ie. thousands of records added, updated, and deleted 
every hour) on a few tables. This kind of data flow is not at all well 
handled by the default postgresql settings. Experiments have also been 
conducted using a much smaller test database with a text field written to a 
TOAST relation (which is what the large table contains).

I think this example is useful because it encapsulates in several hours the 
level of updates that most of us see in several weeks, so the rules below 
should apply equally well but in different time frames, with provisos as 
noted. The database in question is subject to periodic bulk deletes where 
up to 50% of the rows in the large table are deleted. It is also has to run 
24x7.

Any comments or suggestions would be welcome.


Tuning
======

1. max_fsm_relations
--------------------

First of all, the free space manager is useless at managing free space if 
it can not map all relations (including system relations and toast 
relations). The following query should give the correct ballpark:
    select count(*) from pg_class where not relkind in ('i','v');

Set max_fsm_relations to a number greater than this. Add extra to deal with 
any tables you will create etc.  It costs 40 bytes per table, so be 
generous - if it is set too low, you will get bizarre space usage.

[Note: the FSM is so bad at reclaiming space when this value is too low 
that I believe it should be overridden at startup if it is not at least 
equal to the result of the above query. Similarly, I think a warning should 
be given at startup and/or runtime when it is exceeded, or work should be 
done to make it dynamic - and it should then not be a config item].


2. VACUUM Frequency
-------------------

Ideally VACUUM should run constantly; a future version will support 
something like it. But for now, vacuum should be run when a significant 
amount of data has been inserted, updated or deleted. The definition of 
'significant' is not immediately obvious.

Most tables will *not* be updated frequently in most databases; such tables 
can be vacuumed irregularly, or vacuumed when the more frequently updated 
tables are vacuumed.

In our specific case we have one table that has a few rows (< 1000), but it 
is updated as many as 3 times per second. In this case, we chose a 5 minute 
interval, which results in at worst 1000 'dead' rows in the table as a 
result of the updates. Since it was such a small table, we saw no reason to 
vacuum every minute, or even constantly.

For larger or more complex tables, the output of VACUUM ANALYZE must be used.

The following is an extract of the output from a VACUUM VERBOSE of a simple 
test database - the table is the TOAST table of a large text column, where 
the table has been constructed to be 75% empty. The output is after 
deleting some rows.

1 INFO:  --Relation pg_toast.pg_toast_16979--
2 INFO:  Index pg_toast_16979_index: Pages 575; Tuples 16384: Deleted 25984.
3         CPU 0.05s/0.16u sec elapsed 7.41 sec.
4 INFO:  Removed 25984 tuples in 6496 pages.
5         CPU 0.75s/0.79u sec elapsed 14.17 sec.
6 INFO:  Pages 22480: Changed 6496, Empty 0; Tup 16384: Vac 25984, Keep 0, 
UnUsed 47552.
7         Total CPU 1.98s/1.05u sec elapsed 23.30 sec.

Line 6 shows that there are 22480 pages, and 6496 (roughly 25%) were 
changed since the last vacuum. Line 4 indicates that these were all 
removed. Note that when tuples are updated, a new copy of the record is 
written and the old one deleted, so updates will also result in tuples 
being 'removed'.

A more complex example follows; this was after deleting 512 rows and adding 
256:

1 INFO:  --Relation pg_toast.pg_toast_16979--
2 INFO:  Index pg_toast_16979_index: Pages 667; Tuples 24576: Deleted 16384.
3         CPU 0.02s/0.10u sec elapsed 4.73 sec.
4 INFO:  Removed 16384 tuples in 4096 pages.
5         CPU 0.52s/0.48u sec elapsed 9.38 sec.
6 INFO:  Pages 20528: Changed 6144, Empty 0; Tup 24576: Vac 16384, Keep 0, 
UnUsed 41152.
7         Total CPU 1.81s/0.64u sec elapsed 22.51 sec.

note that line 6 has a 'changed' value, and line 4 has a 'removed' value. 
This gives some indication of the pages consumed and released in any period.

The final example is for 512 inserts, 512 updates (of different records) 
and 512 deletes.

1 INFO:  --Relation pg_toast.pg_toast_16979--
2 INFO:  Index pg_toast_16979_index: Pages 854; Tuples 32768: Deleted 32768.
3         CPU 0.05s/0.20u sec elapsed 8.41 sec.
4 INFO:  Removed 32768 tuples in 8192 pages.
5         CPU 1.01s/0.91u sec elapsed 13.52 sec.
6 INFO:  Pages 26672: Changed 12288, Empty 0; Tup 32768: Vac 32768, Keep 0, 
UnUsed 41152.
7         Total CPU 2.92s/1.25u sec elapsed 30.01 sec.

again it shows the effects of UPDATE/DELETE vs. INSERT.

In each case the 'Changed' value indicates the maximum number of pages 
required between vaccuums; and the 'removed' values indicates that some 
pages will be added to the FSM when vacuum is run. The high 'unused' value 
shows the results of an earleir bulk delete.

Note that 'Changed' seems to be 'updates+deletes+inserts' whereas 'removed' 
is 'deletes+updates', so it is not possible to determine 'updates+inserts', 
which would be the best indicator of the required number of new pages. If 
necessary, it *could* be derived by looking at tuple counts across vacuums, 
but using the 'changed' figure will give a good upper limit, since in most 
meaningful cases deletes will be lower than inserts -- hence it will be out 
by at worst a factor of two.

We have chosen (arbitrarily) to keep the number of pages 'changed' below 
25% of the total number of pages; in the above case that would involve 
running VACUUM twice as often.


3. max_fsm_pages
----------------

Contrary to other advice, FOR TABLES THAT ARE SUBJECT PERIODIC TO BULK 
DELETES, this figure should not be set based on how many pages you delete 
between vacuums, but how many pages you will consume between vacuums (the 
'changed' figure, above). This difference is important for sites that purge 
data periodically: it does not need to be set high enough to hold all 
deleted pages for one bulk delete -- it should be set high enough to have 
sufficient pages to manage the consumption between vacuums. If the pool is 
exhausted, then vacuum will find more 'unused' pages the next time it is 
run. UPDATE & INSERT will consume free pages, DELETE will create free pages.

For tables that are more typical (insert-intensive, or a general mix of 
updates, deletes and inserts), the 'removed' value should be used.

====
NOTE: max_fsm_pages is a cumulative value. It should be set to the sum of 
these values for all tables.
====

Based on the example above, VACUUM should probably be run more frequently. 
However, if the above was typical of the chosen VACUUM frequency, then 
max_fsm_pages should be set to at least 12288 for this table, despite the 
fact that only 8192 pages were released (since we assume there may be bulk 
deletes freeing up many pages).

If VACUUM were run twice as often, the max_fsm_pages should be set to at 
least 6144 for this table.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/