Re: DB Tuning Notes for comment...
От | Philip Warner |
---|---|
Тема | Re: DB Tuning Notes for comment... |
Дата | |
Msg-id | 5.1.0.14.0.20021210111409.04d11c20@mail.rhyme.com.au обсуждение исходный текст |
Ответ на | Re: DB Tuning Notes for comment... (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: DB Tuning Notes for comment...
Re: DB Tuning Notes for comment... |
Список | pgsql-hackers |
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 |/
В списке pgsql-hackers по дате отправления: