VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3

[R. Willmington]

Ladies and gentlemen,

I am running a Postgres 7.3.2 on a Red Hat Linux release 7.3
(Valhalla)
in a multiprocessor (4) environment.

There is a db table in one of the databases containing some 20.000
records updated on a daily basis. When attempting to delete about 4000
deprecated records from it the query ran for almost half an hour and
postmaster occupied 100% CPU, so i decided to cancel it. (Note: select
querys run fine and finish within milliseconds).

In order to reproduce the problem, i copied the above database to my
staging system (same linux, same postgres), and ran a vacuum full
analyze. It finished after 33 minutes. After the vacuum full the
deletion from the 20.000 records table succeeded within 23 ms.

Now, here's the problem:
When running the VACUUM FULL on the live system, it finished after 20
Seconds (without error messages) - and did not change anything.
Performance is still bad, and the delete from xxx where ... still
takes forever.

Additional information:
The applications using the database are java - based and create
persistent connections (from a connection pool) to the postgres
database. These connections are on autocommit, thus, there should not
be any opened transactions preventing the vacuum full from write -
locking the tables.

Any idea what i am doing wrong? Any help appreciated.

Kind regards,

R. Willmington

[Gaetano Mendola]

Post by R. Willmington
The applications using the database are java - based and create
persistent connections (from a connection pool) to the postgres
database. These connections are on autocommit, thus, there should not
be any opened transactions preventing the vacuum full from write -
locking the tables.
Any idea what i am doing wrong? Any help appreciated.

Are you sure you dont have connection in status: "idle in transaction"?
Check with:

ps -eafwww | grep post


Gaetano

[R. Willmington]

Post by Gaetano Mendola
Are you sure you dont have connection in status: "idle in transaction"?
ps -eafwww | grep post
Gaetano

Hello Gaetano,

Thank you for the reply.
I have checked the connections with the command you suggested, they
are all in status "idle".

Update on the problem described in my initial posting:

I have deleted some 4000 records from the table on the live system in
the meantime, it took about 36 minutes to complete.
After that, i ran a Vacuum full verbose on the table. The vacuum full
removed only 25 deleted records within about 5 seconds.

Regards,

R. Willmington

[Gaetano Mendola]

Post by R. Willmington

Post by Gaetano Mendola
Are you sure you dont have connection in status: "idle in transaction"?
ps -eafwww | grep post
Gaetano

Hello Gaetano,
Thank you for the reply.
I have checked the connections with the command you suggested, they
are all in status "idle".
I have deleted some 4000 records from the table on the live system in
the meantime, it took about 36 minutes to complete.

Do you have cascade delete ? 4000 record in 36 minutes are too much, in my
system I delete milion records in a few seconds, are you sure you are presenting
the entire scenario ?



Regards
Gaetano Mendola

[Chester Kustarz]

Post by R. Willmington
In order to reproduce the problem, i copied the above database to my
staging system (same linux, same postgres), and ran a vacuum full
analyze. It finished after 33 minutes. After the vacuum full the
deletion from the 20.000 records table succeeded within 23 ms.

you should have given the verbose output from the vacuum full. you
could have seen how many dead row versions it had removed. perhaps
there were enough versions to justify 33 minutes of whatever it was
doing.

Post by R. Willmington
When running the VACUUM FULL on the live system, it finished after 20
Seconds (without error messages) - and did not change anything.

it is likely other connections are preventing the row versions from
being reclaimed. again, you should have included verbose output.

Post by R. Willmington
The applications using the database are java - based and create
persistent connections (from a connection pool) to the postgres
database. These connections are on autocommit, thus, there should not
be any opened transactions preventing the vacuum full from write -
locking the tables.

well, to prove it disconnect all the other connections and then try the
delete. you are not really eliminating the unknowns by assuming they
don't exist.



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)