Re: Update statistics

At 06:20 AM 7/10/98 +0200, Henk J. Sanders wrote: >Hi everybody. > >Some weeks ago someone tried to start up at discussion about "update >statistics" but as far as I know nobody reacted. > >Since it is cucumber -time now I want to try at again. > >The items (perhaps a litte overcharged): > >- why do we all accept that we have to run "update statistics" at =20 >regular intervals? >- why do we accept that this is so vital for the performance? >- do you also have the feeling that Informix has left out something =20 >off the update/ insert routines in order to get high benchmarks=20 > figures forcing us to "update statistics" >- haven=B4t we seen this before that we are forced to "batch runs" in =20 >order to corrent the lack of fucntionality in "on line modes". > >Look e.g. to our business: >we are selling software to business areas where it is being used 24 >hours seven days a week. One of the big selling points of Online is that >you can run an .... OF COURSE ONLINE backup!!! However now they are >forced to go OFF LINE in order to get a reliable UPDATE STATISTICS. > >Do you understand? Yes, I do understand. First, let me declare that I am an Informix employee but NOT a developer. The following opinions are based on my experience and DO NOT reflect the opinion or position of anyone else. Do not construe this to represent the Informix position in any way. Such an inference is completely false. A DBMS design, for any DBMS, must make certain tradeoffs. This is one of them. If a set of correct stats is required, then some counters and values need to be checked (as in high/low values) or updated (as in row counts) either on every insert/update/delete or at some interval or at some level of activity (number of threads/number of sessions/number of open tables, etc.) There are no options. If the stats are required, then the counters and values have to be maintained. =20 So the question is: when is a good time to interrupt activities to do these checks?? =20 The second consideration is whether the counters/values have to be correct?? Are you willing to live with randomly incorrect statistics?? =20 The two questions come together in the following way. Whenever the statistics maintenance is done, if the values must be correct, then the changes must be serialized. That is, exactly one thread maybe changing a counter at any time. So, for example, if multiple threads are deleting tuples from a relation, then they must both latch the row counter before changing it. Similarly for inserting rows. For updates, the high/low values must be latched and checked on every update. Those latches will be a bottleneck. You could do without the latches, but then the stats may or may not be correct, and there's no good way to know the difference. Beleive me, the latches are necessary and they WILL be a bottleneck. So a compromise design is to let each customer choose when to take the hit. The update stats command does that. IMHO, that's a reasonable design. One change I find reasonable is to offer some sort of option to regularly do the updates without a command. I don't think constant maintenance is necessary, and it does become a performance problem. However, if a customer can describe a set of conditions when updating the stats is acceptable, then recognition of those conditions can be programmed. The only issue, as always, is the cost of the checks to determine whether or not the desired conditions exist. This becomes recursive: is there ever time in a 7x24 operation that some extra processing is acceptable?? If not, then no satisfactory design can be found. The second change, the one I'd really like to have, is to get the stats processing done faster by making it a parallel operation like everything else. I beleive some changes to do that are being considered or may be in 7.30 or 9.20. I don't know. Just my thoughts, Dick Snoke Informix Software dsnoke@informix.com