Thread: Disk is full, what's cool to get rid of?
Hello, quick question. I've run into an issue with the disk that my development box is on filling up and preventing pretty much any writing (inserts, updates, deletes, etc...) from happening. Other than some piddly text logs the db is pretty much the only thing on the box. So, my question is: what can I do to free space and run a full vacuum? (I was not the one who set up this box and there has been virtually no administration or maintenance on it that I know of...) How about the WAL files in pg_xlog? How critical are they when no data on the system is critical in and of itself? Any suggestions would be greatly appreciated... -- erik jones <erik@myemma.com> software development emma(r)
You can probably just "tune2fs -m 0 <device name>" to give yourself enough space to get out of the jam before you go deleting things. Then you might want to vacuum full afterwards. On Thu, 27 Jul 2006, Erik Jones wrote: > Hello, quick question. I've run into an issue with the disk that my > development box is on filling up and preventing pretty much any writing > (inserts, updates, deletes, etc...) from happening. Other than some piddly > text logs the db is pretty much the only thing on the box. So, my question > is: what can I do to free space and run a full vacuum? (I was not the one > who set up this box and there has been virtually no administration or > maintenance on it that I know of...) How about the WAL files in pg_xlog? > How critical are they when no data on the system is critical in and of > itself? Any suggestions would be greatly appreciated... > > -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
> administration or maintenance on it that I know of...) How about the > WAL files in pg_xlog? How critical are they when no data on the system > is critical in and of itself? Any suggestions would be greatly > appreciated... If the data isn't critical, you maybe could truncate a table to clear enough space. Deleting anything under pg_xlog is more or less guaranteed to mean your database is garbage. A -- Andrew Sullivan | ajs@crankycanuck.ca If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA
I can't tell you the number of times that little trick has saved my life. On Thu, 2006-07-27 at 11:32, Jeff Frost wrote: > You can probably just "tune2fs -m 0 <device name>" to give yourself enough > space to get out of the jam before you go deleting things. Then you might > want to vacuum full afterwards. > > On Thu, 27 Jul 2006, Erik Jones wrote: > > > Hello, quick question. I've run into an issue with the disk that my > > development box is on filling up and preventing pretty much any writing > > (inserts, updates, deletes, etc...) from happening. Other than some piddly > > text logs the db is pretty much the only thing on the box. So, my question > > is: what can I do to free space and run a full vacuum? (I was not the one > > who set up this box and there has been virtually no administration or > > maintenance on it that I know of...) How about the WAL files in pg_xlog? > > How critical are they when no data on the system is critical in and of > > itself? Any suggestions would be greatly appreciated... > > > >
Awesome. Do I need to reset that to any magic # after the vacuum? I'm not all that up on filesystem maintenance/tweaking... Scott Marlowe wrote: > I can't tell you the number of times that little trick has saved my > life. > > On Thu, 2006-07-27 at 11:32, Jeff Frost wrote: > >> You can probably just "tune2fs -m 0 <device name>" to give yourself enough >> space to get out of the jam before you go deleting things. Then you might >> want to vacuum full afterwards. >> >> On Thu, 27 Jul 2006, Erik Jones wrote: >> >> >>> Hello, quick question. I've run into an issue with the disk that my >>> development box is on filling up and preventing pretty much any writing >>> (inserts, updates, deletes, etc...) from happening. Other than some piddly >>> text logs the db is pretty much the only thing on the box. So, my question >>> is: what can I do to free space and run a full vacuum? (I was not the one >>> who set up this box and there has been virtually no administration or >>> maintenance on it that I know of...) How about the WAL files in pg_xlog? >>> How critical are they when no data on the system is critical in and of >>> itself? Any suggestions would be greatly appreciated... >>> >>> >>> > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- erik jones <erik@myemma.com> software development emma(r)
Depends what the default is on your system. The default is 5% with the version of mke2fs that I have here, so you would just: tune2fs -m 5 <devicename> to put it back. On Thu, 27 Jul 2006, Erik Jones wrote: > Awesome. Do I need to reset that to any magic # after the vacuum? I'm not > all that up on filesystem maintenance/tweaking... > > Scott Marlowe wrote: >> I can't tell you the number of times that little trick has saved my >> life. >> >> On Thu, 2006-07-27 at 11:32, Jeff Frost wrote: >> >>> You can probably just "tune2fs -m 0 <device name>" to give yourself enough >>> space to get out of the jam before you go deleting things. Then you might >>> want to vacuum full afterwards. >>> >>> On Thu, 27 Jul 2006, Erik Jones wrote: >>> >>> >>>> Hello, quick question. I've run into an issue with the disk that my >>>> development box is on filling up and preventing pretty much any writing >>>> (inserts, updates, deletes, etc...) from happening. Other than some >>>> piddly text logs the db is pretty much the only thing on the box. So, my >>>> question is: what can I do to free space and run a full vacuum? (I was >>>> not the one who set up this box and there has been virtually no >>>> administration or maintenance on it that I know of...) How about the WAL >>>> files in pg_xlog? How critical are they when no data on the system is >>>> critical in and of itself? Any suggestions would be greatly >>>> appreciated... >>>> >>>> >>>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> > > > -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Awesome. Makes sense as 5% is exactly the amount of space that appeared after running it. Thanks! Jeff Frost wrote: > Depends what the default is on your system. The default is 5% with > the version of mke2fs that I have here, so you would just: > > tune2fs -m 5 <devicename> > > to put it back. > > On Thu, 27 Jul 2006, Erik Jones wrote: > >> Awesome. Do I need to reset that to any magic # after the vacuum? >> I'm not all that up on filesystem maintenance/tweaking... >> >> Scott Marlowe wrote: >>> I can't tell you the number of times that little trick has saved my >>> life. >>> >>> On Thu, 2006-07-27 at 11:32, Jeff Frost wrote: >>> >>>> You can probably just "tune2fs -m 0 <device name>" to give yourself >>>> enough space to get out of the jam before you go deleting things. >>>> Then you might want to vacuum full afterwards. >>>> >>>> On Thu, 27 Jul 2006, Erik Jones wrote: >>>> >>>> >>>>> Hello, quick question. I've run into an issue with the disk that >>>>> my development box is on filling up and preventing pretty much any >>>>> writing (inserts, updates, deletes, etc...) from happening. Other >>>>> than some piddly text logs the db is pretty much the only thing on >>>>> the box. So, my question is: what can I do to free space and run >>>>> a full vacuum? (I was not the one who set up this box and there >>>>> has been virtually no administration or maintenance on it that I >>>>> know of...) How about the WAL files in pg_xlog? How critical are >>>>> they when no data on the system is critical in and of itself? Any >>>>> suggestions would be greatly appreciated... >>>>> >>>>> >>>>> >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 6: explain analyze is your friend >>> >> >> >> > -- erik jones <erik@myemma.com> software development emma(r)
Andrew Sullivan <ajs@crankycanuck.ca> writes:
> If the data isn't critical, you maybe could truncate a table to clear
> enough space. Deleting anything under pg_xlog is more or less
> guaranteed to mean your database is garbage.
If you're desperate you could shut down the postmaster, run
pg_resetxlog, restart the postmaster. This would cut xlog contents
to the minimum --- however, they'd probably soon grow back to whatever
they are now, so it's not much of a long-term solution. It might give
you some breathing room to look for other places to trim though.
If the database hasn't been maintained well then you likely are
suffering from table bloat and/or index bloat. A desperation measure
for fixing that is drop all indexes, vacuum full, recreate all indexes.
(There are other procedures you may find recommended, such as CLUSTER,
but they have transient disk-space requirements that you're not gonna
be able to afford when your back is to the wall.)
regards, tom lane
Won't help some of us, who set -m 0 on selected filesystems to begin with. But if we could get tune2fs -m -5 <devicename> to work, then we could unreserve space that didn't previously exist. Think of the possibilties! I'll look into that as soon as I'm done modding my C compiler to handle the --force option. -cw- -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Erik Jones Sent: Thursday, July 27, 2006 4:00 PM To: Jeff Frost Cc: Scott Marlowe; General Postgresql List Subject: Re: [SQL] Disk is full, what's cool to get rid of? Awesome. Makes sense as 5% is exactly the amount of space that appeared after running it. Thanks! Jeff Frost wrote: > Depends what the default is on your system. The default is 5% with > the version of mke2fs that I have here, so you would just: > > tune2fs -m 5 <devicename> > > to put it back. >
Tom Lane wrote: > Andrew Sullivan <ajs@crankycanuck.ca> writes: > >> If the data isn't critical, you maybe could truncate a table to clear >> enough space. Deleting anything under pg_xlog is more or less >> guaranteed to mean your database is garbage. >> > > If you're desperate you could shut down the postmaster, run > pg_resetxlog, restart the postmaster. This would cut xlog contents > to the minimum --- however, they'd probably soon grow back to whatever > they are now, so it's not much of a long-term solution. It might give > you some breathing room to look for other places to trim though. > > If the database hasn't been maintained well then you likely are > suffering from table bloat and/or index bloat. A desperation measure > for fixing that is drop all indexes, vacuum full, recreate all indexes. > (There are other procedures you may find recommended, such as CLUSTER, > but they have transient disk-space requirements that you're not gonna > be able to afford when your back is to the wall.) > Thanks for your suggestions. Doing the tune2fs trick worked enough for me to get a vacuum full to run -- which then pointed out the fact that max_fsm_pages was still running off of the default 1000 (doing a SELECT COUNT(*) FROM pg_class; showed over 100k relations) so, I made some much needed tweaks to postresql.conf, restarted the db and then restarted the vacuum full (it's still running now). As far as the rest of the suggestion (CLUSTER, index drop/add, etc...), there really isn't much of a point as this database is for development only and is only going to be around for about another month when we build a whole new pristine development db and environment from the ground up (I can't wait!), but these are all good things to know. -- erik jones <erik@myemma.com> software development emma(r)