Thread: Why is RELEASE SAVEPOINT sometimes slow?
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? Thanks, Evan
On 11 May 2012 11:16, Evan Martin <postgresql@realityexists.net> 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
I dug a bit further into this and found that the code was also creating and releasing a savepoint for each insert (and there were over 10,000 of them). When I removed that the delay at the end disappeared. Regards, Evan On 11/05/2012 8:26 PM, Simon Riggs wrote: > On 11 May 2012 11:16, Evan Martin<postgresql@realityexists.net> 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 >