Database tuning and Operating System Tuning

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

There are various configurations per Operating System. For example , on Windows setting the start up service account to Lock Pages in Memory , assists in alleviating paging for SQL Server.  Or check OLTP checklist for DB2 configurations.

 DB2 and Threads

 Threads allow application logic to be separated into multiple execution paths.

Starting a thread is less resource-intensive than starting a process.

Database servers , with heavy Disk IO profiling \ multi-user environment , exploit thread management via managing multiple threads. Read More on How to check for DB2 Direct IO and FILE SYSTEM CACHING - DBA ...

For example, one thread may be CPU-bound while another thread is executing a disk write\read.

Minimise context switching caused by short term segments for threads. For example , Microsoft Windows servers moves threads between processors. From a OS perspective this is efficient , but if the system is under heavy stress , reloading data into the processor cache regularly , can have a negative performance impact.Read more on the DB2 process model - DBA DB2

Note: Prior to enabling affinity options , prove that excessive thread migration between processors causes excessive context switching.

Allow DBMS threads high priority over other applications. DB2 and SQL server have priority options.  Ensure testing ,  as assigning priority can have a negative impact on other instances.

Assign dedicated CPU to DBMS.  Various options such as affinity masking.

Set Multi programming level (MPL) of the DBMS.  Increasing the MPL , will increase the database servers throughput , but at a cost of : increased contention, thrashing , query processing impact

 Weigh up costs versus benefits of using these approaches.

 DB2 and File System

 Plan placement of files.

Create IO profile of files and assign to separate drive\LUN

There are increased benefits of performance but disk\SAN engineers are required to create disk profiles

Read More on DB2 Tuning

Performance Tuning series – Performance Stack

Database Tuning  overview – Hardware Tuning

OLTP checklist for DB2 configurations.