Skip site navigation (1) Skip section navigation (2)

Re: transactions start time

From: Richard Huxton <dev(at)archonet(dot)com>
To: Aleksei Arefjev <aleksei(dot)arefjev(at)nordicgaming(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: transactions start time
Date: 2012-07-24 17:21:38
Message-ID: 500ED9A2.3040101@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 24/07/12 12:14, Aleksei Arefjev wrote:
> Hi,
>
> In statistical reports gathered by PgBadger on our PostgreSQL databases
> almost always we have in "Queries that took up the most time" report
> table information about transactions start time ('BEGIN;' command).
> Something like that in example below:
>
> 2    3h34m52.26s    48,556,167    0.00s    BEGIN;
>
>                          0.82s | BEGIN;
>                          0.82s | BEGIN;
>                          0.82s | BEGIN;
>                          0.81s | BEGIN;
>                          0.81s | BEGIN;
>                          0.81s | BEGIN;
>                          0.80s | BEGIN;
>                          0.80s | BEGIN;
>                          0.79s | BEGIN;
>                          0.79s | BEGIN;

I'm not sure if I'm reading this right, but are there more than 48 
million BEGINs that took 0s each (presumably rounded down) and then a 
handful taking about 0.8s?

If so, then it's likely nothing to do with the BEGIN and just that the 
machine was busy doing other things when you started a transaction.

> Databases placed on different hardware, OS - Debian GNU/Linux,
> PostgreSQL 9.1
>
> So, questions are:
> 1. Is this a normal situation with transactions start time ( BEGIN method) ?

See above

> 2. How can we reduce transactions start time if it's possible in principle?

Below 0.00? Probably not

> 3. What happens in PostgreSQL on transaction starting time? Can someone
> describe this process in detail? (of course, I saw in PostgreSQL source
> code, for example, definition such kind functions, like StartTransaction
> function, but it's not so easy to understand for third-party researcher,
> that all of these operations mean in real for performance)

Well there are two important things to understand:
1. All* commands run in a transaction
2. I think most of the work in getting a new snapshot etc gets pushed 
back until it's needed.

So - the overall impact of issuing BEGIN should be close to zero.

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-performance by date

Next:From: Laszlo NagyDate: 2012-07-24 18:27:19
Subject: Re: ZFS vs. UFS
Previous:From: Tom LaneDate: 2012-07-24 17:12:46
Subject: Re: Using ctid column changes plan drastically

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group