Re: transactions start time

[Aleksei Arefjev <aleksei.arefjev@xxxxxxxxxxxxxxxx>]

On 24 July 2012 20:21, Richard Huxton <dev@xxxxxxxxxxxx> wrote:

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?

0.00s - this is the average duration parameter column. Them, seems, much more, and those were shown like examples.

 

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.

Perhaps so, but, at execution time, there were not any problem with performance on those machines.
 

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

Yes, I know it.
 

2. I think most of the work in getting a new snapshot etc gets pushed back until it's needed.

Probably so, but I wanna know, is there any opportunity to optimize this process.
 

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

--
  Richard Huxton
  Archonet Ltd

And yet, repeating the question: What happens in PostgreSQL on transaction starting time? Can someone

describe this process in detail?

Regards

Aleksei