Re: Index clustering and performance

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) 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