UPDATE STATISTICS low in parallel?

Hi, This has been bothering me for a while but has until now been a low priority. I am using a special build of 11.70.FC7W1 but the feature has been there since at least 11.50. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.perf.doc/i ds_prf_638.htm According to the above section of the manual UPDATE STATISTICS LOW can work in parallel when: - PDQPRIORITY is set. - Indices are fragmented. The documented talks about an "extremely large" database: I am not sure if this is a requirement or is just a suggested situation in which it might be useful. I presume the latter. Anyway the database as a whole I am working with is in excess of 10 Tb. The document is also vague about whether it is the actual PDQ_PRIORITY that matters or the effective one, i.e. what is asked for reduced by MAX_PDQPRIORITY. Try as I might, I cannot see any evidence of parallelism when the above conditions are met. While my session running UPDATE STATISTICS LOW has a non-zero PDQ value, it has one thread, there are no threads running I can't account for and the memory grant manager shows no PDQ queries. The table I am testing with has 1,253,635,127 rows, uses 7691017 8 kb pages and is partitioned by round-robin 8 ways. It has three indices on a single column each: - serial column, fragmented 8 ways by mod(value). - int column, fragmented 8 ways by mod(value). - datetime column, fragmented 12 ways by month(value). I feel this meets the criteria. I have also tried with and without USTLOW_SAMPLE set. Is it just a bug or documentation bug? Ben.

Hi Ben, > On 10 Apr 2017, at 17:00, BENJAMIN THOMPSON <benjamin.thompson@skybettingandgaming.com> wrote: > > Hi, > > This has been bothering me for a while but has until now been a low priority. > I am using a special build of 11.70.FC7W1 but the feature has been there since > at least 11.50. > > > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.perf.doc/i ds_prf_638.htm > > According to the above section of the manual UPDATE STATISTICS LOW can work in > parallel when: > > - PDQPRIORITY is set. > - Indices are fragmented. > > The documented talks about an "extremely large" database: I am not sure if > this is a requirement or is just a suggested situation in which it might be > useful. I presume the latter. Anyway the database as a whole I am working with > is in excess of 10 Tb. > > The document is also vague about whether it is the actual PDQ_PRIORITY that > matters or the effective one, i.e. what is asked for reduced by > MAX_PDQPRIORITY. > > Try as I might, I cannot see any evidence of parallelism when the above > conditions are met. While my session running UPDATE STATISTICS LOW has a > non-zero PDQ value, it has one thread, there are no threads running I can't > account for and the memory grant manager shows no PDQ queries. > > The table I am testing with has 1,253,635,127 rows, uses 7691017 8 kb pages > and is partitioned by round-robin 8 ways. It has three indices on a single > column each: > > - serial column, fragmented 8 ways by mod(value). > - int column, fragmented 8 ways by mod(value). > - datetime column, fragmented 12 ways by month(value). > > I feel this meets the criteria. I have also tried with and without > USTLOW_SAMPLE set.> > Is it just a bug or documentation bug? Ive seen similar behaviour on some versions. It definitely used to help, but something changed and I never really got to the bottom of it. Just out idle curiosity, what are your DS_* values set to in your onconfig? Spokey

Hi Spokey, Here are my DS_ parameters DS_MAX_QUERIES 2 DS_TOTAL_MEMORY 8388608 DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 32768 I can build new indices in parallel, no problem. Ben.

