Re: [GENERAL] idle in transaction, why
Re: [GENERAL] idle in transaction, why
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"David G. Johnston" writes: > You should probably login as your application user and do "show > idle_in_transaction_session_timeout" to see what a clean session has for a > value and then figure out from there where that value is coming from. You don't have to guess about the latter: the pg_settings view will tell you exactly where the active value came from. See the source, sourcefile, sourceline columns. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Rob Sargent writes: > idle_in_transaction_session_timeout | 0 | default | > | | A value of 0 turns off the timeout. | user Meh. I think we're barking up the wrong tree anyway: so far as I can find, there is no error message reading 'idle transaction timeout' in the existing PG sources (and I sure hope no committer would have thought that such an ambiguous message text was satisfactory). So I think your error is coming from client-side or third-party code. What other moving parts have you got in there? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Rob Sargent <robjsargent@gmail.com>
Дата:
> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: > > Rob Sargent schrieb am 06.11.2017 um 23:09: >> Gosh I wish I could learn to proof-read my posts. >> My support crew graciously set >> >> idle_transaction_timeout = 1 >> >> Now to ponder if I need zero or some large number. > > The unit of that setting is milliseconds (if no unit is specified). > zero disables that feature. > > One millisecond seems like an awfully short period to allow a transaction to be idle. > > I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. > > A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. > > > Thomas > > > I started with the default zero and the save went through perfectly. It takes ten minutes so I’ll have a concurrency issue I imagine. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Rob Sargent <robjsargent@gmail.com>
Дата:
On 11/07/2017 09:09 AM, Scott Marlowe wrote: > On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent wrote: >> >>> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: >>> >>> I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. >>> >>> A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. >>> >> I started with the default zero and the save went through perfectly. It takes ten minutes so I’ll have a concurrency issue I imagine. > 10 minutes is long-ish but if it's not run all the time etc it may be > what you're stuck with. Idle in transaction doesn't necessarily mean > concurrency issues, as long as you're not operating on a whole table > other people need to update as well. I guess now's a good time to > profile what your code is doing, what's take the longest, and see if > maybe you can split that big transaction up into bite sized pieces. Short story: The save operation in question is insert only. Done in a single transaction (currently). The last run generated 1.7M new 'segments', each of those grouped into one of 131K segmentsets (so another 1.7M members), those 131K sets recorded as 'outputs'. In production we'll start 22 jobs, one per chromosome (no XY) for each pedigree. We spread those across machines, starting roughly at the same time. Analysis time is measured in days and is pretty much linear with chromosome size (and inversely to processor count) but pretty sure at some point two of those send their payload within minutes of each other. You know they will. Do two sets of writes to same table interfere with each other horribly? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Rob Sargent <robjsargent@gmail.com>
Дата:
> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: > > Rob Sargent schrieb am 06.11.2017 um 23:09: >> Gosh I wish I could learn to proof-read my posts. >> My support crew graciously set >> >> idle_transaction_timeout = 1 >> >> Now to ponder if I need zero or some large number. > > The unit of that setting is milliseconds (if no unit is specified). > zero disables that feature. > > One millisecond seems like an awfully short period to allow a transaction to be idle. > > I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. > > A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. > > > Thomas > I see I didn’t clarify that the timeout was set in the pgbouncer configuration. (I had shown upstream that it was NOT set for the postgres server.) In pgbouncer, the unit is seconds. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Rob Sargent <robjsargent@gmail.com>
Дата:
On 11/06/2017 01:17 PM, Tom Lane wrote: > "David G. Johnston" writes: >> You should probably login as your application user and do "show >> idle_in_transaction_session_timeout" to see what a clean session has for a >> value and then figure out from there where that value is coming from. > You don't have to guess about the latter: the pg_settings view will tell > you exactly where the active value came from. See the source, sourcefile, > sourceline columns. > > regards, tom lane select name, setting, source, sourcefile, sourceline, extra_desc, context from pg_settings where name ~ 'idle'; name | setting | source | sourcefile | sourceline | extra_desc | context -------------------------------------+---------+---------+------------+------------+---------------------------------------+--------- idle_in_transaction_session_timeout | 0 | default | | | A value of 0 turns off the timeout. | user tcp_keepalives_idle | 7200 | default | | | A value of 0 uses the system default. | user (2 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Rob Sargent <robjsargent@gmail.com>
Дата:
On 11/06/2017 01:41 PM, Tom Lane wrote: > Rob Sargent writes: >> idle_in_transaction_session_timeout | 0 | default | >> | | A value of 0 turns off the timeout. | user > Meh. I think we're barking up the wrong tree anyway: so far as I can > find, there is no error message reading 'idle transaction timeout' > in the existing PG sources (and I sure hope no committer would have > thought that such an ambiguous message text was satisfactory). > So I think your error is coming from client-side or third-party code. > What other moving parts have you got in there? > > regards, tom lane The most likely culprit is JOOQ, which I chose as a learning experience (normally I use ORM tools). But that said, I just ran the same data into my test env, (postgres 10.0 (real) on centos 6.9, ubuntu client) and all went swimmingly. It's a sizable payload (several batches of over 100K items, deserialized from json) and takes 5 minutes to save. I was hoping to blame the virt or the beta. Not a good time to start doubt JOOQ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Rob Sargent <robjsargent@gmail.com>
Дата:
On 11/06/2017 01:50 PM, Rob Sargent wrote: > > > On 11/06/2017 01:41 PM, Tom Lane wrote: >> Rob Sargent writes: >>> idle_in_transaction_session_timeout | 0 | default | >>> | | A value of 0 turns off the timeout. | user >> Meh. I think we're barking up the wrong tree anyway: so far as I can >> find, there is no error message reading 'idle transaction timeout' >> in the existing PG sources (and I sure hope no committer would have >> thought that such an ambiguous message text was satisfactory). >> So I think your error is coming from client-side or third-party code. >> What other moving parts have you got in there? >> >> regards, tom lane > The most likely culprit is JOOQ, which I chose as a learning > experience (normally I use ORM tools). But that said, I just ran the > same data into my test env, (postgres 10.0 (real) on centos 6.9, > ubuntu client) and all went swimmingly. It's a sizable payload > (several batches of over 100K items, deserialized from json) and takes > 5 minutes to save. > > I was hoping to blame the virt or the beta. Not a good time to start > doubt JOOQ My bet is that those 'org.postgres' messages came from the jdbc driver. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Merlin Moncure <mmoncure@gmail.com>
Дата:
On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent wrote: > > > On 11/06/2017 01:41 PM, Tom Lane wrote: >> >> Rob Sargent writes: >>> >>> idle_in_transaction_session_timeout | 0 | default | >>> | | A value of 0 turns off the timeout. | user >> >> Meh. I think we're barking up the wrong tree anyway: so far as I can >> find, there is no error message reading 'idle transaction timeout' >> in the existing PG sources (and I sure hope no committer would have >> thought that such an ambiguous message text was satisfactory). >> So I think your error is coming from client-side or third-party code. >> What other moving parts have you got in there? >> >> regards, tom lane > > The most likely culprit is JOOQ, which I chose as a learning experience > (normally I use ORM tools). But that said, I just ran the same data into my > test env, (postgres 10.0 (real) on centos 6.9, ubuntu client) and all went > swimmingly. It's a sizable payload (several batches of over 100K items, > deserialized from json) and takes 5 minutes to save. > > I was hoping to blame the virt or the beta. Not a good time to start doubt > JOOQ I can't speak to JOOQ (who are fantastic postgres supporters BTW), but in the java world this typically comes from one of two things: 1) you have long running in-transaction process that has very heavy computation between sql statements. this is a rare case --or-- 2) you are connecting pooling and the app sent a connection back into the pool without having a transaction committed. "2" is a common and dangerous bug. It can happen due to bug in application code (most likely), the jdbc wrapping library code (less likely), or the connection pooler itself if you're using one. A typical cause of application side problems is manual transaction management and some uncaught exception paths where errors (say, a duplicate key error). So investigate causes like that first (database errors in the database log might be a helpful clue) and go from there. If the problem is within JOOQ, you ought to take it up with them, which I encourage you to do, since I consider JOOQ to be a wonderful treatment of SQL integration from the java perspective. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
"David G. Johnston" <david.g.johnston@gmail.com>
Дата:
Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4
The postgresql.conf file has
#idle_in_transaction_session_
timeout = 0 # in milliseconds, 0 is disabled
There are numerous places where default settings can be configured.
You should probably login as your application user and do "show idle_in_transaction_session_timeout" to see what a clean session has for a value and then figure out from there where that value is coming from.
David J.
Re: [GENERAL] idle in transaction, why
От:
Scott Marlowe <scott.marlowe@gmail.com>
Дата:
On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent wrote: > > >> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: >> >> I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. >> >> A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. >> > I started with the default zero and the save went through perfectly. It takes ten minutes so I’ll have a concurrency issue I imagine. 10 minutes is long-ish but if it's not run all the time etc it may be what you're stuck with. Idle in transaction doesn't necessarily mean concurrency issues, as long as you're not operating on a whole table other people need to update as well. I guess now's a good time to profile what your code is doing, what's take the longest, and see if maybe you can split that big transaction up into bite sized pieces. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle in transaction, why
От:
Rob Sargent <robjsargent@gmail.com>
Дата:
On 11/06/2017 01:09 PM, David G. Johnston wrote:
From logging in with the application role I getUsing postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4
The postgresql.conf file has
#idle_in_transaction_session_
timeout = 0 # in milliseconds, 0 is disabled There are numerous places where default settings can be configured.You should probably login as your application user and do "show idle_in_transaction_session_timeout" to see what a clean session has for a value and then figure out from there where that value is coming from.David J.
coon=> show idle_in_transaction_session_timeout;
idle_in_transaction_session_timeout
-------------------------------------
0
(1 row)
Re: [GENERAL] idle in transaction, why
От:
Thomas Kellerer <spam_eater@gmx.net>
Дата:
Rob Sargent schrieb am 06.11.2017 um 23:09: > Gosh I wish I could learn to proof-read my posts. > My support crew graciously set > > idle_transaction_timeout = 1 > > Now to ponder if I need zero or some large number. The unit of that setting is milliseconds (if no unit is specified). zero disables that feature. One millisecond seems like an awfully short period to allow a transaction to be idle. I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
