Question about STMT_CACHE
Susan Jones asked why a query runs much slower the first time after an instance restart than on subsequent runs, suspecting STMT_CACHE (turning it off changed nothing; SET EXPLAIN showed identical query costs). Dan Mueller, Art Kagel and Martin Fuerderer explained it's not the statement cache but the buffer pool: the first run must read data and index pages from disk, after which they're in memory. She accepted the explanation. A follow-up question about flushing the buffer pool to repeat cold-cache timings drew Art's answer that there is no supported way other than restarting the engine, with Tristan Ball suggesting shrinking the buffer pool instead.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
AIX 5.3 IBM Informix Dynamic Server Version 11.50.FC8 Hello, We have a situation where a query that is executed immediately after a stop and restart of the database instance takes much longer than the second time the query is executed. I assumed that was because I had STMT_CACHE set to 2 'ON' and the second time the query was executed, the statement was cached in memory. By the way, "Update Statistics" was executed just prior to running this test. Both times the query was executed I had 'SET EXPLAIN ON' and the cost of the queries are identical. So, I set STMT_CACHE, STMT_CACHE_HITS, STMT_CACHE_NOLIMIT, AND STMT_CACHE_NUMPOOL back to the default values "OFF", then stopped and restarted the instance before re-testing. The results are the same. Still, the first query considerably faster than subsequent queries. Any ideas?
The first time you run this query after starting the engine, none of the data pages would be in the buffer pool. The second time they would be. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SUSAN JONES Sent: Thursday, February 24, 2011 4:07 PM To: ids@iiug.org Subject: Question about STMT_CACHE [22911] AIX 5.3 IBM Informix Dynamic Server Version 11.50.FC8 Hello, We have a situation where a query that is executed immediately after a stop and restart of the database instance takes much longer than the second time the query is executed. I assumed that was because I had STMT_CACHE set to 2 'ON' and the second time the query was executed, the statement was cached in memory. By the way, "Update Statistics" was executed just prior to running this test. Both times the query was executed I had 'SET EXPLAIN ON' and the cost of the queries are identical. So, I set STMT_CACHE, STMT_CACHE_HITS, STMT_CACHE_NOLIMIT, AND STMT_CACHE_NUMPOOL back to the default values "OFF", then stopped and restarted the instance before re-testing. The results are the same. Still, the first query considerably faster than subsequent queries. Any ideas? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
More likely it is because the data and index pages you query needs are not yet in cache after a restart. Art On Feb 24, 2011 4:08 PM, "SUSAN JONES" <sjones@clerk.org> wrote: > AIX 5.3 IBM Informix Dynamic Server Version 11.50.FC8 > > Hello, > We have a situation where a query that is executed immediately after a stop > and restart of the database instance takes much longer than the second time > the query is executed. I assumed that was because I had STMT_CACHE set to 2 > 'ON' and the second time the query was executed, the statement was cached in > memory. By the way, "Update Statistics" was executed just prior to running > this test. > > Both times the query was executed I had 'SET EXPLAIN ON' and the cost of the > queries are identical. > > So, I set STMT_CACHE, STMT_CACHE_HITS, STMT_CACHE_NOLIMIT, AND > STMT_CACHE_NUMPOOL back to the default values "OFF", then stopped and > restarted the instance before re-testing. The results are the same. Still, the > first query considerably faster than subsequent queries. Any ideas? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --0015175ce182715534049d0db0ad
Hi, can you please clarify what exactly is the problem? I read in your e-mail at the beginning: > ... a query that is executed immediately after a stop > and restart of the database instance takes much longer than the second time... but at the end you say: > ... Still, the > first query considerably faster than subsequent queries... These two statements are somewhat contradicting each other (I think). If the first it true, then you already got two answers. :) It is having to read data from slow disk the first time after startup. This will put the read pages into the buffer pool (also referred to as "cache"), which is an Informix internal data structure in main memory (and not to be msitaken for something liek a file system (buffer) cache, which also exists on some operating systems and can have influence, if the Informix chunks are cooked files and not on a raw device). The second time the data will be taken from this buffer pool only, where main memory is very fast compared to slow disk reading. If the second statement in your e-mail is true ... Then I have to say that this really would be a somewhat strange situation that needs further investigation. So for the moment I'll believe that this is not the case. Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management -- Go Cruising with Informix in 2011 ... -- IIUG Informix Conference -- Overland Park Marriott, Kansas -- May 15 - 18, 2011 IBM Deutschland Research & Development GmbH Chairman of the Supervisory Board: Martin Jetter Board of Management: Dirk Wittkopp Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 ids-bounces@iiug.org wrote on 02/24/2011 10:07:04 PM: > > AIX 5.3 IBM Informix Dynamic Server Version 11.50.FC8 > > Hello, > We have a situation where a query that is executed immediately after a stop > and restart of the database instance takes much longer than the second time > the query is executed. I assumed that was because I had STMT_CACHE set to 2 > 'ON' and the second time the query was executed, the statement was cached in > memory. By the way, "Update Statistics" was executed just prior to running > this test. > > Both times the query was executed I had 'SET EXPLAIN ON' and the cost of the > queries are identical. > > So, I set STMT_CACHE, STMT_CACHE_HITS, STMT_CACHE_NOLIMIT, AND > STMT_CACHE_NUMPOOL back to the default values "OFF", then stopped and > restarted the instance before re-testing. The results are the same. Still, the > first query considerably faster than subsequent queries. Any ideas? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thank you all for you quick responses. I believe I understand now what's happening. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SUSAN JONES Sent: Thursday, February 24, 2011 4:07 PM To: ids@iiug.org Subject: Question about STMT_CACHE [22911] AIX 5.3 IBM Informix Dynamic Server Version 11.50.FC8 Hello, We have a situation where a query that is executed immediately after a stop and restart of the database instance takes much longer than the second time the query is executed. I assumed that was because I had STMT_CACHE set to 2 'ON' and the second time the query was executed, the statement was cached in memory. By the way, "Update Statistics" was executed just prior to running this test. Both times the query was executed I had 'SET EXPLAIN ON' and the cost of the queries are identical. So, I set STMT_CACHE, STMT_CACHE_HITS, STMT_CACHE_NOLIMIT, AND STMT_CACHE_NUMPOOL back to the default values "OFF", then stopped and restarted the instance before re-testing. The results are the same. Still, the first query considerably slower than subsequent queries. Any ideas? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I have one more question about this. We are working on speeding up this query and would like to test it as though the engine was reading from disk each time. Is there a way to flush the buffer pool, (the cache) rather than having to restart the engine each time to test for the slower results?
There is no other supported way to clear out the buffer pool, no. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 2, 2011 at 3:01 PM, SUSAN JONES <sjones@clerk.org> wrote: > I have one more question about this. > > We are working on speeding up this query and would like to test it as > though > the engine was reading from disk each time. Is there a way to flush the > buffer > pool, (the cache) rather than having to restart the engine each time to > test > for the slower results? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175cb2560bf251049d859cc8
Would simply making the buffer pool very small be satisfactory? I did a similar thing recently... Regards, Tristan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SUSAN JONES Sent: Thursday, 3 March 2011 7:02 AM To: ids@iiug.org Subject: Re: RE: Question about STMT_CACHE [22967] I have one more question about this. We are working on speeding up this query and would like to test it as though the engine was reading from disk each time. Is there a way to flush the buffer pool, (the cache) rather than having to restart the engine each time to test for the slower results? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.