Postgres Pro
Downloads
Home   >   mailing lists

Thread: disk space usage unexpected

disk space usage unexpected

From
"Rose Zhou"
Date:
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

Re: disk space usage unexpected

From
Ben Chobot
Date:
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.

Re: disk space usage unexpected

From
"Rose Zhou"
Date:
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.


Re: disk space usage unexpected

From
"Joshua D. Drake"
Date:
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.

Re: disk space usage unexpected

From
"Joshua D. Drake"
Date:
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.

Re: disk space usage unexpected

From
Ben Chobot
Date:
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?

Re: disk space usage unexpected

From
"Rose Zhou"
Date:
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?