Database tuning – How to start and what to tune.

Database tuning – How to start and what to tune.

This question is frequently asked by many people, both online and offline.
There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should ask oneself: Is it really necessary to tune this database?

People read articles on the Internet along the lines of, "Best practice for database PT" and immediately begin implementing the article's recommendations on their production database. Don't do this. Always take some precautionary steps to avoid problems related to performance. But, this does not mean that you should implement every "Best Practice" on your database.

Performance tuning in practice is like visiting a doctor. When you first visit your doctor, he/she asks you a series of questions to find out what problem you are having and, most importantly, the root CAUSE of the problem before suggesting any medicine. Just imagine what would happen if he/she gave you medicine for all general diseases without diagnosing anything. In a case like this, do we really need doctor? Of course not. Suppose you have a fever, a headache and a body-ache. In this case root cause of problem is fever so you should take medicine for the fever, not just aspirin for your headache.

So, the important first step is to diagnose the problem. Without knowing the actual cause of the problem you may not be able to resolve it for a long time. :( Though, you may be able to resolve it for a short time :).

What I am trying to convey here is – first identify the symptoms (the root cause of the problem) and then take the necessary steps to solve it accordingly. This will solve your performance related problems, without any side-effects.

The most common error messages are ones that indicate the application is running slow on the end-user side. It is your task to find out why it is running slow. Most of the time you will find that the slow response is because of a poorly written sql query. If that's the case, rewrite your queries before upgrading hardware or software. :)