HELP: Urgent, Vacuum problem

We had the same problem recently on our data warehouse.

Check out the reindex and cluster commands.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Schwenker,
Stephen
Sent: Monday, December 04, 2006 9:56 AM
To: pgsql-general@PostgreSQL.org
Subject: [GENERAL] HELP: Urgent, Vacuum problem

Hello,

I'm having a major Vacuuming problem. I used to do a full vacuum every
morning on my postgres database to clean up empty space on a table but
because of it's size, the locking of the database causes my application
server to max out the database connections and causes database errors.
To fix that problem, I have turned off the full vacuum and are just
doing a standard analyze vacuum. No I'm getting very close to running
out of space on my disks because the table keeps on growing and the
database is not re-using deleted record space. I know this because I
delete 99% of the records from the table after I have exported them but
the size of the database tables are not decreasing. Now I can't shrink
the size of the tables because the full vacuum takes too long to run
Over 2 hours and locks the table for too long.

Can anyone help me with fixing my problem with vacuuming and disk space?

I'm using version 7.4.2 on solaris.

Thank you,

Steve.

Schwenker, Stephen wrote:

I'm having a major Vacuuming problem. I used to do a full vacuum every
morning on my postgres database to clean up empty space on a table but
because of it's size, the locking of the database causes my application
server to max out the database connections and causes database errors.
To fix that problem, I have turned off the full vacuum and are just
doing a standard analyze vacuum. No I'm getting very close to running
out of space on my disks because the table keeps on growing and the
database is not re-using deleted record space. I know this because I
delete 99% of the records from the table after I have exported them but
the size of the database tables are not decreasing. Now I can't shrink
the size of the tables because the full vacuum takes too long to run
Over 2 hours and locks the table for too long.

You probably need to vacuum some tables more often than once a day;
and/or increase the FSM settings.

After a deletion of 99% of records, you probably should do a VACUUM FULL
anyway (or maybe CLUSTER); plain VACUUM won't be able to recover from
such a shock.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, 2006-12-04 at 09:56, Schwenker, Stephen wrote:

Hello,

I'm having a major Vacuuming problem. I used to do a full vacuum
every morning on my postgres database to clean up empty space on a
table but because of it's size, the locking of the database causes my
application server to max out the database connections and causes
database errors. To fix that problem, I have turned off the full
vacuum and are just doing a standard analyze vacuum. No I'm getting
very close to running out of space on my disks because the table keeps
on growing and the database is not re-using deleted record space. I
know this because I delete 99% of the records from the table after I
have exported them but the size of the database tables are not
decreasing. Now I can't shrink the size of the tables because the
full vacuum takes too long to run Over 2 hours and locks the table
for too long.

Can anyone help me with fixing my problem with vacuuming and disk
space?

I'm using version 7.4.2 on solaris.

A few points:

1: UPGRADE YOUR DATABASE to the latest 7.4 version. There were, if I
remember correctly, data eating bugs in 7.4.2 that were fixed later. Of
all the pieces of software I've ever used, none has ever been more
reliable to upgrade than postgresql. Whatever conservative philosophy
might be keeping you from updating is playing against you here. You're
far more likely to suffer catastrophic failure from running a buggy
version than from upgrading.

2: STOP THE FULL VACUUMS! Full vacuums should not be necessary. If
they are, something else is wrong. You're using a sledge hammer to swat
a fly. Plus in 7.4.xx series, vacuum fulls can cause problems with
index bloat, iffin I remember correctly.

3: Use vacuum verbose to see how many pages / entries you need in your
fsm, and adjust accordingly.

4: Look at migrating to 8.1 or even 8.2 (due out real soon now). There
have been a lot of advances in pg since 7.4, and the upgrade is pretty
painless as long as the dump / restore isn't too much of a burden.

"Schwenker, Stephen" <SSchwenker@thestar.ca> writes:

To fix that problem, I have turned off the full vacuum and are just
doing a standard analyze vacuum.

Good.

No I'm getting very close to running
out of space on my disks because the table keeps on growing and the
database is not re-using deleted record space. I know this because I
delete 99% of the records from the table after I have exported them but
the size of the database tables are not decreasing.

Standard vacuum isn't really intended to decrease table size, but to
help you maintain it at a steady state. If you're re-using the same
tables this should be OK though. It sounds to me like you need to raise
your FSM size to let the database track all the free space. You should
also consider vacuuming more often than once a day.

I'm using version 7.4.2 on solaris.

You *really* need to update.

regards, tom lane

To recover disk space, reindex the heavily updated tables. You can do
this while the database is in production.

Check the REINDEX command.

John

Schwenker, Stephen wrote:

I'm pretty sure reindexing a table takes out an exclusive lock, which
means you might wanna wait til off hours to do one.

Scott Marlowe wrote:

On Mon, 2006-12-04 at 09:56, Schwenker, Stephen wrote:

