PDQ - Why does its performance suck sooooo bad?

I've been doing a lot of performance testing over the past couple of months and I've found several things that ended up being significant performance gains across the board in our Informix (4gl) environment. What sticks out is PDQ. It remains what it was 10 years ago when I last did this type of testing....a massive performance hit. Most of my 4gls run considerably longer. One in particular goes from about 1:45 to over 10 minutes. I've yet to figure out how this feature helps anyone and I'm hoping somebody here might have some ideas what I could possibly be doing wrong to not only not get a boost, but to get a massive hit in performance. I've done everything I know of to get performance out of parallel processing. This system has 300gb of memory (essentially the entire DB caches in buffers), NVMes, fastest processors available. I've given the instance 50gb of memory dedicated for PDQ. I've got PSORT_NPROCS set. I've got tempdb in a ramdisk (and PSORT_DBTMP). The script I'm running executes over 100 programs, with plenty of group bys/order bys, etc that I can see the engine go parallel on. None of these programs run faster. There isn't any way this is lack of indexing for the PDQ to use...they have probably too many indexes. I could see a couple of these programs suffering from a bad index....not hundreds of different apps doing different things. Are there some non-obvious settings / configuration to get PDQ to perform at least equal to non-PDQ? If not, is there a way to make the engine disregard PDQ entirely and give all the memory it forces me to configure in the onconfig to my non-PDQ queries rather than hamstring the instance by having 75% of the memory dedicated to a PDQ DSS memory system that is completely useless? Here are some relevant settings (14.10.FC7 on Suse Linux 15sp3) MULTIPROCESSOR 1 SINGLE_CPU_VP 0 VPCLASS cpu,num=14,noage,aff=(0-13) VP_MEMORY_CACHE_KB 10000 VPCLASS aio,num=5 CLEANERS 40AUTO_AIOVPS 1 DIRECT_IO 1 LOCKS 5000000 RESIDENT -1 SHMBASE 0x44000000L SHMVIRTSIZE 25000000 SHMADD 200000 EXTSHMADD 32768 SHMTOTAL 0 CKPTINTVL 600AUTO_CKPTS 1 STMT_CACHE 1 STMT_CACHE_HITS 0 STMT_CACHE_SIZE 819200 STMT_CACHE_NOLIMIT 1 STMT_CACHE_NUMPOOL 3 PC_HASHSIZE 263 PC_POOLSIZE 500 DD_HASHSIZE 709 DD_HASHMAX 10 DS_HASHSIZE 2027 DS_POOLSIZE 4300 MAX_PDQPRIORITY 100 DS_MAX_QUERIES DS_TOTAL_MEMORY 15000000 DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 3750000 OPTCOMPIND 2 DIRECTIVES 1 EXT_DIRECTIVES 2 OPT_GOAL -1 IFX_FOLDVIEW 1AUTO_REPREPARE 1 AUTO_STAT_MODE 1 STATCHANGE 5 USTLOW_SAMPLE 0 OPT_SEEK_FACTOR 0 BATCHEDREAD_TABLE 1 BATCHEDREAD_INDEX 1 AUTO_READAHEAD 1,16 BUFFERPOOL default,memory=1mb,lrus=14,lru_min_dirty=50.00,lru_max_dirty=60.00 BUFFERPOOL size=2K,memory=10gb,start_memory=10gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=4K,memory=6gb,start_memory=6gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=8K,memory=6gb,start_memory=6gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=16K,memory=14gb,start_memory=14gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 AUTO_LRU_TUNING 1 IFX_LOCK_MODE_WAIT=30 IFX_LARGE_PAGES=1 IFX_NETBUF_SIZE=65536 FET_BUF_SIZE=1024000 IFX_NETBUF_PVTPOOL_SIZE=2 PSORT_NPROCS=8 PSORT_DBTEMP=/opt/ramdisk/data DBSPACETEMP=tmpdbs1:tmpdbs2:tmpdbs3:tmpdbs4:tmpdbs5:tmpdbs6 OPTOFC=1 IFX_USEPUT=1PDQPRIORITY=100 ------------------------------ Jared Heath ------------------------------

