dbs_partn_ mutex contention

Hi, IDS 11.70.FC5XE, AIX 7.1 Sometimes we encounter a situation when many sessions are waiting for mutex. onstat -g wmx says this is dbs_partn_ mutexes, please see below.What does a number in a mutex name mean? What particular resource is the mutex responsible for? The only workaround we found to get rid of the contention is restarting the IDS. Just after restart 'Number of mutexes on VP free lists' is below 1000, and when the situation occurs it raises over 1m or even 2m. My hypothesis is that the list is too long, that takes much more time to operate with mutexes. Does a way exist to shorten the 'VP free lists' without restart? At whole, how to avoid the dbs_partn mutex contention? IBM Informix Dynamic Server Version 11.70.FC5XE -- On-Line -- Up 01:12:31 -- 282766176 KbytesMutexes with waiters: mid addr name holder lkcnt waiter waittime 10989 700001f1ad4d890 dbs_partn_32 319924 0 380956 0 11185 700001f1ae187e8 dbs_partn_37 342233 0 335167 0 410142 0 405147 0 357788 0 405393 0 335735 0 363246 0 405751 0 342264 0 409760 0 405617 0 409124 0 358552 0 409641 0 405607 0 358019 0 336502 0 358512 0 405546 0 394992 0 408420 0 358021 0 349425 0 349500 0 362926 0 370976 0 409452 0 371145 0 368501 0 341408 0 358500 0 395491 0 405738 0 363082 0 363674 0 379522 0 395399 0 362927 0 409306 0 350071 0 409081 0 336639 0 357783 0 336758 0 341663 0 409947 0 338104 0 338226 0 409353 0 339217 0 358502 0 363696 0 335241 0 408611 0 335183 0 335415 0 405745 0 335663 0 335714 0 349771 0 338383 0 409696 0 365838 0 338409 0 349773 0 405358 0 363164 0 Number of mutexes on VP free lists: 1497948 Read/Write Locks addr id name slocks xlock waiters ------------------------------ Sincerely, Dennis ------------------------------

dbs_partn is mutex on the partition number of the database tblspace. It is the same that partition number of the tables (tblspaces).

That number in dbs_partn_<#> is a dbspace number. Any change dbspaces #32 + #37 are (used as) temp dbspaces? Just a guess: might high amount of temp tables being created and discarded by many sessions in parallel? ------------------------------ Andreas Legner ------------------------------

Yes, they are temp dbspaces. Your guess is highly likely true, many sessions are creating and dropping temp tables. BTW, 'onmode -F' proved itself to be a fairly effective way to cut Number of mutexes on VP free lists and to eliminate the mutex contention. ------------------------------ Sincerely, Dennis ------------------------------

I'd doubt a relation between VP mutex free list size (a per-VP cache of currently unused mutex structures) and contention on certain (used) mutexes, so, while it's true that onmode -F returns certain unused memory, in an attempt to then freeing entire shmem segments, any observed effect on mutex contention might be either some more indirect effect or coincidence only. I'd try adding more temp dbspaces, hoping to better spread the temp table load and thus reducing the contention on each of them. HTH, Andreas ------------------------------ Andreas Legner ------------------------------

Hello everyone. About this issue, how was it finally resolved? We have the same case in 12.10 FC4. We followed the recommendation of multiple temporary dbspaces and the problem continues. The only solution is to restart the engine but it is a production system. Thanks for your help in advance ------------------------------ Mario Barbosa ------------------------------

