Database Tuning overview – Buffer Tuning

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

 Memory usage

Measure and assign memory space for IO and memory data structures e.g procedure cache

 Ensure the buffer space is sufficient.  To low buffer space for DBMS memory requirements causes a low hit rate. A low hit rate is where a high proportion of pages are read from disk not the cache , causing a performance decrease.

 On the other hand , if the buffer space is to big , the OS causes applications to swap to disk.

The aim is to establish and maintain a high level Buffer Cache Hit ratio , although this requires expertise .

Prefetching

IO is composed of Logical and Physical Reads.  If the database engine does not find the data in the buffer cache it reads from disk.

The capacity to prefetch is useful for some access plans. Prime examples are: sequential scans and index use.

The DBA can increase performance through exploiting prefetching through aligning parallelization and CPU usage.

Systems such as SQL Server have read-ahead features . The read-ahead forecasts the pages required to service the execution plan.   The pages are returned to the buffer cache prior to being needed.  CPU and disk can work more efficiently

Related Posts

 Performance Tuning series – Performance Stack

Database Tuning  overview – Hardware Tuning

OLTP checklist for DB2 configurations.

Database tuning and Operating System Tuning