Обсуждение: slony issues.

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.

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

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.

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