Re: Index clustering and performance

In article <3259EA83.66D30FD7@www.weideneder.de>, Stefan Weideneder <stefan@www.weideneder.de> writes >Land, Todd wrote: >> >> I remember a thread from long ago . . . can you refresh my memory? >> >> My database performance is declining, and my DBA wants to dump and reload >> the database, hoping that will help. >> >> Isn't there something I can do to my indices (re-cluster?) to regain >> performance? >> >> The OnLine Administrator's Guide (admitedly an older copy) doesn't say >> anything about perfomance except for initial tuning parameters. Help! >> (We're using OnLine 7.x) > Use alter index <indexname> to cluster for the 'most important' index on each table. Next do update statistics. Anything else would depend on the application/ version of the engine you are used. If using 4GL try putting SET EXPLAIN ON in the code and check the sqexplain.out file produced to find out how SQL is being processed. If you are not using 4GL things get harder... Most tuning problems are that indices are missing on the database/ poor application coding E.g. using OR's instead of UNION's in the SQL used. Once the correct indicies are being used you can consider engine/UNIX tuning which is what Stefan is considering. PS Setfan where did you get the info to decode WSTATS output?? I've only starting playing with this as have only seen aio wait, ready and other mutex wait counts. >Hi, > >above all you should try to find the reason why your performance is >declining. Start with monitoring your system resources during your >peaks. Use the iostat and vmstat commands, or "sar -d, -u, -f, -p" if >you have a Sys V Unix. >Look at your message file and ensure, that your checkpoint duration >does not exceed 1 or 2 seconds, otherwise you can read my pages at: >http://www.weideneder.de/informix/faq/checkdur.html . >If you still have a performance problem you must monitor your >OnLine System. There are a lot of usefull commands, but I think there >is only one way to find your problem: >Imagine the OnLine System could measure all the wait-times inside the >OnLine System, for instance: The time spent for waiting for I/O, the >time spent inside the ready queue, the time spent waiting for >checkpoints, and so on. Imagine, your OnLine System measures the >time it takes to run a user-thread... >If you would now accumulate all the "User-Thread-Times" and substract >the total amount of "Waiting-Times" you would receive the real >"Running-Time". Okay, at least you would get s.th. like this: > >Time spent for: waiting for I/O ( aio-wait ) >Time spent for: waiting for checkpoints ( checkpoint-wait) >Time spent for: waiting for a buffer ( buffer-wait) >Time spent for: waiting for a lock ( lock-wait ) >Time spent for: waiting in the ready Q ( mt-ready-wait ) >Real Time - Waiting-Times ( running ) > >If you would collect this information during your peaks you >could find the problem in your OnLine System. Try to enter >the following query: > >select sum(cumtime), reason from sysseswts where >reason != "condition" and cumtime > 1000 -- microseconds >group by reason order by 1 desc; > >Do you see nothing ? No rows found ? If you want to monitor >your system you must set the configuration parameter WSTATS to 1. >You can't find it, but you will see this configuration parameter >if you enter at the command line "strings oninit | grep WSTATS". >Be sure to reset this configuration parameter after you finished >monitoring. Try the query above once again. Now you can see how >many time you spent with waiting for I/O, waiting for locks, and so >on. >If you really have an AIO problem, please send me a short reply >and I will tell you what to do. > >Bye > >Stefan > >stefan@weideneder.de -- David Williams