DB2 10 for Linux on System z Using z/VM v6.2, Single System Image Clusters and Live Guest Relocation

30 DB2 10 for Linux on System z Using z/VM v6.2, SSI Clusters and LGR

A database test depends on how the workloads use the various DB2 subsystems, especially

memory. For example, queries with many ORDER BY clauses may gain more benefits from

greater sort space than from greater bufferpool space. So we used two SQL queries to

simulate a complex sort and select operation, which would attempt to use most of the sort

heap and the buffer pools allocated respectively. When DB2 self-tuning memory manager

finds that there needs to be some adjusting to the memory configuration, it tunes those

optimally.

Example 4-10 Starting memory tracking

db2inst1@itsodb1:~> db2mtrk -d -v -r 10 > workload_stats.txt &

[1] 47012

For the sake of demonstrating how the sort heap and buffer pools are tuned automatically by

DB2, we purposely created complex queries to test. In our tests, Query1 (q1.sql in

Example 4-11) will do a lot of sort operations and Query2 (q2.sql in Example 4-11) will be a

more complex select operation.

Example 4-11 Execution of load generation queries

db2inst1@itsodb1:~> db2 -tvf q1.sql

db2inst1@itsodb1:~> db2 -tvf q2.sql

Once the SQL queries are finished, stop the DB2 memory tracker (db2mtrk) using the

command line prompt by pressing the Ctrl + c keys or the kill command.

Example 4-12 Stopping db2 memory tracker

db2inst1@itsodb1:~> kill -9 47012

[1]+ Killed db2mtrk -d -v -r 10 > workload_stats.txt

4.3.5 Analysis of the DB2 memory tracker report

The results in Example 4-13 clearly show that the self-tuning memory was behaving

consistently as per our expectations. During runtime, the self-tuning memory manager

dynamically updates the size of performance-critical heaps within the database shared

memory set according to the free physical memory on the system, while ensuring that there is

sufficient free instance_memory available for functional memory requirements.

For this database, STMM was dynamically shifting memory to SortHeap and into the buffer

pools to help address the prolific and costly scans and sorts. While 16 pages had been

defined in the database configuration, DB2 automatically increased the SortHeap up to 304

pages (1,245,184 bytes) on the system. For the buffer pool size, the initial configuration was

set to 50 pages. Being too small for a database of this size, STMM normalized immediately to

around 200 pages (983040 bytes). But when we actually started the SQL queries, the buffer

pool was adjusted to 2224 pages (9,109,504 bytes).

See Example 4-13 for memory configuration before query execution and Figure 4-14 on

page 31 for memory configuration during query execution.

Example 4-13 Memory tracking report from db2mtrk before query execution

Tracking Memory on: 2012/06/19 at 10:27:42

Memory for database: SAMPLE