pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddisk space usage unexpected
6+ messages / 3 participants
[nested] [flat]
* disk space usage unexpected
@ 2010-02-15 19:59 Rose Zhou <rose@anatec.com>
0 siblings, 2 replies; 6+ messages in thread
From: Rose Zhou @ 2010-02-15 19:59 UTC (permalink / raw)
To: pgsql-performance
Good day,
I have a PostgreSQL 8.4 database installed on WinXP x64 with very heavy
writing and updating on a partitioned table. Sometimes within one minute,
there are tens of file with size=1,048,576kb (such as
filenode.1,filenode.2,...filenode.43) created in the database subdirectory
within PGDATA/base.
This caused the disk space quickly used up. Is this expected?
Thanks for any information
Best Regards
Rose Zhou
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: disk space usage unexpected
@ 2010-02-15 21:15 Ben Chobot <bench@silentmedia.com>
parent: Rose Zhou <rose@anatec.com>
1 sibling, 1 reply; 6+ messages in thread
From: Ben Chobot @ 2010-02-15 21:15 UTC (permalink / raw)
To: Rose Zhou <rose@anatec.com>; +Cc: pgsql-performance
On Feb 15, 2010, at 11:59 AM, Rose Zhou wrote:
> Good day,
>
> I have a PostgreSQL 8.4 database installed on WinXP x64 with very heavy
> writing and updating on a partitioned table. Sometimes within one minute,
> there are tens of file with size=1,048,576kb (such as
> filenode.1,filenode.2,...filenode.43) created in the database subdirectory
> within PGDATA/base.
>
> This caused the disk space quickly used up. Is this expected?
It's expected if you're doing lots of inserts, and/or lots of updates or deletes without an appropriate amount of vacuuming.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: disk space usage unexpected
@ 2010-02-15 21:25 Rose Zhou <rose@anatec.com>
parent: Ben Chobot <bench@silentmedia.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rose Zhou @ 2010-02-15 21:25 UTC (permalink / raw)
To: 'Ben Chobot' <bench@silentmedia.com>; +Cc: pgsql-performance
Thanks Ben:
I will adjust the auto vacuum parameters. It is on now, maybe not frequently
enough.
How to get the disk space back to OS? Will a Vacuum Full Verbose get the
disk space back to OS?
Best Regards
Rose Zhou
_____
From: Ben Chobot [mailto:bench@silentmedia.com]
Sent: 15 February 2010 16:16
To: Rose Zhou
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] disk space usage unexpected
On Feb 15, 2010, at 11:59 AM, Rose Zhou wrote:
> Good day,
>
> I have a PostgreSQL 8.4 database installed on WinXP x64 with very heavy
> writing and updating on a partitioned table. Sometimes within one minute,
> there are tens of file with size=1,048,576kb (such as
> filenode.1,filenode.2,...filenode.43) created in the database subdirectory
> within PGDATA/base.
>
> This caused the disk space quickly used up. Is this expected?
It's expected if you're doing lots of inserts, and/or lots of updates or
deletes without an appropriate amount of vacuuming.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: disk space usage unexpected
@ 2010-02-16 01:24 Joshua D. Drake <jd@commandprompt.com>
parent: Rose Zhou <rose@anatec.com>
1 sibling, 0 replies; 6+ messages in thread
From: Joshua D. Drake @ 2010-02-16 01:24 UTC (permalink / raw)
To: Rose Zhou <rose@anatec.com>; +Cc: pgsql-performance
On Mon, 2010-02-15 at 14:59 -0500, Rose Zhou wrote:
> Good day,
>
> I have a PostgreSQL 8.4 database installed on WinXP x64 with very heavy
> writing and updating on a partitioned table. Sometimes within one minute,
> there are tens of file with size=1,048,576kb (such as
> filenode.1,filenode.2,...filenode.43) created in the database subdirectory
> within PGDATA/base.
>
> This caused the disk space quickly used up. Is this expected?
Yes. Especially if autovacuum is not keeping up with the number of
updates.
Joshua D. Drake
>
> Thanks for any information
>
>
>
> Best Regards
>
> Rose Zhou
>
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: disk space usage unexpected
@ 2010-02-17 18:34 Ben Chobot <bench@silentmedia.com>
parent: Rose Zhou <rose@anatec.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ben Chobot @ 2010-02-17 18:34 UTC (permalink / raw)
To: Rose Zhou <rose@anatec.com>; +Cc: pgsql-performance
On Feb 15, 2010, at 1:25 PM, Rose Zhou wrote:
> Thanks Ben:
>
> I will adjust the auto vacuum parameters. It is on now, maybe not frequently enough.
> How to get the disk space back to OS? Will a Vacuum Full Verbose get the disk space back to OS?
>
>
Yes, but it might bloat your indexes. Do you actually need to get your disk space back? If you did, would the database just eat it up again after more activity?=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: disk space usage unexpected
@ 2010-02-17 18:43 Rose Zhou <rose@anatec.com>
parent: Ben Chobot <bench@silentmedia.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rose Zhou @ 2010-02-17 18:43 UTC (permalink / raw)
To: 'Ben Chobot' <bench@silentmedia.com>; +Cc: pgsql-performance
Vacuum Full Verbose did not get the disk space back to OS, it did bloat my
indexes, also got out of memory error. I dumped the suspicious table,
deleted it, re-created it, then restored the data and re-indexed the data,
so I got the disk space back.
To avoid the database eating up the disk space again, I have adjusted the
autovacuum parameters, to make it keep up with the updates.
Not sure if this is the right way to solve this kind of problem.
Best Regards
Rose Zhou
_____
From: Ben Chobot [mailto:bench@silentmedia.com]
Sent: 17 February 2010 13:34
To: Rose Zhou
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] disk space usage unexpected
On Feb 15, 2010, at 1:25 PM, Rose Zhou wrote:
Thanks Ben:
I will adjust the auto vacuum parameters. It is on now, maybe not frequently
enough.
How to get the disk space back to OS? Will a Vacuum Full Verbose get the
disk space back to OS?
Yes, but it might bloat your indexes. Do you actually need to get your disk
space back? If you did, would the database just eat it up again after more
activity?
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-02-17 18:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-15 19:59 disk space usage unexpected Rose Zhou <rose@anatec.com>
2010-02-15 21:15 ` Ben Chobot <bench@silentmedia.com>
2010-02-15 21:25 ` Rose Zhou <rose@anatec.com>
2010-02-17 18:34 ` Ben Chobot <bench@silentmedia.com>
2010-02-17 18:43 ` Rose Zhou <rose@anatec.com>
2010-02-16 01:24 ` Joshua D. Drake <jd@commandprompt.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