Regular performance tuning and maintenance

Question

How often do you conduct regular maintenance such as stress test your application and/or tune your database indexes for your applications?

E.G., Do you tune (defrag, reorganise or rebuild) your database indexes once a week, every six months or only after large volumes of data have been input and do you stress test your application after each major or minor build, weekly, yearly, never?

Answer 1

My guess is that this is the kind of thing that might be done when the client first complains about performance, not before.

Answer 2

Our (web) application is stress-tested in a pre-live environment before installing any new version live. The test plan is developed and executed by an external company that directly reports to the customer.

The only problem: often, there have been performance problems in the live environment, even while the pre-live stress test worked flawlessly - the "synthetic" load seems to be too different from the "real" load.

Answer 3

In a live, production system where the code is evolving, every single day is a stress test. Database tuning, similarly, is about knowing when to stop; when the performance is acceptable, you stop.

Specifically to Oracle, the debate about whether or not to rebuild indexes has raged for years; some people believe in doing so, some do not. An index is a B*tree structure; it's going to accurately reflect the data in the table. In many cases (exceptions to follow) rebuilding an index is akin to going on a crash diet; sure, the indexes will get skinny in the short term, but over time -- perhaps as little as a few days or hours processing -- they'll return to their previous state. So long as performance is meeting objectives, why worry about it? Rebuilding indexes generates significant I/O activity (gotta read the table and/or index), and either generates significant redo activity (writing the redo vectors for the new index entries) or requires an immediate backup (if you rebuilt the index with NOLOGGING, the index is now unrecoverable).

Exceptions:

• Bitmap indexes should generally be taken offline and rebuilt between dataloads, as they can pathologically bloat via DML activity

• If one radically offloads a great deal of data and is using global indexes or some other non-locally partitioned index, coalescing (not rebuilding, just pushing adjacent space on neighboring leaves together) may be prudent.

Answer 4

When I worked at a major 3D CAD company, we had tests that ran:

• Whenever you wanted to check in code. About 40 tests. You had to pass all the tests before you could check the code in (you also had to pass a code review).
• Whenever the build server finished the rolling build
• Every night (many tests, taking about 8 hours).
• Once a week (even more tests - this set of tests would take many days to complete).

Each test was loading a particular 3D model created by the QA team to stress various problems. I'm sure some of the models were customer supplied to stress previously known customer bugs. Models ranged in size from microns to 1km in all 3 directions.