Обсуждение: server disk space

Поиск
Список
Период
Сортировка

server disk space

От
Brian Modra
Дата:
Hi,
I'm maintaining a fairly large online database, and am trying to free
up disk space. Its got to 98% full.
I am certain that the postgresql data files are responsible for more
than 97% of this partition's usage.
The WAL logs for example are stored elsewhere.

The largest tables in this database are only inserted, not updated.
There are about 6 inserts per second. Its all time-stamped, and I am
deleting old rows.
There are 5 such tables, each 3 times as large as the previous.

On the 2 smallest tables, I have already done a create table ... (like
...), a re-insert of everything after a certain date, a vaccuum
analyse, and recreated the indexes. But they are relatively small, so
no real gains.

On the larger tables though, I have deleted old rows, and am now
running a (plain) vacuum.
The 3rd largest table's vacuum has completed. No space gain at all.

The other two (largest) table's vacuums are still in progress (still
running since last evening). I have shut down part of the service so
that its no longer inserting data to the tables, but rather caching it
for later insertion.

I suspect I need to run vacuum full, and drop indexes. Then re-create
the indexes...

But is there something I'm missing, e.g. that although the database
disk is 98% full, postgresql sees the database as having large blocks
of free space that it can write into? A vacuum full is going to take
an age, and I'm not sure if I can afford to have the database offline
for that period...

I will appreciate your help.
Thanks
Brian

--
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

Re: server disk space

От
Kevin Kempter
Дата:
On Sunday 06 September 2009 00:05:04 Brian Modra wrote:
> Hi,
> I'm maintaining a fairly large online database, and am trying to free
> up disk space. Its got to 98% full.
> I am certain that the postgresql data files are responsible for more
> than 97% of this partition's usage.
> The WAL logs for example are stored elsewhere.
>
> The largest tables in this database are only inserted, not updated.
> There are about 6 inserts per second. Its all time-stamped, and I am
> deleting old rows.
> There are 5 such tables, each 3 times as large as the previous.
>
> On the 2 smallest tables, I have already done a create table ... (like
> ...), a re-insert of everything after a certain date, a vaccuum
> analyse, and recreated the indexes. But they are relatively small, so
> no real gains.
>
> On the larger tables though, I have deleted old rows, and am now
> running a (plain) vacuum.
> The 3rd largest table's vacuum has completed. No space gain at all.
>
> The other two (largest) table's vacuums are still in progress (still
> running since last evening). I have shut down part of the service so
> that its no longer inserting data to the tables, but rather caching it
> for later insertion.
>
> I suspect I need to run vacuum full, and drop indexes. Then re-create
> the indexes...
>
> But is there something I'm missing, e.g. that although the database
> disk is 98% full, postgresql sees the database as having large blocks
> of free space that it can write into? A vacuum full is going to take
> an age, and I'm not sure if I can afford to have the database offline
> for that period...
>
> I will appreciate your help.
> Thanks
> Brian

Brian;