Hi Jared, I have seen mixed results when using PDQ - some things work much better, other things run longer. You do need to keep an eye on "onstat -g mgm" to see whether things are being gated. With using PDQPRIORITY of 100 and MAX_PDQPRIORITY of 100 can mean that sometimes only one thing can run at a time...not always, but sometimes. Lowering MAX_PDQPRIORITY may be the way to go. Chekcing the gating and the particular gates is important to determine what might be slowing things down. Also I have seen the session values shown in "mgm" change during the run, like the amount of memory used. I recently ran into an issue where we sometimes had a PDQ query slow to a crawl as the memory assigned to it would drop. The query would go from around 2 hours to never completing. We dropped the PDQPRIORITY and (so far) the problem has not resurfaced, while not impacting the execution time. We were never able to figure out why the problem was intermittent. I have seen better performance with lower PDQ settings. When using fragmented tables, especially ones with many partitions, hundreds of threads can be started, and that can just be too much for the engine to handle. Personally I think that issues crop up with the communication between the threads and their coordinators. It is not uncommon to see lots of threads, but almost all of them are in a waiting state. Lowering the PDQPRIORITY and/or MAX_PDQPRIORITY can help these situations. Using an effective PDQPRIORITY of 1 to enable the parallel reads can really help over no PDQ, and then beyond that there is usually a sweet spot for your workload. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Jared: Did you try DS_NONPDQ_QUERY_MEM without PDQ set? Send me schema and sample queries for one or two queries and I'll see what there is to see Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Yes. The onconfig values I provided are the same with and without any PDQ priority. I'm running my entire script through again today so I'll have some better output tomorrow. I set PDQPRIORITY to 100 for this test as it is the only thing running in this environment. From the other reply I'm wondering if that might have been a factor to the performance. I probably should have mentioned we don't do table fragmentation at all on this database. I know that can help it but it isn't something we probably would ever do on this db. ------------------------------ Jared Heath ------------------------------

Here is one example of a running session. This app typically runs in the 6.5 minute range. It spends almost all its time on the query below. As of my writing this, it has been running over 35 minutes. I'm pretty sure this might be a bad example though....the indexing on this one is suspect.... session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 806 xxxxxx- - 25427 xxxxxxxxxxxxxxxxxxxxxxxx 30 1650688 1603008 off Program : /snc/hos/bin/aaaa.4ge tid name rstcb flags curstk status 27581231 sqlexec 82c7b980 ---P--- 3696 cond wait netnorm - 27581242 nljoin_1 82c75fd0 ------- 768 sleeping secs: 1 - 27581243 nljoin_1 82c77ab8 ------- 768 sleeping secs: 1 - 27581244 nljoin_1 82c78ca8 ------- 768 sleeping secs: 1 - 27581245 nljoin_1 82c732f8 ------- 768 sleeping secs: 1 - 27581246 nljoin_1 82c6e240 Y------ 608 cond wait await_MC1 - 27581247 nljoin_1 82c6d948 Y------ 608 cond wait await_MC1 - 27581248 nljoin_1 82c6f430 Y------ 608 cond wait await_MC1 - 27581249 nljoin_1 82c6eb38 Y------ 608 cond wait await_MC1 - 27581250 nljoin_1 82c70620 Y------ 608 cond wait await_MC1 - 27581251 nljoin_1 82c70f18 Y------ 608 cond wait await_MC1 - 27581252 nljoin_1 82c71810 Y------ 608 cond wait await_MC1 - 27581253 nljoin_1 82c72108 Y------ 608 cond wait await_MC1 - 27581254 nljoin_1 82c72a00 Y------ 608 cond wait await_MC1 - 27581255 nljoin_1 82c6b568 Y------ 608 cond wait await_MC1 - 27581256 nljoin_1 82c744e8 Y------ 608 cond wait await_MC1 - 27581257 nljoin_1 82c73bf0 Y------ 608 cond wait await_MC1 - 27581258 nljoin_1 82c74de0 Y------ 608 cond wait await_MC1 - 27581259 nljoin_1 82c6fd28 Y------ 608 cond wait await_MC1 - 27581260 nljoin_1 82c771c0 Y------ 608 cond wait await_MC1 - 27581261 nljoin_1 82c6c758 Y------ 608 cond wait await_MC1 - 27581262 nljoin_1 82c768c8 Y------ 608 cond wait await_MC1 - 27581263 nljoin_1 82c6be60 Y------ 608 cond wait await_MC1 - 27581264 nljoin_1 82c664b0 Y------ 608 cond wait await_MC1 - 27581265 nljoin_1 82c5de28 Y------ 608 cond wait await_MC1 - 27581266 nljoin_1 82c65bb8 ------- 768 sleeping secs: 1 - 27581267 nljoin_1 82c640d0 ------- 768 sleeping secs: 1 - 27581268 nljoin_1 82c5d530 ------- 768 sleeping secs: 1 - 27581269 nljoin_1 82c5cc38 ------- 768 sleeping secs: 1 - 27581270 scan_2.0 82c68890 Y------ 608 cond wait await_MC2 - Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 806 V a7bcf040 2019328 36592 2774 37 806*O0 V 96a11040 4096 744 1 1 name free used name free used overhead 0 6704 scb 0 144 opentable 0 99392 filetable 0 14920 ru 0 600 misc 0 320 log 0 711048 temprec 0 114400 keys 0 1880 ralloc 0 229904 gentcb 0 28808 ostcb 0 2992 sqscb 0 28376 sql 0 123192 xchg_desc 0 44744 xchg_port 0 17880 xchg_packet 0 30392 xchg_group 0 312 xchg_priv 0 4568 hashfiletab 0 23736 osenv 0 3264 sqtcb 0 342032 fragman 0 115408 shmblklist 0 17680 sapi 0 3456 rsam_seqscan 0 19936 sqscb info scb sqscb optofc pdqpriority optcompind directives 835df218 ae587028 1 100 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 806 SELECT tba CR Not Wait 0 0 9.41 Off Current Role : sncfull Current SQL statement (1218977) : select min ( actg_dt ) from pascancl where policy_id = ? and actg_dt <= ? and can_quote_flg <> "Q" Host variables : address type flags value ------------------------------ 0x00000000af87d948 INT 0x000 1234 0x00000000af87d9d8 DATE 0x000 03/31/2023 Last parsed SQL statement : select min ( actg_dt ) from pascancl where policy_id = ? and actg_dt <= ? and can_quote_flg <> "Q" 106488 byte(s) of memory is allocated from the sscpool create table "informix".pascancl ( can_quote_id serial not null , policy_id integer not null , actg_dt date not null , pol_can_dt date, prem_ref money(11,2) not null , fee_ref money(11,2) not null , tax_ref money(11,2) not null , int_ref money(11,2) not null , ref_doc_no char(9) not null , pay_meth char(2) not null , coll_pct decimal(5,2) not null , contact char(20) not null , paid_flg char(1) not null , can_quote_flg char(1) not null ); revoke all on "informix".pascancl from "public" as "informix";create index "informix".i_pascancl1 on "informix".pascancl (policy_id) using btree ; create unique index "informix".i_pascancl2 on "informix".pascancl (can_quote_id) using btree ; create index "informix".i_pascancl3 on "informix".pascancl (actg_dt) using btree ; alter table "informix".pascancl add constraint primary key (can_quote_id) constraint "informix".pk_pascancl ; ------------------------------ Jared Heath ------------------------------ ------------------------------

