Question about sysindexes.clust

To the very wise watchers of this group. I have a question which I would like your input. Background: Currently we are running IDS 10.00.FC8 on AIX 5.3 TL6 on an IBM P570 (6-way) using an IBM DS4500 SAN. We have seen some slowdowns within the system and have had a heck of a time pairing down to what has changed and is causing the problem. The main table we are currently working with is fragmented and has approx. 69million rows. While we are testing index/table rebuilds we have noted that our performance is dropping through the floor and the only values that change are the sysindexes.clust values for the indexes, pgreads for the table, and the over all run time. With some testing we have found a fragmentation schema which appears to work but... I haven't found much on performance monitoring and expectations of the sysindexes.clust value. How many of you great DBA's, DBSA's, and Informix Guru's use the sysindexes.clust to determine when it is time to rebuild a table and if the rebuild was in the best form? -- Eric B. Rowell

Eric Rowell wrote: > To the very wise watchers of this group. I have a question which I would > like your input. > > Background: > Currently we are running IDS 10.00.FC8 on AIX 5.3 TL6 on an IBM P570 > (6-way) using an IBM DS4500 SAN. > > We have seen some slowdowns within the system and have had a heck of a time > pairing down to what has changed and is causing the problem. > > The main table we are currently working with is fragmented and has approx. > 69million rows. While we are testing index/table rebuilds we have noted > that our performance is dropping through the floor and the only values that > change are the sysindexes.clust values for the indexes, pgreads for the > table, and the over all run time. With some testing we have found a > fragmentation schema which appears to work but... > > I haven't found much on performance monitoring and expectations of the > sysindexes.clust value. How many of you great DBA's, DBSA's, and Informix > Guru's use the sysindexes.clust to determine when it is time to rebuild a > table and if the rebuild was in the best form? Personally, I've no idea why you think this should be an issue. I'd first ask the obvious "have you run UPDATE STATISTICS" and then I'd say "B-Tree cleaner". -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Wish it was that easy... Both tried and checked... Update stats has been run many times with no change to runtime. Rebuilding the index then running update stats does nothing to change the runtime or the "clust" values. What it appears to be is that the physical data in the table is in such a different order then the index (many years/inserts/deletes and at least one reload since the table was built) that it is slowing everything down. When the data is sorted by (or clustered) by the table the performance is great. The only things that change are the number of pgreads and the "clust" value goes down . Note: Current Run Time after update stats: 5hr 47min Total Rows in the table: 69234768 sysindex.clust values for indexes: ix_pn_sl 43574071 ix_sk 18992828 ix_sl 24324296 ix_vi_ed_sl 29010585 Run time after reload/index/update stats of table: 9hr 54m Total Rows in the table: 69234768 sysindex.clust values for indexes: ix_pn_sl 62861388 ix_sk 47732317 ix_sl 54720284 ix_vi_ed_sl 56854170 Run time after sorted reload/index/update stats of table: 4hr 45m Total Rows in the table: 69234768 sysindex.clust values for indexes: ix_pn_sl 16681107 ix_sk 3867459 ix_sl 2695472 ix_vi_ed_sl 6089056 So again does anyone watch this value to know about the health of a table? I don't think I ever have. On Tue, Nov 18, 2008 at 12:22 PM, Obnoxio The Clown <obnoxio@serendipita.com > wrote: > Eric Rowell wrote: > > To the very wise watchers of this group. I have a question which I would > > like your input. > > > > Background: > > Currently we are running IDS 10.00.FC8 on AIX 5.3 TL6 on an IBM P570 > > (6-way) using an IBM DS4500 SAN. > > > > We have seen some slowdowns within the system and have had a heck of a > time > > pairing down to what has changed and is causing the problem. > > > > The main table we are currently working with is fragmented and has > approx. > > 69million rows. While we are testing index/table rebuilds we have noted > > that our performance is dropping through the floor and the only values > that > > change are the sysindexes.clust values for the indexes, pgreads for the > > table, and the over all run time. With some testing we have found a > > fragmentation schema which appears to work but... > > > > I haven't found much on performance monitoring and expectations of the > > sysindexes.clust value. How many of you great DBA's, DBSA's, and Informix > > Guru's use the sysindexes.clust to determine when it is time to rebuild a > > table and if the rebuild was in the best form? > > Personally, I've no idea why you think this should be an issue. I'd > first ask the obvious "have you run UPDATE STATISTICS" and then I'd say > "B-Tree cleaner". > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

Eric Rowell wrote: > > Current Run Time after update stats: 5hr 47min > Total Rows in the table: 69234768 > sysindex.clust values for indexes: > > Run time after reload/index/update stats of table: 9hr 54m > Total Rows in the table: 69234768 sysindex.clust values for indexes: > > Run time after sorted reload/index/update stats of table: 4hr 45m > Total Rows in the table: 69234768 sysindex.clust values for indexes: Is this repeatable? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Yes. Man is each of the repeatable. Spent the last 2 months of my life like a hamster on a wheel duplicating this. No one looked at the sysindexes.clust until about a week and a half ago. All test since have been Great, and repeatable. On Tue, Nov 18, 2008 at 12:48 PM, Obnoxio The Clown <obnoxio@serendipita.com > wrote: > Eric Rowell wrote: > > > > Current Run Time after update stats: 5hr 47min > > Total Rows in the table: 69234768 > > sysindex.clust values for indexes: > > > > Run time after reload/index/update stats of table: 9hr 54m > > Total Rows in the table: 69234768 sysindex.clust values for indexes: > > > > Run time after sorted reload/index/update stats of table: 4hr 45m > > Total Rows in the table: 69234768 sysindex.clust values for indexes: > > Is this repeatable? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

