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

Are there open transactions on the table in question? We had the same
issue. A 100K row table was so bloated that the system thought there was
1M rows. We had many <IDLE> transaction that we noticed in TOP, but since
we could not track down which process or user was holding the table we had
to restart Pg. Once restarted we were able to do a VACUUM FULL and this
took care of the issue.
hth
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com

Matteo Sgalaberni
<sgala(at)sgala(dot)com>
Sent by: To
pgsql-performance pgsql-performance(at)postgresql(dot)org
-owner(at)postgresql cc
.org
Subject
[PERFORM] database bloat,non
09/01/06 05:39 AM removovable rows, slow query etc...

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org