Jared: All of those NL Join threads make no sense in a simple single table query. Something is not kosher here. What does SET EXPLAIN report for that query with and without PDQ? Have you tried this with the MULTI_INDEX directive attached? Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Does table "informix".pascancl use fragmentation either for the table itself or any of the indices? With this query, PDQ is only going to help with the sorting necessary to produce the MIN value - more memory, more threads. You already have 3.5 GB for DS_NONPDQ_QUERY_MEM though.I did wonder if without PDQ multiple sort threads would appear anyway with PSORT_NPROCS set but it seems not in this case. This question bothered me enough to try a small reproduction of my own. With 14.10.FC10 and OPTCOMPIND 2 and PDQPRIORITY 100 I get this thread list, more what I would expect than the join threads you see: tid name rstcb flags curstk status 6735677 sqlexec b19a8f60 Y--P--- 4880 cond wait notify_MC1- 6735684 group_1. 61cbde90 ------- 864 yield time- 6735685 group_1. 933b96f8 ------- 864 yield time- 6735686 group_1. ac284cf0 ------- 896 yield time- 6735687 group_1. b19b0598 ------- 896 yield time- 6735688 scan_2.0 93395a10 ----R-- 5904 IO Wait- The explain plan includes ' Maximum Threads: 5'. I am working with a table of 2 million rows or so and distributed the data to make the sort significant. Is this an FC7 bug maybe (the use of nljoin threads)? To your point about the indexing being suspect, yes this query could be supported better by a compound index but this does not explain what you see. Ben. ------------------------------ Benjamin Thompson ------------------------------

