pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtransactions start time
6+ messages / 4 participants
[nested] [flat]
* transactions start time
@ 2012-07-24 11:14 Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
0 siblings, 1 reply; 6+ messages in thread
From: Aleksei Arefjev @ 2012-07-24 11:14 UTC (permalink / raw)
To: pgsql-performance
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;
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) ?
2. How can we reduce transactions start time if it's possible in principle?
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)
Best Regards
Aleksei
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transactions start time
@ 2012-07-24 17:21 Richard Huxton <dev@archonet.com>
parent: Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
0 siblings, 2 replies; 6+ messages in thread
From: Richard Huxton @ 2012-07-24 17:21 UTC (permalink / raw)
To: Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transactions start time
@ 2012-07-25 06:52 Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 6+ messages in thread
From: Aleksei Arefjev @ 2012-07-25 06:52 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
On 24 July 2012 20:21, Richard Huxton <dev@archonet.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transactions start time
@ 2012-07-25 07:37 Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 6+ messages in thread
From: Aleksei Arefjev @ 2012-07-25 07:37 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
On 24 July 2012 20:21, Richard Huxton <dev@archonet.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?
>
> 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.
>
If so, maybe using of 'SET TRANSACTION SNAPSHOT' command with the
pre-existing transaction exported snapshot by the pg_export_snapshot
function could be usefull for reducing transactions start time -
http://www.postgresql.org/docs/9.2/static/sql-set-transaction.html
>
> So - the overall impact of issuing BEGIN should be close to zero.
>
> --
> Richard Huxton
> Archonet Ltd
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transactions start time
@ 2012-07-25 14:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2012-07-25 14:56 UTC (permalink / raw)
To: Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
Aleksei Arefjev <aleksei.arefjev@nordicgaming.com> writes:
> On 24 July 2012 20:21, Richard Huxton <dev@archonet.com> wrote:
>> 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?
I'm wondering exactly where/how the duration was measured. If it was at
a client, maybe the apparent delay had something to do with network
glitches? It seems suspicious that all the outliers are around 0.8s.
It would be useful to look to see if there's any comparable pattern
for statements other than BEGIN.
As Richard says, a BEGIN by itself ought to take negligible time.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transactions start time
@ 2012-07-25 17:01 Andres Freund <andres@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Andres Freund @ 2012-07-25 17:01 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>; Richard Huxton <dev@archonet.com>
Hi,
On Wednesday, July 25, 2012 04:56:20 PM Tom Lane wrote:
> Aleksei Arefjev <aleksei.arefjev@nordicgaming.com> writes:
> > On 24 July 2012 20:21, Richard Huxton <dev@archonet.com> wrote:
> >> 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?
>
> I'm wondering exactly where/how the duration was measured. If it was at
> a client, maybe the apparent delay had something to do with network
> glitches? It seems suspicious that all the outliers are around 0.8s.
> It would be useful to look to see if there's any comparable pattern
> for statements other than BEGIN.
>
> As Richard says, a BEGIN by itself ought to take negligible time.
He earlier also asked on the IRC-Channel and I got the idea that the problem
could be explained by pgbouncer in transaction pooling mode waiting for a free
backend connection. Aleksei confirmed that they use pgbouncer in that
configuration, so that might be it.
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-07-25 17:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-24 11:14 transactions start time Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
2012-07-24 17:21 ` Richard Huxton <dev@archonet.com>
2012-07-25 06:52 ` Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
2012-07-25 14:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-07-25 17:01 ` Andres Freund <andres@2ndquadrant.com>
2012-07-25 07:37 ` Aleksei Arefjev <aleksei.arefjev@nordicgaming.com>
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