Re: One long transaction or multiple short transactions?

Laurent Martelli <laurent.martelli@xxxxxxxxxxxx> · Fri, 09 Oct 2015 08:45:28 +0200



    Le 08/10/2015 01:40, Carlo a écrit :

    
        >> How
            many cores do you have on that machine?
        Test if
            limiting number of simultaneous feeds, like bringing their
            number down to half of your normal connections has the same
            positive effect.
        << 
         
        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.
         
        I am just
            looking to see if there is any reason to think that lock
            contention (or anything else) over longer vs. shorter
            single-row-write transactions under the same conditions
            might explain this.
      
    
    I don't think inserts can cause contention on the server. Insert do
    not lock tables during the transaction. You may have contention on
    sequence but it won't vary with transaction size.

    
    Have you checked the resource usage (CPU,memory) on the client side
    ?

    
    How do you insert rows ? Do you use plain postgres API ?

    
    Regards,

    Laurent