I don't know what MULTI_INDEX is. Let me look it up. Ahhh, query directives. I don't have access to change the code to even test that. The dev team as a rule do not use directives though, so I doubt I'd ever get the chance to test that inside a 4gl. As far as the nljoin goes, this is the same behavior I remember encountering on 11.70 years ago.....TONS of threads always waiting for no reason slowing everything to a crawl. Honestly I just thought it was a function of PDQ just not being worth it in Informix, but I've been challenged on that topic by the SQL server & Oracle guys who claim it should increase almost query performance enough on these multi-day scripts to make up for any negative performance it might add on some queries. Here are some explain outputs.....non PDQ first. Note these are running directly on the database server vs on the app server with a TCP connection....I don't know how to capture sqexplain that way. It doesn't seem to output anywhere that I can find. I don't see anything in the sqexplain that makes me think something is wrong. On another note...could this be related to networking? Both systems are Hyper-V VMs on the same physical host with 10g backplane so I doubt it could be anything other than something misconfigured in the Linux setup, but I've gone through that tuning it over the years. This query has sub-second response for both, so I can't catch it with onstat. I am re-prepping the env to grab some more queries from different programs. I've also got a related issue with this PDQ test. One of the programs later in the script crashes with an attempt to insert a NULL into a table....it doesn't get that error with PDQ turned of. QUERY: (OPTIMIZATION TIMESTAMP: 06-05-2023 09:15:19) ------ select min ( actg_dt ) from pascancl where policy_id = 6977440 and actg_dt <= "03/31/2023" and can_quote_flg <> "Q" Estimated Cost: 3 Estimated # of Rows Returned: 1 1) informix.pascancl: INDEX PATH Filters: (informix.pascancl.can_quote_flg != 'Q' AND informix.pascancl.actg_dt <= 03/31/2023 ) (1) Index Name: informix.i_pascancl1 Index Keys: policy_id (Serial, fragments: ALL) Lower Index Filter: informix.pascancl.policy_id = 6977440 Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 pascancl type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 1 1 1 00:00.00 3 type rows_prod est_rows rows_cons time ------------------------------ group 1 1 1 00:00.00 Estimated Cost: 3 Estimated # of Rows Returned: 1 Maximum Threads: 15 1) informix.pascancl: INDEX PATH Filters: (informix.pascancl.can_quote_flg != 'Q' AND informix.pascancl.actg_dt <= 03/31/2023 ) (1) Index Name: informix.i_pascancl1 Index Keys: policy_id (Parallel, fragments: ALL) Lower Index Filter: informix.pascancl.policy_id = 6977440 Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 pascancl type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 1 1 1 00:00.00 3 type rows_prod est_rows rows_cons time ------------------------------ group 14 1 1 00:00.00 type rows_prod est_rows rows_cons time ------------------------------ group 1 1 14 00:00.00 ------------------------------ Jared Heath

We don't use fragmentation on anything. This table has 7.2 million rows, smallish for the bigger tables in this database, some are 20+ millions. This is the smaller of the two instances I've tested on. The other instance has two tables with 300+ million rows each, both of which have sub half-second index response time many millions of times a day....but PDQ slaughtered those queries 10 years ago. ------------------------------ Jared Heath ------------------------------

