pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDisk is full, what's cool to get rid of?
10+ messages / 6 participants
[nested] [flat]
* Disk is full, what's cool to get rid of?
@ 2006-07-27 16:20 Erik Jones <erik@myemma.com>
0 siblings, 2 replies; 10+ messages in thread
From: Erik Jones @ 2006-07-27 16:20 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-27 16:32 Jeff Frost <jeff@frostconsultingllc.com>
parent: Erik Jones <erik@myemma.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jeff Frost @ 2006-07-27 16:32 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-27 16:37 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Erik Jones <erik@myemma.com>
1 sibling, 1 reply; 10+ messages in thread
From: Andrew Sullivan @ 2006-07-27 16:37 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-27 18:41 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2006-07-27 18:41 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Erik Jones <erik@myemma.com>; pgsql-sql
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...
> >
> >
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-27 19:27 Erik Jones <erik@myemma.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 10+ messages in thread
From: Erik Jones @ 2006-07-27 19:27 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-27 19:40 Jeff Frost <jeff@frostconsultingllc.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff Frost @ 2006-07-27 19:40 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-27 20:00 Erik Jones <erik@myemma.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Erik Jones @ 2006-07-27 20:00 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-27 20:17 Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 10+ messages in thread
From: Wilkinson Charlie E @ 2006-07-27 20:17 UTC (permalink / raw)
To: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-27 20:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2006-07-27 20:36 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Disk is full, what's cool to get rid of?
@ 2006-07-28 16:43 Erik Jones <erik@myemma.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Erik Jones @ 2006-07-28 16:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-07-28 16:43 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-27 16:20 Disk is full, what's cool to get rid of? Erik Jones <erik@myemma.com>
2006-07-27 16:32 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-07-27 18:41 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-27 19:27 ` Erik Jones <erik@myemma.com>
2006-07-27 19:40 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-07-27 20:00 ` Erik Jones <erik@myemma.com>
2006-07-27 20:17 ` Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>
2006-07-27 16:37 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-07-27 20:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 16:43 ` Erik Jones <erik@myemma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox