Thread: slow DDL creation

slow DDL creation

From
Kevin Kempter
Date:
Hi all ;

we have an automated partition creation process that includes the creation of
an FK constraint. we have a few other servers with similar scenarios and this
is the only server that stinks per when we create the new partitions.

Anyone have any thoughts on how to debug this?  were running postgres 8.4.4 on
CentOS 5.5

Thanks in advance

Re: slow DDL creation

From
bricklen
Date:
On Mon, Aug 30, 2010 at 3:28 PM, Kevin Kempter
<cs_dba@consistentstate.com> wrote:
> Hi all ;
>
> we have an automated partition creation process that includes the creation of
> an FK constraint. we have a few other servers with similar scenarios and this
> is the only server that stinks per when we create the new partitions.
>
> Anyone have any thoughts on how to debug this?  were running postgres 8.4.4 on
> CentOS 5.5
>
> Thanks in advance

Is the referenced column indexed?

Re: slow DDL creation

From
David Fetter
Date:
On Mon, Aug 30, 2010 at 04:28:25PM -0600, Kevin Kempter wrote:
> Hi all ;
>
> we have an automated partition creation process that includes the creation of
> an FK constraint. we have a few other servers with similar scenarios and this
> is the only server that stinks per when we create the new partitions.
>
> Anyone have any thoughts on how to debug this?  were running postgres 8.4.4 on
> CentOS 5.5

If you're doing the partitions on demand, you could be getting
deadlocks.  Any reason not to pre-create a big bunch of them in
advance?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: slow DDL creation

From
Kevin Kempter
Date:
On Monday 30 August 2010 17:04, bricklen wrote:
> On Mon, Aug 30, 2010 at 3:28 PM, Kevin Kempter
>
> <cs_dba@consistentstate.com> wrote:
> > Hi all ;
> >
> > we have an automated partition creation process that includes the
> > creation of an FK constraint. we have a few other servers with similar
> > scenarios and this is the only server that stinks per when we create the
> > new partitions.
> >
> > Anyone have any thoughts on how to debug this?  were running postgres
> > 8.4.4 on CentOS 5.5
> >
> > Thanks in advance
>
> Is the referenced column indexed?

no, but its for a new partition so there's no data as of yet in the partition

Re: slow DDL creation

From
Robert Haas
Date:
On Tue, Aug 31, 2010 at 11:35 AM, Kevin Kempter
<cs_dba@consistentstate.com> wrote:
> On Monday 30 August 2010 17:04, bricklen wrote:
>> On Mon, Aug 30, 2010 at 3:28 PM, Kevin Kempter
>>
>> <cs_dba@consistentstate.com> wrote:
>> > Hi all ;
>> >
>> > we have an automated partition creation process that includes the
>> > creation of an FK constraint. we have a few other servers with similar
>> > scenarios and this is the only server that stinks per when we create the
>> > new partitions.
>> >
>> > Anyone have any thoughts on how to debug this?  were running postgres
>> > 8.4.4 on CentOS 5.5
>> >
>> > Thanks in advance
>>
>> Is the referenced column indexed?
>
> no, but its for a new partition so there's no data as of yet in the partition

What exactly does "stinks" mean?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company