>> Sounds like a locking problem This is what I am trying to get at. The reason that I am not addressing hardware or OS configuration concerns is that this is not my environment, but my client's. The client is running my import software and has a choice of how long the transactions can be. They are going for long transactions, and I am trying to determine whether there is a penalty for single long transactions over a configuration which would allow for more successive short transactions. (keep in mind all reads and writes are single-row). There are other people working on hardware and OS configuration, and that's why I can't want to get into a general optimization discussion because the client is concerned with just this question. -----Original Message----- From: Graeme B. Bell [mailto:graeme.bell@xxxxxxxx] Sent: October 8, 2015 4:55 AM To: Carlo Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: One long transaction or multiple short transactions? Sounds like a locking problem, but assuming you aren't sherlock holmes and simply want to get the thing working as soon as possible: 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 problem with 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 end labelled "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 risk of DB corruption from untrustworthy VM fsyncs. One day there will be a power cut or O/S crash during these your writes and 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@xxxxxxxxxxxxx> wrote: > > > I am told 32 cores on a LINUX VM. The operators have tried limiting the number of threads. They feel that the number of connections is optimal. However, under the same conditions they noticed a sizable boost in performance if the same import was split into two successive imports which had shorter transactions. > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
