many threads for process ?

Posted in 2005

Floyd Wellershaus — Fri, 04 Feb 2005 12:53:58 — source: IIUG Forums & Mailing Lists

We are running a batch process, and when monitoring it, I see a huge amount of threads associated with it, and most of them are waiting. One of the tables in the query is fragmented, but it only has 10 fragments, so I wouldn't expect to see more than 10 scan threads. Any ideas ? below is an onstat -g ses for the session ( 265)and an onstat -g wai Thanks. IBM Informix Dynamic Server Version 9.40.FC3 -- On-Line -- Up 01:27:12 --2291184 Kbytes session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 265 sentrycf - 602224 grace 136 9986048 9969864 off tid name rstcb flags curstk status 409 sqlexec 700000060404b60 ---P--- 3648 sleeping(secs: 3) 421 group_1. 7000000604063d8 Y------ 1392 cond wait(await_MC1) 422 scan_2.0 700000060406c00 Y------ 1392 cond wait(await_MC2) 423 scan_3.0 700000061f33028 Y------ 1392 cond wait(await_MC3) 427 scan_5.0 700000061f33850 Y------ 1392 cond wait(await_MC5) 428 scan_5.1 700000061f34078 Y------ 1392 cond wait(await_MC5) 429 scan_5.2 700000061f348a0 Y------ 1392 cond wait(await_MC5) 430 scan_5.3 700000061f350c8 Y------ 1392 cond wait(await_MC5) 431 scan_5.4 700000061f358f0 Y------ 1392 cond wait(await_MC5) 432 scan_5.5 700000061f36118 Y------ 1392 cond wait(await_MC5) 433 scan_5.6 700000061f36940 Y------ 1392 cond wait(await_MC5) 434 scan_5.7 700000061f37168 Y------ 1392 cond wait(await_MC5) 435 scan_5.8 700000061f37990 Y------ 1392 cond wait(await_MC5) 436 scan_5.9 700000061f381b8 Y------ 1392 cond wait(await_MC5) 444 scan_6.0 700000061f39208 Y------ 1392 cond wait(await_MC6) 445 psortpro 700000061f39a30 Y------ 1488 cond wait(backend:0) 446 psortpro 700000061f3a258 Y------ 1488 cond wait(backend:1) 447 scan_7.0 700000061f3aa80 Y------ 1392 cond wait(await_MC7) 448 join_8.0 700000061f3b2a8 Y------ 1392 cond wait(await_MC8) 449 join_8.1 700000061f3bad0 Y------ 1392 cond wait(await_MC8) 450 scan_9.0 700000061f3c2f8 Y------ 1392 cond wait(await_MC9) 451 scan_9.1 700000061f3cb20 Y------ 1392 cond wait(await_MC9) 452 scan_9.2 700000061f3d348 Y------ 1392 cond wait(await_MC9) 453 scan_9.3 700000061f3db70 Y------ 1392 cond wait(await_MC9) 454 scan_9.4 700000061f3e398 Y------ 1392 cond wait(await_MC9) 455 scan_9.5 700000061f3ebc0 Y------ 1392 cond wait(await_MC9) 456 scan_9.6 700000061f3f3e8 Y------ 1392 cond wait(await_MC9) 457 scan_9.7 700000061f3fc10 Y------ 1392 cond wait(await_MC9) 458 scan_9.8 700000061f40438 Y------ 1392 cond wait(await_MC9) 459 scan_9.9 700000061f40c60 Y------ 1392 cond wait(await_MC9) 460 join_10. 700000061f41488 ------- 2000 sleeping(secs: 3) 461 join_10. 700000061f41cb0 ------- 2000 sleeping(secs: 3) 462 scan_11. 700000061f424d8 B---R-- 6272 sleeping(Forever) 463 join_12. 700000061f42d00 Y------ 1392 cond wait(await_MC12) 464 join_12. 700000061f43528 Y------ 1392 cond wait(await_MC12) 465 join_13. 700000061f43d50 Y------ 1392 cond wait(await_MC13) 466 join_13. 700000061f44578 Y------ 1392 cond wait(await_MC13) 467 join_14. 700000061f44da0 Y------ 1392 cond wait(await_MC14) 468 join_14. 700000061f455c8 Y------ 1392 cond wait(await_MC14) 469 scan_15. 700000061f45df0 Y------ 1392 cond wait(await_MC15) 470 scan_16. 700000061f46618 Y------ 1392 cond wait(await_MC16) 471 scan_17. 700000061f46e40 Y------ 1392 cond wait(await_MC17) 472 scan_18. 700000061f47668 Y------ 1392 cond wait(await_MC18) 473 scan_19. 700000061f47e90 Y------ 1392 cond wait(await_MC19) 474 scan_20. 700000061f486b8 Y------ 1392 cond wait(await_MC20) 475 scan_21. 700000061f48ee0 Y------ 1392 cond wait(await_MC21) 476 scan_22. 700000061f49708 Y------ 1392 cond wait(await_MC22) 477 scan_23. 700000061f49f30 Y------ 1392 cond wait(await_MC23) 478 scan_24. 700000061f4a758 Y------ 1392 cond wait(await_MC24) 479 scan_25. 700000061f4af80 Y------ 1392 cond wait(await_MC25) 480 scan_26. 700000061f4b7a8 Y------ 1392 cond wait(await_MC26) 481 scan_27. 700000061f4bfd0 Y------ 1392 cond wait(await_MC27) 482 scan_28. 700000061f4c7f8 Y------ 1392 cond wait(await_MC28) 483 scan_29. 700000061f4d020 Y------ 1392 cond wait(await_MC29) 484 scan_30. 700000061f4d848 Y------ 1392 cond wait(await_MC30) 485 scan_31. 700000061f4e070 Y------ 1392 cond wait(await_MC31) 486 scan_32. 700000061f4e898 Y------ 1392 cond wait(await_MC32) 487 scan_33. 700000061f4f0c0 Y------ 1392 cond wait(await_MC33) 488 scan_34. 700000061f4f8e8 Y------ 1392 cond wait(await_MC34) 489 scan_35. 700000061f50110 Y------ 1392 cond wait(await_MC35) 490 scan_36. 700000061f50938 Y------ 1392 cond wait(await_MC36) 491 scan_37. 700000061f51160 Y------ 1392 cond wait(await_MC37) 492 scan_38. 700000061f51988 Y------ 1392 cond wait(await_MC38) 494 scan_39. 700000061f529d8 Y------ 1392 cond wait(await_MC39) 495 scan_40. 700000061f53200 Y------ 1392 cond wait(await_MC40) 496 scan_41. 700000061f53a28 Y------ 1392 cond wait(await_MC41) 497 scan_42. 700000061f54250 Y------ 1392 cond wait(await_MC42) 498 scan_43. 700000061f54a78 Y------ 1392 cond wait(await_MC43) 499 scan_44. 700000061f552a0 Y------ 1392 cond wait(await_MC44) 500 scan_45. 700000061f55ac8 Y------ 1392 cond wait(await_MC45) 501 scan_46. 700000061f562f0 Y------ 1392 cond wait(await_MC46) 502 scan_47. 700000061f56b18 Y------ 1392 cond wait(await_MC47) 503 scan_48. 700000061f57340 Y------ 1392 cond wait(await_MC48) 504 scan_49. 700000061f57b68 Y------ 1392 cond wait(await_MC49) 505 scan_50. 700000061f58390 Y------ 1392 cond wait(await_MC50) 506 scan_51. 700000061f58bb8 Y------ 1392 cond wait(await_MC51) 507 scan_52. 700000061f593e0 Y------ 1392 cond wait(await_MC52) 508 scan_53. 700000061f59c08 Y------ 1392 cond wait(await_MC53) 509 scan_54. 700000061f5a430 Y------ 1392 cond wait(await_MC54) 510 scan_55. 700000061f5ac58 Y------ 1392 cond wait(await_MC55) 511 scan_56. 700000061f5b480 Y------ 1392 cond wait(await_MC56) 512 scan_57. 700000061f5bca8 Y------ 1392 cond wait(await_MC57) 513 scan_58. 700000061f5c4d0 Y------ 1392 cond wait(await_MC58) 514 scan_59. 700000061f5ccf8 Y------ 1392 cond wait(await_MC59) 515 scan_60. 700000061f5d520 Y------ 1392 cond wait(await_MC60) 516 scan_61. 700000061f5dd48 Y------ 1392 cond wait(await_MC61) 517 scan_62. 700000061f5e570 Y------ 1392 cond wait(await_MC62) 518 scan_63. 700000061f5ed98 Y------ 1392 cond wait(await_MC63) 519 scan_64. 700000061f5f5c0 Y------ 1392 cond wait(await_MC64) 520 scan_65. 700000061f5fde8 Y------ 1392 cond wait(await_MC65) 521 scan_66. 700000061f521b0 Y------ 1392 cond wait(await_MC66) 522 scan_67. 700000061f60610 Y------ 1392 cond wait(await_MC67) 523 scan_68. 700000061f60e38 Y------ 1392 cond wait(await_MC68) 524 scan_69. 700000061f61660 Y------ 1392 cond wait(await_MC69) 525 scan_70. 700000061f61e88 Y------ 1392 cond wait(await_MC70) 526 scan_71. 700000061f626b0 Y------ 1392 cond wait(await_MC71) 527 scan_72. 700000061f62ed8 Y------ 1392 cond wait(await_MC72) 528 scan_73. 700000061f63700 Y------ 1392 cond wait(await_MC73) 529 scan_74. 700000061f63f28 Y------ 1392 cond wait(await_MC74) 530 scan_75. 700000061f64750 Y------ 1392 cond wait(await_MC75) 531 scan_76. 700000061f64f78 Y------ 1392 cond wait(await_MC76) 532 scan_77. 700000061f657a0 Y------ 1392 cond wait(await_MC77) 533 scan_78. 700000061f65fc8 Y------ 1392 cond wait(await_MC78) 534 scan_79. 700000061f667f0 Y------ 1392 cond wait(await_MC79) 535 scan_80. 700000061f67018 Y------ 1392 cond wait(await_MC80) 536 scan_

