Обсуждение: 9.3 regression with dbt2

Hi,

We recently observed ~15% performance regression with dbt2 from PG 9.3.

We narrowed down on testing master between 9.2 cut and 9.3 cut.

It seems that 0ac5ad5134f2769ccbaefec73844f8504c4d6182 is the culprit commit.

We did several runs and perf profiling comparing it against its parent (f925c79b9f36c54b67053ade5ad225a75b8dc803).

Also tested a 12/16 commit on the master (3b97e6823b949624afdc3ce4c92b29a80429715f) once, it performed similarly as 0ac..

Regards,

Dong

Results:

f92: 53k-56k'ish notpm

0ac: 47k-48k'ish notpm

Server SUT:

HP ML350 G6

Two  Xeon E5520 (4c/p, 8 cores total, hyper-threading disabled)

12GB DRAM

HP P410i RAID controller (256MB battery-backed cache)

- three 10k-rpm SAS: /

- three 10k-rpm SAS: /pgdata

- one 15k-rpm SAS: /pgxlog

- ext4 (rw,relatime,data=ordered) on all mounts.

Fedora 19 (3.11.10-200.fc19.x86_64)

max_connections=100

shared_buffers=8192MB

effective_cache_size=10GB

temp_buffers=8186kB

work_mem=4093kB

maintenance_work_mem=399MB

wal_buffers=-1

checkpoint_segments=300

checkpoint_completion_target=0.9

logging_collector=on

log_timezone=UTC

datestyle='iso, mdy'

lc_messages=C

lc_monetary=C

lc_numeric=C

lc_time=C

default_text_search_config='pg_catalog.english'

listen_addresses='*'

log_destination=csvlog

log_directory=pg_log

log_filename='pg-%a'

log_rotation_age=1440

log_truncate_on_rotation=on

Client and workload:

Dell 390. Two core. Direct connect with the Server SUT.

dbt2 (ToT)

40 warehouse

8 terminals, 8 connections

zero think/key time

12-min run

Flat perf profiles of two such runs look like:

f92:

Samples: 608K of event 'cycles', Event count (approx.): 6679607097416                                                

+   4.04%       postgres  postgres                  [.] heap_hot_search_buffer                                       

+   3.63%       postgres  postgres                  [.] AllocSetAlloc                                                

+   3.37%       postgres  postgres                  [.] hash_search_with_hash_value                                  

+   2.85%       postgres  postgres                  [.] _bt_compare                                                  

+   2.67%       postgres  postgres                  [.] SearchCatCache                                               

+   2.46%       postgres  postgres                  [.] LWLockAcquire                                                

+   2.16%       postgres  postgres                  [.] XLogInsert                                                   

+   2.08%       postgres  postgres                  [.] PinBuffer                                                    

+   1.32%       postgres  postgres                  [.] ExecInitExpr                                                 

+   1.31%       postgres  libc-2.17.so              [.] _int_malloc                                                   

+   1.29%        swapper  [kernel.kallsyms]         [k] intel_idle                                                   

+   1.23%       postgres  postgres                  [.] MemoryContextAllocZeroAligned                                 

+   1.13%       postgres  postgres                  [.] heap_page_prune_opt                                          

+   1.06%       postgres  libc-2.17.so              [.] __memcpy_ssse3_back                                           

+   1.02%       postgres  postgres                  [.] LWLockRelease                                                

+   0.94%       postgres  postgres                  [.] copyObject                                                   

+   0.89%       postgres  postgres                  [.] fmgr_info_cxt_security                                       

+   0.82%       postgres  postgres                  [.] _bt_checkkeys                                                

+   0.81%       postgres  postgres                  [.] hash_any                                                     

+   0.73%       postgres  postgres                  [.] FunctionCall2Coll                                            

+   0.69%       postgres  libc-2.17.so              [.] __strncpy_sse2_unaligned                                     

+   0.67%       postgres  postgres                  [.] HeapTupleSatisfiesMVCC                                       

+   0.66%       postgres  postgres                  [.] MemoryContextAlloc                                           

+   0.65%       postgres  postgres                  [.] expression_tree_walker                                       

+   0.59%       postgres  postgres                  [.] check_stack_depth                                             

+   0.57%       postgres  libc-2.17.so              [.] __printf_fp                                                  

+   0.56%       postgres  libc-2.17.so              [.] _int_free                                                     

+   0.52%       postgres  postgres                  [.] base_yyparse

0ac:

Samples: 706K of event 'cycles', Event count (approx.): 6690377376522                                                

+   3.82%         postgres  postgres                  [.] GetMultiXactIdMembers                                      

+   3.43%         postgres  postgres                  [.] LWLockAcquire                                              

+   3.31%         postgres  postgres                  [.] hash_search_with_hash_value                                

+   3.09%         postgres  postgres                  [.] heap_hot_search_buffer                                     

+   3.00%         postgres  postgres                  [.] AllocSetAlloc                                              

+   2.56%         postgres  postgres                  [.] _bt_compare                                                

+   2.19%         postgres  postgres                  [.] PinBuffer                                                  

+   2.13%         postgres  postgres                  [.] SearchCatCache                                             

+   1.99%         postgres  postgres                  [.] XLogInsert                                                  

+   1.53%         postgres  postgres                  [.] LWLockRelease                                              

+   1.32%         postgres  postgres                  [.] HeapTupleSatisfiesMVCC                                      

+   1.31%          swapper  [kernel.kallsyms]         [k] intel_idle                                                 

+   1.25%         postgres  postgres                  [.] ExecInitExpr                                                

+   1.21%         postgres  postgres                  [.] heap_page_prune_opt

+   1.03%         postgres  libc-2.17.so              [.] _int_malloc

+   0.99%         postgres  postgres                  [.] MemoryContextAllocZeroAligned

+   0.97%         postgres  libc-2.17.so              [.] __memcpy_ssse3_back

+   0.89%         postgres  postgres                  [.] heapgetpage

+   0.76%         postgres  postgres                  [.] hash_any

+   0.69%         postgres  postgres                  [.] fmgr_info_cxt_security

+   0.69%         postgres  postgres                  [.] _bt_checkkeys

+   0.65%         postgres  postgres                  [.] copyObject

+   0.63%         postgres  postgres                  [.] FunctionCall2Coll

+   0.58%         postgres  postgres                  [.] MemoryContextAlloc

+   0.56%         postgres  libc-2.17.so              [.] __strncpy_sse2_unaligned

+   0.53%         postgres  postgres                  [.] XidInMVCCSnapshot

+   0.52%         postgres  [kernel.kallsyms]         [k] copy_user_generic_string

+   0.51%         postgres  libc-2.17.so              [.] __printf_fp

+   0.50%         postgres  libc-2.17.so              [.] vfprintf

P.S.

The regression was found when testing vPostgres performance.

During the narrow-down, we found the commit 005f583ba4e6d4d19b62959ef8e70a3da4d188a5 (on REL9_2_STABLE) improves performance (versus its parent commit) by 5-10%. Perhaps serve an extra data point for the patch.