Postgres Pro
Downloads
Home   >   mailing lists

often PREPARE can generate high load (and sometimes minutes long unavailability) - Mailing list pgsql-hackers

From Pavel Stehule
Subject often PREPARE can generate high load (and sometimes minutes long unavailability)
Date
Msg-id CAFj8pRDCingX=b42+FoMM+pk7JL63zUXc3d48OMpaqHxrhSpeA@mail.gmail.com
Whole thread Raw
Responses Re: often PREPARE can generate high load (and sometimes minutes long unavailability)  (Jeff Janes <jeff.janes@gmail.com>)
Re: often PREPARE can generate high load (and sometimes minutes long unavailability)  (Andres Freund <andres@2ndquadrant.com>)
Re: often PREPARE can generate high load (and sometimes minutes long unavailability)  (Andres Freund <andres@2ndquadrant.com>)
List pgsql-hackers
Tree view
Hello

I got a example of code, that generate relatively high load with minimal connections.

This code is +/- bad - it repeatedly generate prepare statement, but somewhere uses prepared statements as protections against SQL injections and they can use same use case.

Pseudocode (I can send a test case privately):

Script a:

 -- A,B are in RAM
  for i in 1 .. N loop
    insert into A values();
    for j in 1 .. M loop
      insert into B values();
    end loop;
  end loop;

Script b:

-- query is extremely fast - returns 0 or 1 rows usually
40 threads execute
while true loop
  pr = PREPARE SELECT * FROM A LEFT JOIN B ON ..
  EXECUTE pr(...)
  sleep(10 ms)
end loop
 
running both script together can produce high load with minimal number of executed queries.

           354246.00 93.0% s_lock                           /usr/lib/postgresql/9.2/bin/postgres    
            10503.00  2.8% LWLockRelease                    /usr/lib/postgresql/9.2/bin/postgres    
             8802.00  2.3% LWLockAcquire                    /usr/lib/postgresql/9.2/bin/postgres    
              828.00  0.2% _raw_spin_lock                   [kernel.kallsyms]                       
              559.00  0.1% _raw_spin_lock_irqsave           [kernel.kallsyms]                       
              340.00  0.1% switch_mm                        [kernel.kallsyms]                       
              305.00  0.1% poll_schedule_timeout            [kernel.kallsyms]                       
              274.00  0.1% native_write_msr_safe            [kernel.kallsyms]                       
              257.00  0.1% _raw_spin_lock_irq               [kernel.kallsyms]                       
              238.00  0.1% apic_timer_interrupt             [kernel.kallsyms]                       
              236.00  0.1% __schedule                       [kernel.kallsyms]                       
              213.00  0.1% HeapTupleSatisfiesMVCC

With systemtap I got list of spin locks

light weight locks
            lockname       mode      count        avg (time)
        DynamicLocks  Exclusive       2804       1025
        DynamicLocks     Shared        106        130
       ProcArrayLock  Exclusive         63     963551
       ProcArrayLock     Shared         50       4160
        LockMgrLocks  Exclusive         18        159
     IndividualLock   Exclusive          2          7

There is relative few very long ProcArrayLocks lwlocks

This issue is very pathologic on fast computers with more than 8 CPU. This issue was detected after migration from 8.4 to 9.2. (but tested with same result on 9.0)  I see it on devel 9.4 today actualized.

When I moved PREPARE from cycle, then described issues is gone. But when I use a EXECUTE IMMEDIATELY, then the issue is back. So it looks it is related to planner, ...

Regards

Pavel


pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Should PostgresMain() do a LWLockReleaseAll()?
Next
From: Jeff Janes
Date:
Subject: Re: often PREPARE can generate high load (and sometimes minutes long unavailability)