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 | 23424808pg_toast_23424804     | 23424806grow_pk
|23424809grow                  | 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