pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
disk 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