Re: VACUUM FULL not working with persistent connections - Mailing list pgsql-general

The connections are staying idle in a transaction (both in v7.1.3 and v7.2),
but I'm not sure why. We've developed a Java application that uses a database
connection pool (developed by us) that runs a rollback transaction whenever a
connection is returned to the pool. We do this to ensure that each connection
is in a clean state when inside the pool. Somehow the JDBC drivers interpret
this in a strange way and leave a transaction open. Other than that,
everything seems to be working perfectly.

It is very strange, because under v7.2 Postgres seems to stop vacuuming before
deleting all the useless tuples (see example below). If I run a verbose
VACUUM you will notice that it moves some tuples instead of removing them. Is
this the expected behavior? We are seeing a large performance drop with
PostgreSQL v7.2 when having a lot of updates on one table.

Thanks.

--- PostgreSQL v7.2
--- Approx. 4 updates/sec to the "merchant_client" table and only two distinct
--- rows.

<database_name>=# explain select * from merchant_client;
NOTICE:  QUERY PLAN:

Seq Scan on merchant_client  (cost=0.00..11.02 rows=2 width=59)

EXPLAIN
<database_name>=# vacuum full verbose analyze merchant_client;
NOTICE:  --Relation merchant_client--
NOTICE:  Pages 45: Changed 3, reaped 43, Empty 0, New 0; Tup 363: Vac 3481,
Keep/VTL 360/360, UnUsed 0, MinLen 88, MaxLen 88; Re-using: Free/Avail. Space
320420/320344; EndEmpty/Avail. Pages 0/44.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Index merchant_client_pkey: Pages 61; Tuples 363: Deleted 3478.
        CPU 0.02s/0.03u sec elapsed 0.04 sec.
NOTICE:  Rel merchant_client: Pages: 45 --> 5; Tuple(s) moved: 363.
        CPU 0.02s/0.03u sec elapsed 0.10 sec.
NOTICE:  Index merchant_client_pkey: Pages 61; Tuples 363: Deleted 363.
        CPU 0.01s/0.00u sec elapsed 0.01 sec.
NOTICE:  Analyzing merchant_client
VACUUM
<database_name>=# vacuum full verbose analyze merchant_client;
NOTICE:  --Relation merchant_client--
NOTICE:  Pages 5: Changed 1, reaped 1, Empty 0, New 0; Tup 425: Vac 0,
Keep/VTL 422/422, UnUsed 15, MinLen 88, MaxLen 88; Re-using: Free/Avail.
Space 1700/1396; EndEmpty/Avail. Pages 0/1.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Index merchant_client_pkey: Pages 61; Tuples 425: Deleted 0.
        CPU 0.01s/0.00u sec elapsed 0.00 sec.
NOTICE:  Rel merchant_client: Pages: 5 --> 5; Tuple(s) moved: 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Analyzing merchant_client
VACUUM
<database_name>=# explain select * from merchant_client;
NOTICE:  QUERY PLAN:

Seq Scan on merchant_client  (cost=0.00..9.25 rows=425 width=59)

EXPLAIN
<database_name>=# vacuum full verbose analyze merchant_client;
NOTICE:  --Relation merchant_client--
NOTICE:  Pages 6: Changed 1, reaped 5, Empty 0, New 0; Tup 56: Vac 409,
Keep/VTL 53/53, UnUsed 0, MinLen 88, MaxLen 88; Re-using: Free/Avail. Space
42244/42244; EndEmpty/Avail. Pages 0/6.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Index merchant_client_pkey: Pages 61; Tuples 56: Deleted 409.
        CPU 0.01s/0.00u sec elapsed 0.00 sec.
NOTICE:  Rel merchant_client: Pages: 6 --> 1; Tuple(s) moved: 56.
        CPU 0.00s/0.01u sec elapsed 0.01 sec.
NOTICE:  Index merchant_client_pkey: Pages 61; Tuples 56: Deleted 56.
        CPU 0.01s/0.00u sec elapsed 0.00 sec.
NOTICE:  Analyzing merchant_client
VACUUM
<database_name>=# explain select * from merchant_client;
NOTICE:  QUERY PLAN:

Seq Scan on merchant_client  (cost=0.00..1.56 rows=56 width=59)

EXPLAIN




On Monday 28 October 2002 13:53, Stephan Szabo wrote:
> On Thu, 24 Oct 2002, Juan Jose Comellas wrote:
> > The problem is that Postgres seems to be making a logical delete for each
> > row that is updated (i.e. it seems to be doing an INSERT followed by a
> > DELETE), so a lot of cruft is created on the database. We need to avoid
> > this because there are other processes that are permanently querying this
> > table (normally needing a sequential scan over the table) and the
> > performance of the queries gradually decreases with each update. We run a
> > VACUUM ANALYZE every 30 minutes on this table and with v7.1.3 the system
> > performs acceptably. With v7.2 (we tried v7.2.1, v7.2.2 and v7.2.3) if we
> > run a VACUUM FULL ANALYZE on the table almost none of the deleted rows
> > are really vacuumed until the clients disconnect. When using the VERBOSE
> > modifier, I noticed that sometimes some of  the rows were effectively
> > removed, but these we normally very few (less than 10%). We even tried a
> > VACUUM FULL FREEZE ANALYZE without success.
>
> Are the persistent connections staying idle in a transaction or outside of
> one?  If it were in one, I could see this, although I don't wee why it'd
> be different from 7.1.x.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Juan Jose Comellas
(juanjo@comellas.com.ar)