Re: Unbounded (Possibly) Database Size Increase - Test

On Wed, 2002-05-08 at 01:45, Tom Lane wrote:

> Which files grew exactly? (Main table, indexes, toast table, toast index?)

Here a listing (from another run - I dumped and reloaded before getting
any of that info last time...)

[:/data1/pgdata/7.2/base/23424803]$ du -sk .
4900806 .

-rw------- 1 postgres dba 1073741824 May 9 21:20 23424806.3
-rw------- 1 postgres dba 1073741824 May 9 21:19 23424806.2
-rw------- 1 postgres dba 1073741824 May 9 21:18 23424806.1
-rw------- 1 postgres dba 1073741824 May 9 21:16 23424806
-rw------- 1 postgres dba 124444672 May 9 21:16 23424808
-rw------- 1 postgres dba 587505664 May 9 21:14 23424806.4
-rw------- 1 postgres dba 5914624 May 9 21:05 23424804
-rw------- 1 postgres dba 2441216 May 9 21:05 23424809

These files are for :

grow=# select relname,oid
grow-# from pg_class where oid in
('23424806','23424808','23424804','23424809'); relname |
oid
-----------------------+----------
pg_toast_23424804_idx | 23424808
pg_toast_23424804 | 23424806
grow_pk | 23424809
grow | 23424804
(4 rows)

so the big guy is the toast table and index
- BTW the table design is
CREATE TABLE grow (id integer,body text,CONSTRAINT grow_pk PRIMARY KEY
(id))

The row length is big ~ 14K. I am wondering if this behaviour will "go
away" if I use recompile with a 32K page size (also seem to recall I can
tell Pg not to toast certain column types)
>
> Was the FSM size parameter set large enough to cover the amount of space
> you need the system to be able to recycle --- viz, the amount used
> between vacuum runs? As with most everything else in PG, the default
> value is not real large: 10000 pages = 80MB.

I thought I was generous here ...~ 960M free space map

max_fsm_relations = 100 # min 10, fsm is free space map
max_fsm_pages = 120000 # min 1000, fsm is free space map

I think I need to count how many vacuums performed during the test, so I
can work out if this amount should have been enough. I timed a vacuum
now at 12 minutes. (So with 10 concurrent threads it could take a lot
longer during the run )

regards

Mark