Здравствуйте!

Есть 2 сервера на gentoo. На обоих стоит postgresql-9.4.5. Базы одинаковые переносил туда- сюда с помощью dump. Приложение стоит на DOC.

Сервер DOC
IBM 7915K5G Express x3650 M4, 2x Xeon E5-2650 2.0GHz M5110e(512MB flash)

3.10.25-gentoo-2 #7 SMP x86_64 Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz

Память 128GB

max_connections = 300
shared_buffers = 32GB
work_mem = 512MB
random_page_cost = 1.5
effective_cache_size = 96GB


Сервер DOC2
Сервер Proliant DL380R07 E5620 P410iwBBWC(512Mb/RAID5+0/5/1+

3.10.25-gentoo-2 #5 SMP x86_64 Intel(R) Xeon(R) CPU E5620 @ 2.40GHz

Память 56GB

max_connections = 300
shared_buffers = 10GB
work_mem = 300MB
random_page_cost = 2
effective_cache_size = 28GB

База около 16-20 GB

Есть запрос select count(*) as col_0_0_ from TBL_WORKITEM workitem0_ where (workitem0_.owner='employ18502200000j8u5sa52csdvtlg' or (workitem0_.owner is null) and (workitem0_.uuid in (select workitem1_.uuid from TBL_WORKITEM workitem1_ inner join TBL_WORKITEM_POTENTIAL_OWNERS potentialo2_ on workitem1_.uuid=potentialo2_.workitemUid inner join TBL_BO corebo3_ on potentialo2_.userId=corebo3_.UUID where corebo3_.UUID='corebofs000080000ipq5e48ed6ka4d0')) or 'employ18502200000kb9v6p5s350kikk' in (select inspectors4_.USER_UUID from TBL_WORKITEM_INSPECTORS inspectors4_ where workitem0_.uuid=inspectors4_.WORKITEM_UUID)) and workitem0_.state<>'COMPLETED' and ('employfs000080000ipq6mn70pum9pak' not in (select readers5_.readerUid from TBL_WORKITEM_READERS readers5_ where workitem0_.uuid=readers5_.workitemUid))

На обоих серверах запрос на незагруженном сервере выполняется ~ 3 сек.


Если поспамить из приложения запросами штук по 30, то на DOC они могут выполнятся до минуты, занимая все процессоры и заканчиваются в разнобой. На загруженной системе они вообще плодятся до 80-100 штук и повисают до нескольких минут, что сильно тормозит систему. Если снимать pg_cancel_backend особо долгие запросы штук по 20-30, то затор может рассосаться, но скоро снова плодятся. Параллельно они тормозят нормально выполняющиеся запросы, т.к. занимают ресурсы.

На DOC2 при спаме 30 штук они разбираются все ~ 17 сек и на заруженной системе не накапливаются.

Голову сломал, не знаю куда копать? На DOC было в БИОСе NUMA, я ее отключил. Не помогло. Или может включить и в ядре настроить? Еще читал про spinlock, вроде есть опция сборки без него, только в gentoo ее нет.

Что посоветуете?


"QUERY PLAN DOC
Aggregate (cost=4191455.08..4191455.09 rows=1 width=0) (actual time=2473.126..2473.126 rows=1 loops=1)
Buffers: shared hit=2294076 read=2734
-> Seq Scan on tbl_workitem workitem0_ (cost=4674.81..4190762.41 rows=277068 width=0) (actual time=0.269..2471.518 rows=10789 loops=1)
Filter: (((state)::text <> 'NEW'::text) AND (((owner)::text = 'employ18502200000j8u5sa52csdvtlg'::text) OR ((owner IS NULL) AND (hashed SubPlan 1)) OR (SubPlan 2)) AND (NOT (SubPlan 3)))
Rows Removed by Filter: 1082806
Buffers: shared hit=2294076 read=2734
SubPlan 1
-> Nested Loop (cost=1.40..4672.38 rows=969 width=33) (actual time=0.099..0.099 rows=0 loops=1)
Buffers: shared hit=8
-> Index Only Scan using tbl_bo_pkey on tbl_bo corebo3_ (cost=0.42..3.44 rows=1 width=33) (actual time=0.058..0.058 rows=1 loops=1)
Index Cond: (uuid = 'corebofs000080000ipq5e48ed6ka4d0'::text)
Heap Fetches: 1
Buffers: shared hit=4
-> Nested Loop (cost=0.98..4659.26 rows=969 width=66) (actual time=0.037..0.037 rows=0 loops=1)
Buffers: shared hit=4
-> Index Scan using idx_wi_po_userid on tbl_workitem_potential_owners potentialo2_ (cost=0.56..1400.44 rows=969 width=66) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: ((userid)::text = 'corebofs000080000ipq5e48ed6ka4d0'::text)
Buffers: shared hit=4
-> Index Only Scan using tbl_workitem_pkey on tbl_workitem workitem1_ (cost=0.43..3.35 rows=1 width=33) (never executed)
Index Cond: (uuid = (potentialo2_.workitemuid)::text)
Heap Fetches: 0
SubPlan 2
-> Index Scan using idx_wi_inspctrs_wiuuid1 on tbl_workitem_inspectors inspectors4_ (cost=0.28..3.29 rows=1 width=33) (actual time=0.001..0.001 rows=0 loops=1078208)
Index Cond: ((workitem0_.uuid)::text = (workitem_uuid)::text)
Buffers: shared hit=2161863
SubPlan 3
-> Index Scan using idx_wi_readers_wi_uuid on tbl_workitem_readers readers5_ (cost=0.43..3.45 rows=1 width=33) (actual time=0.018..0.018 rows=1 loops=10789)
Index Cond: ((workitem0_.uuid)::text = (workitemuid)::text)
Buffers: shared hit=41009 read=2734
Planning time: 3.594 ms
Execution time: 2473.291 ms


"QUERY PLAN DOC2
Aggregate (cost=5311456.46..5311456.47 rows=1 width=0) (actual time=3265.895..3265.895 rows=1 loops=1)
Buffers: shared hit=2297266
-> Seq Scan on tbl_workitem workitem0_ (cost=6247.48..5310762.44 rows=277607 width=0) (actual time=0.307..3263.657 rows=10789 loops=1)
Filter: (((state)::text <> 'NEW'::text) AND (((owner)::text = 'employ18502200000j8u5sa52csdvtlg'::text) OR ((owner IS NULL) AND (hashed SubPlan 1)) OR (SubPlan 2)) AND (NOT (SubPlan 3)))
Rows Removed by Filter: 1082824
Buffers: shared hit=2297266
SubPlan 1
-> Nested Loop (cost=25.25..6244.95 rows=1013 width=33) (actual time=0.109..0.109 rows=0 loops=1)
Buffers: shared hit=8
-> Index Only Scan using tbl_bo_pkey on tbl_bo corebo3_ (cost=0.42..4.44 rows=1 width=33) (actual time=0.056..0.056 rows=1 loops=1)
Index Cond: (uuid = 'corebofs000080000ipq5e48ed6ka4d0'::text)
Heap Fetches: 1
Buffers: shared hit=4
-> Nested Loop (cost=24.83..6230.38 rows=1013 width=66) (actual time=0.048..0.048 rows=0 loops=1)
Buffers: shared hit=4
-> Bitmap Heap Scan on tbl_workitem_potential_owners potentialo2_ (cost=24.41..1844.93 rows=1013 width=66) (actual time=0.048..0.048 rows=0 loops=1)
Recheck Cond: ((userid)::text = 'corebofs000080000ipq5e48ed6ka4d0'::text)
Buffers: shared hit=4
-> Bitmap Index Scan on idx_wi_po_userid1 (cost=0.00..24.15 rows=1013 width=0) (actual time=0.047..0.047 rows=0 loops=1)
Index Cond: ((userid)::text = 'corebofs000080000ipq5e48ed6ka4d0'::text)
Buffers: shared hit=4
-> Index Only Scan using tbl_workitem_pkey on tbl_workitem workitem1_ (cost=0.43..4.32 rows=1 width=33) (never executed)
Index Cond: (uuid = (potentialo2_.workitemuid)::text)
Heap Fetches: 0
SubPlan 2
-> Index Scan using idx_wi_inspctrs_wiuuid1 on tbl_workitem_inspectors inspectors4_ (cost=0.28..4.29 rows=1 width=33) (actual time=0.001..0.001 rows=0 loops=1078224)
Index Cond: ((workitem0_.uuid)::text = (workitem_uuid)::text)
Buffers: shared hit=2161895
SubPlan 3
-> Index Scan using idx_wi_readers_wi_uuid on tbl_workitem_readers readers5_ (cost=0.43..4.45 rows=1 width=33) (actual time=0.022..0.023 rows=1 loops=10789)
Index Cond: ((workitem0_.uuid)::text = (workitemuid)::text)
Buffers: shared hit=43743
Planning time: 5.245 ms
Execution time: 3266.127 ms

Для сервера DOC видимо неудачный непрогретый запрос был.
Вот без ferch с диска

"QUERY PLAN
Aggregate (cost=4191455.08..4191455.09 rows=1 width=0) (actual time=2466.755..2466.755 rows=1 loops=1)
Buffers: shared hit=2296810
-> Seq Scan on tbl_workitem workitem0_ (cost=4674.81..4190762.41 rows=277068 width=0) (actual time=0.241..2465.259 rows=10789 loops=1)
Filter: (((state)::text <> 'NEW'::text) AND (((owner)::text = 'employ18502200000j8u5sa52csdvtlg'::text) OR ((owner IS NULL) AND (hashed SubPlan 1)) OR (SubPlan 2)) AND (NOT (SubPlan 3)))
Rows Removed by Filter: 1082806
Buffers: shared hit=2296810
SubPlan 1
-> Nested Loop (cost=1.40..4672.38 rows=969 width=33) (actual time=0.095..0.095 rows=0 loops=1)
Buffers: shared hit=8
-> Index Only Scan using tbl_bo_pkey on tbl_bo corebo3_ (cost=0.42..3.44 rows=1 width=33) (actual time=0.054..0.054 rows=1 loops=1)
Index Cond: (uuid = 'corebofs000080000ipq5e48ed6ka4d0'::text)
Heap Fetches: 1
Buffers: shared hit=4
-> Nested Loop (cost=0.98..4659.26 rows=969 width=66) (actual time=0.037..0.037 rows=0 loops=1)
Buffers: shared hit=4
-> Index Scan using idx_wi_po_userid on tbl_workitem_potential_owners potentialo2_ (cost=0.56..1400.44 rows=969 width=66) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: ((userid)::text = 'corebofs000080000ipq5e48ed6ka4d0'::text)
Buffers: shared hit=4
-> Index Only Scan using tbl_workitem_pkey on tbl_workitem workitem1_ (cost=0.43..3.35 rows=1 width=33) (never executed)
Index Cond: (uuid = (potentialo2_.workitemuid)::text)
Heap Fetches: 0
SubPlan 2
-> Index Scan using idx_wi_inspctrs_wiuuid1 on tbl_workitem_inspectors inspectors4_ (cost=0.28..3.29 rows=1 width=33) (actual time=0.001..0.001 rows=0 loops=1078208)
Index Cond: ((workitem0_.uuid)::text = (workitem_uuid)::text)
Buffers: shared hit=2161863
SubPlan 3
-> Index Scan using idx_wi_readers_wi_uuid on tbl_workitem_readers readers5_ (cost=0.43..3.45 rows=1 width=33) (actual time=0.017..0.017 rows=1 loops=10789)
Index Cond: ((workitem0_.uuid)::text = (workitemuid)::text)
Buffers: shared hit=43743
Planning time: 3.639 ms
Execution time: 2466.918 ms

select count(*) as col_0_0_ from TBL_WORKITEM workitem0_ where (workitem0_.owner='employ18502200000j8u5sa52csdvtlg' or (workitem0_.owner is null))
Результат 23673

Индексы TBL_WORKITEM
idx_wi_initiator1 initiator btree
idx_wi_marker1 workitemmarker btree
idx_wi_owner1 owner btree
idx_wi_pid1 pid btree
idx_wi_proc_initiator processinitiator btree
idx_wi_remote_uuid remoteuuid, remotesid btree
idx_workitem_back_secretary back_secretary_workitem btree
idx_workitem_hierarchyparent hierarchyparent btree
idx_workitem_mainowner mainowners_workitem btree
idx_workitem_parent parent btree
idx_workitem_recent_history recenthistoryentry btree
idx_workitem_secretary secretary_workitem btree
idx_workitem_state1 state btree
idx_workitem_state_owner1 state, owner btree
idx_workitem_type type btree
tbl_workitem_pkey uuid btree, primary, uniq