Обсуждение: Feedback on auto-pruning approach
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
"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
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
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 whicheverthreshold 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 dbsize limit to be as large as possible - how much disk space should I reserve for standard Postgres operations - e.g. sortspace, WAL, etc.. I'm sure this depends a bit on our configuration, etc.. but if someone can point me in the directionas 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
> 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
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.
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,
regardlessof the disk size, they will run out at some time. Some of our solutions grow by 3.5 Gigs per day - and 6
monthsof history is not an unreasonable expectation. We've just decided we want to keep as much history as possible
givenspace 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
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
>
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