My name is Tim Fritz. I am a database solution architect for Quest Software. I spend much of my time helping customers monitor and tune their database systems. Foglight will monitor your databases on Oracle, SQL Server, Sybase, and DB2. Each of these has unique challenges and tuning opportunities for the DBA, but one that is universal among them is server memory usage to make I/O more efficient - especially for OLTP (online transaction processing) applications. In this post, the first in a series on tuning the application database, I will describe how Foglight and its diagnostic counterpart, Spotlight, can help you tune I/O on DB2 LUW.
DB2 LUW database administrators have some unique tuning opportunities thanks to IBM's architectural design of DB2 and the numerous configuration parameters available. One of the architectural portions of the DB2 LUW ‘process model’ is the buffer pool. Buffer pools in DB2 are not unlike buffer cache or data cache in other vendors' database management systems. They provide structures in memory for data to reside for a time so that physical access to disk can be (sometimes considerably) reduced; access to data in memory is much faster. This is certainly an area of focus for any DBA who is responsible for the performance of OLTP applications.
DB2 DBAs have the means to tune their buffer pool strategy. By altering a combination of 'page size' (default is 4K) for table data, 'extent size', 'prefetch size', and the number and size of distinct buffer pools available for a particular page size, DBAs can alter the flow of data for tables of a tablespace through a chosen buffer pool. Ultimately, moving a table to a different tablespace using a different buffer pool may dramatically improve I/O efficiency and result in better application performance.
To correctly tune DB2 LUW buffer pools, a DBA needs to know I/O metrics by database table. The number of reads and writes against a table that uses a particular buffer pool are important clues to find reasons for poor buffer pool hit ratios and poor OLTP performance. Foglight provides these metrics for your top I/O consumer DB2 tables as part of the standard collections gathered by the Foglight DB2 agent and presents the information in standard out-of-the-box views, or on easily-built custom trend graphs and dashboards.
Example #1 below shows an alarm in Foglight for a low buffer pool hit ratio. The remaining example screens that follow show the workflow I would take as a DB2 DBA to resolve a buffer pool efficiency issue that may well be causing significant slowdown in our OLTP application.
Example #1 above – The “Hosts” dashboard in the Foglight web console indicates some critical and warning alarms on our database server. In this case, we see that memory utilization on the box is quite high (82%) and the alarm list shows a DB2 buffer pool hit ratio problem. Remember, like any alarm in Foglight, this hit ratio alert could be defined to fire only after the hit ratio has remained low for a period of time, removing unnecessary alerts on brief anomalies.
Example #2 – we can drill into the “Instance” link on the Alarm Detail pop-up to see metrics behind this alarm.
Example #3 – I/O totals by table name for database AUTO_PRD for chosen time frame.
This Foglight view, DB2_Tables_Top_Accessed, shows total I/O numbers by table name for the chosen time frame. We can quickly see which tables are being access the most. If I/O patterns appear vastly different for particular tables that use the buffer pool we are analyzing, we may decide that those tables belong in another (or their own) buffer pool.
Example #4 - This custom graph that was built using Foglight’s simple drag-and-drop dashboard builder is showing the trend of this buffer pool’s hit ratio over time (in this case, about four months). We can clearly see that the hit ratio was fine (near 100% earlier in the year, but suddenly problems started to appear – consistently low hit ratios. We can see that the downward trend started in April.
Example #5 - We see confirmation of the poor buffer pool hit ratio in real-time with Spotlight on DB2 LUW, our diagnostic tool that speeds investigation and identification of performance bottleneck root cause. In this case, the bottleneck (red icon) is buffer pool hit ratio, confirming what the Foglight alarm had told us.
Example #6 - If we watch the buffer pool hit ratio for a while in Spotlight, we will see whether the problem is an anomaly this time. If it remains low, we can investigate more details by clicking the Overall Hit Rate icon, then the “drilldown” link on the alarm pop-up.
Example #7 - Now we are drilling in to see details of our buffer pools, including AUTO_BP, the buffer pool identified by Foglight as having a low hit ratio. Sure enough, it still does – the hit ratio is only 50%. This buffer pool is used for an OLTP application, so this is a major concern to the DBAs.
Example 8 - For each of the database’s buffer pools, we will be able to investigate I/O in detail by table, and will be able to see large differences in I/O patterns by object in nearly real-time.
To summarize this post, the first in a series addressing Foglight and tuning the application database, DB2 LUW database administrators have some unique tuning opportunities and will find Foglight and Spotlight useful in addressing poor buffer pool efficiency for ever-important I/O tuning in OLTP applications. By having historical trends of read/write metrics by object and of buffer pool hit ratios, the DB2 DBA will be able to take the correct action to optimize I/O throughput in the database and improve application performance.