Re: server disk space - Mailing list pgsql-admin

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/