Here is are different queries from different programs. This first program typically runs in about 1.5 minutes. With PDQ on, it goes to 10. It does a lot of different queries, but this one sticks out in the onstat....its doing the same nljoin stuff: session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 125 sncuser - - 2076 uatappx21.statenational.com 8 438272 435352 off Program : /snc/hos/bin/pasedit.4ge tid name rstcb flags curstk status 193 sqlexec 82c59f60 Y--P--- 5072 running- 196 scan_1.0 82c5ba48 ------- 768 sleeping secs: 1 - 467035 group_29 82c5b150 ------- 608 running- 467036 nljoin_2 82c5de28 ------- 608 ready- 467037 nljoin_2 82c5c340 S------ 688 mutex wait sync_lock2- 467038 nljoin_2 82c5cc38 S------ 688 mutex wait sync_lock2- 467039 nljoin_2 82c58d70 S------ 688 mutex wait sync_lock2- 467040 scan_291 82c56990 Y------ 608 cond wait await_MC29- Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 125 V 98a16040 434176 2488 542 6 125*O0 V 989e8040 4096 744 1 1 name free used name free used overhead 0 6704 scb 0 144 opentable 0 22848 filetable 0 3616 misc 0 320 log 0 132288 temprec 0 31840 keys 0 592 ralloc 0 81984 gentcb 0 6128 ostcb 0 2992 sqscb 0 29200 sql 0 8592 xchg_desc 0 10520 xchg_port 0 5656 xchg_packet 0 4368 xchg_group 0 456 xchg_priv 0 960 hashfiletab 0 4416 osenv 0 3008 sqtcb 0 62320 fragman 0 9432 shmblklist 0 2688 sapi 0 1832 rsam_seqscan 0 2136 sqscb info scb sqscb optofc pdqpriority optcompind directives 836351c0 98a5b028 0 40 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 125 SELECT tba CR Not Wait 0 0 9.41 Off Current Role : sncfull Current SQL statement (189022) : select count ( * ) from stastcd , aimmisc where tba_no = ? and ins_co_no =? and state = ? and corp_state = ? Host variables : address type flags value ------------------------------ 0x000000009bdac9b8 CHAR 0x000 40P 0x000000009bdaca48 CHAR 0x000 S8 0x000000009bdacad8 CHAR 0x000 CA 0x000000009bdacb68 CHAR 0x000 CA Last parsed SQL statement : select count ( * ) from stastcd , aimmisc where tba_no = ? and ins_co_no =? and state = ? and corp_state = ? 143952 byte(s) of memory is allocated from the sscpool The second program bounces back and forth between three queries. This one gets runtime above 3 seconds sometimes....in fact the one I ran for the attached explain runs several seconds for both. Non-PDQ first. session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 765 xx- - 1152 xxx 4 335872 321296 off Program : /snc/hos/bin/bbb.4ge tid name rstcb flags curstk status 1401871 sqlexec 82c6b568 ---PX-- 8160 running- 1401876 scan_1.0 82c6c758 Y------ 608 cond wait await_MC1 - 1401877 scan_2.0 82c57b80 Y------ 608 cond wait await_MC2 - 1401878 scan_3.0 82c68890 Y------ 608 cond wait await_MC3 - Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 765 V b1da4040 327680 12872 451 16 765*O0 V b051a040 4096 744 1 1 name free used name free used overhead 0 6704 scb 0 144 opentable 0 20568 filetable 0 3584 ru 0 1200 misc 0 320 log 0 66144 temprec 0 26240 keys 0 848 ralloc 0 65104 gentcb 0 3536 ostcb 0 2992 sort 0 104 sqscb 0 30032 sql 0 21112 xchg_desc 0 4544 xchg_port 0 3360 xchg_packet 0 1344 xchg_group 0 312 xchg_priv 0 512 hashfiletab 0 2208 osenv 0 3136 sqtcb 0 36096 fragman 0 12128 shmblklist 0 1912 sapi 0 1080 rsam_seqscan 0 3024 sqscb info scb sqscb optofc pdqpriority optcompind directives 8356d200 b3070028 0 10 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 765 SELECT tba CR Not Wait 0 0 9.41 Off Current statement name : i0006ec6bzydk2kobw Current Role : sncfull Current SQL statement (1599) : SELECT term_ann, gap_flag, prem_can_meth, count(*) FROM triangle,aimmastr WHERE cancel_month = ? AND pol_acctg_period <= ? AND eom_date <= ? AND triangle.tba_no = aimmastr.tba_no AND area != 'ADD1' AND prem_amt > 0 AND triangle.tba_no NOT IN (SELECT curr_tba_no FROM depvar WHERE exclude_ctrlbrk = 'Y' and triangle.term_ann = depvar.curr_term_ann and triangle.prem_can_meth = depvar.curr_can_meth) GROUP BY 1,2,3 QUERY_TIMEOUT setting: 0 (No Timeout) Clock time elapsed : 00:00:02 Host variables : address type flags value ------------------------------ 0x00000000ad0e9230 SMINT 0x000 29 0x00000000ad0e92c0 SMINT 0x000 1450 0x00000000ad0e9350 DATE 0x000 02/28/2023 Last parsed SQL statement : select months_needed from trimthctrl where ? between beg_cycle_month andend_cycle_month and ? between trimthctrl . eff_dt and trimthctrl . exp_dt User-created Temp tables : partnum tabname rowsize 1700002 t_cbrkfactor 22 100936 byte(s) of memory is allocated from the sscpool QUERY: (OPTIMIZATION TIMESTAMP: 06-05-2023 10:10:08) ------ SELECT term_ann, gap_flag, prem_can_meth, count(*) FROM triangle, aimmastr WHERE cancel_month = 5 AND pol_acctg_period <= 1475 AND eom_date <= "03/31/2023" AND triangle.tba_no = aimmastr.tba_no AND area != 'ADD1' AND prem_amt > 0 AND triangle.tba_no NOT IN (SELECT curr_tba_no FROM depvar WHERE exclude_ctrlbrk = 'Y' and triangle.term_ann = depvar.curr_term_ann and triangle.prem_can_meth = depvar.curr_can_meth) GROUP BY 1,2,3Estimated Cost: 802823 Estimated # of Rows Returned: 9 Maximum Threads: 0 Temporary Files Required For: Group By 1) informix.aimmastr: SEQUENTIAL SCAN Filters: informix.aimmastr.area != 'ADD1' 2) informix.triangle: INDEX PATH Filters: ((informix.triangle.prem_amt > $0.00 AND informix.triangle.tba_no != ALL <subquery> ) AND informix.triangle.eom_date <= 03/31/2023 ) (1) Index Name: informix.i_triangle3 Index Keys: tba_no cancel_month pol_acctg_period (Parallel, fragments: ALL) Lower Index Filter: (informix.triangle.tba_no = informix.aimmastr.tba_no AND informix.triangle.cancel_month = 5 ) Upper Index Filter: informix.triangle.pol_acctg_period <= 1475 NESTED LOOP JOIN Subquery: --------- Estimated Cost: 2 Estimated # of Rows Returned: 1 Maximum Threads: 1 1) informix.depvar: INDEX PATH Filters: informix.depvar.exclude_ctrlbrk = 'Y' (1) Index Name: informix.i_depvar2 Index Keys: curr_term_ann curr_can_meth (Parallel, fragments: ALL) Lower Index Filter: (informix.depvar.curr_term_ann = informix.triangle.term_ann AND informix.depvar.curr_can_meth = informix.triangle.prem_can_meth ) Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 aimmastr t2 triangle type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 5171 5171 5180 00:00.00 732 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t2 223175 136847 293083 00:11.87 97 type rows_prod est_rows time est_cost ------------------------------ nljoin 223175 136610 00:11.90 502633 type rows_prod est_rows rows_cons time est_cost ------------------------------ group 7 10 223175 00:12.01 300191 Subquery statistics: ------------------------------ Table map : ------------------------------ Internal name Table name ------------------------------ t1 depvar type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 889979 1 13740134 00:06.59 2 QUERY: (OPTIMIZATION TIMESTAMP: 06-05-2023 10:10:33) ------ SELECT term_ann, gap_flag, prem_can_meth, count(*) FROM triangle, aimmastr WHERE cancel_month = 5 AND pol_acctg_period <= 1475 AND eom_date <= "03/31/2023" AND triangle.tba_no = aimmastr.tba_no AND area != 'ADD1' AND prem_amt > 0 AND triangle.tba_no NOT IN (SELECT curr_tba_no FROM depvar WHERE exclude_ctrlbrk = 'Y' and triangle.term_ann = depvar.curr_term_ann and triangle.prem_can_meth = depvar.curr_can_meth) GROUP BY 1,2,3Estimated Cost: 802823 Estimated # of Rows Returned: 9 Maximum Threads: 0 Temporary Files Required For: Group By 1) informix.aimmastr: SEQUENTIAL SCAN Filters: informix.aimmastr.area != 'ADD1' 2) informix.triangle: INDEX PATH Filters: ((informix.triangle.prem_amt > $0.00 AND informix.triangle.tba_no != ALL <subquery> ) AND informix.triangle.eom_date <= 03/31/2023 ) (1) Index Name: informix.i_triangle3 Index Keys: tba_no cancel_month pol_acctg_period (Parallel, fragments: ALL) Lower Index Filter: (informix.triangle.tba_no = informix.aimmastr.tba_no AND informix.triangle.cancel_month = 5 ) Upper Index Filter: informix.triangle.pol_acctg_period <= 1475 NESTED LOOP JOIN Subquery: --------- Estimated Cost: 2 Estimated # of Rows Returned: 1 Maximum Threads: 1 1) informix.depvar: INDEX PATH Filters: informix.depvar.exclude_ctrlbrk = 'Y' (1) Index Name: informix.i_depvar2 Index Keys: curr_term_ann curr_can_meth (Parallel, fragments: ALL) Lower Index Filter: (informix.depvar.curr_term_ann = informix.triangle.term_ann AND informix.depvar.curr_can_meth = informix.triangle.prem_can_meth ) Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 aimmastr t2 triangle type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 5171 5171 5180 00:00.00 732 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t2 223175 136847 293083 00:12.33 97 type rows_prod est_rows time est_cost ------------------------------ nljoin 223175 136610 00:12.35 502633 type rows_prod est_rows rows_cons time est_cost ------------------------------ group 7 10 223175 00:12.47 300191 Subquery statistics: ------------------------------ Table map : ------------------------------ Internal name Table name ------------------------------ t1 depvar type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 889979 1 13740134 00:06.90 2

