From: Matteo Sgalaberni <sgala@sgala.com>
To: pgsql-performance@postgresql.org
Subject: database bloat,non removovable rows, slow query etc...
Date: Fri, 1 Sep 2006 14:39:15 +0200
Message-ID: <20060901123915.GT2266@sgala.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgala@sgala.com
Subject: Re: database bloat,non removovable rows, slow query etc...
In-Reply-To: <20060901123915.GT2266@sgala.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox