Re: DB Tuning Notes for comment...

On Sun, 2002-12-08 at 09:41, Philip Warner wrote:
> Any comments or suggestions would be welcome.
>

first and foremost, this is really excellent work! We need to look into
getting this info into the standard documentation and/or Bruce's tuning
guide.

>
> 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');
>

It should be noted that if you have multiple databases, you'll need to
get the number of relations minus the system tables for each database,
then add in the number of system tables.

> 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.
>
>
> 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.
>

I don't think this is entirely true. On tables that have large numbers
of inserts, but no updates or deletes, you do not need to run vacuum. It
might be helpful to run analyze on these tables if your inserting enough
data to change the statistical relationships, but vacuum itself is not
needed.

> 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.

I have some similar tables in my system, with between 250 and 3500 rows.
These tables turn over at least every 15 minutes, so I have decided on a
10 minute vacuum interval. As with Phillip's, since they are small
tables, more frequent vacuuming seemed excessive.

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

again, great work Philip.

Robert Treat