Database Tuning overview – Buffer Tuning

11 August,2011 by Jack Vamvas

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  

Author: Jack Vamvas(http://www.dba-db2.com)
Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Database Tuning overview – Buffer Tuning

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright