Re: DB Tuning Notes for comment... - Mailing list pgsql-hackers

Robert Treat wrote:
> 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.
> 

Seconded!

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

In my experience I've seen tables with numerous indexes continue to 
benefit greatly from vacuum/vacuum full operations when large volumes of 
inserts are performed. This is true even when the update/delete activity 
on the base table itself is manageable. While dropping and recreating 
the index after loading is possible in some cases, my general comment is 
that index maintenance is an issue you should keep in mind when 
designing your vacuum strategy.

> 
>>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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


ss


Scott Shattuck
Technical Pursuit Inc.