Ben, Very low chance this helps you, but you might want to look and see if DBUPSPACE environment variable has anything to do with turning this on/off. Maybe also see if setting/unsetting BATCHEDREAD_INDEX has any effect on this behavior. Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJAMIN THOMPSON Sent: Monday, April 10, 2017 11:00 AM To: ids@iiug.org Subject: UPDATE STATISTICS low in parallel? [38886] Hi, This has been bothering me for a while but has until now been a low priority. I am using a special build of 11.70.FC7W1 but the feature has been there since at least 11.50. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.perf.d oc/ids_prf_638.htm According to the above section of the manual UPDATE STATISTICS LOW can work in parallel when: - PDQPRIORITY is set. - Indices are fragmented. The documented talks about an "extremely large" database: I am not sure if this is a requirement or is just a suggested situation in which it might be useful. I presume the latter. Anyway the database as a whole I am working with is in excess of 10 Tb. The document is also vague about whether it is the actual PDQ_PRIORITY that matters or the effective one, i.e. what is asked for reduced by MAX_PDQPRIORITY. Try as I might, I cannot see any evidence of parallelism when the above conditions are met. While my session running UPDATE STATISTICS LOW has a non-zero PDQ value, it has one thread, there are no threads running I can't account for and the memory grant manager shows no PDQ queries. The table I am testing with has 1,253,635,127 rows, uses 7691017 8 kb pages and is partitioned by round-robin 8 ways. It has three indices on a single column each: - serial column, fragmented 8 ways by mod(value). - int column, fragmented 8 ways by mod(value). - datetime column, fragmented 12 ways by month(value). I feel this meets the criteria. I have also tried with and without USTLOW_SAMPLE set. Is it just a bug or documentation bug? Ben. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Andrew, I played around with DBUPSPACE (my usual value is "0:50:0") by turning on indices for sorting but this only normally affects MEDIUM and HIGH modes and didn't have any effect here. I also didn't have any luck with BATCHEDREAD_INDEX. I am happy to try long shots to make sure I am not doing anything stupid :) Another thing I would experiment with - if I could actually get it working - is whether all indices on the table need to be fragmented or not, as the manual is somewhat vague on this point too. Ben.

Hi Ben, > On 10 Apr 2017, at 17:47, BENJAMIN THOMPSON <benjamin.thompson@skybettingandgaming.com> wrote: > > Hi Andrew, > > I played around with DBUPSPACE (my usual value is "0:50:0") by turning on > indices for sorting but this only normally affects MEDIUM and HIGH modes and > didn't have any effect here. I also didn't have any luck with > BATCHEDREAD_INDEX. I am happy to try long shots to make sure I am not doing > anything stupid :) > > Another thing I would experiment with - if I could actually get it working - > is whether all indices on the table need to be fragmented or not, as the > manual is somewhat vague on this point too. I doubt this is an issue, as USL works with the data, as far as I know. So the data would have to be fragmented.

In the early versions of update statistics low it read the entire index with a single thread in pagesize reads. In the parallel model it still reads the entire index, but does each index fragment at the same time in large buffers. This had two advantages 1/10 the number of I/O operations and reading in parallel. This requires a PDQPRIORITY of 10 be set during update stats low. If you have 100 index fragments and you want only 10 (i.e. 10%) being scanned at one time then set PDQPRIORITY at 1, if you wanted 20 (i.e 20%) being scanned then set PDQPRIORITY to 2. There was then a better improvement which was low sampling with self checking. This would sample random pages of the index based on the indexes layout. It would then check the sample and if the sample was not within tolerance the routine would sample a few more pages. This would often mean reading less than 1% of the index a great reduction in the total I/O caused by update statistics low. This method is only used for large indexes, as small index are just read in their entirety. John F. Miller III miller3@us.ibm.com 503-747-1366 ids-bounces@iiug.org wrote on 04/10/2017 09:47:43 AM: > From: "BENJAMIN THOMPSON" <benjamin.thompson@skybettingandgaming.com> > To: ids@iiug.org > Date: 04/10/2017 09:48 AM > Subject: Re: RE: UPDATE STATISTICS low in parallel? [38890] > Sent by: ids-bounces@iiug.org > > Hi Andrew, > > I played around with DBUPSPACE (my usual value is "0:50:0") by turning on > indices for sorting but this only normally affects MEDIUM and HIGH modes and > didn't have any effect here. I also didn't have any luck with > BATCHEDREAD=5FINDEX. I am happy to try long shots to make sure I am not doing > anything stupid :) > > Another thing I would experiment with - if I could actually get it working - > is whether all indices on the table need to be fragmented or not, as the > manual is somewhat vague on this point too. > > Ben. > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >

