pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3
5+ messages / 3 participants
[nested] [flat]

* VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3
@ 2004-09-15 08:33 R. Willmington <willmington@gmx.net>
  2004-09-15 14:16 ` Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 Gaetano Mendola <mendola@bigfoot.com>
  2004-09-16 15:32 ` Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat Chester Kustarz <chester@arbor.net>
  0 siblings, 2 replies; 5+ messages in thread

From: willmington@gmx.net @ 2004-09-15 08:33 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3
  2004-09-15 08:33 VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 R. Willmington <willmington@gmx.net>
@ 2004-09-15 14:16 ` Gaetano Mendola <mendola@bigfoot.com>
  2004-09-16 08:42   ` Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 R. Willmington <willmington@gmx.net>
  1 sibling, 1 reply; 5+ messages in thread

From: Gaetano Mendola @ 2004-09-15 14:16 UTC (permalink / raw)
  To: R. Willmington <willmington@gmx.net>

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






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3
  2004-09-15 08:33 VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 R. Willmington <willmington@gmx.net>
  2004-09-15 14:16 ` Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 Gaetano Mendola <mendola@bigfoot.com>
@ 2004-09-16 08:42   ` R. Willmington <willmington@gmx.net>
  2004-09-16 22:24     ` Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 1 reply; 5+ messages in thread

From: willmington@gmx.net @ 2004-09-16 08:42 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3
  2004-09-15 08:33 VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 R. Willmington <willmington@gmx.net>
  2004-09-15 14:16 ` Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 Gaetano Mendola <mendola@bigfoot.com>
  2004-09-16 08:42   ` Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 R. Willmington <willmington@gmx.net>
@ 2004-09-16 22:24     ` Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Gaetano Mendola @ 2004-09-16 22:24 UTC (permalink / raw)
  To: R. Willmington <willmington@gmx.net>

R. Willmington wrote:
 > 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.

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






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat
  2004-09-15 08:33 VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 R. Willmington <willmington@gmx.net>
@ 2004-09-16 15:32 ` Chester Kustarz <chester@arbor.net>
  1 sibling, 0 replies; 5+ messages in thread

From: Chester Kustarz @ 2004-09-16 15:32 UTC (permalink / raw)
  To: R. Willmington <willmington@gmx.net>; +Cc: pgsql-admin

On 15 Sep 2004, R. Willmington wrote:
> 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.

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

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

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

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.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-09-16 22:24 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-15 08:33 VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3 R. Willmington <willmington@gmx.net>
2004-09-15 14:16 ` Gaetano Mendola <mendola@bigfoot.com>
2004-09-16 08:42   ` R. Willmington <willmington@gmx.net>
2004-09-16 22:24     ` Gaetano Mendola <mendola@bigfoot.com>
2004-09-16 15:32 ` Re: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat Chester Kustarz <chester@arbor.net>

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