Обсуждение: server disk space
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/
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.
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
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/
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