Database Tuning overview – Hardware Tuning

As part of the Performance Tuning series – Performance Stack , this section looks at Hadware 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.

The “Kill it With Iron” approach to Hardware Tuning is popular as the cost of components decreases. Before upgrading hardware \ changing components gather metrics and justify the expenditure.

A DBA can increase the capacity of these components. 

Memory

CPU

Disk

Bus Speed

Storage Controller Cache

Storage Controller

Fibre Channel Switch Rates

RAID systems – Develop in-depth knowledge of RAID systems . RAID levels overview

RAID summary:

RAID level 0  aka disk striping : Good read\write ; No redundancy ; No mirroring

RAID level 1 aka disk mirroring : Mirroring ; No redundancy ; No striping

RAID level 3 : Not suitable for database storage. The parity is a major bottleneck

RAID level 5 : Blocks striped. Parity . Very widely used for database storage

RAID level 6 :  Extra parity , makes writes expensive.  Database storage suffers.

RAID level 10: Mirroring and Striping. A stripe of mirrors.

The major benefit of a applying a RAID system is improved throughput. Disk IO remains a major bottleneck on DBMS systems.

I’ll mention Solid State Disk (SSD) . The costs have decreased and is increasing used .

 Create IO patterns per database server activity type. Use as basis for RAID choices and configurations.  Are the requirements of a real-time financial transaction system the same as an overnight ETL process?

 The overall benefit must be weighed up against costs. Costs include : storage system ,  storage system support and engineers.

Read More

Performance Tuning series – Performance Stack

RAID levels overview

SQL Server – tempdb and solid state drives