Eric Rowell wrote: > Yes. Man is each of the repeatable. Spent the last 2 months of my life > like a hamster on a wheel duplicating this. No one looked at the > sysindexes.clust until about a week and a half ago. All test since have > been Great, and repeatable. OK, that's actually a good thing. :o) So, can you post up the schema (dbschema -ss ) of the table, and run me through the way your job works? Just high level so I can get a feel for what I'm looking at. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Obnoxio The Clown wrote: > Eric Rowell wrote: >> Yes. Man is each of the repeatable. Spent the last 2 months of my life >> like a hamster on a wheel duplicating this. No one looked at the >> sysindexes.clust until about a week and a half ago. All test since have >> been Great, and repeatable. > > OK, that's actually a good thing. :o) > > So, can you post up the schema (dbschema -ss ) of the table, and run me > through the way your job works? Just high level so I can get a feel for > what I'm looking at. Oh, and I bet there's one nasty query in there that's causing all the pain. Can you share that query and its explain plan, please? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Below is the table. The problem with explaining the process is there are many of them that affect this data and they appear to cause issues for the one selecting data. The process which is having the largest problem with performance only selects data from this table using the serial_link (which is the key from the parent table). This process opens a cursor and then processes each row to determin if they are in another table and added them if needed. No processes on the other table have issues and it's sysptprof stats don't change during different parts of this process. I keep coming back to the location of the physical rows in the following table as the problem. When in order performance is great. The onlything we have found that gives insight is the "clust" value. --Current create table "product".qty_det ( serial_key serial not null , serial_link integer not null , option_list varchar(82,33), option_count smallint not null , option1 char(7), option2 char(7), cost_classify char(1), vendor_id char(5), unit_id char(2), phase_activity char(1) not null , phase_draw smallint, taxable char(1) not null , discounted_part char(1) not null , qty_or_amt decimal(12,3) not null , partno char(8), qty_comments varchar(60), eff_dt date, exp_dt date, create_per char(10) default user, create_dt date default today, updat_per char(10) default user, updat_dt date default today, updat_time datetime hour to second default current hour to second ) fragment by round robin in qty_det_dt_1 , qty_det_dt_2 , qty_det_dt_3 , qty_det_dt_4 , qty_det_dt_5 , qty_det_dt_6 extent size 825000 next size 50000 lock mode row; create index "product".ix_qty_det_pn_sl on "product".qty_det (partno, serial_link) using btree in qty_det_ix_2 ; create unique index "product".ix_qty_det_sk on "product".qty_det (serial_key) using btree in qty_det_ix_1 ; create index "product".ix_qty_det_sl on "product".qty_det (serial_link) using btree in qty_det_ix_1 ; create index "product".ix_qty_det_vi_ed_sl on "product".qty_det (vendor_id,exp_dt,serial_link) using btree in qty_det_ix_2 ; create trigger "product".tr_ins_qty_det insert on "product".qty_det referencing new as n for each row ( execute function "product".spl_userinserttimestamp(n.create_per ) into "product".qty_det.create_per,"product".qty_det.create_dt,"product" .qty_det.updat_per,"product".qty_det.updat_dt,"product".qty_det.updat_time); create trigger "product".tr_upd_qty_det update on "product".qty_det referencing new as n for each row ( execute function "product".spl_userupdatetimestamp(n.updat_per ) into "product".qty_det.updat_per,"product".qty_det.updat_dt,"product" .qty_det.updat_time); --Suggested change (but requires sorting the data for best performance) fragment by expression (abs(mod(serial_link , 6 )) = 0 ) in qty_det_dt_1 , (abs(mod(serial_link , 6 )) = 1 ) in qty_det_dt_2 , (abs(mod(serial_link , 6 )) = 2 ) in qty_det_dt_3 , (abs(mod(serial_link , 6 )) = 3 ) in qty_det_dt_4 , (abs(mod(serial_link , 6 )) = 4 ) in qty_det_dt_5 , (abs(mod(serial_link , 6 )) = 5 ) in qty_det_dt_6 extent size 825000 next size 50000 lock mode row; On Tue, Nov 18, 2008 at 1:00 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Eric Rowell wrote: > > Yes. Man is each of the repeatable. Spent the last 2 months of my life > > like a hamster on a wheel duplicating this. No one looked at the > > sysindexes.clust until about a week and a half ago. All test since have > > been Great, and repeatable. > > OK, that's actually a good thing. :o) > > So, can you post up the schema (dbschema -ss ) of the table, and run me > through the way your job works? Just high level so I can get a feel for > what I'm looking at. > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

Eric Rowell wrote: > Below is the table. > > The problem with explaining the process is there are many of them that > affect this data and they appear to cause issues for the one selecting data. > > The process which is having the largest problem with performance only > selects data from this table using the serial_link (which is the key from > the parent table). This process opens a cursor and then processes each row > to determin if they are in another table and added them if needed. No > processes on the other table have issues and it's sysptprof stats don't > change during different parts of this process. Does the select that drives this process scan the entire table, or only a subset? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

The following is the explain plain for the only query in the program which isn't running correctly. The first is the plan from the normal run (filter data had to be changed to project my job). The info after that is from a diff of the explain outs for 2 more configurations. Also during all tests we used a production like system (smaller CPU and slower SAN) but we know the approx. modifier from prod to dev. When we sorted the data by the serial_link and reloaded (changing the fragmentation as shown earlier things run much better. EXPLAIN from normal run... QUERY: ------ SELECT qd.option_list, oq.optid FROM qty_head qh, qty_det qd, optcombo_qty oq WHERE ((qh.set_no = "FINDA" AND qh.version = "09") OR (qh.set_no = "ORFND" AND qh.version = "01")) AND qh.serial_key = qd.serial_link AND qd.serial_key = oq.option_group_id AND qh.dept_code IN ("AAA","BBB","CCC","DDD","EEE") AND qh.community = "00" Estimated Cost: 1049986 Estimated # of Rows Returned: 1083718 1) informix.qh: INDEX PATH (1) Index Keys: dept_code community set_no version phase_no lot area_group_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.qh.dept_code = 'AAA' AND informix.qh.community = '00' ) Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND informix.qh.version = '09') OR (informix.qh.set_no = 'ORFND' AND informix.qh.version = '01'))) (2) Index Keys: dept_code community set_no version phase_no lot area_group_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.qh.dept_code = 'BBB' AND informix.qh.community = '00' ) Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND informix.qh.version = '09') OR (informix.qh.set_no = 'ORFND' AND informix.qh.version = '01'))) (3) Index Keys: dept_code community set_no version phase_no lot area_group_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.qh.dept_code = 'CCC' AND informix.qh.community = '00' ) Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND informix.qh.version = '09') OR (informix.qh.set_no = 'ORFND' AND informix.qh.version = '01'))) (4) Index Keys: dept_code community set_no version phase_no lot area_group_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.qh.dept_code = 'DDD' AND informix.qh.community = '00' ) Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND informix.qh.version = '09') OR (informix.qh.set_no = 'ORFND' AND informix.qh.version = '01'))) (5) Index Keys: dept_code community set_no version phase_no lot area_group_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.qh.dept_code = 'EEE' AND informix.qh.community = '00' ) Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND informix.qh.version = '09' ) OR (informix.qh.set_no = 'ORFND' AND informix.qh.version = '01'))) 2) informix.qd: INDEX PATH (1) Index Keys: serial_link (Serial, fragments: ALL) Lower Index Filter: informix.qh.serial_key = informix.qd.serial_link NESTED LOOP JOIN 3) informix.optcombo_head: INDEX PATH (1) Index Keys: serial_key option_list option_count (Serial, fragments: ALL) Lower Index Filter: informix.qd.optcombo_head_key = informix.optcombo_head.serial_key NESTED LOOP JOIN 4) product.qty_detail: INDEX PATH (1) Index Keys: serial_key (Serial, fragments: ALL) Lower Index Filter: informix.qd.serial_key = product.qty_detail.serial_key NESTED LOOP JOIN 5) informix.oq: INDEX PATH (1) Index Keys: optcombo_head_key opt_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.oq.optcombo_head_key = product.qty_detail.optcombo_head_key NESTED LOOP JOIN Diff of Explain Plan after just reloading the table: < Estimated Cost: 1049986 < Estimated # of Rows Returned: 1083718 --- > Estimated Cost: 2348476 > Estimated # of Rows Returned: 1486409 Diff of Explain Plan after reloading the sorting data (using serial_link): < Estimated Cost: 1049986 < Estimated # of Rows Returned: 1083718 --- > Estimated Cost: 851019 > Estimated # of Rows Returned: 1094319 The cost difference appears to be in line with the change to the sysindex.clust value for the indexes. Thanks for your time. On Tue, Nov 18, 2008 at 1:03 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Obnoxio The Clown wrote: > > Eric Rowell wrote: > >> Yes. Man is each of the repeatable. Spent the last 2 months of my life > >> like a hamster on a wheel duplicating this. No one looked at the > >> sysindexes.clust until about a week and a half ago. All test since have > >> been Great, and repeatable. > > > > OK, that's actually a good thing. :o) > > > > So, can you post up the schema (dbschema -ss ) of the table, and run me > > through the way your job works? Just high level so I can get a feel for > > what I'm looking at. > > Oh, and I bet there's one nasty query in there that's causing all the > pain. Can you share that query and its explain plan, please? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