Curiouser and curiouser Jared! Those two query plans are identical and I see no evidence of PDQ in either one. Both show "Maimum Threads: 0" which would indicate that they were both run under non-zero PDQPRIORITY ( = 1 ??). I do see that the data distributions on the triangle and depvar tables are either stale or insufficiently detailed as the estimated and actual counts are very different. Also the query has a correlated subquery which hurts performance (though I do understand that this is not related to the issue you reported). Here's a rewrite that should run faster regardless, and especially if you update statistics on those tables: SELECT term_ann, gap_flag, prem_can_meth, count(*) FROM triangle INNER JOIN aimmastr ONtriangle.tba_no = aimmastr.tba_no AND area != 'ADD1' LEFT JOIN depvar ON triangle.tba_no = depvar.curr_tba_no AND triangle.term_ann = depvar.curr_term_ann AND triangle.prem_can_meth = depvar.curr_can_meth AND no exclude_ctrlbrk = 'Y' WHERE cancel_month = 5 AND pol_acctg_period <= 1475 AND eom_date <= "03/31/2023" AND prem_amt > 0 AND depvar.curr_tba_no IS NULL GROUP BY 1,2,3; Test it though, sometimes putting filters ( noexclude_ctrlbrk = 'Y' ) in the ON clause of an outer join causes unexpected results. An index on depvar.curr_tba_no (with multi-index scan), or adding that column to the existing index on curr_term_ann and curr_can_meth, would speed things up even more. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

