Database Tuning

Database tuning is comprised of a group of activities used to optimize and regulate the performance of a database. It refers to configuration of the database files, the database management system (DBMS), as well as the hardware and operating system on which the database is hosted. The goal of database tuning is to maximize the application of system resources in an attempt to execute transactions as efficiently and quickly as possible. The large majority of DBMS are designed with efficiency in mind; however, it is possible to enhance a database’s performance via custom settings and configurations.

The tuning of a database management system centers around the configuration of memory and the processing resources of the computer running the DBMS. This can involve setting the recovery interval of the DMBS, establishing the level of concurrency control, and assigning which network protocols are used to communicate throughout the database. Memory utilized by the DBMS is allocated for data, execution procedures, procedure cache, and work space. Since it is faster to directly access data in memory than data on storage, it is possible to decrease the average access time of database transactions by maintaining a decent sized data cache. Database performance can also be improved by using the cache to store execution procedures as they would not need to be recompiled with every transaction. By assigning processing resources to specific functions and activities, it is also possible to improve the concurrency of the system. “Database concurrency controls ensure that transactions occur in an ordered fashion. The main job of these controls is to protect transactions issued by different users/applications from the effects of each other. They must preserve the four characteristics of database transactions: atomicity, isolation, consistency and durability”(About.com).

Input/Output(I/O) tuning is another major component of database tuning. I/O tuning mainly deals with database transaction logs. Database transaction logs are files that are associated with temporary work spaces as well as both table and index file storage. Transaction logs and temporary spaces are heavy consumers of I/O, and affect performance for all users of the database. Placing them appropriately is crucial. The main goal of I/O tuning a database is to optimize and balance the read and write transactions of the system in order to achieve an increased speed in database transactions and a decreased database access time.

Another method of ensuring that a database is fast and reliable is the Use of RAID in the creation of the

This shows the data layout for a RAID-6 array.

database. RAID stands for Redundant Array of Independent Disks. Here is an example as to why RAID is superior to a single disk. If data are stored on one disk, the entire database is completely reliant on that one disk; if it were to fail, the database would not exist anymore. Another drawback to having it on a single disk is the read/write time. One hard disk can only be so fast. If there is a lot of I/O data being processed, it can be a lengthy process. One thing that RAID does is it divides and replicates the data onto several independent disks. Instead of having all our eggs in one basket, we have diversified our risk or disk failure away. If we had a RAID 6 array with 4 drives, the tolerance for failure is 2 disks. This means that if 2 hard drives fail completely, the database will still function perfectly. On top of failure tolerance, another great upside to using RAID is tasks are performed faster. There is an increase in speed equal to this multiplication factor: (n−2)X. Reading is faster, and writing is faster because instead of one disk trying to find all the data, the task is broken down into parts, and each hard disk does part of the job.

Another common part of database tuning revolves around database maintenance. Database maintenance includes things such as backing up the database as well as the defragmentation of the data residing within the database. When a database is under heavy use, transaction log entries must be removed in order to create space for future entries. Since regular transaction log backups are smaller in size, they take less time to complete and, therefore, interrupt scheduled database activity for much shorter periods of time. Much like a computer system, the defragmentation of database tables and indexes greatly increase the efficiency of the database’s ability in accessing data. A database’s level of fragmentation depends on the data’s nature, how the data is manipulated, and the amount of free space left in database pages.