Original post: Hi, This has been bothering me for a while but has until now been a low priority. I am using a special build of 11.70.FC7W1 but the feature has been there since at least 11.50. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.perf.doc/i ds_prf_638.htm According to the above section of the manual UPDATE STATISTICS LOW can work in parallel when: - PDQPRIORITY is set. - Indices are fragmented. The documented talks about an "extremely large" database: I am not sure if this is a requirement or is just a suggested situation in which it might be useful. I presume the latter. Anyway the database as a whole I am working with is in excess of 10 Tb. The document is also vague about whether it is the actual PDQ_PRIORITY that matters or the effective one, i.e. what is asked for reduced by MAX_PDQPRIORITY. Try as I might, I cannot see any evidence of parallelism when the above conditions are met. While my session running UPDATE STATISTICS LOW has a non-zero PDQ value, it has one thread, there are no threads running I can't account for and the memory grant manager shows no PDQ queries. The table I am testing with has 1,253,635,127 rows, uses 7691017 8 kb pages and is partitioned by round-robin 8 ways. It has three indices on a single column each: - serial column, fragmented 8 ways by mod(value). - int column, fragmented 8 ways by mod(value). - datetime column, fragmented 12 ways by month(value). I feel this meets the criteria. I have also tried with and without USTLOW_SAMPLE set. Is it just a bug or documentation bug? Ben. Response: What are you actually setting PDQPRIORITY to, or what values have you tried? I was looking at the code and I also think it isn't affected by MAX_PDQPRIORITY, but I will admit I might have missed that (I didn't look super close at if anything might have affected the value of where the code is looking for the value). But the code that decides how many threads to start up is affected by what value you have set for PDQPRIORITY and the number of fragments the index is split over. It looks like you have 8 fragments for your index. To get more then 1 thread to scan the index fragments in this case, I think you would need to have PDQPRIORITY set to at least 3. I guess I could assume that you tried setting PDQPRIORITY to 10 like the link you provided, but I'd like confirmation. The only other thing I can think of is, it's somehow not getting into the piece of code that tries to do the update stats in parallel, but the only things that should be preventing that is that if the index isn't fragmented, it's not an index on a user defined type and some memory structures can get allocated and set up. Jacques Renaut IBM Informix Adavanced Support

Specify only the columns of a single index.... just for the sake of science. If it works you can then decide what's faster. In any case... sampling without bugs, is a good choice. On Apr 10, 2017 18:48, "BENJAMIN THOMPSON" < benjamin.thompson@skybettingandgaming.com> wrote: Hi Andrew, I played around with DBUPSPACE (my usual value is "0:50:0") by turning on indices for sorting but this only normally affects MEDIUM and HIGH modes and didn't have any effect here. I also didn't have any luck with BATCHEDREAD_INDEX. I am happy to try long shots to make sure I am not doing anything stupid :) Another thing I would experiment with - if I could actually get it working - is whether all indices on the table need to be fragmented or not, as the manual is somewhat vague on this point too. Ben. ************************************************************ ******************* Forum Note: Use "Reply" to post a response in the discussion forum. --94eb2c05a2b6b53e73054cd51a68

Original post: Hi, This has been bothering me for a while but has until now been a low priority. I am using a special build of 11.70.FC7W1 but the feature has been there since at least 11.50. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.perf.doc/i ds_prf_638.htm According to the above section of the manual UPDATE STATISTICS LOW can work in parallel when: - PDQPRIORITY is set. - Indices are fragmented. The documented talks about an "extremely large" database: I am not sure if this is a requirement or is just a suggested situation in which it might be useful. I presume the latter. Anyway the database as a whole I am working with is in excess of 10 Tb. The document is also vague about whether it is the actual PDQ_PRIORITY that matters or the effective one, i.e. what is asked for reduced by MAX_PDQPRIORITY. Try as I might, I cannot see any evidence of parallelism when the above conditions are met. While my session running UPDATE STATISTICS LOW has a non-zero PDQ value, it has one thread, there are no threads running I can't account for and the memory grant manager shows no PDQ queries. The table I am testing with has 1,253,635,127 rows, uses 7691017 8 kb pages and is partitioned by round-robin 8 ways. It has three indices on a single column each: - serial column, fragmented 8 ways by mod(value). - int column, fragmented 8 ways by mod(value). - datetime column, fragmented 12 ways by month(value). I feel this meets the criteria. I have also tried with and without USTLOW_SAMPLE set. Is it just a bug or documentation bug? Ben. Response: I've looked at the code further and done a bit of a code walk through myself, and I believe this is a defect. It looks like if you have AUTO_STAT_MODE set to 0, there is no way to get update stats low to parallelize the scan of a fragmented index. So my guess is you have AUTO_STAT_MODE set to 0 so you'll have no chance to get pdq to scan the index fragments in parallel. Just note, that if you do turn it on, then for the update stats low to do anything you would need to change enough stuff in each fragment to get it to get looked at. I would open a pmr for this to report the problem. Jacques Renaut IBM Informix Advanced Support

I don't think this is true: I am pretty sure it scans the indices and can see this with 'onstat -X' or 'onstat -g ppf'. If you try to get it to update a non-indexed column it completes very quickly indeed and appears to just update nrows in systables, possibly some other things too: I haven't thoroughly checked. This is described here: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0039.htm "The LOW mode generates the least amount of information about the column. If you want the UPDATE STATISTICS statement to do minimal work, specify a column that is not part of an index. The colmax and colmin values in syscolumns are not updated unless there is an index on the column." Ben.

Thanks John for the explanation. The problem I have is that for more than half of the indices on our largest tables skew is detected and it takes a very long time for UPDATE STATISTICS LOW to run. This is why I am looking at the feasibility of the parallel method. Ben.

Hi Jacques, Thank you for your reply. It's interesting that AUTO_STAT_MODE comes into it at all. I actually have AUTO_STAT_MODE=1 but because I am using a test instance and repeatedly testing I am using "UPDATE STATISTICS... FORCE". I'll experiment with single column updates (thanks Fernando) and actually insert some new rows so I don't need FORCE. For PDQ values I am using 20 but did also try 10 and 100. Realistically, as we use AUS, whatever value goes here needs to be set in AUS_PDQ in ph_threshold and so needs to be appropriate for UPDATE STATISTICS MEDIUM/HIGH too. Unless we do something separately of course. Ben.

Original post: Hi Jacques, Thank you for your reply. It's interesting that AUTO_STAT_MODE comes into it at all. I actually have AUTO_STAT_MODE=1 but because I am using a test instance and repeatedly testing I am using "UPDATE STATISTICS... FORCE". I'll experiment with single column updates (thanks Fernando) and actually insert some new rows so I don't need FORCE. For PDQ values I am using 20 but did also try 10 and 100. Realistically, as we use AUS, whatever value goes here needs to be set in AUS_PDQ in ph_threshold and so needs to be appropriate for UPDATE STATISTICS MEDIUM/HIGH too. Unless we do something separately of course. Ben. Response: So for PDQ, you should just need to use 1 - 10. 10, or anything greater, should do all fragments at once (assuming all fragments have enough change in them to warrant the update stats to look at them), and numbers less then 10 try to do that percent of fragments at a time, but we are using integer math so if you have 8 fragments and used 2 you would end up with 2/10 * 8 = 1.6. Since we treat that as an int, I believe that would then go down to 1. So in that case, to get more then 1 fragment at a time you would need to at least use a PDQ value of 3. Also, I did verify that MAX_PDQPRIORITY should not affect this. From the code, if you use FORCE in the update statistics command, it looks like the variable that was causing problems gets set to the same value as when AUTO_STAT_MODE was set to 0 (or off). So FORCE in this sense is going to have the same affect in this area as running update stats low and having AUTO_STAT_MODE set to 0. So for you to see the parallelism, you are going to have to not use FORCE (so you will have to do enough changes to more then 1 fragment so that multiple threads will get kicked off). Jacques Renaut IBM Informix Advanced Support

Hi Jacques, I coded in some inserts that touched all fragments across all three indices and removed FORCE. I have "AUTO_STAT_MODE 1" and "STATCHANGE 0" in the onconfig. I still can't get it to run with PDQ. I have done some more tests just in case I am being really dumb and as far as I can tell the PDQPRIORITY value in the session makes no difference. I can even see each fragment of the index being worked on in turn by monitoring with "onstat -g ppf". I also tried a few last ditch things such as a 12.10.FC8W1 upgrade and removing PDQPRIORITY=0 from the server's environment at start up which did not help. As no-one else has responded saying this is working for them I think I will raise a PMR as you suggested. Thank you for your insight. Ben.

Hi, Thanks to help from Jacques I got this working in a test system. Performance is excellent compared with single threaded performance. I can confirm that it appears to need in addition to what is in the manual, i.e. fragmented indices: - AUTO_STAT_MODE must be 1. - STATCHANGE must be greater than 0. - does not work with the FORCE option regardless of the above. - appears to be trumped by USTLOW_SAMPLE. Positive confirmation is difficult. You need genuine change on the table to trigger STATCHANGE and, as it is very quick, an empty buffer cache and intensive onstat collection to see what is going on. I haven't yet been able to test what happens if all criteria are met except that USTLOW_SAMPLE is on and skew is detected. Threads look like this: tid name rstcb flags curstk status 411 sqlexec 3a4d202108 ---P--- 17952 join wait 412 - 412 updstat 3a48e16608 ----R-- 8032 running- 413 updstat 3a4d201068 B---R-- 8032 yield bufwait- 414 updstat 3a48e1c178 B---R-- 8032 ready- 415 updstat 3a4d202958 ----R-- 8032 running- 416 updstat 3a4d2031a8 ----R-- 8032 running- 417 updstat 3a4d2039f8 B---R-- 8032 yield bufwait- 418 updstat 3a4d204248 ----R-- 8032 running- 419 updstat 3a4d204a98 B---R-- 8032 yield bufwait- I wrote a blog post a while ago explaining why non-zero values of STATCHANGE can leave your distributions too old to be useful to the optimiser. Ben.

Or force the update statistics to run even if the data is not yet stale by adding the FORCE option. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Apr 11, 2017 at 10:11 AM, JACQUES RENAUT <jrenaut@us.ibm.com> wrote: > Original post: > > Hi, > > This has been bothering me for a while but has until now been a low > priority. > I am using a special build of 11.70.FC7W1 but the feature has been there > since > at least 11.50. > > > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11. > 70.0/com.ibm.perf.doc/ids_prf_638.htm > > According to the above section of the manual UPDATE STATISTICS LOW can > work in > parallel when: > > - PDQPRIORITY is set. > - Indices are fragmented. > > The documented talks about an "extremely large" database: I am not sure if > this is a requirement or is just a suggested situation in which it might be > useful. I presume the latter. Anyway the database as a whole I am working > with > is in excess of 10 Tb. > > The document is also vague about whether it is the actual PDQ_PRIORITY that > matters or the effective one, i.e. what is asked for reduced by > MAX_PDQPRIORITY. > > Try as I might, I cannot see any evidence of parallelism when the above > conditions are met. While my session running UPDATE STATISTICS LOW has a > non-zero PDQ value, it has one thread, there are no threads running I can't > account for and the memory grant manager shows no PDQ queries. > > The table I am testing with has 1,253,635,127 rows, uses 7691017 8 kb pages > and is partitioned by round-robin 8 ways. It has three indices on a single > column each: > > - serial column, fragmented 8 ways by mod(value). > - int column, fragmented 8 ways by mod(value). > - datetime column, fragmented 12 ways by month(value). > > I feel this meets the criteria. I have also tried with and without > USTLOW_SAMPLE set.> > Is it just a bug or documentation bug? > > Ben. > > Response: > > I've looked at the code further and done a bit of a code walk through > myself, > and I believe this is a defect. It looks like if you have AUTO_STAT_MODE > set > to 0, there is no way to get update stats low to parallelize the scan of a > fragmented index. So my guess is you have AUTO_STAT_MODE set to 0 so you'll > have no chance to get pdq to scan the index fragments in parallel. Just > note, > that if you do turn it on, then for the update stats low to do anything you > would need to change enough stuff in each fragment to get it to get looked > at. > I would open a pmr for this to report the problem. > > Jacques Renaut > IBM Informix Advanced Support > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f403045fdf96001af4054d0bec36

Counter-intuitively the feature does not get switched on with the FORCE option: there must be at least 1% real data change. I have raised a PMR regarding this issue now as it does appear to be a bug. Ben.