Eric Rowell wrote: > The following is the explain plain for the only query in the program which > isn't running correctly. The first is the plan from the normal run (filter > data had to be changed to project my job). The info after that is from a > diff of the explain outs for 2 more configurations. Also during all tests > we used a production like system (smaller CPU and slower SAN) but we know > the approx. modifier from prod to dev. When we sorted the data by the > serial_link and reloaded (changing the fragmentation as shown earlier things > run much better. > > EXPLAIN from normal run... > > QUERY: > ------ > SELECT qd.option_list, oq.optid > FROM qty_head qh, qty_det qd, optcombo_qty oq > WHERE ((qh.set_no = "FINDA" AND qh.version = "09") OR> > (qh.set_no = "ORFND" AND qh.version = "01")) > > AND qh.serial_key = qd.serial_link > > AND qd.serial_key = oq.option_group_id > > AND qh.dept_code IN ("AAA","BBB","CCC","DDD","EEE") > > AND qh.community = "00" > > Estimated Cost: 1049986 > Estimated # of Rows Returned: 1083718 > 1) informix.qh: INDEX PATH > > (1) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'AAA' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09') > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > > (2) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'BBB' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09') > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > > (3) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'CCC' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09') > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > > (4) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'DDD' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09') > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > > (5) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'EEE' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09' ) > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > 2) informix.qd: INDEX PATH > > (1) Index Keys: serial_link (Serial, fragments: ALL) > > Lower Index Filter: informix.qh.serial_key = informix.qd.serial_link > NESTED LOOP JOIN > 3) informix.optcombo_head: INDEX PATH > > (1) Index Keys: serial_key option_list option_count (Serial, > fragments: ALL) > > Lower Index Filter: informix.qd.optcombo_head_key = > informix.optcombo_head.serial_key > NESTED LOOP JOIN > 4) product.qty_detail: INDEX PATH > > (1) Index Keys: serial_key (Serial, fragments: ALL) > > Lower Index Filter: informix.qd.serial_key = > product.qty_detail.serial_key > NESTED LOOP JOIN > 5) informix.oq: INDEX PATH > > (1) Index Keys: optcombo_head_key opt_id (Key-Only) (Serial, > fragments: ALL) > > Lower Index Filter: informix.oq.optcombo_head_key = > product.qty_detail.optcombo_head_key > NESTED LOOP JOIN > > Diff of Explain Plan after just reloading the table: > < Estimated Cost: 1049986 > < Estimated # of Rows Returned: 1083718 > --- >> Estimated Cost: 2348476 >> Estimated # of Rows Returned: 1486409 > > Diff of Explain Plan after reloading the sorting data (using serial_link): > < Estimated Cost: 1049986 > < Estimated # of Rows Returned: 1083718 > --- >> Estimated Cost: 851019 >> Estimated # of Rows Returned: 1094319 > > The cost difference appears to be in line with the change to the > sysindex.clust value for the indexes. And which table do you query from to see if there are missing rows and insert them? Basically, I can't disagree with your analysis, I'm just curious as to why ordering should have such a measurable impact on performance given the explain plan. In general, it doesn't. I feel like there is something lurking in the nether hells of my brain, but I can't quite drag it out. Last time I saw something like this, the root cause was "XXXX in my opinion" but I can't remember what "XXXX" was. Give me time. Or Art will be along shortly. :o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Obnoxio The Clown wrote: > Basically, I can't disagree with your analysis, I'm just curious as to > why ordering should have such a measurable impact on performance given > the explain plan. In general, it doesn't. > > I feel like there is something lurking in the nether hells of my brain, > but I can't quite drag it out. Last time I saw something like this, the > root cause was "XXXX in my opinion" but I can't remember what "XXXX" was. > > Give me time. Or Art will be along shortly. :o) Oh. Any chance of your onconfig? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

This will be hard to look at without putting it in something to line it all up. The following is the stats form sysptprof for just this application running. These are just for the table in question. The other table involved are processed by a different loop of code and appears to have no difference in the sysptprof's captured at runtime. Base tabname partnum isreads bufreads pagreads ix_qty_det_pn_sl 112197634 0 0 0 ix_qty_det_sk 95420418 0 0 0 ix_qty_det_sl 95420419 52177249 56982064 22533 ix_qty_det_vi_ed_sl 112197635 0 0 0 qty_det 19922946 8661696 8661632 321953 qty_det 56623106 8635728 8635745 377128 qty_det 83886082 8656948 8656879 377073 qty_det 85983234 8648646 8648658 371842 qty_det 134217730 8649673 8649651 375125 qty_det 137363458 8667249 8667221 410169 Bad tabname partnum isreads bufreads pagreads ix_qty_det_pn_sl 112197634 0 0 0 ix_qty_det_sk 95420418 0 0 0 ix_qty_det_sl 95420419 52210032 55410714 11602 ix_qty_det_vi_ed_sl 112197635 0 0 0 qty_det 19922946 8522764 8527210 23631 qty_det 56623106 8339915 8344259 22995 qty_det 83886082 8642249 8647025 25673 qty_det 85983234 8368976 8371153 24633 qty_det 134217730 9862512 9864620 27256 qty_det 137363458 8185929 8192707 25085 Diff- Base vs. Bad tabname partnum isreads bufreads pagreads ix_qty_det_pn_sl 112197634 0 0 0 ix_qty_det_sk 95420418 0 0 0 ix_qty_det_sl 95420419 5785 61657 22553 ix_qty_det_vi_ed_sl 112197635 0 0 0 qty_det 19922946 5838 5930 1323271 qty_det 56623106 12784 12790 1360551 qty_det 83886082 -16823 -16727 1349929 qty_det 85983234 -14263 -14267 1362454 qty_det 134217730 17986 18054 1374809 qty_det 137363458 -3544 -3484 1386884 Good tabname partnum isreads bufreads pagreads ix_qty_det_pn_sl 112197634 0 0 0 ix_qty_det_sk 95420418 0 0 0 ix_qty_det_sl 95420419 52183034 57043721 45086 ix_qty_det_vi_ed_sl 112197635 0 0 0 qty_det 19922946 8667534 8667562 1645224 qty_det 56623106 8648512 8648535 1737679 qty_det 83886082 8640125 8640152 1727002 qty_det 85983234 8634383 8634391 1734296 qty_det 134217730 8667659 8667705 1749934 qty_det 137363458 8663705 8663737 1797053 Diff- Base vs. Good tabname partnum isreads bufreads pagreads ix_qty_det_pn_sl 112197634 0 0 0 ix_qty_det_sk 95420418 0 0 0 ix_qty_det_sl 95420419 32783 -1571350 -10931 ix_qty_det_vi_ed_sl 112197635 0 0 0 qty_det 19922946 -138932 -134422 -298322 qty_det 56623106 -295813 -291486 -354133 qty_det 83886082 -14699 -9854 -351400 qty_det 85983234 -279670 -277505 -347209 qty_det 134217730 1212839 1214969 -347869 qty_det 137363458 -481320 -474514 -385084 On Tue, Nov 18, 2008 at 3:01 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Eric Rowell wrote: > > The following is the explain plain for the only query in the program > which > > isn't running correctly. The first is the plan from the normal run > (filter > > data had to be changed to project my job). The info after that is from a > > diff of the explain outs for 2 more configurations. Also during all tests > > we used a production like system (smaller CPU and slower SAN) but we know > > the approx. modifier from prod to dev. When we sorted the data by the > > serial_link and reloaded (changing the fragmentation as shown earlier > things > > run much better. > > > > EXPLAIN from normal run... > > > > QUERY: > > ------ > > SELECT qd.option_list, oq.optid > > FROM qty_head qh, qty_det qd, optcombo_qty oq > > WHERE ((qh.set_no = "FINDA" AND qh.version = "09") OR> > > > (qh.set_no = "ORFND" AND qh.version = "01")) > > > > AND qh.serial_key = qd.serial_link > > > > AND qd.serial_key = oq.option_group_id > > > > AND qh.dept_code IN ("AAA","BBB","CCC","DDD","EEE") > > > > AND qh.community = "00" > > > > Estimated Cost: 1049986 > > Estimated # of Rows Returned: 1083718 > > 1) informix.qh: INDEX PATH > > > > (1) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'AAA' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (2) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'BBB' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (3) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'CCC' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (4) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'DDD' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (5) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'EEE' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09' ) > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > 2) informix.qd: INDEX PATH > > > > (1) Index Keys: serial_link (Serial, fragments: ALL) > > > > Lower Index Filter: informix.qh.serial_key = informix.qd.serial_link > > NESTED LOOP JOIN > > 3) informix.optcombo_head: INDEX PATH > > > > (1) Index Keys: serial_key option_list option_count (Serial, > > fragments: ALL) > > > > Lower Index Filter: informix.qd.optcombo_head_key = > > informix.optcombo_head.serial_key > > NESTED LOOP JOIN > > 4) product.qty_detail: INDEX PATH > > > > (1) Index Keys: serial_key (Serial, fragments: ALL) > > > > Lower Index Filter: informix.qd.serial_key = > > product.qty_detail.serial_key > > NESTED LOOP JOIN > > 5) informix.oq: INDEX PATH > > > > (1) Index Keys: optcombo_head_key opt_id (Key-Only) (Serial, > > fragments: ALL) > > > > Lower Index Filter: informix.oq.optcombo_head_key = > > product.qty_detail.optcombo_head_key > > NESTED LOOP JOIN > > > > Diff of Explain Plan after just reloading the table: > > < Estimated Cost

Any suggestions would be greatfully tested out... Since this system is very mixed and we don't have a testing tool it has been very hard to convince anyone to change anything. No the only difference with production is the names of the instance and "UNSECURE_ONSTAT" is not set. onstat -c IBM Informix Dynamic Server Version 10.00.FC8 -- On-Line -- Up 09:01:20 -- 4358208 Kbytes Configuration File: /usr/informix/etc/onconfig.DMS #************************************************************************** ## Licensed Material - Property Of IBM # # "Restricted Materials of IBM" # # IBM Informix Dynamic Server # (c) Copyright IBM Corporation 1996, 2005 All rights reserved. # # Title: onconfig.std # Description: IBM Informix Dynamic Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME root_dms_dt_1 # Root dbspace nameROOTPATH /ifx_devices/dms_vol01 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 524288 # Size of root dbspace (Kbytes)# Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes)# Physical Log Configuration PHYSDBS root_dms_dt_1 # Location (dbspace) of physical log PHYSFILE 250000 # Physical log file size (Kbytes)# Logical Log Configuration LOGFILES 8 # Number of logical log files LOGSIZE 15000 # Logical log size (Kbytes)LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT) # Tablespace Tablespace Configuration in Root Dbspace TBLTBLFIRST 0 # First extent size (Kbytes) (0 = default) TBLTBLNEXT 0 # Next extent size (Kbytes) (0 = default) # Security # DBCREATE_PERMISSION:# By default any user can create a database. Uncomment DBCREATE_PERMISSON to # limit database creation to a specific user. Add a new DBCREATE_PERMISSION # line for each permitted user. DBCREATE_PERMISSION informix # DB_LIBRARY_PATH: # When loading a (C or C++) shared object (for a UDR or UDT), IDS checks that # the user-specified path starts with one of the directory prefixes listed in # the comma-separated list of prefixes in DB_LIBRARY_PATH. The string # "$INFORMIXDIR/extend" must be included in DB_LIBRARY_PATH in order for # extensibility and IBM supplied blades to work correctly. # DB_LIBRARY_PATH $INFORMIXDIR/extend # IFX_EXTEND_ROLE: # 0 (or off) => Disable use of EXTEND role to control who can register # external routines. # 1 (or on) => Enable use of EXTEND role to control who can register # external routines. This is the default behaviour. # IFX_EXTEND_ROLE 1 # To control the usage of EXTEND role.# Diagnostics MSGPATH /usr/informix/log/online.DMS.log # System message log file path CONSOLE /dev/console # System console message path# To automatically backup logical logs, edit alarmprogram.sh and set # BACKUPLOGS=Y ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any event occur TBLSPACE_STATS 1 # Maintain tblspace statistics# System Archive Tape Device #TAPEDEV /dev/null # Tape device path TAPEDEV /dev/rmt0 # Tape device path TAPEBLK 32 # Tape block size (Kbytes) TAPESIZE 0 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device #LTAPEDEV /dev/null # Log tape device path LTAPEDEV /ifx_devices/logical_log_dms # Log tape device path LTAPEBLK 32 # Log tape block size (Kbytes) LTAPESIZE 0 # Max amount of data to put on log tape (Kbs) # OpticalSTAGEBLOB # Informix Dynamic Server staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME dms_shm # Name of default database serverDBSERVERALIASES dms,dms_dev2 # List of alternate dbservernames NETTYPE ipcshm,10,40,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,6,200,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 10 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps toone NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors# Shared Memory Parameters LOCKS 800000 # Maximum number of locks NUMAIOVPS 3 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes) CLEANERS 128 # Number of buffer cleaner processes SHMBASE 0x700000010000000L # Shared memory base address SHMVIRTSIZE 1048576 # initial virtual shared memory segment size SHMADD 51200 # Size of new shared memory segments (Kbytes) EXTSHMADD 51200 # Size of new extension shared memory segments (Kbytes) SHMTOTAL 5242880 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) TXTIMEOUT 300 # Transaction timeout (in sec) STACKSIZE 128# Dynamic Logging # DYNAMIC_LOGS: # 2 : server automatically add a new logical log when necessary. (ON) # 1 : notify DBA to add new logical logs when necessary. (ON) # 0 : cannot add logical log on the fly. (OFF) # # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM, # because the server can add new logical logs during long transaction rollback. # However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM # can be set to smaller values. # # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values # to avoid long transaction rollback hanging the server due to lack of logical # log space, i.e. 50/60 or lower. # # In case of system configured with CDR, the difference between LTXHWM and # LTXEHWM should be atleast 30% so that we could minimize log overrun issue. DYNAMIC_LOGS 1 LTXHWM 70 LTXEHWM 80# System Page Size # BUFFSIZE - OnLine no longer supports this configuration parameter. # To determine the page size used by OnLine on your platform # see the last line of output from the command, 'onstat -b'. # Recovery Variables # OFF_RECVRY_THREADS: # Number of parallel worker threads during fast recovery or an offline restore. # ON_RECVRY_THREADS: # Number of parallel worker threads during an online restore. OFF_RECVRY_THREADS 10 # Default number of offline worker threads ON_RECVRY_THREADS 1 # Default number of online worker threads# Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path DRIDXAUTO 0 # DR automatic index repair. 0=off, 1=on# CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 n

Eric Rowell wrote: > Any suggestions would be greatfully tested out... Since this system is very > mixed and we don't have a testing tool it has been very hard to convince > anyone to change anything. onstat -p onstat -g iov onstat -g iof onstat -g ioq -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Have you considered running PDQ with this process? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Have you looked at the read ahead parameters and utilizaiton? Are you using KAIO or not? How much of your buffer pool is occupied by the pages of this table and its indexes? At this point this feels like it might be that the buffer pages are being thrashed when the data is not pre-sorted (which the clustering accomplishes). I've seen that as the cause of similar symptoms in other applications. Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "Obnoxio The Clown" <obnoxio@serendipita.com> To: ids@iiug.org Date: 11/18/2008 03:08 PM Subject: Re: Question about sysindexes.clust [14015] Eric Rowell wrote: > The following is the explain plain for the only query in the program which > isn't running correctly. The first is the plan from the normal run (filter > data had to be changed to project my job). The info after that is from a > diff of the explain outs for 2 more configurations. Also during all tests > we used a production like system (smaller CPU and slower SAN) but we know > the approx. modifier from prod to dev. When we sorted the data by the > serial_link and reloaded (changing the fragmentation as shown earlier things > run much better. > > EXPLAIN from normal run... > > QUERY: > ------ > SELECT qd.option_list, oq.optid > FROM qty_head qh, qty_det qd, optcombo_qty oq > WHERE ((qh.set_no = "FINDA" AND qh.version = "09") OR> > (qh.set_no = "ORFND" AND qh.version = "01")) > > AND qh.serial_key = qd.serial_link > > AND qd.serial_key = oq.option_group_id > > AND qh.dept_code IN ("AAA","BBB","CCC","DDD","EEE") > > AND qh.community = "00" > > Estimated Cost: 1049986 > Estimated # of Rows Returned: 1083718 > 1) informix.qh: INDEX PATH > > (1) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'AAA' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09') > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > > (2) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'BBB' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09') > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > > (3) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'CCC' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09') > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > > (4) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'DDD' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09') > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > > (5) Index Keys: dept_code community set_no version phase_no lot > area_group_id > > (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.qh.dept_code = 'EEE' AND > informix.qh.community = '00' ) > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > informix.qh.version = '09' ) > > OR (informix.qh.set_no = 'ORFND' AND > informix.qh.version = '01'))) > 2) informix.qd: INDEX PATH > > (1) Index Keys: serial_link (Serial, fragments: ALL) > > Lower Index Filter: informix.qh.serial_key = informix.qd.serial_link > NESTED LOOP JOIN > 3) informix.optcombo_head: INDEX PATH > > (1) Index Keys: serial_key option_list option_count (Serial, > fragments: ALL) > > Lower Index Filter: informix.qd.optcombo_head_key = > informix.optcombo_head.serial_key > NESTED LOOP JOIN > 4) product.qty_detail: INDEX PATH > > (1) Index Keys: serial_key (Serial, fragments: ALL) > > Lower Index Filter: informix.qd.serial_key = > product.qty_detail.serial_key > NESTED LOOP JOIN > 5) informix.oq: INDEX PATH > > (1) Index Keys: optcombo_head_key opt_id (Key-Only) (Serial, > fragments: ALL) > > Lower Index Filter: informix.oq.optcombo_head_key = > product.qty_detail.optcombo_head_key > NESTED LOOP JOIN > > Diff of Explain Plan after just reloading the table: > < Estimated Cost: 1049986 > < Estimated # of Rows Returned: 1083718 > --- >> Estimated Cost: 2348476 >> Estimated # of Rows Returned: 1486409 > > Diff of Explain Plan after reloading the sorting data (using serial_link): > < Estimated Cost: 1049986 > < Estimated # of Rows Returned: 1083718 > --- >> Estimated Cost: 851019 >> Estimated # of Rows Returned: 1094319 > > The cost difference appears to be in line with the change to the > sysindex.clust value for the indexes. And which table do you query from to see if there are missing rows and insert them? Basically, I can't disagree with your analysis, I'm just curious as to why ordering should have such a measurable impact on performance given the explain plan. In general, it doesn't. I feel like there is something lurking in the nether hells of my brain, but I can't quite drag it out. Last time I saw something like this, the root cause was "XXXX in my opinion" but I can't remember what "XXXX" was. Give me time. Or Art will be along shortly. :o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I can provide you with a copy of the onstat -p but don't have the others. I will get them in future tests (I'm hoping we are almost done for now). On Tue, Nov 18, 2008 at 3:32 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Eric Rowell wrote: > > Any suggestions would be greatfully tested out... Since this system is > very > > mixed and we don't have a testing tool it has been very hard to convince > > anyone to change anything. > > onstat -p > onstat -g iov > onstat -g iof > onstat -g ioq> > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

I have concidered but it is done the long list of ideas provided from the whole of IT staff at this point. It's lovely being a DBA... On Tue, Nov 18, 2008 at 3:33 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Have you considered running PDQ with this process? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

Eric Rowell wrote: > I have concidered but it is done the long list of ideas provided from the > whole of IT staff at this point. It's lovely being a DBA... > > On Tue, Nov 18, 2008 at 3:33 PM, Obnoxio The Clown > <obnoxio@serendipita.com>wrote: > >> Have you considered running PDQ with this process? I reckon you've got a problem of some sort, which, if we fix, will stabilise performance somewhere NEAR to your current best. But PDQ could cut your run-time for this process by 50%. ;o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

I will try to copy this data in. The read ahead isn't 100% but never drops below 99.99% The following are the different run times, the calulcated BTR, Read Ahead Utilization, and Buffer Wait Ratio. The 9 hour run is the only bad run this in this data set. The good runs are all after basicly clustering the data. Runtime 5:47 9:54 4:56 4:45 4:50 4:46 BTR 7.46823 4.27814 8.56996 8.90136 8.74626 8.900555 RAU 99.9938% 99.9946% 99.9947% 99.9948% 99.9947% 99.9938% BR 0.6950% 0.4281% 0.3898% 0.3953% 0.3885% 0.3883% On Tue, Nov 18, 2008 at 3:37 PM, Richard Snoke <dsnoke@us.ibm.com> wrote: > Have you looked at the read ahead parameters and utilizaiton? Are you > using KAIO or not? How much of your buffer pool is occupied by the pages > of this table and its indexes? At this point this feels like it might be > that the buffer pages are being thrashed when the data is not pre-sorted > (which the clustering accomplishes). I've seen that as the cause of > similar symptoms in other applications. > > Dick Snoke > IBM Data Management - ChannelWorks > dsnoke@us.ibm.com > (404) 487-1595 > > From: > "Obnoxio The Clown" <obnoxio@serendipita.com> > To: > ids@iiug.org > Date: > 11/18/2008 03:08 PM > Subject: > Re: Question about sysindexes.clust [14015] > > Eric Rowell wrote: > > The following is the explain plain for the only query in the program > which > > isn't running correctly. The first is the plan from the normal run > (filter > > data had to be changed to project my job). The info after that is from a > > > diff of the explain outs for 2 more configurations. Also during all > tests > > we used a production like system (smaller CPU and slower SAN) but we > know > > the approx. modifier from prod to dev. When we sorted the data by the > > serial_link and reloaded (changing the fragmentation as shown earlier > things > > run much better. > > > > EXPLAIN from normal run... > > > > QUERY: > > ------ > > SELECT qd.option_list, oq.optid > > FROM qty_head qh, qty_det qd, optcombo_qty oq > > WHERE ((qh.set_no = "FINDA" AND qh.version = "09") OR> > > > (qh.set_no = "ORFND" AND qh.version = "01")) > > > > AND qh.serial_key = qd.serial_link > > > > AND qd.serial_key = oq.option_group_id > > > > AND qh.dept_code IN ("AAA","BBB","CCC","DDD","EEE") > > > > AND qh.community = "00" > > > > Estimated Cost: 1049986 > > Estimated # of Rows Returned: 1083718 > > 1) informix.qh: INDEX PATH > > > > (1) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'AAA' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (2) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'BBB' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (3) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'CCC' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (4) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'DDD' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (5) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'EEE' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09' ) > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > 2) informix.qd: INDEX PATH > > > > (1) Index Keys: serial_link (Serial, fragments: ALL) > > > > Lower Index Filter: informix.qh.serial_key = informix.qd.serial_link > > NESTED LOOP JOIN > > 3) informix.optcombo_head: INDEX PATH > > > > (1) Index Keys: serial_key option_list option_count (Serial, > > fragments: ALL) > > > > Lower Index Filter: informix.qd.optcombo_head_key = > > informix.optcombo_head.serial_key > > NESTED LOOP JOIN > > 4) product.qty_detail: INDEX PATH > > > > (1) Index Keys: serial_key (Serial, fragments: ALL) > > > > Lower Index Filter: informix.qd.serial_key = > > product.qty_detail.serial_key > > NESTED LOOP JOIN > > 5) informix.oq: INDEX PATH > > > > (1) Index Keys: optcombo_head_key opt_id (Key-Only) (Serial, > > fragments: ALL) > > > > Lower Index Filter: informix.oq.optcombo_head_key = > > product.qty_detail.optcombo_head_key > > NESTED LOOP JOIN > > > > Diff of Explain Plan after just reloading the table: > > < Estimated Cost: 1049986 > > < Estimated # of Rows Returned: 1083718 > > --- > >> Estimated Cost: 2348476 > >> Estimated # of Rows Returned: 1486409 > > > > Diff of Explain Plan after reloading the sorting data (using > serial_link): > > < Estimated Cost: 1049986 > > < Estimated # of Rows Returned: 1083718 > > --- > >> Estimated Cost: 851019 > >> Estimated # of Rows Returned: 1094319 > > > > The cost difference appears to be in line with the change to the > > sysindex.clust value for the indexes. > > And which table do you query from to see if there are missing rows and > insert them? > > Basically, I can't disagree with your analysis, I'm just curious as to > why ordering should have such a measurable impact on performance given > the explain plan. In general, it doesn't. > > I feel like there is something lurking in the nether hells of my brain, > but I can't quite drag it out. Last time I saw something like this, the > root cause was "XXXX in my opinion" but I can't remember what "XXXX" was. > > Give me time. Or Art will be along shortly. :o) > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum.@@NL

