Cherry:
So, your cache hit rate is NOT zero, but rather it is just less than 0.1%. A BTR of 950/hour means that over the course of any given minute those 10,000 pages are replaced 15 times or about every 4 seconds on average. Since the buffer pool is SO small, it is unlikely that any pages stay around to be accessed more than a few times before they are swapped out for other data. So, how to configure your buffer pool? First, I would note that if this system is insert heavy for the 2K dbspaces, then the BTR at 950 may be inflated a bit and I would recommend using the BTR3 calculation to perform the analysis below instead of BTR. Best case, if the 2K dbspaces are not insert heavy then the BTR3 will be almost exactly the same as the BTR, so no worries switching which you use. Basically I no longer look at the original BTR at all.
So, an ideal BTR is under 6 turns per hour, so 950 turns is 158X ideal that indicates that you MAY need to increase the size of that cache from 10,000 pages to 1,580,000 pages. Unfortunately that ignores the fact that much of a properly sized buffer pool is populated with relatively static data like small lookup tables and the "tops" of index trees. It also ignores how much active data you really have that might need to be cached which may be far less that 3GB. So, I would start with 790,000 as a working number then try to estimate the size of your normal and peak active working set of data from the 2K dbspaces. Finally it ignores how much memory you can afford to add to the server without either running our of physical memory completely or of starving other critical processes. Your starting number will be the smallest of those values.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Tue October 10, 2023 09:21 AM
From: Cherry
Subject: Bufferpool for 2K pagesize
Hello Mike,
I think it should at least show some number instead of 0. i see only default BUFFERPOOL is available in onconfig file.
should i explicitly mention BUFFERPOOL for 2K as below in onconfig file?
BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50.00,lru_max_dirty=60.50
BUFFERPOOL size=2K,buffers=10000,lrus=8,lru_min_dirty=50.00,lru_max_dirty=60.50
------------------------------
Cherry
------------------------------
Original Message:
Sent: Tue October 10, 2023 08:56 AM
From: Mike Walker
Subject: Bufferpool for 2K pagesize
While it seems that you have plenty of activity in the 2K bufferpool, the 21 MB is just too tiny for any significant caching, and things are being swapped out way too quickly. When a read or write is performed, for something that was in the cache, the page has already been pushed out of the buffers by something else. The Buffer Turnover of 950 confirms that. You should be looking for a much lower number, preferably in the single digits. Look at increasing the size of that 2K bufferpool.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com