From: Richard Huxton <dev@archonet.com>
To: Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: transactions start time
Date: Tue, 24 Jul 2012 18:21:38 +0100
Message-ID: <500ED9A2.3040101@archonet.com> (raw)
In-Reply-To: <CAFTgB8iMS8LYLbdCTmv=RBzDHhFaCvEBG9CAD3EyDxzuBvyTMA@mail.gmail.com>
References: <CAFTgB8iMS8LYLbdCTmv=RBzDHhFaCvEBG9CAD3EyDxzuBvyTMA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, aleksei.arefjev@nordicgaming.com
Subject: Re: transactions start time
In-Reply-To: <500ED9A2.3040101@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox