pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Feedback on auto-pruning approach
9+ messages / 6 participants
[nested] [flat]

* Feedback on auto-pruning approach
@ 2006-02-15 01:41 Mark Liberman <mliberman@goldpocket.com>
  2006-02-15 03:33 ` Re: Feedback on auto-pruning approach Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Mark Liberman @ 2006-02-15 01:41 UTC (permalink / raw)
  To: pgsql-admin

Hi,

We have a system build on PG 8.1 that is constantly running and adding data to a bunch of tables, and can grow by over a Gig a day.  We deliver this as a packaged solution on a server that runs unattended.  So, we need to make sure we delete some of the data periodically (we have auto-vacuum running to minimize the amount of obsolete tuples).  What I am working on is a solution to delete the appropriate amount of historical data at any time to keep the free disk space above a certain threshold.  This will allow us to maximize the length of historical data we can keep without running out of disk space.

So, I have built a function that returns an approximation of the space taken up by the non-obsolete tuples, I call this used_size, and the physical disk space taken up by the data files - I call this disk_size.

The following sql query is what I use to return these values:

SELECT sum((bytes_per_row + row_overhead_bytes) * tuples) as used_size,
       sum(pages)::float * 8192  as disk_size,
       sum(pages) as total_pages
from
(
select c.relname,
       sum(case when a.attlen = -1 then _var_col_bytes
                else a.attlen
           end) as bytes_per_row,
       max(CASE WHEN c.relkind = 'i' THEN 4 ELSE 32 END) as row_overhead_bytes,
       max(c.reltuples) as tuples,
       max(c.relpages) as pages
from pg_class c,
     pg_attribute a,
     pg_namespace n 
where c.oid = a.attrelid
  and c.relnamespace = n.oid
  and c.relkind in ('i','r')
  and a.atttypid not in (26,27,28,29)
group by c.relname) by_table;

A few notes:

1) I have used 32 bytes for the row tuple header overhead and 4 bytes for index tuple overhead
2) The attribute types 26,27,28,29 are oid,tid,xid,cid - which, I believe are already counted in the row overhead
3) _var_col_bytes is an input parameter to the function which measures the assumed size of any variable length columns

I then use the function to actively manage the used_size (after statistics are computed via ANALYZE, of course).  Through a process I track the daily growth of used_size and this tells me how many days of data I need to remove to stay within my limits.  The disk_size is not actively managed, but just represents the "high-water-mark" of the used_size.

Questions:

1) I have found the 32 bytes overhead mentioned in a few places, but have not seen any specific reference to the byte overhead of an index header row.  Does know the best number to use here for an assumption?

2) Are there any other holes in my logic/query?

3) Has anyone solved this entire problem in another fashion (e.g. auto-pruning - only delete what's necessary to stay within limits).

Any feedback is greatly appreciated,

Mark

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Feedback on auto-pruning approach
  2006-02-15 01:41 Feedback on auto-pruning approach Mark Liberman <mliberman@goldpocket.com>
@ 2006-02-15 03:33 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2006-02-15 03:33 UTC (permalink / raw)
  To: Mark Liberman <mliberman@goldpocket.com>; +Cc: pgsql-admin

"Mark Liberman" <mliberman@goldpocket.com> writes:
> where c.oid = a.attrelid
>   and c.relnamespace = n.oid
>   and c.relkind in ('i','r')
>   and a.atttypid not in (26,27,28,29)
> group by c.relname) by_table;

> A few notes:

> 1) I have used 32 bytes for the row tuple header overhead and 4 bytes =
> for index tuple overhead
> 2) The attribute types 26,27,28,29 are oid,tid,xid,cid - which, I =
> believe are already counted in the row overhead

You should not do it that way, because those are perfectly valid
datatypes for user columns.  Instead of the type test, check for
attnum > 0.  The "system columns" that represent row overhead items
have attnum < 0.  You might want to consider ignoring columns where
attisdropped, too, though this is a bit of a judgment call since a
dropped column might still be eating storage space.

Another thing you could do is left-join to pg_stats and use ANALYZE's
estimate of average column width where available, instead of hardwired
guesses.

Another important point is that this calculation is ignoring TOAST
space ... do you have any columns wide enough to get toasted?

> 1) I have found the 32 bytes overhead mentioned in a few places, but =
> have not seen any specific reference to the byte overhead of an index =
> header row.  Does know the best number to use here for an assumption?

12 bytes (8-byte header + 4-byte line pointer).

> 3) Has anyone solved this entire problem in another fashion (e.g. =
> auto-pruning - only delete what's necessary to stay within limits).

Have you looked at contrib/pgstattuple?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Feedback on auto-pruning approach
@ 2006-03-28 02:32 Mark Liberman <mliberman@mixedsignals.com>
  2006-03-28 18:19 ` Re: Feedback on auto-pruning approach Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Mark Liberman @ 2006-03-28 02:32 UTC (permalink / raw)
  To: pgsql-admin

