How does database performance tuning and data migration affect database performance?
Hugely. Massively. Immensely. OK, so maybe this isn’t the most helpful answer so far, but it does give you a feeling for why you should be interested in this topic. (It also tells you that this is a great question!)
To be more helpful, let’s look at tuning first. Tuning a database can be one of the most cost-effective ways of improving performance. But it’s very similar to tuning a car – in that it is as much an art as a science. It’s also analogous because you need to know what the database/car is going to be used for before you can tune it optimally. Tuning for drag racing is not the same as tuning for Le Mans; in a similar vein, tuning for transactions is not the same as tuning for analysis.
For example, one of the database tuner’s most potent tools is the index. If you index a column in a table, you can improve the speed of access to the data it contains by as much as 10,000 times. Great. So why don’t we index all columns? Because an index slows down data entry.
However, the relationship isn’t symmetrical. In other words, the speed gain for querying is usually much greater than the speed hit on data entry. But if a table is mainly used for data entry, we might elect not to index the majority of the columns. Or if a table is used for data entry most of the time but queried heavily on the last day of the month, we might decide to index it the night before that day and then delete the index afterwards. We also wouldn’t index a table that is very small because a table scan might be faster than using the index.
Of course, tuning isn’t just about indices. We can partition tables (chop them up into chunks) as part of tuning; we can change data types; we can rewrite SQL. The number of options is huge – choosing the appropriate one(s) is where the skill lies.
One golden rule in tuning is to find the bottleneck – the place where the problem lies – before you start trying to solve it. I know that sounds like gratuitously insulting advice. But in my experience, many people first try the tuning technique they know (and love) best in the optimistic hope that because it worked the last time, it should work again. If it does, the superstitious behavior is reinforced; if it doesn’t, unhappiness results.
As for data migration, I assume you mean migrating to a different database engine. That usually requires a great deal of effort, and most people think very carefully before doing it and take care to only migrate from an engine that is working poorly to one that will perform much better. As a result, there usually is a significant performance gain. Of course, tuning skills are a mix of the generic (index columns that are queried frequently) and the specific (what is the best index to apply to a text column in Oracle 9?) – so migrating between database engines usually means a steep learning curve for the DBAs as they acquire specific knowledge of the new one.
This was first published in August 2010