Postgres Pro
Downloads
Home   >   mailing lists

Thread: slony issues.

slony issues.

From
Scott Marlowe
Date:
I already asked this basic question on the slony mailing lists, but
they seem kinda quiet this week.

So, I've got a database with about 30,000 objects in it, and running
the setaddtable() function is taking about 30 seconds, and spinning a
CPU 100% while doing it.  Any suggestions on indexes, or changing
functional costs to get this to run in a more reasonable time.  I'm
adding about 600 tables and 200 sequences, and with each table taking
30 seconds it's taking about 5 hours to create a set, which is just
crazy to me.  On a much slower machine but without the other 29,000 or
so objects, each table takes around 5 to 10 seconds to add, which is
still long, but not quite as long as on my production server.

Re: slony issues.

From
Jeff Davis
Date:
On Sat, 2009-11-07 at 09:23 -0700, Scott Marlowe wrote:
> I already asked this basic question on the slony mailing lists, but
> they seem kinda quiet this week.
>
> So, I've got a database with about 30,000 objects in it, and running
> the setaddtable() function is taking about 30 seconds, and spinning a
> CPU 100% while doing it.

auto_explain may be able to help. It can automatically collect slow
queries and provide explain analyze output, even for queries inside
functions.

Regards,
    Jeff Davis

Re: slony issues.

From
Scott Marlowe
Date:
On Sat, Nov 7, 2009 at 10:57 AM, Jeff Davis <pgsql@j-davis.com> wrote:
> On Sat, 2009-11-07 at 09:23 -0700, Scott Marlowe wrote:
>> I already asked this basic question on the slony mailing lists, but
>> they seem kinda quiet this week.
>>
>> So, I've got a database with about 30,000 objects in it, and running
>> the setaddtable() function is taking about 30 seconds, and spinning a
>> CPU 100% while doing it.
>
> auto_explain may be able to help. It can automatically collect slow
> queries and provide explain analyze output, even for queries inside
> functions.

Thanks I'll take a look.  I'm afraid that I might not get much out of
it if the cost is hidden by the function.  But it's a start.

Re: slony issues.

From
Jeff Davis
Date:
On Sat, 2009-11-07 at 11:59 -0700, Scott Marlowe wrote:
> Thanks I'll take a look.  I'm afraid that I might not get much out of
> it if the cost is hidden by the function.  But it's a start.

You should be able to get the cost of any SPI executed in the function
by using auto_explain.log_nested_statements. Other activity may be
hidden, but I can't think of something else that a function in slony
would spend a lot of time doing.

Regards,
    Jeff Davis