2009/9/6 Kevin Kempter <kevink@xxxxxxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxxxxxxx>: >> > 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/ -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