I'm using version 7.4.2 on solaris.

A few points:

4: Look at migrating to 8.1 or even 8.2 (due out real soon now). There
have been a lot of advances in pg since 7.4, and the upgrade is pretty
painless as long as the dump / restore isn't too much of a burden.

I couldn't agree more. 8.* is light years from where 7.4 was.
Just do it, you'll be very happy you did.

-Glen

On Tue, 2006-12-05 at 14:56, Glen Parker wrote:

Scott Marlowe wrote:

On Mon, 2006-12-04 at 09:56, Schwenker, Stephen wrote:

I'm using version 7.4.2 on solaris.

A few points:

4: Look at migrating to 8.1 or even 8.2 (due out real soon now). There
have been a lot of advances in pg since 7.4, and the upgrade is pretty
painless as long as the dump / restore isn't too much of a burden.

I couldn't agree more. 8.* is light years from where 7.4 was.
Just do it, you'll be very happy you did.

I recently tossed 8.1 on my workstation which runs a little reporting
application here. I pointed the app from 7.4 to 8.1 and got a visit
within about an hour from a user, asking if I'd done anything to my
database. Worrying that I'd made some mistake and he was getting an
error or something, I asked why he'd asked, and his comment was that it
now seemed to be much faster. I then commenced to breath again.

8.1 is awesome. I'll be putting 8.2 on my workstation this week. yee
ha!

Thank you everybody,

I decided to modify the export process to vacuum right after the export
and that has done the trick to clean up the filesystem. I know it's not
the best solution but I couldn't get an answer fast enough because every
time I send an email to the list, I get a message saying it was stalled
and I have to wait for it to be approved by the moderator. I don't know
why. :|

Anyway, I'm going to take what you guys suggested and schedule the
server to be upgraded in the coming months.

Thanks again for all your imput,

Steve.

-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Tuesday, December 05, 2006 4:09 PM
To: Glen Parker
Cc: Schwenker, Stephen; pgsql general
Subject: Re: [GENERAL] HELP: Urgent, Vacuum problem

On Tue, 2006-12-05 at 14:56, Glen Parker wrote:

Scott Marlowe wrote:

On Mon, 2006-12-04 at 09:56, Schwenker, Stephen wrote:

I'm using version 7.4.2 on solaris.

A few points:

4: Look at migrating to 8.1 or even 8.2 (due out real soon now).
There have been a lot of advances in pg since 7.4, and the upgrade
is pretty painless as long as the dump / restore isn't too much of a

burden.

I couldn't agree more. 8.* is light years from where 7.4 was.
Just do it, you'll be very happy you did.

I recently tossed 8.1 on my workstation which runs a little reporting
application here. I pointed the app from 7.4 to 8.1 and got a visit
within about an hour from a user, asking if I'd done anything to my
database. Worrying that I'd made some mistake and he was getting an
error or something, I asked why he'd asked, and his comment was that it
now seemed to be much faster. I then commenced to breath again.

8.1 is awesome. I'll be putting 8.2 on my workstation this week. yee
ha!

On Dec 5, 2006, at 4:09 PM, Scott Marlowe wrote:

I recently tossed 8.1 on my workstation which runs a little reporting
application here. I pointed the app from 7.4 to 8.1 and got a visit
within about an hour from a user, asking if I'd done anything to my
database. Worrying that I'd made some mistake and he was getting an

I think you got lucky. We had some issues with the transition from
7.4 to 8.0 due to more strictness of some queries, and some changes
in how strings were interpreted as numbers. They were minor issues,
but you really need to regression test your app against new major
releases of Pg.

On Wed, 2006-12-06 at 10:15, Vivek Khera wrote:

On Dec 5, 2006, at 4:09 PM, Scott Marlowe wrote:

I recently tossed 8.1 on my workstation which runs a little reporting
application here. I pointed the app from 7.4 to 8.1 and got a visit
within about an hour from a user, asking if I'd done anything to my
database. Worrying that I'd made some mistake and he was getting an

I think you got lucky. We had some issues with the transition from
7.4 to 8.0 due to more strictness of some queries, and some changes
in how strings were interpreted as numbers. They were minor issues,
but you really need to regression test your app against new major
releases of Pg.

Well, actually I knew about those kinds of things and had already made
changes in any queries that would need it. Basically, I wrote the
reporting app, and I wrote the queries, and after the change, there were
only like 2 queries that needed any changing that I hadn't caught.

Just because I didn't mention any testing doesn't mean none got done...
:)

On Wed, Dec 06, 2006 at 10:45:08AM -0500, Schwenker, Stephen wrote:

... I know it's not
the best solution but I couldn't get an answer fast enough because every
time I send an email to the list, I get a message saying it was stalled
and I have to wait for it to be approved by the moderator. I don't know
why. :|

You need to subscribe. If you don't actually want to receive list mail,
you configure your email address "nomail". Then you can send messages
and they'll get through straight away.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/