Minimizing Buffer Turnover Rate

Optimize Informix database server memory management to help reduce database memory churn

One of the key metrics in all database systems is how effectively does the database use the computer memory for storing data to reduce disk I/O. Disk I/O is normally the slowest subsystem in a database. The demand and interest in in-memory databases is a result of the I/O bottleneck. Informix uses a buffer pool for pages that have been read from disk. It also uses a set of least recently used (LRU) threads to manage and bring pages in from disk, discard pages when they are unused, and write them out to disk if they have been modified. Buffer Turnover Rate is a measure of how effectively a database server manages memory.

The first item to consider is how much of the database will fit into the buffer pool memory and what percent of the active pages of the database will fit into buffers. Mike Walker, a senior Informix Database consultant, developed a simple script to calculate the size of a database in pages (see Figure 1). The script reads the sysmaster database, adds up all the pages, and calculates the size of the entire database in pages.

-------------------------------------------------------------- -- Module: @(#)database_size2.sql     2.1     Date: 2013/06/10 -- Author: Mike Walker  Email: --         Advanced DataTools Corporation -- Discription: Displays size of database in pages allocated ---------------------------------------------------------------   database sysmaster;select n.dbsname[1,20] dbname, i.ti_pagesize pagesize, sum(i.ti_npused) npused from systabnames n, systabinfo i where n.partnum = i.ti_partnum and n.tabname != "TBLSpace" group by 1,2 order by 2,1;

Figure 1. Calculate the size of an entire Informix database

The Informix buffer pool is defined and set up in pages, so knowing how many pages your database requires is important. As a simple example, if all your databases take up 100,000 pages and you have over 100,000 pages of buffers configured for your Informix server, then all of your data will fit in memory. A more realistic scenario is that your databases will need 1 million pages and with 100,000 pages of buffers only10 percent of your data will fit in memory.

When more pages of data are required, the oldest pages in memory will be swapped out and new pages loaded into memory. And when someone needs a page that was just swapped out, it has to be reloaded again; this page reloading is what creates database memory churn or swapping. Any database server in which the whole database does not fit into memory will have some churn—the question is, how bad is the database memory churn?

As an example, one of my customers had a process that performed a sequential scan of one of its biggest tables every hour. The scan took 30 minutes, and the results would not fit into the buffer pool—resulting in pages being swapped out continually and reloaded into buffer memory. The very high buffer turnover affected the performance of every application on the system. On average, every 30 seconds all pages in memory were swapped out and new pages reloaded. Many pages that were used often were swapped out to make room for new pages, and then reloaded back into the buffer pool a few seconds later, instead of staying in the buffer pool.

One solution to this high buffer turnover is to increase your database memory and buffer pool. Another solution is to identity applications that cause this swapping and redesign them.


Buffer turnover rate

How do you measure buffer turnover? Art Kagel, principal database consultant at Advanced Data Tools Corporation and a member of the Board of Directors at IIUG, has developed an excellent formula for measuring Buffer Turnover Rate. His formula is as follows:

Buffer Turnover Rate = ((bufwrits + pagreads) / number of buffers ) / <time since onstat -z last run>

Art’s definition of this formula is as follows: “Buffer Turnover Rate is an estimate of the number of times the entire buffer cache is being replaced per unit of time (usually per hour). Values less than 10.00 per hour are acceptable. Higher values may indicate that increasing buffers will reduce cache thrashing and improve cache percentages.”

You can calculate Buffer Turnover Rate from the output of “onstat –p” or the sysmaster database. I presented an SQL script in my webcast in April 2013 on “Informix Performance Tuning using the Sysmaster Database” to calculate database Buffer Turnover Rate from the sysmaster database (see Figure 2).

---------------------------------------------------------------------- -- Module: @(#)buff_btr_ratio.sql     2.0     Date: 2013/04/10 -- Author: Lester Knutsen  Email: --         Advanced DataTools Corporation -- Discription: Display Buffer Turnovers per hour --          Based on Art Kagels performance tuning tip on monitoring --          how much buffer churn your server has. --          Goal is BTR of less then 7 times per hour ----------------------------------------------------------------------   select  bufsize, pagreads, bufwrites, nbuffs, ((( pagreads + bufwrites ) /nbuffs ) / ( select (ROUND ((( sh_curtime - sh_pfclrtime)/60)/60) )  from sysshmvals ) ) BTR from sysbufpool;

Figure 2. Calculate the Buffer Turnover Rate of an Informix database

The script calculates how many times per hour the buffers turn over. Your goal should be to keep your Buffer Turnover Rate to less then 7 times per hour. If it is over 10 times per hour, you could have a performance bottleneck.

[followbutton username='lesterknutsen' count='false' lang='en' theme='light']
[followbutton username='IBMdatamag' count='false' lang='en' theme='light']