We are still facing the problem of the dbs_partn_ mutex contention. We have 16 temp dbspaces, tmpapp01..16 What I noticed is that a session holding a mutex has DBSPACETEMP set to 'tmpapp01,tmpapp02,tmpapp03,tmpapp04,tmpapp05,tmpapp06,tmpapp07,tmpapp08'. And waiters has the same. And they have pretty long lists of temp tables per session, please see below. As one can see, some tables are partitioned among temp dbspaces. Is my guess valid that when the session creates such a table it tends to hold all assigned tempspaces' mutexes, and if other sessions are doing the same, they clash in a sort of deadlock? And the more tmpspaces has DBSPACETEMP the much likely the clash occures. So, to reduce the contention we have to assign one tempspace per session, not 8 -- provided we're not using PDQ. User-created Temp tables : partnum tabname rowsize 2800ec5 tmp_agr_rok 17 2101596 tmp_agr_rok 17 2201206 tmp_agr_rok 17 2301325 tmp_agr_rok 17 2400b52 tmp_agr_rok 17 250017b tmp_agr_rok 17 260031d tmp_agr_rok 17 2701714 tmp_agr_rok 17 26002b1 time_deliv_tmp 30 2600df9 tmp_sap_doc_num 10 25001fb tmp_calendar_lim 8 2400ff2 tmp_calendar 24 23006dd tmp_id_list 4 2300df4 tmp_t_l_a 42 2300df3 tmp_t_l_l 120 24014ce tmp_t_l_l 120 250054c tmp_t_l_l 120 2600584 tmp_t_l_l 120 27013c0 tmp_t_l_l 120 2801067 tmp_t_l_l 120 2100c4a tmp_t_l_l 120 22013c7 tmp_t_l_l 120 2500cd9 tmp_detail 32 2100466 tmp_inv_calc_val_ext 1606 2200b86 tmp_inv_calc_val_ext 1606 2300c01 tmp_inv_calc_val_ext 1606 2400124 tmp_inv_calc_val_ext 1606 2500cce tmp_inv_calc_val_ext 1606 2600d8d tmp_inv_calc_val_ext 1606 2700c61 tmp_inv_calc_val_ext 1606 28000e3 tmp_inv_calc_val_ext 1606 28000fd tmp_sert_id 4 2700c7e tmp_metro_print 4 2600d8e tmp_metro_l 12 2500ccb tmp_rate 82 2500cc5 tmp_inv_calc_val 286 2600d88 tmp_inv_calc_val 286 2700c6d tmp_inv_calc_val 286 28000e9 tmp_inv_calc_val 286 2100469 tmp_inv_calc_val 286 2200b89 tmp_inv_calc_val 286 2300c08 tmp_inv_calc_val 286 240011e tmp_inv_calc_val 286 2400115 temp_report 4112 2300bc4 alloc_errors 92 2200b06 work_alloc 212 2100460 tmp_list_kd_trans_2 17 2400048 temp_ship_via 14 28012b3 tmp_time_deliv 14 25004c1 t_err_l_id 8 2400f3a t_result 27 230049c t_date 4 22003cf t_cal 18 21015ad time_deliv_tm 35 2801265 t_shedule 33 27005e6 tmp_deliv_info 141 26005ad tmp_deliv_msg 270 26005a9 tmp_reestr_ord_l_k1 117 27005e3 tmp_reestr_ord_l_k1 117 2801274 tmp_reestr_ord_l_k1 117 21015bb tmp_reestr_ord_l_k1 117 22003ca tmp_reestr_ord_l_k1 117 2300499 tmp_reestr_ord_l_k1 117 2400f33 tmp_reestr_ord_l_k1 117 25004b4 tmp_reestr_ord_l_k1 117 26005a5 tmp_reestr_ord_addr_k1 1107 27005e1 tmp_reestr_ord_addr_k1 1107 2801273 tmp_reestr_ord_addr_k1 1107 21015b8 tmp_reestr_ord_addr_k1 1107 22003c8 tmp_reestr_ord_addr_k1 1107 2300497 tmp_reestr_ord_addr_k1 1107 2400f32 tmp_reestr_ord_addr_k1 1107 25004b2 tmp_reestr_ord_addr_k1 1107 26005a3 tmp_reestr_ord_k1 444 27005e0 tmp_reestr_ord_k1 444 2801271 tmp_reestr_ord_k1 444 21015ba tmp_reestr_ord_k1 444 22003c7 tmp_reestr_ord_k1 444 2300495 tmp_reestr_ord_k1 444 2400f30 tmp_reestr_ord_k1 444 25004b0 tmp_reestr_ord_k1 444 260059f tmp_cg_l 253 27005dd tmp_cg_l 253 280126c tmp_cg_l 253 21015b5 tmp_cg_l 253 22003c1 tmp_cg_l 253 2300491 tmp_cg_l 253 2400f27 tmp_cg_l 253 25004aa tmp_cg_l 253 260059e tmp_cg 145 27005d7 tmp_cg 145 280126d tmp_cg 145 21015b4 tmp_cg 145 22003c0 tmp_cg 145 2300490 tmp_cg 145 2400f1d tmp_cg 145 25004a7 tmp_cg 145 25004a4 tmp_mark 36 2400f1c t_whse_load 18 2300487 temp_deliv_info 360 22003b5 tmp_firm_whse_list 20 21015ab temp_assembly 32 2801264 tmp_deliv 4 27005c2 temp_lines_data_2 556 260059b temp_lines_data 421 230048c tmp_bar_codes 46 ------------------------------ Sincerely, Dennis ------------------------------

Mario, Has `onmode -F` a curing effect in your case? ------------------------------ Sincerely, Dennis ------------------------------