Date: Fri, 1 Sep 2006 14:39:15 +0200
From: Matteo Sgalaberni <sgala@sgala.com>
To: pgsql-performance@postgresql.org
Subject: database bloat,non removovable rows, slow query etc...
Message-ID: <20060901123915.GT2266@sgala.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.11+cvs20060126

Hi, probably this is a very frequenfly question... I read archivies of
this list but I didn't found a finally solution for this aspect. I'll
explain my situation.

PSQL version 8.1.3
configuration of fsm,etcc default
autovacuum and statistics activated

22 daemons that have a persistent connection to this database(all
connection are in "idle"(no transaction opened).

this is the vacuum output of a table that it's updated frequently:
database=# VACUUM ANALYZE verbose cliente;
INFO:  vacuuming "public.cliente"
INFO:  index "cliente_pkey" now contains 29931 row versions in 88 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "cliente_login_key" now contains 29931 row versions in 165 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO:  "cliente": found 0 removable, 29931 nonremovable row versions in 559 pages
DETAIL:  29398 dead row versions cannot be removed yet.
There were 9 unused item pointers.
0 pages are entirely empty.
CPU 0.01s/0.01u sec elapsed 0.01 sec.
INFO:  vacuuming "pg_toast.pg_toast_370357"
INFO:  index "pg_toast_370357_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_370357": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.cliente"
INFO:  "cliente": scanned 559 of 559 pages, containing 533 live rows and 29398 dead rows; 533 rows in sample, 533 estimated total rows
VACUUM

database=# SELECT * from  pgstattuple('cliente');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
   4579328 |         533 |     84522 |          1.85 |            29398 |        4279592 |              93.45 |      41852 |         0.91
(1 row)

The performance of this table it's degraded now and autovacuum/vacuum full
don't remove these dead tuples. Only if I do a CLUSTER of the table the tuples
are removed.

The same problem is on other very trafficated tables.

I think that the problems probably are:
- tune the value of my fsm/etc settings in postgresql.conf but i don't
understdand how to tune it correctly.
- the persistent connections to this db conflict with the
autovacuum but i don't understand why. there are no transaction opened,
only connections in "idle" state.

Tell me what do you think...

Regards,

Matteo