Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3

Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3

From
Gaetano Mendola
Date:
R. Willmington wrote:


> 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.

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

ps -eafwww | grep post


Gaetano

Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3

From
Gaetano Mendola
Date:
R. Willmington wrote:> Gaetano Mendola <mendola@bigfoot.com> wrote in message
news:<41484EA5.9060904@bigfoot.com>...>>>>Areyou sure you dont have connection in status: "idle in transaction"?>>Check
with:>>>>ps-eafwww | grep post>>>>>>Gaetano>>> Hello Gaetano,>> Thank you for the reply.> I have checked the
connectionswith the command you suggested, they> are all in status "idle".>> Update on the problem described in my
initialposting:>> I have deleted some 4000 records from the table on the live system in> the meantime, it took about 36
minutesto 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

Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3

From
willmington@gmx.net (R. Willmington)
Date:
Gaetano Mendola <mendola@bigfoot.com> wrote in message news:<41484EA5.9060904@bigfoot.com>...

>
> Are you sure you dont have connection in status: "idle in transaction"?
> Check with:
>
> 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