Re: Benefits of reducing extents and running update stats

Bill Martinez wrote: Bill, see my notes below: > I was recently tasked with coming up with preventative maintenance on an > Informix database (7.31 on AIX 5.3) for a 3rd party application we run. > > I noticed a few things: > > 1: We never run UPDATE STATISTICS > > 2: one table had 7 exents and another had 8. > > 3: We never ran any onchecks > > 4: We didn't monitor logical logs to see if they were filling up. > > > > So, I said we should: > > 1: Run update statistics on a nightly basis. ON the advice of someone > else, I did Medium although I'm not convinced that I shouldn't have done > High or gone the route of running it on specific columns. > > 2: Drop/Rebuild the 2 tables with at least 7 extents - for some reason, > I recall hearing that performance started to drop at 8 extents - I don't > know where this came from, but others seem to agree. > > 3: Run oncheck once a week - (4 of them actually - oncheck -ce; oncheck > -cR -y; oncheck -cD -y; oncheck -cI -y) > > 4: Send alerts if logical logs exceeded 75% - If they do, I believe > there's a good chance someone is not running ontape -c or there is > something wacky going on. > > > Any comments/recommendations/etc... on what I said we should do? > > > In particular: > > 1: I'm wondering about whether Update Stats Medium is the best > advisable. Why wouldn't HIGH be better? Or even better than that, > choose to run it for specific indexed columns? Follow the recommended suite of UPDATE STATISTICS commands in the Performance Guide (if you have a recent update to 7.31 (UD3 or later) get John Miller III's white paper on taking advantage of optimizations inside the later IDS engines that make running update stats faster). Or just download my package, utils2_ak, and compile dostats.ec which automatically implements these protocols with many options for control. The package also contains drive_dostats script which will run multiple copies of dostats in parallel to more quickly update large databases. > 2: Where does this notion of 8 extents being too many come from? How > big of a hit is it? And if so, shouldn't we address tables approaching > that number? The notion comes from OnLine 5.xx which kept 8 extents mapped into memory so that if a table had more than 8 extents accesses to data in a table from more than 8 of its extents cause that memory table to thrash. IDS 7/8/9/10/11 does not have that problem as the entire extent list is kept in a memory cache for active tables. Worry if you are approaching 200 extents because a table can't have many more than that (actual number depends on pagesize - closer to 400 on AIX - number of keys and number of 'special' columns). Accesses to large portions of tables with more than 30 extents or so tends to be slower than with the data being more contiguous. For this reason, I tend to reorg tables with many more extents than that unless I know that the active working set resides in only a few extents out of the total. > I was unable to actualy rebuild the table with 7 extents as users > started hitting the system an hour after I was told it would be idle and > had been idle. But then I noticed that even though that table had 5500 > rows on Friday, it only had a little over 100 today. I have no idea how > this table is used, but obviously it is very volatile. But with only > between 100 and 5500 rows, how big of a deal is it that it takes up 7 - > or even 8 (if it grows) extents? > > I figure running Update Stats should be a much bigger win on such a > volatile table. > > 3: I was a bit hesitant to add -y to the onchecks, but I figured what > else could we do besides say Yes to the prompts. I can't imagine what > else to do if there were problems detected, so I went with the -y option. The -y is OK, except for -cI. Oncheck rebuilds damaged indexes using a single threaded algorithm so you don't want to let oncheck do that. Better to always pass -n to oncheck -cI and rebuild any damaged indexes manually in dbaccess/sqlcmd/etc. > 4: As for filling up logical logs, I was told a couple of years ago > they filled up due to someone neglecting to run ontape -c and they had > to get Informix involved as there was not enough log space to even roll > back. I'd never heard of anything like that before. I know rolling > back could sometimes be time-consuming, but they claimed they were stuck. Yes, that can happen. You want to make sure that ontape -c is always running or use the ALARMPROGRAM to back up each logical log as it fills using ontape -a of onbar. My package utils4_ak contains such an alarmprogram that will send email alerts about critical server events and trigger logical log backups. > 5: I suppose I could have scrutinized every setting in the onconfig > file, but I didn't feel comfortable doing so, especially as everything > has been running okay and that would require a lot more research on my > part. > > Without posting the whole thing here, I don't expect much advice on > this point, but is there anything in particular you would look for? Get the ratios package by Marat Kotik. The script, ratios.ksh, will run onstat -pand calculate three basic metrics and how to interpret the output which will give you a quick health check of your system. > Thanks for reading this far and for anything you have to offer. > > - Bill "Martino" Martinez Art S. Kagel