Only thing that seems to fix this is to cluster the data on the serial_link. I will have them try out the program with PDQ set to see if there is any difference... When I'm doing things for them it helps allot. Thanks for the input. Eric B. Rowell On Tue, Nov 18, 2008 at 3:45 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Eric Rowell wrote: > > I have concidered but it is done the long list of ideas provided from the > > whole of IT staff at this point. It's lovely being a DBA... > > > > On Tue, Nov 18, 2008 at 3:33 PM, Obnoxio The Clown > > <obnoxio@serendipita.com>wrote: > > > >> Have you considered running PDQ with this process? > > I reckon you've got a problem of some sort, which, if we fix, will > stabilise performance somewhere NEAR to your current best. > > But PDQ could cut your run-time for this process by 50%. ;o) > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

32-bit Informix? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

OK Clown, you asked for my analysis, you got it. There are: 69million rows rowsize ranges from 155 to 244 bytes (e to a varchar column) (8-12 rows fit per page on 2K pages) assume an average of 10 rows per page Estimated number of data pages: 7 million Estimated number of index pages for table qty_det for the selected index: 410,000 with FILLFACTOR = 100 FILLFACTOR = 50: 820,000 pages of index (168 keys per page: 2020/ (4byte key + 4byte rowid + 50% internal overhead)) Those facts in mind we can analyse what may be happening during this query before and after CLUSTERING or sorting the incoming data which accomplishes the same thing as long as the data load is single threaded. Searching the index for this table for the estimated 1,094,000 rows of table qty_head which match the selection criteria (I'm assuming that the optcombo_qty table adds little filter value since it is searched AFTER the join between qty_head and qty_det) would require reading from a minimum of 6,500 index pages for table qty_det if ALL 1.1million matching rows had sequential serial numbers to 820,000 pages if the serial values are evenly distributed across the 69 million value range. Then if the rows are not stored on the same data pages there may be as many as 1.1million data pages to be read (minimum ~100,000 pages). That would require a cache of at least 106,500 pages just to hold the data and index keys for this one table plus additional space for the other two tables in the query and for other queries - and a maximum of 2.2 million pages. Since originally the data was likely to have been fairly randomely distributed across the disks due to prior deletions and VARCHAR row relocations, the number of pages needed in cache will tend towards the upper limit of 2.2 million rather than the lower limit. I assume that the cache on this server is not in excess of 2.5 million pages. Now, if the rows are sorted into serial number order when loaded (or after CLUSTERING), then the data rows that we are most likely to be interested in (assuming we are interested mostly in the most recent rows added to the table) are all contiguous on disk as are their keys in the index. That means the number of disk pages needed to be read into cache (and the chances that a page needed for more than one row has to be read more than once due to timing out of the cache during the search) are minimized to only 106,500 pages as compared to the worst case - that the rows we are interested in are scattered all over the disk due to the data not being sorted. Add to this the possibility that since these rows contain a VARCHAR column that may have grown over time, there may be many of these rows that required two IOs to fetch into memory not one before any reorganization. Now the actual timings show that the actual distribution of data on disk was far from worst case, but also far worse that the best case. So, back to the original question: Do we watch the sysindices/sysindexes.cluster column to determine when a table should be reorganized? Honestly and embarrasedly, no. Most of us have fallen away from that particular optimization. I used to look at this in 4.0 and 5.0 days, but have not concerned with it for many years. One reason is that it is a problem that tends to affect a small subset of queries (indeed in your case it was killing only one particular SELECT statement) so it is one of the last things I check for if I cannot improve a stubborn query any other way. Also, other reorg opportunities tend to minimize the impact of this problem as we try to minimize the number of extents in very large tables. Art On Tue, Nov 18, 2008 at 3:01 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Eric Rowell wrote: > > The following is the explain plain for the only query in the program > which > > isn't running correctly. The first is the plan from the normal run > (filter > > data had to be changed to project my job). The info after that is from a > > diff of the explain outs for 2 more configurations. Also during all tests > > we used a production like system (smaller CPU and slower SAN) but we know > > the approx. modifier from prod to dev. When we sorted the data by the > > serial_link and reloaded (changing the fragmentation as shown earlier > things > > run much better. > > > > EXPLAIN from normal run... > > > > QUERY: > > ------ > > SELECT qd.option_list, oq.optid > > FROM qty_head qh, qty_det qd, optcombo_qty oq > > WHERE ((qh.set_no = "FINDA" AND qh.version = "09") OR> > > > (qh.set_no = "ORFND" AND qh.version = "01")) > > > > AND qh.serial_key = qd.serial_link > > > > AND qd.serial_key = oq.option_group_id > > > > AND qh.dept_code IN ("AAA","BBB","CCC","DDD","EEE") > > > > AND qh.community = "00" > > > > Estimated Cost: 1049986 > > Estimated # of Rows Returned: 1083718 > > 1) informix.qh: INDEX PATH > > > > (1) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'AAA' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (2) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'BBB' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (3) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'CCC' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (4) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'DDD' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09') > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > > > (5) Index Keys: dept_code community set_no version phase_no lot > > area_group_id > > > > (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.qh.dept_code = 'EEE' AND > > informix.qh.community = '00' ) > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > informix.qh.version = '09' ) > > > > OR (informix.qh.set_no = 'ORFND' AND > > informix.qh.version = '01'))) > > 2) informix.qd: INDEX PATH > > > > (1) Index Keys

Art Kagel wrote: > Add to this the possibility that since these rows contain a VARCHAR column > that may have grown over time, there may be many of these rows that required > two IOs to fetch into memory not one before any reorganization. Now the > actual timings show that the actual distribution of data on disk was far > from worst case, but also far worse that the best case. There seem to be three cases: 1. As we are 2. Unsorted fresh load 3. Sorted fresh load Performance seems to be: 1. Sorted fresh load 2. As we are 3. Unsorted fresh load For this reason, I'm inclined to think that varchar growth over time isn't a significant issue. Still something niggling. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Just out of idle curiosity, do you have stats at the disk level for the number of I/O's taking place during each iteration of these tests? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

64bit. On Tue, Nov 18, 2008 at 3:54 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > 32-bit Informix? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

No but we can in a future run. On Tue, Nov 18, 2008 at 4:52 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Just out of idle curiosity, do you have stats at the disk level for the > number of I/O's taking place during each iteration of these tests? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

Eric Rowell wrote: > No but we can in a future run. > > On Tue, Nov 18, 2008 at 4:52 PM, Obnoxio The Clown > <obnoxio@serendipita.com>wrote: > >> Just out of idle curiosity, do you have stats at the disk level for the >> number of I/O's taking place during each iteration of these tests? I'd be interested to see if the slow runs are doing significantly more IO or whether it is something internal to the engine. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

Based on the sysptprof they are a very large amount of page reads on the main table which doesn't happen after clustiner the data. On Tue, Nov 18, 2008 at 5:08 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Eric Rowell wrote: > > No but we can in a future run. > > > > On Tue, Nov 18, 2008 at 4:52 PM, Obnoxio The Clown > > <obnoxio@serendipita.com>wrote: > > > >> Just out of idle curiosity, do you have stats at the disk level for the > >> number of I/O's taking place during each iteration of these tests? > > I'd be interested to see if the slow runs are doing significantly more > IO or whether it is something internal to the engine. > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

Art, Thank you for your analysis we came up with the same thing but backed into this via watching the page reads on the table during the applications run. We happened to find this as a production issue when trying to normalize this table in development so that the large highly repeated varchar coud be moved to another table. What was odd to me was that the Buffer Turn Over, Buffer Wait, and Read Ahead Utilization all doesn't appear to change for the worse, just the runtime and page reads (note we don't report on total page reads until now since day to day useage is different). I was embarrased at first about not monitoring this value but the more people tell me that they don't watch this value the more I understand it is not do to my own lack of trying. It appears that most people don't look at the sysindexes.clust value since like any go DB/SA data is moved around and reloaded for other reasons as a course of normal business. We too haven't run into this because we normally migration to new hardware every 3 years and about every 1 1/2 year find a reason to reload this table. At the end of last year we should have looked at reloading some tables since the migration to new hardware wasn't required. This would be way we haven't seen this before. It's now time for us to come up with some matrix to monitor this over time to see when this value indicates the need to reload or cluster a table just incase for the future. But it looks like a table by table a different value wouldn't indicate this need. Thanks again for all that replied, Eric B. Rowell On Tue, Nov 18, 2008 at 4:28 PM, Art Kagel <art.kagel@gmail.com> wrote: > OK Clown, you asked for my analysis, you got it. There are: > > 69million rows > rowsize ranges from 155 to 244 bytes (e to a varchar column) (8-12 rows fit > per page on 2K pages) assume an average of 10 rows per page > Estimated number of data pages: 7 million > Estimated number of index pages for table qty_det for the selected index: > 410,000 with FILLFACTOR = 100 > > FILLFACTOR = 50: 820,000 pages of index (168 keys per page: 2020/ > (4byte key + 4byte rowid + 50% internal overhead)) > > Those facts in mind we can analyse what may be happening during this query > before and after CLUSTERING or sorting the incoming data which accomplishes > the same thing as long as the data load is single threaded. > > Searching the index for this table for the estimated 1,094,000 rows of > table > qty_head which match the selection criteria (I'm assuming that the > optcombo_qty table adds little filter value since it is searched AFTER the > join between qty_head and qty_det) would require reading from a minimum of > 6,500 index pages for table qty_det if ALL 1.1million matching rows had > sequential serial numbers to 820,000 pages if the serial values are evenly > distributed across the 69 million value range. Then if the rows are not > stored on the same data pages there may be as many as 1.1million data pages > to be read (minimum ~100,000 pages). That would require a cache of at least > 106,500 pages just to hold the data and index keys for this one table plus > additional space for the other two tables in the query and for other > queries > - and a maximum of 2.2 million pages. Since originally the data was likely > to have been fairly randomely distributed across the disks due to prior > deletions and VARCHAR row relocations, the number of pages needed in cache > will tend towards the upper limit of 2.2 million rather than the lower > limit. I assume that the cache on this server is not in excess of 2.5 > million pages. > > Now, if the rows are sorted into serial number order when loaded (or after > CLUSTERING), then the data rows that we are most likely to be interested in > (assuming we are interested mostly in the most recent rows added to the > table) are all contiguous on disk as are their keys in the index. That > means the number of disk pages needed to be read into cache (and the > chances > that a page needed for more than one row has to be read more than once due > to timing out of the cache during the search) are minimized to only 106,500 > pages as compared to the worst case - that the rows we are interested in > are > scattered all over the disk due to the data not being sorted. > > Add to this the possibility that since these rows contain a VARCHAR column > that may have grown over time, there may be many of these rows that > required > two IOs to fetch into memory not one before any reorganization. Now the > actual timings show that the actual distribution of data on disk was far > from worst case, but also far worse that the best case. > > So, back to the original question: Do we watch the > sysindices/sysindexes.cluster column to determine when a table should be > reorganized? Honestly and embarrasedly, no. Most of us have fallen away > from that particular optimization. I used to look at this in 4.0 and 5.0 > days, but have not concerned with it for many years. One reason is that it > is a problem that tends to affect a small subset of queries (indeed in your > case it was killing only one particular SELECT statement) so it is one of > the last things I check for if I cannot improve a stubborn query any other > way. Also, other reorg opportunities tend to minimize the impact of this > problem as we try to minimize the number of extents in very large tables. > > Art > > On Tue, Nov 18, 2008 at 3:01 PM, Obnoxio The Clown > <obnoxio@serendipita.com>wrote: > > > Eric Rowell wrote: > > > The following is the explain plain for the only query in the program > > which > > > isn't running correctly. The first is the plan from the normal run > > (filter > > > data had to be changed to project my job). The info after that is from > a > > > diff of the explain outs for 2 more configurations. Also during all > tests > > > we used a production like system (smaller CPU and slower SAN) but we > know > > > the approx. modifier from prod to dev. When we sorted the data by the > > > serial_link and reloaded (changing the fragmentation as shown earlier > > things > > > run much better. > > > > > > EXPLAIN from normal run... > > > > > > QUERY: > > > ------ > > > SELECT qd.option_list, oq.optid > > > FROM qty_head qh, qty_det qd, optcombo_qty oq > > > WHERE ((qh.set_no = "FINDA" AND qh.version = "09") OR> > > > > > (qh.set_no = "ORFND" AND qh.version = "01")) > > > > > > AND qh.serial_key = qd.serial_link > > > > > > AND qd.serial_key = oq.option_group_id > > > > > > AND qh.dept_code IN ("AAA","BBB","CCC","DDD","EEE") > > > > > > AND qh.community = "00" > > > > > > Estimated Cost: 1049986 > > > Estimated # of Rows Returned: 1083718 > > > 1) informix.qh: INDEX PATH > > > > > > (1) Index Keys: dept_code community set_no version phase_no lot > > > area_group_id > > > > > > (Key-First) (Serial, fragments: ALL) > > > > > > Lower Index Filter: (informix.qh.dept_code = 'AAA' AND > > > informix.qh.community = '00' ) > > > > > > Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND > > > informix.q

