database bloat,non removovable rows, slow query etc...

From: Matteo Sgalaberni <sgala(at)sgala(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: database bloat,non removovable rows, slow query etc...
Date: 2006-09-01 12:39:15
Message-ID: 20060901123915.GT2266@sgala.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2006-09-01 14:43:30 Re: database bloat,non removovable rows, slow query etc...
Previous Message Francisco Reyes 2006-09-01 12:20:57 Re: how to partition disks