so now I'm really intrigued. The stats on the triangle and depvar tables should be 100% new....these are two of the tables that had to be entirely modified to migrate from my raw filesystem to cooked files. They were very fragmented tables that are now in a single file....I dropped the indexes and rebuilt them as well. Now I'm wondering if my stats are trashed. I'll start another test later tonight and will have some feedback later. I do have free reign to add/modify indexes as I see fit.....I might also do that. The query re-write all I can do is run it and see if it makes a difference. None of the 4gl developers have any clue how to deal with ANSI SQL. I barely do....20+ years of doing it the old-school informix way is seriously hard to back out of people's minds. I doubt any of them would even know what Left Join/Right Join do. As for the query plans, yeah, that was weird. I'll try to re-do that again being extra careful to ensure PDQ is definitely off in one and on in another. I am wondering if I started the engine with it on and it kept it.... ------------------------------ Jared Heath ------------------------------

Forcing stats seems to have mostly cleared up the nonsense output, and trimming the performance drop from outrageous to 5-20% depending on the 4gl. The same query with all the weird nljoins is provided again below...it only has a couple of those now. I am still working on some of the other items, but at this point I have a different PDQ issue that I am stumped on. Why would a program work without it and crash with an attempted null insert with it on? Is this a bug ? session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 814 aa - - 8635 abc 4 270336 262912 off Program : /snc/hos/bin/a.4ge tid name rstcb flags curstk status 5300998 sqlexec 82c649c8 Y--P--- 3824 cond wait netnorm - 5301009 nljoin_1 82c5f018 ------- 768 sleeping secs: 1 - 5301010 nljoin_1 82c80a38 ------- 768 sleeping secs: 1 - 5301011 scan_2.0 82c744e8 ------- 768 sleeping secs: 1 - Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 814 V 99b0f040 270336 3344 380 8 814*O0 V a8b57040 4096 744 1 1 name free used name free used overhead 0 6704 scb 0 144 opentable 0 16248 filetable 0 2920 ru 0 600 misc 0 320 log 0 66144 temprec 0 30376 keys 0 1088 ralloc 0 35520 gentcb 0 3536 ostcb 0 2992 sqscb 0 28168 sql 0 12584 xchg_desc 0 4560 xchg_port 0 2552 xchg_packet 0 1776 xchg_group 0 240 xchg_priv 0 512 hashfiletab 0 2232 osenv 0 3264 sqtcb 0 33128 fragman 0 10904 shmblklist 0 1128 sapi 0 960 rsam_seqscan 0 1600 sqscb info scb sqscb optofc pdqpriority optcompind directives 836891c0 a8bf4028 1 10 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 814 SELECT tba CR Not Wait 0 0 9.41 Off Current Role : sncfull Current SQL statement (169953) : select min ( actg_dt ) from pascancl where policy_id = ? and actg_dt <= ? and can_quote_flg <> "Q" Host variables : address type flags value ------------------------------ 0x000000009adf7ab0 INT 0x000 7815707 0x000000009adf7b40 DATE 0x000 02/28/2023 Last parsed SQL statement : select min ( actg_dt ) from pascancl where policy_id = ? and actg_dt <= ? and can_quote_flg <> "Q" 106288 byte(s) of memory is allocated from the sscpool ------------------------------ Jared Heath

PDQ is definitely there in both Art: (Parallel, fragments: ALL) Here is a diff of the two, no differences except in the durations. < QUERY: (OPTIMIZATION TIMESTAMP: 06-05-2023 10:10:08) --- > QUERY: (OPTIMIZATION TIMESTAMP: 06-05-2023 10:10:33) 72c72 < scan t2 223175 136847 293083 00:11.87 97 --- > scan t2 223175 136847 293083 00:12.33 97 76c76 < nljoin 223175 136610 00:11.90 502633 --- > nljoin 223175 136610 00:12.35 502633 80c80 < group 7 10 223175 00:12.01 300191 --- > group 7 10 223175 00:12.47 300191 94c94,95 < scan t1 889979 1 13740134 00:06.59 2 --- > scan t1 889979 1 13740134 00:06.90 2 > I see further down the thread updating stats has helped. (Where is Spokey when you need him?) Is PDQ set in the server's environment at start up? I see you posted at the top. IFX_LOCK_MODE_WAIT=30 IFX_LARGE_PAGES=1 IFX_NETBUF_SIZE=65536 FET_BUF_SIZE=1024000 IFX_NETBUF_PVTPOOL_SIZE=2 PSORT_NPROCS=8 PSORT_DBTEMP=/opt/ramdisk/data DBSPACETEMP=tmpdbs1:tmpdbs2:tmpdbs3:tmpdbs4:tmpdbs5:tmpdbs6 OPTOFC=1 IFX_USEPUT=1 PDQPRIORITY=100 Or is this just the environment for the client program? Ben. ------------------------------ Benjamin Thompson ------------------------------

