Dave:
I found the same thing years ago when this trick to clear the buffer cache was revealed to me and I tested it. Clearing/invalidating the buffers without restarting the engine is definitely different than bouncing the engine and so I never publicized it, nor do I use it when I'm performance testing something.
What I'd REALLY like to see is the ability to save the cache state to disk somehow and to tell the engine to reload that saved state on startup so that we can avoid the cache misses after a server restart! Hmm, maybe I'll put an RFE in for this. Anyone like the idea?
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Fri July 08, 2022 09:21 AM
From: Dave Baligo
Subject: Clear BUFFERPOOL
Thank you John and Vladimir,
I tried both "onmode -B reset" and the "zap_all_buffers" functions and I see they both give the same output in the message log:
All dirty buffers flushed to disk. All cached buffers invalidated.
So I'm guessing they are doing the same thing behind the scenes. As far the query timing, after running the onmode/zap_all_buffers the query takes about 3 seconds instead of 1 second so it definitely seems to be doing something. Though when I do the good old fashioned bounce of the DB then the query goes back to 30 seconds on the first run and then 1 second on subsequent runs.
Thank you for the suggestions.
--Dave
------------------------------
Dave Baligo
Original Message:
Sent: Fri July 08, 2022 07:11 AM
From: John Lengyel
Subject: Clear BUFFERPOOL
What, you guys are all too fancy for onmode -B reset? :)
In your defense this is an undocumented 14.10.xC2 feature. But it is supported, and will be going forward.
onmode -B -- Flush all dirty buffers without a checkpoint. Leave the cache intact.
onmode -B reset -- Flush all dirty buffers and invalidate them so that the cache is essentially "empty".
-jc
------------------------------
John Lengyel
Original Message:
Sent: Thu July 07, 2022 05:51 PM
From: Dave Baligo
Subject: Clear BUFFERPOOL
Hi,
We are tuning some queries on Informix 14.10.FC8. Is there a command or a way to clear the BUFFERPOOL without restarting Informix? The first run of the query takes about 30 seconds though since the data is in memory subsequent runs of the query take less than a second.
Thank You,
--Dave
------------------------------
Dave Baligo
------------------------------
#Informix