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.

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
[mailtogsql-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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

On Tue, 2006-12-05 at 13:26, John Sidney-Woollett wrote:
> 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:
> > 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.
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/