Re: Planner performance extremely affected by an hanging transaction (20-30 times)?

[didier <did447@xxxxxxxxx>]

Hi

On Wed, Sep 25, 2013 at 1:30 AM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:

On Tue, Sep 24, 2013 at 11:03 AM, didier <did447@xxxxxxxxx> wrote:

As a matter of fact you get the same slow down after a rollback until autovacuum, and if autovacuum can't keep up...

Have you experimentally verified the last part?  btree indices have some special kill-tuple code which should remove aborted tuples from the index the first time they are encountered, without need for a vacuum.

Cheers,

Jeff

 

 

 

Yes my bad, it works but there's leftover  junk and a vacuum is still needed

Running above test with autovacuum off, 1 client  and insert 50 000 on postgresql 9.4 qit version.

Before insert 2 000 queries/s

after insert 80/s

after rollback 800/s (back to 2 000/s if commit)

after vacuum 2 000 /s again and vacuum output:

INFO:  vacuuming "public.categories"                                                                                                                
INFO:  scanned index "categories_pkey" to remove 50000 row versions                                                                                 
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.                                                                                                      
INFO:  "categories": removed 50000 row versions in 319 pages                                                                                        
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.                                                                                                      
INFO:  index "categories_pkey" now contains 1000 row versions in 278 pages                                                                          
DETAIL:  50000 index row versions were removed.                                                                                                     
272 index pages have been deleted, 136 are currently reusable.                                                                                      
CPU 0.00s/0.00u sec elapsed 0.00 sec.                                                                                                               
INFO:  "categories": found 50000 removable, 1000 nonremovable row versions in 325 out of 325 pages                                                  
DETAIL:  0 dead row versions cannot be removed yet.                                                                                                 
There were 0 unused item pointers.                                                                                                                  
0 pages are entirely empty.                                                                                                                         
CPU 0.00s/0.01u sec elapsed 0.02 sec.                                                                                                               
INFO:  "categories": stopping truncate due to conflicting lock request                                                                              
INFO:  vacuuming "pg_toast.pg_toast_16783"                                                                                                          
INFO:  index "pg_toast_16783_index" now contains 0 row versions in 1 pages                                                                          
DETAIL:  0 index row versions were removed.                                                                                                         
0 index pages have been deleted, 0 are currently reusable.                                                                                          
CPU 0.00s/0.00u sec elapsed 0.00 sec.                                                                                                               
INFO:  "pg_toast_16783": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages                                                         
DETAIL:  0 dead row versions cannot be removed yet.                                                                                                 
There were 0 unused item pointers.                                                                                                                  
0 pages are entirely empty.                                                                                                                         
CPU 0.00s/0.00u sec elapsed 0.00 sec.                                                                                                               
INFO:  analyzing "public.categories"                                                                                                                
INFO:  "categories": scanned 325 of 325 pages, containing 1000 live rows and 0 dead rows; 1000 rows in sample, 1000 estimated total rows         
  
perf output after rollback but before vacuum
    93.36%         postgres  /var/lib/vz/root/165/usr/local/pgsql/bin/postgres    
                |         
                |--41.51%-- _bt_checkkeys
                |          |         
                |          |--93.03%-- _bt_readpage
                |          |          |         
                |          |          |--97.46%-- _bt_steppage
                |          |          |          _bt_first
                |          |          |          btgettuple
                |          |          |          FunctionCall2Coll
                |          |          |          index_getnext_tid


Didier