At 03:30 PM 2/12/2002 -0800, Joby Walker wrote:
What tuning have you done to Postgres and what is your regular maintenance
schedule?
Some of the basic factors from a PG point of view (these are my view on
things based on using PG and the general advice found on the lists and
documentation):
1. Any table that is updated, deleted or inserted frequently should be
vacuumed often. For the purpose of this, 'frequently' should be measured as:
total updates + inserts + deletes
---------------------------------
total active records
There are no hard and fast figures for what is 'high', but I'd say 10% was
at least note-worthy.
This means that the 'users' and 'pbsp' tables are a good candidates because
they are frequently updated. And the messages & messageblks tables are good
candidates because they are frequently inserted/deleted (in our case most
users delete their mail from the server not long after they are received,
resulting in an update, then a delete when the dbmail-maintenance task runs).
Because the users table is updated so frequently in our case (about 2-4/sec
in higher load, and 1/3sec in lower load we vacuum the users table every 5 min.
2. All tables that are subject to any activity should be analyzed
regularly. The regularity should depend on knowledge of the data and nature
of the updates and queries.
Because most larger tables are very active, we analyze hourly. Running the
analyze command is cheap.
3. PG default settings are not good at handling very large transient data
volumes (ie. email). It tries to maintain a list of free space internally,
but when the dbmail-maintenance process is run, it actually deletes large
quantities of mail, resulting in the default free space tables overflowing
- so pages are lost until a *full* vacuum. The default setting is 10000
pages, which is plenty for most situations. However, we found that between
25000 and 35000 pages were being cleared each night. As a result we 'lost'
about 15000 to 25000 pages each night. When you run a 'vacuum verbose' you
get output like:
NOTICE: --Relation pg_toast_82493--
NOTICE: Index pg_toast_82493_idx: Pages 3134; Tuples 226090: Deleted 98728.
CPU 0.15s/0.45u sec elapsed 7.01 sec.
NOTICE: Removed 98728 tuples in 25047 pages.
CPU 2.13s/1.55u sec elapsed 57.10 sec.
The relevant part here is:
NOTICE: Removed 98728 tuples in 25047 pages.
You need to either set max_fsm_pages in postgresql.conf to a number greater
than this, or vacuum more frequently so that the number is smaller. Out of
paranoia, we have done both until the database settles down and we have
more accurate tuning figures to use. Since the records are only deleted
once per day (when we run dbmail-maintenance), we don't expect to get much
less that half this value.
As a result of the above, we currently:
set max_fsm_pages to 40000
vacuum every two hours
vacuum 'users' every 5 min
analyze every hour
dbmail-maintenance -p -d -f daily.
since vacuuming/analyzing small tables is cheap, and the ones that really
need it in this case are the large ones, we just vacuum/analyze the entire DB.
At some point in the future I expect we will decrease the FSM size and/or
reduce the vacuum frequency.
I hope this helps.
----------------------------------------------------------------
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 |/
