Why memory is not used ? Why vacuum so slow ? - Mailing list pgsql-performance

Hi,

I have tried to tune a database that I'm using only for statistical access ...
I mean that I'm importing a dump of my production database each night, but
preserving some aggregat tables, and statistics ones ... (that I'm
calculating after the importation of the dump). This database is only used by
few people but make some big requests, tables have mixed sizes between 200
000 rows up to 10 000 000 records.

This server's got 2Gb memory, and 100 Gb RAID 5 Hard disk, is a woody Debian,
and I'm using a self compiled version of PotsgreSQL v7.3.4.

My postgresql.conf file looks like this :

#
#   Shared Memory Size
#
shared_buffers = 31000      # min max_connections*2 or 16, 8KB each
max_fsm_relations = 1000    # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 10000       # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
wal_buffers = 32            # min 4, typically 8KB each

#
#   Non-shared Memory Sizes
#
sort_mem = 32768        # min 64, size in KB
vacuum_mem = 32768      # min 1024, size in KB

#checkpoint_segments = 3    # in logfile segments, min 1, 16MB each
checkpoint_timeout = 160    # range 30-3600, in seconds
effective_cache_size = 400000   # typically 8KB each
random_page_cost = 1.5          # units are one sequential page fetch cost

Before my effective_cache_size was 1000 ... and reading some tuning pages and
comments telling : "effective_cache_size: You should adjust this according to
the amount of free memory you have." ... I grow it to 400000 ...

Then ... first point I'm only using 5% of my memory (all linux system,and
software) ... and no swap (good point for this) ... Why I don't use more
memory ... ??

Second point ... after importing my dump ... I make a vacuum full analyze of
my base (in same time because of my caculation of the day before for my
aggregats and stats tables about 200 000 row deleted and/or inserted for more
than 20 tables (each)) ... but It takes about 5 hours ...

Example of a (for me) really slow vacuum ... more than 85 min for a table with
only 9105740 records ...

INFO:  --Relation public.hebcnt--
INFO:  Pages 175115: Changed 0, reaped 3309, Empty 0, New 0; Tup 9105740: Vac
175330, Keep/VTL 0/0, UnUsed 0, MinLen 148, MaxLen 148; Re-using: Free/Avail.
Space 46265980/26336600; EndEmpty/Avail. Pages 0/3310.
        CPU 6.75s/1.67u sec elapsed 91.41 sec.
INFO:  Index ix_hebcnt_idc: Pages 40446; Tuples 9105740: Deleted 175330.
        CPU 2.94s/6.17u sec elapsed 222.34 sec.
INFO:  Index ix_hebcnt_cweek: Pages 229977; Tuples 9105740: Deleted 175330.
        CPU 9.64s/3.14u sec elapsed 1136.01 sec.
INFO:  Index ix_hebcnt_cpte: Pages 72939; Tuples 9105740: Deleted 175330.
        CPU 4.86s/9.13u sec elapsed 398.73 sec.
INFO:  Index ix_hebcnt_idctweek: Pages 66014; Tuples 9105740: Deleted 175330.
        CPU 3.87s/8.61u sec elapsed 163.26 sec.
INFO:  Rel hebcnt: Pages: 175115 --> 171807; Tuple(s) moved: 175330.
        CPU 16.49s/52.04u sec elapsed 1406.34 sec.
INFO:  Index ix_hebcnt_idc: Pages 40446; Tuples 9105740: Deleted 175330.
        CPU 1.76s/5.65u sec elapsed 124.98 sec.
INFO:  Index ix_hebcnt_cweek: Pages 230690; Tuples 9105740: Deleted 175330.
        CPU 10.07s/2.60u sec elapsed 1095.17 sec.
INFO:  Index ix_hebcnt_cpte: Pages 72940; Tuples 9105740: Deleted 175330.
        CPU 4.51s/8.90u sec elapsed 353.45 sec.
INFO:  Index ix_hebcnt_idcweek: Pages 66015; Tuples 9105740: Deleted 175330.
        CPU 3.96s/8.58u sec elapsed 147.64 sec.
INFO:  --Relation pg_toast.pg_toast_76059978--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL
0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0; EndEmpty/
Avail. Pages 0/0.
        CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  Index pg_toast_76059978_index: Pages 1; Tuples 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing public.hebcnt

Structure of this table :
frstats=# \d hebcnt
                          Table "public.hebcnt"
      Column      |            Type             |       Modifiers
------------------+-----------------------------+------------------------
 id_c                | integer                     | not null
 contrat          | text                        | not null
 arrete_week      | text                        | not null
 cpte                | text                        | not null
 is_active        | boolean                     | not null
 year              | text                        | not null
 use               | integer                     | not null
 use_priv         | integer                     | not null
 use_ind         | integer                     | not null
 passback         | integer                     | not null
 resa               | integer                     | not null
 noshow           | integer                     | not null
 nbc                | integer                     | not null
 dureecnt         | integer                     | not null
 dureecpt         | integer                     | not null
 anciennete2      | integer                     | not null
 c_week           | text                        | not null
 blacklist        | integer                     | not null
 dcrea            | timestamp without time zone | not null default now()
 dmaj             | timestamp without time zone |
Indexes: ix_hebcnt_cweek btree (c_week),
         ix_hebcnt_cpte btree (cpte),
         ix_hebcnt_idc btree (id_c),
         ix_hebcnt_idcweek btree (id_c, c_week)

Any idea ?

Regards,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902