you may simply have too much data, try the check-postgres script(s) you can
get it here (http://bucardo.org/check_postgres/) , specifically look at the
bloat or dead space in your biggest tables.  You may need to run a 'VACUUM
FULL' on those tables to reclaim disk space, a normal vacuum will not reclaim
any disk space, just make the space in the table available for re-use by the
database.


Re: server disk space

От
Kevin Kempter
Дата:
On Sunday 06 September 2009 10:28:30 you wrote:
> Thanks, I suspected that was the case. However, the plain vacuum on
> the largest table has been running for almost 24 hours now, despite
> the postgresql being idle (no rows being inserted or updated for 24
> hours). The vacuum full will probably take days... which is a problem.
> I can't really take the database offline over the whole weekend.... Do
> you have any suggestions?
> I was just looking at pg_class to see how big the tables were, so I
> know which are the largest tables. This largest one has a really large
> pg_toast_4643492 index...
>
> (I'm using this to find out which are the largest relations:
> SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC;)
>
> How do I force a reindex hitting just that pg_toast_<oid> relation?
>
> 2009/9/6 Kevin Kempter <kevink@consistentstate.com>:
> > On Sunday 06 September 2009 00:05:04 Brian Modra wrote:
> >> Hi,
> >> I'm maintaining a fairly large online database, and am trying to free
> >> up disk space. Its got to 98% full.
> >> I am certain that the postgresql data files are responsible for more
> >> than 97% of this partition's usage.
> >> The WAL logs for example are stored elsewhere.
> >>
> >> The largest tables in this database are only inserted, not updated.
> >> There are about 6 inserts per second. Its all time-stamped, and I am
> >> deleting old rows.
> >> There are 5 such tables, each 3 times as large as the previous.
> >>
> >> On the 2 smallest tables, I have already done a create table ... (like
> >> ...), a re-insert of everything after a certain date, a vaccuum
> >> analyse, and recreated the indexes. But they are relatively small, so
> >> no real gains.
> >>
> >> On the larger tables though, I have deleted old rows, and am now
> >> running a (plain) vacuum.
> >> The 3rd largest table's vacuum has completed. No space gain at all.
> >>
> >> The other two (largest) table's vacuums are still in progress (still
> >> running since last evening). I have shut down part of the service so
> >> that its no longer inserting data to the tables, but rather caching it
> >> for later insertion.
> >>
> >> I suspect I need to run vacuum full, and drop indexes. Then re-create
> >> the indexes...
> >>
> >> But is there something I'm missing, e.g. that although the database
> >> disk is 98% full, postgresql sees the database as having large blocks
> >> of free space that it can write into? A vacuum full is going to take
> >> an age, and I'm not sure if I can afford to have the database offline
> >> for that period...
> >>
> >> I will appreciate your help.
> >> Thanks
> >> Brian
> >
> > Brian;
> >
> > you may simply have too much data, try the check-postgres script(s) you
> > can get it here (http://bucardo.org/check_postgres/) , specifically look
> > at the bloat or dead space in your biggest tables.  You may need to run a
> > 'VACUUM FULL' on those tables to reclaim disk space, a normal vacuum will
> > not reclaim any disk space, just make the space in the table available
> > for re-use by the database.


You could try a dump/restore of this table However Im not sure this would
actually be faster. I'd suggest you let it run as long as you can.

Likewise if the table is that big you should probably look at partitioning the
table. This will help you not only per vacuum/space management but performance
as well


Re: server disk space

От
Brian Modra
Дата:
2009/9/6 Kevin Kempter <kevink@consistentstate.com>:
> On Sunday 06 September 2009 10:28:30 you wrote:
>> Thanks, I suspected that was the case. However, the plain vacuum on
>> the largest table has been running for almost 24 hours now, despite
>> the postgresql being idle (no rows being inserted or updated for 24
>> hours). The vacuum full will probably take days... which is a problem.
>> I can't really take the database offline over the whole weekend.... Do
>> you have any suggestions?
>> I was just looking at pg_class to see how big the tables were, so I
>> know which are the largest tables. This largest one has a really large
>> pg_toast_4643492 index...
>>
>> (I'm using this to find out which are the largest relations:
>> SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC;)
>>
>> How do I force a reindex hitting just that pg_toast_<oid> relation?
>>
>> 2009/9/6 Kevin Kempter <kevink@consistentstate.com>:
>> > On Sunday 06 September 2009 00:05:04 Brian Modra wrote:
>> >> Hi,
>> >> I'm maintaining a fairly large online database, and am trying to free
>> >> up disk space. Its got to 98% full.
>> >> I am certain that the postgresql data files are responsible for more
>> >> than 97% of this partition's usage.
>> >> The WAL logs for example are stored elsewhere.
>> >>
>> >> The largest tables in this database are only inserted, not updated.
>> >> There are about 6 inserts per second. Its all time-stamped, and I am
>> >> deleting old rows.
>> >> There are 5 such tables, each 3 times as large as the previous.
>> >>
>> >> On the 2 smallest tables, I have already done a create table ... (like
>> >> ...), a re-insert of everything after a certain date, a vaccuum
>> >> analyse, and recreated the indexes. But they are relatively small, so
>> >> no real gains.
>> >>
>> >> On the larger tables though, I have deleted old rows, and am now
>> >> running a (plain) vacuum.
>> >> The 3rd largest table's vacuum has completed. No space gain at all.
>> >>
>> >> The other two (largest) table's vacuums are still in progress (still
>> >> running since last evening). I have shut down part of the service so
>> >> that its no longer inserting data to the tables, but rather caching it
>> >> for later insertion.
>> >>
>> >> I suspect I need to run vacuum full, and drop indexes. Then re-create
>> >> the indexes...
>> >>
>> >> But is there something I'm missing, e.g. that although the database
>> >> disk is 98% full, postgresql sees the database as having large blocks
>> >> of free space that it can write into? A vacuum full is going to take
>> >> an age, and I'm not sure if I can afford to have the database offline
>> >> for that period...
>> >>
>> >> I will appreciate your help.
>> >> Thanks
>> >> Brian
>> >
>> > Brian;
>> >
>> > you may simply have too much data, try the check-postgres script(s) you
>> > can get it here (http://bucardo.org/check_postgres/) , specifically look
>> > at the bloat or dead space in your biggest tables.  You may need to run a
>> > 'VACUUM FULL' on those tables to reclaim disk space, a normal vacuum will
>> > not reclaim any disk space, just make the space in the table available
>> > for re-use by the database.
>
>
> You could try a dump/restore of this table However Im not sure this would
> actually be faster. I'd suggest you let it run as long as you can.
>
> Likewise if the table is that big you should probably look at partitioning the
> table. This will help you not only per vacuum/space management but performance
> as well
>
>

Great Idea, thanks, However, I notice that the postmaster seems idle,
according to top, only 1% of the CPU at most... is it busy with IO
maybe? Still seems strange that it sits at 0, or 1% ...

Nothing else is running on the system...

But when I use ps:

postgres  8563 15633  0 Sep05 ?        00:05:01 postgres: tracker
trackerData [local] VACUUM


Should I give up on teh Vacuum?


--
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

Re: server disk space

От
Alvaro Herrera
Дата:
Brian Modra wrote:

> Great Idea, thanks, However, I notice that the postmaster seems idle,
> according to top, only 1% of the CPU at most... is it busy with IO
> maybe? Still seems strange that it sits at 0, or 1% ...
>
> Nothing else is running on the system...
>
> But when I use ps:
>
> postgres  8563 15633  0 Sep05 ?        00:05:01 postgres: tracker
> trackerData [local] VACUUM

Maybe your vacuum_cost_delay settings are too high?  What do you have
them set to?

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