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