Regular setup and maintenance

Question

How often do you carry out regular maintenance, for example, stress testing your application and / or setting up database indexes for your applications?

EG. Do you tune (defragment, reorganize, or restore) your database indexes once a week, every six months, or only after entering large amounts of data and stress the testing of your application after each major or minor assembly, weekly, annually, never?

Answer 1

In a lively production system where the code evolves, every day is a stress test. Setting up a database in a similar way is knowing when to stop; when performance is acceptable, you stop.

In particular, Oracle, the debate about whether to rebuild indexes has been raging for years; some people believe it, some do not. The index represents the structure of the tree B *; It will accurately reflect the data in the table. In many cases (exceptions to be observed), rebuilding the index is akin to what happens on a diet in a collision; Of course, indexes will become skinny in the short term, but over time - perhaps in just a few days or hours of processing - they will return to their previous state. While performance is consistent with goals, why bother? Index recovery generates significant I / O activity (you need to read the table and / or index) and either generates significant repeat activity (by writing repetition vectors for new index entries) or requires immediate backup (if you restored the index using NOLOGGING, the index now does not recover )

Exceptions:

• Raster indexes usually need to be disabled and rebuilt by dataloads, as they can pathologically inflate through DML activity

• If one radically offloads a lot of data and uses global indexes or some other non-locally partitioned index, combining (not rebuilding, just pushing adjacent space on adjacent leaves together) may be reasonable.

Answer 2

When I worked for a large 3D CAD company, we had tests that ran:

• Whenever you want to check the code. About 40 tests. You had to go through all the tests before you could check the code (you also had to go through the code review).
• Whenever the assembly server completes collapsing the assembly
• Every night (many tests, about 8 hours).
• Once a week (even more tests - this test set will take many days).

Each test loaded a specific 3D model created by the QA team to highlight various issues. I am sure that some of the models were supplied by customers to highlight previously known customer errors. Models ranged from microns to 1 km in all three directions.

Answer 3

I guess this is what can be done when a customer first complains about performance, not earlier.

Answer 4

Our (web application) is subjected to stress testing in an environment with pre-accommodation before installing any new version live. The test plan is developed and implemented by an external company that directly informs the client.

The only problem: often there were performance problems in a live environment, even though the preliminary voltage test worked flawlessly - the “synthetic” load seems to be too different from the “real” load.