↪ replying to Floyd Wellershaus

Madison Pruet — Fri, 04 Feb 2005 20:59:33 — source: IIUG Forums & Mailing Lists

--0__=09BBE50DDFE3F0BC8f9e8a93df938690918c09BBE50DDFE3F0BC Content-type: multipart/alternative; Boundary="1__=09BBE50DDFE3F0BC8f9e8a93df938690918c09BBE50DDFE3F0BC" --1__=09BBE50DDFE3F0BC8f9e8a93df938690918c09BBE50DDFE3F0BC Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Well - time for a little talk about PDQ, threading models, consumer thr= eads and producer threads.... You've got PDQ set to 20, which means that the system will parallize as= much as it can throughout the query. If PDQ is set to 1, then all you'= re doing is performing parallel scans on fragmented tables. So just how does parallelism really work? Well, we have multiple threads at various point of the execution tree. = The logic is basically that the threads act as consumers for the threads be= low them in the execution plan, and act a producers for the threads above t= hem. That means that the threads consume 'bits of data' that was passed to t= hem from the threads below, do a bit of work on them, and pass the data to = the threads above them. For instance, for a query which has a scan of a lo= t of fragments, filters the data, groups the data for aggregrate functionali= ty, then sorts the data in a different order for final results. So we have= two sorts ('group by' and the 'order by') So this creates somthing like.... scan the data ---->>>> group the data --->>> aggregrate the data --->= >> order the data -->>> present the data So the 'scan the data' threads are the producers for the consumer threa= ds 'group the data'. But the 'group the data' threads are also producers = for the 'aggregrate the data' consumers. etc... So we have this multiplexor thing (--->>>>) which is sorta like a post office mail drop. Stuff comes from one of the producers (scan thread) = and is put into the mail box. Then one of the consumers (group the data) o= pens the mail box and takes the stuff and processes it. Meanwhile, some oth= er 'group the data' thread is gonna be sitting at the mail station waiting= from somthing else to be put into the box. So what you are seeing is the set of consumer threads waiting for their= producers to give them somthing to do. They're simply waiting at the p= ost office. M.P. = "Floyd Welle...." = <fwellers@yahoo.c = om> = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect many threads for process ? [4169= ] 02/04/2005 06:54 = AM = = = = = = --1__=09BBE50DDFE3F0BC8f9e8a93df938690918c09BBE50DDFE3F0BC Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> Well - time for a little talk about PDQ, threading models, consumer = threads and producer threads.... You've got PDQ set to 20, which means that the system will parallize as= much as it can throughout the query. If PDQ is set to 1, then all you= 're doing is performing parallel scans on fragmented tables. So just how does parallelism really work? Well, we have multiple threads at various point of the execution tree. = The logic is basically that the threads act as consumers for the threa= ds below them in the execution plan, and act a producers for the thread= s above them. That means that the threads consume 'bits of data' that = was passed to them from the threads below, do a bit of work on them, an= d pass the data to the threads above them. For instance, for a query w= hich has a scan of a lot of fragments, filters the data, groups the dat= a for aggregrate functionality, then sorts the data in a different orde= r for final results. So we have two sorts ('group by' and the 'order = by') So this creates somthing like.... scan the data ---->>>> group the data --->>> agg= regrate the data --->>> order the data -->>> present = the data So the 'scan the data' threads are the producers for the consumer threa= ds 'group the data'. But the 'group the data' threads are also produce= rs for the 'aggregrate the data' consumers. etc... So we have this multiplexor thing (--->>>>) which is sorta = like a post office mail drop. Stuff comes from one of the producers (s= can thread) and is put into the mail box. Then one of the consumers (g= roup the data) opens the mail box and takes the stuff and processes it.= Meanwhile, some other 'group the data' thread is gonna be sitting at = the mail station waiting from somthing else to be put into the box. <br= > So what you are seeing is the set of consumer threads waiting for their= producers to give them somthing to do. They're simply waiting at the = post office. M.P. <img src=3D"cid:10__=3D09BBE50DDFE3F0BC8f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "Floyd Welle..= .." <fwellers@yahoo.com>">"Floyd Welle...." <fw= ellers@yahoo.com> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBE50= DDFE3F0BC8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul>"Floyd Welle...." <fwellers@yahoo.= com> Sent by: forum.subscriber@iiug.org 02/04/2005 06:54 AM</ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBE50DDFE3F0BC8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""> <div align=3D"right">To</div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBE50DDFE3F0BC8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""> ids@iiug.org</td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBE50DDFE3F0BC8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""> <div align=3D"right">cc</div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBE50DDFE3F0BC8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""> </td></tr> <tr

Related threads

Glossary

onstat