Without fragmentation, the benefit from PDQ is largely limited to having more threads for sorting. Sometimes different code paths are followed and there are dedicated thread types for individual tasks -- we still have the mystery of nljoin threads for a single-table query. I am not sure what version you were running 10 years ago. In v7.31 and earlier sort memory for non-PDQ queries was very limited but you have up to 3.5 GB available, a massive increase on the default value of 128 kB. I also suspect some of those MC waits you see are because the memory grant manager is constraining your query, as Mike suggested above. Easiest to monitor via 'onstat -g mgm' but at thread level, they wait on a condition. Ben. ------------------------------ Benjamin Thompson ------------------------------

Ben: I did see the "Parallel, fragments: ALL" but no other indications of PDQ hence my comment that it looked like PDQPRIORITY was set to '1'. Also those minor duration differences are not out of range of what you would see between any two runs of the same query. Something odd is going on here. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

we were 11.7.FC2 the last time I did testing. There is nothing gating these queries in mgm....this is the only thing running on the server. I haven't ever seen onstat -g mgm show anything gated during this testing. ------------------------------ Jared Heath ------------------------------

I have PDQPRIORITY on the server set at 40, 10 on the client, and max set to 50 in the onconfig. ------------------------------ Jared Heath ------------------------------

I am gong to work on this specific post tomorrow. On a positive note, I upgraded to 14.10.FC10 and it mostly seems to have removed the nljoin stuff. I wonder if FC7 is broken. It looks like it is running quite a bit faster now, but still generally slower on almost all of the programs. I need to wait for the the end to see if the last couple programs (the ones that run the longest) get any speed benefit. ------------------------------ Jared Heath ------------------------------

I'm glad that has been cleared up. ------------------------------ Benjamin Thompson ------------------------------

Final results....38% slower. Its still crazy slow, especially on big reports which are where I would expect the biggest increase since the reports are full of grouping and ordering. Does PDQ not help ORDER BY in a report? ------------------------------ Jared Heath ------------------------------

Art, The query formatted with LEFT/RIGHT join becomes sub-second.....and I have to wonder why that makes a difference. Also, the index discussed probably is irrelevant....depvar has less than 500 rows. I suspect removing that index makes the existing one run faster just by seq scanning. ------------------------------ Jared Heath ------------------------------

Jared: The reason that the LEFT JOIN version of the query is faster than the subquery version is that the subquery is a correlated subquery which means that the query is excuted for each of the 200,000+ rows examined by the outer query. The JOIN version simply joins that table to the other two scanning once for its matching rows, or, possibly, driving the whole shebang of that table (unlikely in an OUTER join - LEFT JOIN = LEFT OUTER JOIN = OUTER JOIN - but possible). Any correlated subquery that does not contain a aggregation function can be rewritten as a simple join and unless the joining table in the outer query is missing an index on the join column(s) the join will nearly always run faster than the subquery version. Informix can unwind many correlated subqueries into joins automatically, but since this one was in an IN() clause the optimizer was not able to do so. Note that if there are aggregation functions in the subquery, you can simply put the subquery (non-correlated) into a derived table and join that to the outer query with the same performance improvement. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Oh, as for the index, you cannot know whether it will make a positive difference until you have tested it! Same for removing (or using a directive to force a sequential scan)! Don't forget: Kagel's Second Law of SQL: Any SELECT can be rewritten at least four different ways to return the same results and you don't know if you have found the best performing version until you have tested them all! I first formulated that statement/law in 1990 and in the succeeding 33 years no one has ever shown me a select that I cannot rewrite, sometimes in seven different ways! Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi, The real benefit from PDQ only comes with table fragmentation/partitioning. The whole idea of PDQ is parallel data i.e. scan multiple fragments in parallel. Unless you are getting parallel reads across partitions then you would not expect much speedup just the overhead of having to spawn and co-ordinate multiple threads. One producer can only hand rows to one consumer and you get the overhead of having to hand rows to multuple consumers. If it was that simple all queries would just run in parallel on all cpus and there would be no configuration parameters! If you run everything with PDQ you have many more threads than cores and the overhead of task switching all the threads as well. Just use PDQ for longer running queries than process many rows and can scan multiple table/index parttions in parallel! Regards, David. ------------------------------ David Williams ------------------------------

I'm leaving on vacation for 10 days....I'm going to be picking this back up when I get back ------------------------------ Jared Heath ------------------------------

Stumbled across this which probably doesn't add much new to the discussion but is an official doc.: https://www.ibm.com/support/pages/several-scan-threads-cond-wait-awaitmcx-causing-performance-bottlenecks ------------------------------ Benjamin Thompson ------------------------------

Good point in that link Ben. Similar issue: if you compile stored procedures/functions (UPDATE STATISTICS FOR PROCDURE ...) with non-zero PDQPRIORITY those procedures/functions will always run with that PDQPRIORITY even if the session that calls it has PDQPRIORITY set to zero which can trigger the same behavior described in your link. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------