Re: [GENERAL] idle in transaction, why

On 11/07/2017 09:09 AM, Scott Marlowe wrote:
> On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent <robjsargent@gmail.com> wrote:
>>
>>> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer <spam_eater@gmx.net> 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
issueI 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