Обсуждение: Pre-creating partitions incurs insert penalty
I noticed a very strange performance issue after I pre-create daily partitions for the next month on a table that has a very large insert volume (30 million a day). After the partitions are created..the inserts seem to slow down. I verifiied that this was the issue by dropping the partitions...When I dropped the pre-created partitions..the performance issue disappeared. Looks like you cannot have too many partitions (in this case..I had a total of 35 partitions when the performance issue was noticed)
Postgres version is 8.1.9 on a 8 core Red Hat linux box with a Raid 10 configured hard frive set up that has 6 15K disks . The load factor on the box is very low ..just the insert performance suffers after the partition creation.
On Thu, May 28, 2009 at 5:22 PM, Anj Adu <fotographs@gmail.com> wrote: > I noticed a very strange performance issue after I pre-create daily > partitions for the next month on a table that has a very large insert volume > (30 million a day). After the partitions are created..the inserts seem to > slow down. I verifiied that this was the issue by dropping the > partitions...When I dropped the pre-created partitions..the performance > issue disappeared. Looks like you cannot have too many partitions (in this > case..I had a total of 35 partitions when the performance issue was noticed) How are you enforcing partiitoning on your inserts? Via app knowledge, triggers, or rules? I'd expect rules might have a penalty with more partitions, but not expect it from app or trigger based partitioning.
Partitioning is implemented via rules and check constraints to ensure partition integrity.
On Thu, May 28, 2009 at 5:21 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, May 28, 2009 at 5:22 PM, Anj Adu <fotographs@gmail.com> wrote:How are you enforcing partiitoning on your inserts? Via app
> I noticed a very strange performance issue after I pre-create daily
> partitions for the next month on a table that has a very large insert volume
> (30 million a day). After the partitions are created..the inserts seem to
> slow down. I verifiied that this was the issue by dropping the
> partitions...When I dropped the pre-created partitions..the performance
> issue disappeared. Looks like you cannot have too many partitions (in this
> case..I had a total of 35 partitions when the performance issue was noticed)
knowledge, triggers, or rules? I'd expect rules might have a penalty
with more partitions, but not expect it from app or trigger based
partitioning.
Yeah, rules have more overhead the more partitions you have, whereas triggers do not. If you can switch to triggers you'd like see better performance, but be aware that plpgsql is kind of retarded when it comes to doing anything fancy with triggers. On Thu, May 28, 2009 at 6:43 PM, Anj Adu <fotographs@gmail.com> wrote: > Partitioning is implemented via rules and check constraints to ensure > partition integrity. > > On Thu, May 28, 2009 at 5:21 PM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> On Thu, May 28, 2009 at 5:22 PM, Anj Adu <fotographs@gmail.com> wrote: >> > I noticed a very strange performance issue after I pre-create daily >> > partitions for the next month on a table that has a very large insert >> > volume >> > (30 million a day). After the partitions are created..the inserts seem >> > to >> > slow down. I verifiied that this was the issue by dropping the >> > partitions...When I dropped the pre-created partitions..the performance >> > issue disappeared. Looks like you cannot have too many partitions (in >> > this >> > case..I had a total of 35 partitions when the performance issue was >> > noticed) >> >> How are you enforcing partiitoning on your inserts? Via app >> knowledge, triggers, or rules? I'd expect rules might have a penalty >> with more partitions, but not expect it from app or trigger based >> partitioning. > > -- When fascism comes to America, it will be intolerance sold as diversity.
Thanks..I'll rewrite the rules as triggers. Would that in any way impact constraint exclusion for select statements ?..I assume not.
On Thu, May 28, 2009 at 5:46 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
Yeah, rules have more overhead the more partitions you have, whereas
triggers do not. If you can switch to triggers you'd like see better
performance, but be aware that plpgsql is kind of retarded when it
comes to doing anything fancy with triggers.--
On Thu, May 28, 2009 at 6:43 PM, Anj Adu <fotographs@gmail.com> wrote:
> Partitioning is implemented via rules and check constraints to ensure
> partition integrity.
>
> On Thu, May 28, 2009 at 5:21 PM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Thu, May 28, 2009 at 5:22 PM, Anj Adu <fotographs@gmail.com> wrote:
>> > I noticed a very strange performance issue after I pre-create daily
>> > partitions for the next month on a table that has a very large insert
>> > volume
>> > (30 million a day). After the partitions are created..the inserts seem
>> > to
>> > slow down. I verifiied that this was the issue by dropping the
>> > partitions...When I dropped the pre-created partitions..the performance
>> > issue disappeared. Looks like you cannot have too many partitions (in
>> > this
>> > case..I had a total of 35 partitions when the performance issue was
>> > noticed)
>>
>> How are you enforcing partiitoning on your inserts? Via app
>> knowledge, triggers, or rules? I'd expect rules might have a penalty
>> with more partitions, but not expect it from app or trigger based
>> partitioning.
>
>
When fascism comes to America, it will be intolerance sold as diversity.
Nope, they're independent of each other. On Thu, May 28, 2009 at 8:49 PM, Anj Adu <fotographs@gmail.com> wrote: > Thanks..I'll rewrite the rules as triggers. Would that in any way impact > constraint exclusion for select statements ?..I assume not. > > On Thu, May 28, 2009 at 5:46 PM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> Yeah, rules have more overhead the more partitions you have, whereas >> triggers do not. If you can switch to triggers you'd like see better >> performance, but be aware that plpgsql is kind of retarded when it >> comes to doing anything fancy with triggers. >> >> On Thu, May 28, 2009 at 6:43 PM, Anj Adu <fotographs@gmail.com> wrote: >> > Partitioning is implemented via rules and check constraints to ensure >> > partition integrity. >> > >> > On Thu, May 28, 2009 at 5:21 PM, Scott Marlowe <scott.marlowe@gmail.com> >> > wrote: >> >> >> >> On Thu, May 28, 2009 at 5:22 PM, Anj Adu <fotographs@gmail.com> wrote: >> >> > I noticed a very strange performance issue after I pre-create daily >> >> > partitions for the next month on a table that has a very large insert >> >> > volume >> >> > (30 million a day). After the partitions are created..the inserts >> >> > seem >> >> > to >> >> > slow down. I verifiied that this was the issue by dropping the >> >> > partitions...When I dropped the pre-created partitions..the >> >> > performance >> >> > issue disappeared. Looks like you cannot have too many partitions (in >> >> > this >> >> > case..I had a total of 35 partitions when the performance issue was >> >> > noticed) >> >> >> >> How are you enforcing partiitoning on your inserts? Via app >> >> knowledge, triggers, or rules? I'd expect rules might have a penalty >> >> with more partitions, but not expect it from app or trigger based >> >> partitioning. >> > >> > >> >> >> >> -- >> When fascism comes to America, it will be intolerance sold as diversity. > > -- When fascism comes to America, it will be intolerance sold as diversity.