Re: One long transaction or multiple short transactions? - Mailing list pgsql-performance

From Graeme B. Bell
Subject Re: One long transaction or multiple short transactions?
Date
Msg-id F545968D-7C4A-41CE-B54D-203F1D4D9FEB@skogoglandskap.no
Whole thread Raw
In response to Re: One long transaction or multiple short transactions?  ("Carlo" <reg01@stonebanks.ca>)
Responses Re: One long transaction or multiple short transactions?  ("Carlo" <reg01@stonebanks.ca>)
List pgsql-performance
Sounds like a locking problem, but assuming you aren’t sherlock holmes and simply want to get the thing working as soon
aspossible:  

Stick a fast SSD in there (whether you stay on VM or physical). If you have enough I/O, you may be able to solve the
problemwith brute force. 
SSDs are a lot cheaper than your time.

Suggest you forward this to your operators: a talk I have about optimising multi-threaded work in postgres:

  http://graemebell.net/foss4gcomo.pdf     (Slides: “Input/Output” in the middle of the talk and also the slides at the
endlabelled “For Techies") 

Graeme Bell

p.s. You mentioned a VM. Consider making the machine physical and not VM. You’ll get a performance boost and remove the
riskof DB corruption from untrustworthy VM fsyncs. One day there will be a power cut or O/S crash during these your
writesand with a VM you’ve a reasonable chance of nuking your DB because VM virtualised storage often doesn’t honour
fsync(for performance reasons), but it’s fundamental to correct operation of PG.  



> On 08 Oct 2015, at 01:40, Carlo <reg01@stonebanks.ca> wrote:
>
>
> I am told 32 cores on a LINUX VM. The operators have tried limiting the number of threads. They feel that the number
ofconnections is optimal. However, under the same conditions they noticed a sizable boost in performance if the same
importwas split into two successive imports which had shorter transactions. 
>



pgsql-performance by date:

Previous
From: "Carlo"
Date:
Subject: Re: One long transaction or multiple short transactions?
Next
From: Bram Van Steenlandt
Date:
Subject: large object write performance