Re: Performance problems

Doron Rippel wrote: > > Hi, > > We have the same database system on multiple machines at hundreds of > customer sites. It is based on Online 5.01. > > At several sites, a severe performance problem has developed. At some > sites, this slowed down SELECTs. At some - UPDATEs involving a specific > trigger became very slow (and this was at the trigger condition > evaluation stage, which was false, so that the trigger action was > actually never activated). > > The database tables were not fragmented, no indexes were missing or > corrupted at these customer sites. tbcheck -cIn and -cDn revealed no > problems in regular tables or in sys tables. Dropping and re-creating > the indexes did not help. > > At some other customer sites, with much larger databases of the same > structure and same application - there were no complaints at all > > The only thing that solved this strange problem was dbexporting, > dropping the database and then dbimporting it back. This increased the > transaction speed 10-15 times. > > Any guess what's going on? Maybe some obscure sys tables corruption that > dbimport re-builds? Descpite your assertion that the table is not fragmented, my suspicion would still be that the table(s) giving you trouble has(have) too many extents. However, another possibility is that the index pages are interleaved with the data pages in such a way as to cause head contention. Rebuilding the indexes will not always solve this as the new index pages will just reuse the space freed by the dropped index. If dropping all of the indexes and creating the first one clustered also solves the problem then index interleaving is it. Unloading and reloading the table and clustering with no indexes present both serve to compress the data pages into a contiguous set of extents then creating the indexes will create each index in a contiguous few extents. This serves to improve inter-node locality of the index and reduce head movement when the order in which data is being added is randomly distributed relative to a particular index key. When you move this application to R7.xx seriously consider detaching the index which is getting the worst behavior so that the problem cannot recur. Unfortunately, in 5.0x periodic reorganization is the only solution, as you have already found. Art S. Kagel