Search Postgresql Archives

Re: Why is RELEASE SAVEPOINT sometimes slow?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



On 11 May 2012 11:16, Evan Martin <postgresql@xxxxxxxxxxxxxxxxx> wrote:
> I'm running a bulk import application against PostgreSQL 9.1.3, which has
> several stages and each stage follows the same general pattern:
>
> BEGIN TRANSACTION
> DELETE (many rows)
>
> CREATE SAVEPOINT
> INSERT
> ...
> RELEASE SAVEPOINT
>
> CREATE SAVEPOINT
> INSERT
> INSERT
> ... half an hour of inserts later ...
> RELEASE SAVEPOINT
>
> COMMIT TRANSACTION
>
> I find that for one particular stage of the import the RELEASE SAVEPOINT
> command consistently takes about 6 minutes, while for the rest of them it's
> very quick. COMMIT TRANSACTION is always very quick.
>
> At first I thought the discrepancy may be because that particular stage has
> many INSERT statements inside one savepoint, while other stages create many
> savepoints with a small amount of work in each. However, if I take out the
> savepoints entirely then the COMMIT TRANSACTION statement for that stage
> takes 6 minutes, while for the rest of them it's still very quick.
>
> Could anyone explain what may be happening here? What is PostgreSQL doing
> when I call RELEASE SAVEPOINT that it seems to otherwise do in COMMIT
> TRANSACTION?

Sounds interesting.

Please can you produce a test case that demonstrates this, then post
the SQL file and an output of a run that shows the negative timing?

Thanks

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

Add to Google Powered by Linux