Database Tuning overview –Storage Tuning

Part of the Performance Tuning series – Performance Stack , this section looks at Storage Tuning. The purpose of the series is a general approach to Performance Tuning – independent of database server platform.   A large proportion of DBAs support more than one type of database server and a methodology allows them to apply to all platforms.

Storage Tuning is an important factor in database performance. IOPS and MB/sec throughput are essential to database performance. OLTP is characterised by more random reads, requiring efficient IOPS. Efficient IOPS means quick response. DSS requires good MB\sec with the focus on sequential and usually large block sizes. DSS is characterised by larger amounts of data , therefore MB\sec throughput

What options exist for the DBA to tune storage?

The main focus on this section is about improving IO. Create an IO profile of every database server.  This assists greatly in defining the requirements. The requirements should be passed to the Storage engineers.

Page and File Properties

Extent size – allocation unit for new space.  Pages are written to a container , when the extent size limit is reached ,  writing skips to the new container. In DB2 , there is an option to specify the extent size for new tablespaces.  Consider : a)  the table size and the table type. b) the type of data and how it’s accessed.

Page size  - The size of pages used for the table space. For example, the options may be 4K, 8K, 16K and 32K. Depending on which option is used , different limitation exist around: Row size limit, Column count limit, capacity.  

The chosen Page Size impacts efficient space use , sequential access , increased buffer and lock management, random access and to much data resturned from disk.

 Free space management 

 Maintain adequate space for new rows and updates.  

 Partitioning

1)  Table partitioning – horizontal partitioning across different drives using different LUNS.  

2)  Create an access profile of the database server. An access profile defines the type of access activity , such as Read-Only data , data updated overnight by an ETL.  Match the appropriate partitions to the drives – supported by different RAID levels.

Define IOPS requirements per database server.

3)  Analysis is required to decide which tables to partition.  Decide which tables to partition after analysis of logical partitioning. For example, if a 100 million row table exists , 50 % of the data won’t be updated again, and there some logical way of partitioning –  this is a candidate for table partitioning

 Index tuning

1)  Design data structures for faster access for specific data. An index is a data structure and requires similar attention as other database files. Designing an index requires careful performance analysis.

2)  Which indexes should the DBA create? What index should be created and where should it be stored?

3) Creating an index for every query can be inefficient. Consider the disk requirements , updating disks , and maintaining the indexes. Weigh up against improved performance.

Related Posts

Performance Tuning series – Performance Stack

Database Tuning  overview – Hardware Tuning

OLTP checklist for DB2 configurations.

Database tuning and Operating System Tuning  

Database Tuning overview – Buffer Tuning