pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there a way to SubPartition?
6+ messages / 4 participants
[nested] [flat]
* Is there a way to SubPartition?
@ 2008-08-27 13:45 Jerry Champlin <jchamplin@absolute-performance.com>
0 siblings, 2 replies; 6+ messages in thread
From: Jerry Champlin @ 2008-08-27 13:45 UTC (permalink / raw)
To: pgsql-performance
Is there a way to use multi-level inheritance to achieve sub
partitioning that the query optimizer will recognize? With our current
application design, we would need a partition for every other day for
18 months which will not perform well. The reason we need so many
partitions is that we can't afford to vacuum the active partition (750MM
inserts + updates per day is the performance requirement for 12 months
out). After it's a day old, there are no longer any updates or inserts
and we can vacuum it at that point. If multi-level partitioning
worked, we could solve this problem without changing our code. Ideas?
-Jerry
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there a way to SubPartition?
@ 2008-08-27 14:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jerry Champlin <jchamplin@absolute-performance.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2008-08-27 14:01 UTC (permalink / raw)
To: Jerry Champlin <jchamplin@absolute-performance.com>; +Cc: pgsql-performance
Jerry Champlin <jchamplin@absolute-performance.com> writes:
> Is there a way to use multi-level inheritance to achieve sub
> partitioning that the query optimizer will recognize?
No, I don't think so. How would that make things any better anyway?
You're still going to end up with the same very large number of
partitions.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there a way to SubPartition?
@ 2008-08-27 14:10 Matthew Wakeling <matthew@flymine.org>
parent: Jerry Champlin <jchamplin@absolute-performance.com>
1 sibling, 0 replies; 6+ messages in thread
From: Matthew Wakeling @ 2008-08-27 14:10 UTC (permalink / raw)
To: pgsql-performance
On Wed, 27 Aug 2008, Jerry Champlin wrote:
> After it's a day old, there are no longer any updates or inserts and we
> can vacuum it at that point.
A pattern that has worked very well for other people is to have two
separate tables (or partitions). One contains today's data, and the other
contains historic data that is no longer updated. Once a day, transfer the
data between the partitions, and the historic data partition will not need
vacuuming.
Some changes to your code will be needed however.
Matthew
--
Vacuums are nothings. We only mention them to let them know we know
they're there.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there a way to SubPartition?
@ 2008-08-27 20:21 Jerry Champlin <jchamplin@absolute-performance.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Jerry Champlin @ 2008-08-27 20:21 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
If it were implemented in such a way that when the top level pruning
happens, a set of 3 sub partitions is selected from say 18 total and then at
the next level is selects the 3 matching sub partitions from each matched
group of 30 then you are only looking at 18+3*30 = 108 instead of 548 checks
to evaluate <example assumes monthly first level partitioning and daily sub
partitioning>. If this is not supported, then we will need to solve the
problem a different way - probably weekly partitions and refactor the code
to decrease updates by at least an order of magnitude. While we are in the
process of doing this, is there a way to make updates faster? Postgresql is
spending a lot of CPU cycles for each HOT update. We have
synchronous_commit turned off, commit siblings set to 5, commit_delay set to
50,000. With synchronous_commit off does it make any sense to be grouping
commits? Buffers written by the bgwriter vs checkpoint is 6 to 1. Buffers
written by clients vs buffers by checkpoint is 1 to 6. Is there anything
obvious here?
-Jerry
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 27, 2008 8:02 AM
To: Jerry Champlin
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Is there a way to SubPartition?
Jerry Champlin <jchamplin@absolute-performance.com> writes:
> Is there a way to use multi-level inheritance to achieve sub
> partitioning that the query optimizer will recognize?
No, I don't think so. How would that make things any better anyway?
You're still going to end up with the same very large number of
partitions.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there a way to SubPartition?
@ 2008-08-27 20:32 Alvaro Herrera <alvherre@commandprompt.com>
parent: Jerry Champlin <jchamplin@absolute-performance.com>
1 sibling, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2008-08-27 20:32 UTC (permalink / raw)
To: Jerry Champlin <jchamplin@absolute-performance.com>; +Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>; pgsql-performance
Jerry Champlin wrote:
> If it were implemented in such a way that when the top level pruning
> happens, a set of 3 sub partitions is selected from say 18 total and then at
> the next level is selects the 3 matching sub partitions from each matched
> group of 30 then you are only looking at 18+3*30 = 108 instead of 548 checks
> to evaluate <example assumes monthly first level partitioning and daily sub
> partitioning>. If this is not supported, then we will need to solve the
> problem a different way - probably weekly partitions and refactor the code
> to decrease updates by at least an order of magnitude. While we are in the
> process of doing this, is there a way to make updates faster? Postgresql is
> spending a lot of CPU cycles for each HOT update. We have
> synchronous_commit turned off, commit siblings set to 5, commit_delay set to
> 50,000.
Perhaps you do not realize this, but this is an exciting report to read.
Not many years ago, this kind of system would have been unthinkable.
We've now tuned the system so that people is starting to consider it,
and for a lot of people it is working.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there a way to SubPartition?
@ 2008-08-27 20:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jerry Champlin <jchamplin@absolute-performance.com>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2008-08-27 20:54 UTC (permalink / raw)
To: Jerry Champlin <jchamplin@absolute-performance.com>; +Cc: pgsql-performance
"Jerry Champlin" <jchamplin@absolute-performance.com> writes:
> We have synchronous_commit turned off, commit siblings set to 5,
> commit_delay set to 50,000. With synchronous_commit off does it make
> any sense to be grouping commits?
No. In fact commit_delay is a total no-op in that mode. If it were
doing anything I think you'd have found that to be a counterproductively
large setting ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-08-27 20:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-27 13:45 Is there a way to SubPartition? Jerry Champlin <jchamplin@absolute-performance.com>
2008-08-27 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-27 20:21 ` Jerry Champlin <jchamplin@absolute-performance.com>
2008-08-27 20:32 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-08-27 20:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-27 14:10 ` Matthew Wakeling <matthew@flymine.org>
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