Обсуждение: Pre-creating partitions incurs insert penalty

Поиск
Список
Период
Сортировка

Pre-creating partitions incurs insert penalty

От
Anj Adu
Дата:
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.
 
 

Re: Pre-creating partitions incurs insert penalty

От
Scott Marlowe
Дата:
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.

Re: Pre-creating partitions incurs insert penalty

От
Anj Adu
Дата:
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.

Re: Pre-creating partitions incurs insert penalty

От
Scott Marlowe
Дата:
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.

Re: Pre-creating partitions incurs insert penalty

От
Anj Adu
Дата:
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.

Re: Pre-creating partitions incurs insert penalty

От
Scott Marlowe
Дата:
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.