So, I have finally complete this auto-pruning solution.  It has proven effective in keeping the size of the db under whichever threshold I set in an unattended fashion.

I have one final question.  If my goal is to maximize the amount of historical data that we can keep - e.g. set the db size limit to be as large as possible - how much disk space should I reserve for standard Postgres operations - e.g. sort space, WAL, etc..  I'm sure this depends a bit on our configuration, etc.. but if someone can point me in the direction as to what factors I should consider, I'd greatly appreciate it.

Thanks,

Mark

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Feedback on auto-pruning approach
  2006-03-28 02:32 Re: Feedback on auto-pruning approach Mark Liberman <mliberman@mixedsignals.com>
@ 2006-03-28 18:19 ` Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Jim C. Nasby @ 2006-03-28 18:19 UTC (permalink / raw)
  To: Mark Liberman <mliberman@mixedsignals.com>; +Cc: pgsql-admin

On Mon, Mar 27, 2006 at 06:32:42PM -0800, Mark Liberman wrote:
> So, I have finally complete this auto-pruning solution.  It has proven effective in keeping the size of the db under whichever threshold I set in an unattended fashion.
> 
> I have one final question.  If my goal is to maximize the amount of historical data that we can keep - e.g. set the db size limit to be as large as possible - how much disk space should I reserve for standard Postgres operations - e.g. sort space, WAL, etc..  I'm sure this depends a bit on our configuration, etc.. but if someone can point me in the direction as to what factors I should consider, I'd greatly appreciate it.

Probably your biggest issue will be temporary files created by temporary
tables, sorts that spill to disk, etc.

What I'm confused by is the concern about disk space in the first place.
Drives are very cheap, people are normally much more concerned about IO
bandwidth.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Feedback on auto-pruning approach
@ 2006-03-28 18:23 Mark Liberman <mliberman@mixedsignals.com>
  2006-03-28 18:28 ` Re: Feedback on auto-pruning approach Scott Marlowe <smarlowe@g2switchworks.com>
  2006-03-28 18:32 ` Re: Feedback on auto-pruning approach Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Mark Liberman @ 2006-03-28 18:23 UTC (permalink / raw)
  To: pgsql-admin

> Probably your biggest issue will be temporary files created by temporary tables, sorts that spill to disk, etc.

Is there any way to monitor this so I can estimate?

> What I'm confused by is the concern about disk space in the first place.

We provide a device to customers that must run in an unattended fashion for as long as the hardward holds up.  So, regardless of the disk size, they will run out at some time.  Some of our solutions grow by 3.5 Gigs per day - and 6 months of history is not an unreasonable expectation.  We've just decided we want to keep as much history as possible given space limitations.


-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: Tue 3/28/2006 10:19 AM
To: Mark Liberman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Feedback on auto-pruning approach
 
On Mon, Mar 27, 2006 at 06:32:42PM -0800, Mark Liberman wrote:
> So, I have finally complete this auto-pruning solution.  It has proven effective in keeping the size of the db under whichever threshold I set in an unattended fashion.
> 
> I have one final question.  If my goal is to maximize the amount of historical data that we can keep - e.g. set the db size limit to be as large as possible - how much disk space should I reserve for standard Postgres operations - e.g. sort space, WAL, etc..  I'm sure this depends a bit on our configuration, etc.. but if someone can point me in the direction as to what factors I should consider, I'd greatly appreciate it.

Probably your biggest issue will be temporary files created by temporary
tables, sorts that spill to disk, etc.

What I'm confused by is the concern about disk space in the first place.
Drives are very cheap, people are normally much more concerned about IO
bandwidth.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Feedback on auto-pruning approach
  2006-03-28 18:23 Re: Feedback on auto-pruning approach Mark Liberman <mliberman@mixedsignals.com>
@ 2006-03-28 18:28 ` Scott Marlowe <smarlowe@g2switchworks.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Scott Marlowe @ 2006-03-28 18:28 UTC (permalink / raw)
  To: Mark Liberman <mliberman@mixedsignals.com>; +Cc: pgsql-admin

On Tue, 2006-03-28 at 12:23, Mark Liberman wrote:
> > Probably your biggest issue will be temporary files created by
> temporary tables, sorts that spill to disk, etc.
> 
> Is there any way to monitor this so I can estimate?
> 
> > What I'm confused by is the concern about disk space in the first
> place.
> 
> We provide a device to customers that must run in an unattended
> fashion for as long as the hardward holds up.  So, regardless of the
> disk size, they will run out at some time.  Some of our solutions grow
> by 3.5 Gigs per day - and 6 months of history is not an unreasonable
> expectation.  We've just decided we want to keep as much history as
> possible given space limitations.

If most of that is text, it should be fairly compressible.