Eric, Thinking on this some more in my sleep, I realized that even though there are some 69MM rows in the qty_det table I can't imagine why this query even takes 4+ hours to complete at its best. Several items to consider going forward: - I remembered that there are 5 index accesses on the same index on the qty_head and that all five are doing sub-key scans between the OR'd value pairs of set_no and version. Breaking that OR into a UNION of two queries will instead allow separate equality searches on those two keys along with the two higher level keys which lead the index. The engine is doing this internally to satisfy the IN clause on dept_code - hence the 5 independent accesses to the index - but it cannot do so to satisfy the OR clause on two columns. You'll have to resolve that yourself (you might even test deconstructing this query into 10 UNIONED SELECT statements to resolve the OR and the IN clauses into ten equality searches. Only testing will tell which strategy is best. I always say there are at least three ways to formulate ANY SELECT query. In your case there are at least eleven. - No data is returned from qty_head so if that index also contained the serial_key column access to this table would not require reading any data pages and could be performed key-only rather than key-first. This simple change would remove at least half and likely >90% of the IOs on this table. Also, the added column may allow you to mark an otherwise duplicate index as unique which generates a more efficient structure on disk. So, append serial_key to this important join index. - I see SERIAL FRAGMENT ALL for all three tables. Are the tables fragmented? If not, perhaps they should be, certainly the two larger tables. If they are, perhaps you should revisit the fragmentation scheme to permit fragment elimination. Either way this query may benefit, as the Clown suggested, from running under positive PDQPRIORITY to at least enable fragment elimination and perhaps parallel searching of multiple fragments. - What are the underlying disk structures that these IOs are so slow? In 4.5 hours you could read the entire qty_det table at a rate of only 99 IOs per second! Any decent disk worth its salt should be able to sustain close to 10 times that rate and that's not even considering the multiplier of any multi-spindle RAID implementation under the hood. Something is NOT RIGHT here. - What are the underlying disk structures (single spindle, RAID5, RAID1, RAID10, ???) - Is there perhaps a problem with the writeback cache on the disk array or SAN? A disabled cache can slow down a fast array by an order of magnitude. - Are you using RAW or COOKED chunks? - Is the engine using KAIO for the chunk IO? - Platform and OS and IDS versions? Art On Wed, Nov 19, 2008 at 9:28 AM, Eric Rowell <erowell@gmail.com> wrote: > Art, > > Thank you for your analysis we came up with the same thing but backed > into this via watching the page reads on the table during the applications > run. We happened to find this as a production issue when trying to > normalize this table in development so that the large highly repeated > varchar coud be moved to another table. > > What was odd to me was that the Buffer Turn Over, Buffer Wait, and Read > Ahead Utilization all doesn't appear to change for the worse, just the > runtime and page reads (note we don't report on total page reads until now > since day to day useage is different). > > I was embarrased at first about not monitoring this value but the more > people tell me that they don't watch this value the more I understand it is > not do to my own lack of trying. It appears that most people don't look at > the sysindexes.clust value since like any go DB/SA data is moved around and > reloaded for other reasons as a course of normal business. We too haven't > run into this because we normally migration to new hardware every 3 years > and about every 1 1/2 year find a reason to reload this table. At the end > of last year we should have looked at reloading some tables since the > migration to new hardware wasn't required. This would be way we haven't > seen this before. > > It's now time for us to come up with some matrix to monitor this over > time to see when this value indicates the need to reload or cluster a table > just incase for the future. But it looks like a table by table a different > value wouldn't indicate this need. > > Thanks again for all that replied, > > Eric B. Rowell > > <Previous entries SNIPPED> > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.