From: | Aleksei Arefjev <aleksei(dot)arefjev(at)nordicgaming(dot)com> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: transactions start time |
Date: | 2012-07-25 06:52:35 |
Message-ID: | CAFTgB8gkQTLL_+xo7vspiEXN28L18=hDxQ7y1dSLUsicv_8R8Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 24 July 2012 20:21, Richard Huxton <dev(at)archonet(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Torsten Zuehlsdorff | 2012-07-25 07:00:37 | Re: ZFS vs. UFS |
Previous Message | Claudio Freire | 2012-07-24 22:05:56 | Re: Linux memory zone reclaim |