Re: DB Tuning Notes for comment...

At 07:01 PM 9/12/2002 -0500, Tom Lane wrote:
>We
>could make the constraint be on total space for relation entries + page
>entries rather than either individually, but I think that'd mostly make
>it harder to interpret the config setting rather than offer any real
>ease of administration.

Perhaps doing both? And issue a warning to the logs when max_fsm_relations 
is borrowing from max_fsm_pages.

It might be that the outstanding patches address the problem, but at the 
moment the choice of which relations to include is not well made when 
max_fsm_relations of much too small. We should at least issue a warning; 
but allowing max_fsm_relations to borrow from max_fsm_pages seems like a 
good idea, since having the number too low (with 161 relations setting it 
to the default of 100) is useless.

Secondly, an empty database contains 98 tables, so the default setting of 
max_fsm_pages to 100 is way too low.

The tradeoff of losing 7 pages from the map to include another relation is 
worth it, especially if the logs contain a warning.

But perhaps the test itself is flawed and there is another problem 
resulting in this behaviour (doing vacuums twice in a row seems to make it 
use the free space, but I'd guess this is just edge behaviour of the FSM 
heuristics):

Create Table t(i serial, t text);
insert into t(t) .... 47K of UUEncoded jpeg file -> ~47K of toast.
insert into t(t) select t from t;
...repeat 9 times...
create table t1 as select * from t limit 1;
...
create table t19 as select * from t limit 1;
create table t20(i serial, t text);
insert into t20(t) select t from t;

ie. build a lot of tables, with two big ones separated by OID (not sure if 
the last part is relevant).

select count(*) from pg_class where relkind in ('t','r');

in my case this resulted in 161, so I set max_fsm_relations to 100 (ie. not 
a borderline case, but the default setting).

I also left max_fsm_pages at 10000 so that we should have space for several 
thousand rows.

Stop & start postmaster, then vacuum full to be comfortable no other 
problems occur, an look at file sizes of relation file and toast file.

Now:

delete from t where i <= 128;
delete from t20 where i <= 128;

vacuum;

check file sizes - no surprises, they should be unchanged.

Tue Dec 10 12:03:53 EST 2002
-rw-------    1 pjw      users       65536 2002-12-10 12:03 16979
-rw-------    1 pjw      users       65536 2002-12-10 12:03 33432
-rw-------    1 pjw      users    67108864 2002-12-10 12:03 16982
-rw-------    1 pjw      users    67108864 2002-12-10 12:03 33435

then do:

insert into t(t) select t from t20 limit 10;
insert into t20(t) select t from t limit 10;

and both files have grown:

Tue Dec 10 12:08:20 EST 2002
-rw-------    1 pjw      users       65536 2002-12-10 12:08 33432
-rw-------    1 pjw      users    67764224 2002-12-10 12:08 33435
-rw-------    1 pjw      users    67764224 2002-12-10 12:08 16982
-rw-------    1 pjw      users       65536 2002-12-10 12:08 16979

oddly (bug? edge behaviour?) doing two vacuums in a row results in the free 
space being used.


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