So, how compressible are the data, and have you done some very basic
checks to see how much your postgresql data directory grows when you add
to the database.  It's pretty easy to do.  Just, as the postgres user:

cd $PGDATA
du -sh base
(load a couple gigs of data)
du -sh base

and compare the difference.  It'll at least get you in the ball park.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Feedback on auto-pruning approach
  2006-03-28 18:23 Re: Feedback on auto-pruning approach Mark Liberman <mliberman@mixedsignals.com>
@ 2006-03-28 18:32 ` Jim C. Nasby <jnasby@pervasive.com>
  2006-03-28 18:42   ` Re: Feedback on auto-pruning approach Richard Broersma Jr <rabroersma@yahoo.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Jim C. Nasby @ 2006-03-28 18:32 UTC (permalink / raw)
  To: Mark Liberman <mliberman@mixedsignals.com>; +Cc: pgsql-admin

On Tue, Mar 28, 2006 at 10:23:45AM -0800, Mark Liberman wrote:
> > Probably your biggest issue will be temporary files created by temporary tables, sorts that spill to disk, etc.
> 
> Is there any way to monitor this so I can estimate?

Keep an eye on $PGDATA/base/{database_oid}/pgsql_tmp.

> > What I'm confused by is the concern about disk space in the first place.
> 
> We provide a device to customers that must run in an unattended fashion for as long as the hardward holds up.  So, regardless of the disk size, they will run out at some time.  Some of our solutions grow by 3.5 Gigs per day - and 6 months of history is not an unreasonable expectation.  We've just decided we want to keep as much history as possible given space limitations.

You might want to take a look at http://rrs.decibel.org, which is
something I created for a similar purpose.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Feedback on auto-pruning approach
  2006-03-28 18:23 Re: Feedback on auto-pruning approach Mark Liberman <mliberman@mixedsignals.com>
  2006-03-28 18:32 ` Re: Feedback on auto-pruning approach Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-28 18:42   ` Richard Broersma Jr <rabroersma@yahoo.com>
  2006-03-28 19:41     ` Re: Feedback on auto-pruning approach Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Richard Broersma Jr @ 2006-03-28 18:42 UTC (permalink / raw)
  To: Jim C. Nasby <jnasby@pervasive.com>; Mark Liberman <mliberman@mixedsignals.com>; +Cc: pgsql-admin

Could RRS be implemented in a similar fashion to OPC-HDA?

Regards,
Richard

--- "Jim C. Nasby" <jnasby@pervasive.com> wrote:

> On Tue, Mar 28, 2006 at 10:23:45AM -0800, Mark Liberman wrote:
> > > Probably your biggest issue will be temporary files created by temporary tables, sorts that
> spill to disk, etc.
> > 
> > Is there any way to monitor this so I can estimate?
> 
> Keep an eye on $PGDATA/base/{database_oid}/pgsql_tmp.
> 
> > > What I'm confused by is the concern about disk space in the first place.
> > 
> > We provide a device to customers that must run in an unattended fashion for as long as the
> hardward holds up.  So, regardless of the disk size, they will run out at some time.  Some of
> our solutions grow by 3.5 Gigs per day - and 6 months of history is not an unreasonable
> expectation.  We've just decided we want to keep as much history as possible given space
> limitations.
> 
> You might want to take a look at http://rrs.decibel.org, which is
> something I created for a similar purpose.
> -- 
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Feedback on auto-pruning approach
  2006-03-28 18:23 Re: Feedback on auto-pruning approach Mark Liberman <mliberman@mixedsignals.com>
  2006-03-28 18:32 ` Re: Feedback on auto-pruning approach Jim C. Nasby <jnasby@pervasive.com>
  2006-03-28 18:42   ` Re: Feedback on auto-pruning approach Richard Broersma Jr <rabroersma@yahoo.com>
@ 2006-03-28 19:41     ` Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Jim C. Nasby @ 2006-03-28 19:41 UTC (permalink / raw)
  To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Mark Liberman <mliberman@mixedsignals.com>; pgsql-admin

On Tue, Mar 28, 2006 at 10:42:13AM -0800, Richard Broersma Jr wrote:
> Could RRS be implemented in a similar fashion to OPC-HDA?

I'm not really familiar with that. The idea behind RRS is that you keep
historical information, but you don't keep the detailed data, similar to
how RRD works.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2006-03-28 19:41 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-15 01:41 Feedback on auto-pruning approach Mark Liberman <mliberman@goldpocket.com>
2006-02-15 03:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-28 02:32 Re: Feedback on auto-pruning approach Mark Liberman <mliberman@mixedsignals.com>
2006-03-28 18:19 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-28 18:23 Re: Feedback on auto-pruning approach Mark Liberman <mliberman@mixedsignals.com>
2006-03-28 18:28 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-28 18:32 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-28 18:42   ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-03-28 19:41     ` Jim C. Nasby <jnasby@pervasive.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