Cache some specific tables of any database

Hello, friends! Is there some way to cache an specific database table to improve select operations, since it´s data are not frequently changed? For example: we have a table named "A" that receives an external data update, 3 times a day, at specific hours. After that load process, could we cache it so that the select statements, joins, and so on, have any speed improvement? Obs: if we simply enable sql cache on our instances, could we solve our problem for that situation? Regards, folks! Alexandre Marini Analista de Tecnologia da Informação - DBA SEFAZ-MS / UIMP / Sistemas: Fronteiras e SIG-DW John Miller iii escreveu: > If you are version 11 and want to kill idle user try this. > > http://www.ibm.com/developerworks/blogs/page/idsteam?entry=3Dterminate_= > idle_users_with_the > > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > = > > From: "KAMRAN HAQ" <khaq@i2cinc.com> = > > = > > To: ids@iiug.org = > > = > > Date: 06/02/2009 08:52 AM = > > = > > Subject: trying to free virtual memory [15886] = > > = > > Sent by: ids-bounces@iiug.org = > > = > > Hi, > We are running more than one instance of Informix IDS on single machine= > .. We > > observed that there are some issues with our application that with the > passage > of time idle sessions keep on increasing (idle for days doing nothing).= > The > > virtual memory also keep on increasing. If we find and kill the long id= > le > sessions, even then virtual memory is not get free which can be used by= > > other > instance running on same machine. The only way to free that memory we f= > ound > is > to restart the instance. Please suggest, how can we cope with this prob= > lem. > > regards, > Kamran > > ***********************************************************************= > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >

Two separate questions. First if you had supplied your platform and version information - and EVERY initial post should supply such - I could give you a better answer. As it stands: There were some older IDS versions that allowed you to make a table MEMORY RESIDENT, it was not a particularly successful feature and was removed in the very next release of both the 7 and 9 product code lines. it is still possible in XPS (Informix version 8.xx). The problem was that users over used the feature causing server performance overall to decrease markedly. It turns out that the engine's FIFO policies, which were improved in the releases that removed the MEMORY RESIDENT feature, does a far better job at keeping important data in memory than you can. If you are really having problems with the buffer cache churning data out of memory too often (check the BTR - Buffer Turnover Rate - if it is over 9 times per hour this is what's happening) increase the size of the data buffer cache so that it is large enough to hold the current working set of data at all times. Add memory to the system if you have to. If you are having trouble determining what your working set size is, contact a good database consulting group (Oninit comes to mind ;-) to help. Your second question: No, the statement cache will not improve data cache usage at all. There are a few sites that are taking good advantage of the statement cache, but TMK most sites that have tried using it have experiences no positive effect or a performance slowdown. That's counter intuitive, I know, and I once advocated using the statement cache because it sounds like such a good idea, but reality is sneaky that way. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Jun 4, 2009 at 9:01 AM, Alexandre Marini <amarini@fazenda.ms.gov.br>wrote: > Hello, friends! > Is there some way to cache an specific database table to improve select > operations, since it´s data are not frequently changed? > > For example: we have a table named "A" that receives an external data > update, 3 times a day, at specific hours. > After that load process, could we cache it so that the select > statements, joins, and so on, have any speed improvement? > > Obs: if we simply enable sql cache on our instances, could we solve our > problem for that situation? > > Regards, folks! > > Alexandre Marini > > Analista de Tecnologia da Informação - DBA > > SEFAZ-MS / UIMP / Sistemas: Fronteiras e SIG-DW > > John Miller iii escreveu: > > If you are version 11 and want to kill idle user try this. > > > > http://www.ibm.com/developerworks/blogs/page/idsteam?entry=3Dterminate_= > > idle_users_with_the > > > > John F. Miller III > > STSM, Support Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > = > > > > From: "KAMRAN HAQ" <khaq@i2cinc.com> = > > > > = > > > > To: ids@iiug.org = > > > > = > > > > Date: 06/02/2009 08:52 AM = > > > > = > > > > Subject: trying to free virtual memory [15886] = > > > > = > > > > Sent by: ids-bounces@iiug.org = > > > > = > > > > Hi, > > We are running more than one instance of Informix IDS on single machine= > > .. We > > > > observed that there are some issues with our application that with the > > passage > > of time idle sessions keep on increasing (idle for days doing nothing).= > > The > > > > virtual memory also keep on increasing. If we find and kill the long id= > > le > > sessions, even then virtual memory is not get free which can be used by= > > > > other > > instance running on same machine. The only way to free that memory we f= > > ound > > is > > to restart the instance. Please suggest, how can we cope with this prob= > > lem. > > > > regards, > > Kamran > > > > ***********************************************************************= > > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > = > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502d6c4f5f28a046b85d16e

>> Is there some way to cache an specific database table to improve select >> operations, since it´s data are not frequently changed? In my experience, this is pointless. The engine is pretty good at managing which data best belongs in the cache. Can you post the output from "onstat -p" here? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Sorry about no specs. Our version is 11.50.FC4 running on a Linux_64 machine. Thanks in advance for your helps, mr. Kagel !!! Alexandre Marini Analista de Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / UIMP / Sistemas: Fronteiras e SIG-DW Art Kagel escreveu: > Two separate questions. First if you had supplied your platform and version > information - and EVERY initial post should supply such - I could give you a > better answer. As it stands: > > There were some older IDS versions that allowed you to make a table MEMORY > RESIDENT, it was not a particularly successful feature and was removed in > the very next release of both the 7 and 9 product code lines. it is still > possible in XPS (Informix version 8.xx). The problem was that users over > used the feature causing server performance overall to decrease markedly. > It turns out that the engine's FIFO policies, which were improved in the > releases that removed the MEMORY RESIDENT feature, does a far better job at > keeping important data in memory than you can. If you are really having > problems with the buffer cache churning data out of memory too often (check > the BTR - Buffer Turnover Rate - if it is over 9 times per hour this is > what's happening) increase the size of the data buffer cache so that it is > large enough to hold the current working set of data at all times. Add > memory to the system if you have to. If you are having trouble determining > what your working set size is, contact a good database consulting group > (Oninit comes to mind ;-) to help. > > Your second question: No, the statement cache will not improve data cache > usage at all. There are a few sites that are taking good advantage of the > statement cache, but TMK most sites that have tried using it have > experiences no positive effect or a performance slowdown. That's counter > intuitive, I know, and I once advocated using the statement cache because it > sounds like such a good idea, but reality is sneaky that way. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. 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 Thu, Jun 4, 2009 at 9:01 AM, Alexandre Marini > <amarini@fazenda.ms.gov.br>wrote: > > >> Hello, friends! >> Is there some way to cache an specific database table to improve select >> operations, since it´s data are not frequently changed? >> >> For example: we have a table named "A" that receives an external data >> update, 3 times a day, at specific hours. >> After that load process, could we cache it so that the select >> statements, joins, and so on, have any speed improvement? >> >> Obs: if we simply enable sql cache on our instances, could we solve our >> problem for that situation? >> >> Regards, folks! >> >> Alexandre Marini >> >> Analista de Tecnologia da Informação - DBA >> >> SEFAZ-MS / UIMP / Sistemas: Fronteiras e SIG-DW >> >> John Miller iii escreveu: >> >>> If you are version 11 and want to kill idle user try this. >>> >>> http://www.ibm.com/developerworks/blogs/page/idsteam?entry=3Dterminate_= >>> idle_users_with_the >>> >>> John F. Miller III >>> STSM, Support Architect >>> miller3@us.ibm.com >>> 503-578-5645 >>> IBM Informix Dynamic Server (IDS) >>> >>> = >>> >>> From: "KAMRAN HAQ" <khaq@i2cinc.com> = >>> >>> = >>> >>> To: ids@iiug.org = >>> >>> = >>> >>> Date: 06/02/2009 08:52 AM = >>> >>> = >>> >>> Subject: trying to free virtual memory [15886] = >>> >>> = >>> >>> Sent by: ids-bounces@iiug.org = >>> >>> = >>> >>> Hi, >>> We are running more than one instance of Informix IDS on single machine= >>> .. We >>> >>> observed that there are some issues with our application that with the >>> passage >>> of time idle sessions keep on increasing (idle for days doing nothing).= >>> The >>> >>> virtual memory also keep on increasing. If we find and kill the long id= >>> le >>> sessions, even then virtual memory is not get free which can be used by= >>> >>> other >>> instance running on same machine. The only way to free that memory we f= >>> ound >>> is >>> to restart the instance. Please suggest, how can we cope with this prob= >>> lem. >>> >>> regards, >>> Kamran >>> >>> ***********************************************************************= >>> ******** >>> >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> = >>> >>> >>> >>> >> > ******************************************************************************* > >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> >>> >>> >>> >> >> >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> > > --00504502d6c4f5f28a046b85d16e > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >

Hi Alexandre, Perhaps running an update statistics on the table after the load would help. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Alexandre Marini Sent: 04 June 2009 03:40 PM To: ids@iiug.org Subject: Re: Cache some specific tables of any database [15940] Sorry about no specs. Our version is 11.50.FC4 running on a Linux_64 machine. Thanks in advance for your helps, mr. Kagel !!! Alexandre Marini Analista de Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / UIMP / Sistemas: Fronteiras e SIG-DW Art Kagel escreveu: > Two separate questions. First if you had supplied your platform and > version information - and EVERY initial post should supply such - I > could give you a better answer. As it stands: > > There were some older IDS versions that allowed you to make a table > MEMORY RESIDENT, it was not a particularly successful feature and was > removed in the very next release of both the 7 and 9 product code > lines. it is still possible in XPS (Informix version 8.xx). The > problem was that users over used the feature causing server performance overall to decrease markedly. > It turns out that the engine's FIFO policies, which were improved in > the releases that removed the MEMORY RESIDENT feature, does a far > better job at keeping important data in memory than you can. If you > are really having problems with the buffer cache churning data out of > memory too often (check the BTR - Buffer Turnover Rate - if it is over > 9 times per hour this is what's happening) increase the size of the > data buffer cache so that it is large enough to hold the current > working set of data at all times. Add memory to the system if you have > to. If you are having trouble determining what your working set size > is, contact a good database consulting group (Oninit comes to mind ;-) to help. > > Your second question: No, the statement cache will not improve data > cache usage at all. There are a few sites that are taking good > advantage of the statement cache, but TMK most sites that have tried > using it have experiences no positive effect or a performance > slowdown. That's counter intuitive, I know, and I once advocated using > the statement cache because it sounds like such a good idea, but reality is sneaky that way. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Oninit, the IIUG, nor any > other organization with which I am associated either explicitly or > implicitly. 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 Thu, Jun 4, 2009 at 9:01 AM, Alexandre Marini > <amarini@fazenda.ms.gov.br>wrote: > > >> Hello, friends! >> Is there some way to cache an specific database table to improve >> select operations, since it´s data are not frequently changed?>> >> For example: we have a table named "A" that receives an external data >> update, 3 times a day, at specific hours. >> After that load process, could we cache it so that the select >> statements, joins, and so on, have any speed improvement? >> >> Obs: if we simply enable sql cache on our instances, could we solve >> our problem for that situation? >> >> Regards, folks! >> >> Alexandre Marini >> >> Analista de Tecnologia da Informação - DBA >> >> SEFAZ-MS / UIMP / Sistemas: Fronteiras e SIG-DW >> >> John Miller iii escreveu: >> >>> If you are version 11 and want to kill idle user try this. >>> >>> http://www.ibm.com/developerworks/blogs/page/idsteam?entry=3Dtermina >>> te_= >>> idle_users_with_the >>> >>> John F. Miller III >>> STSM, Support Architect >>> miller3@us.ibm.com >>> 503-578-5645 >>> IBM Informix Dynamic Server (IDS) >>> >>> = >>> >>> From: "KAMRAN HAQ" <khaq@i2cinc.com> = >>> >>> = >>> >>> To: ids@iiug.org = >>> >>> = >>> >>> Date: 06/02/2009 08:52 AM = >>> >>> = >>> >>> Subject: trying to free virtual memory [15886] = >>> >>> = >>> >>> Sent by: ids-bounces@iiug.org = >>> >>> = >>> >>> Hi, >>> We are running more than one instance of Informix IDS on single >>> machine= .. We >>> >>> observed that there are some issues with our application that with >>> the passage of time idle sessions keep on increasing (idle for days >>> doing nothing).= The >>> >>> virtual memory also keep on increasing. If we find and kill the long >>> id= le sessions, even then virtual memory is not get free which can >>> be used by= >>> >>> other >>> instance running on same machine. The only way to free that memory >>> we f= ound is to restart the instance. Please suggest, how can we >>> cope with this prob= lem. >>> >>> regards, >>> Kamran >>> >>> ******************************************************************** >>> ***= >>> ******** >>> >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> = >>> >>> >>> >>> >> > **************************************************************************** *** > >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> >>> >>> >>> >> >> >> > **************************************************************************** *** > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> > > --00504502d6c4f5f28a046b85d16e > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ================== Please read our Email Disclaimer : http://www.thefuelgroup.com/disclaimer.html

Alexandre, On IDS 11.5, if you want to force a high priority table to stay in memory... Move your table to a dedicated dbspace with a unique page size and allocate enough buffers of the specified page size to hold the entire table. If your high priority table is the only one with a X page size, nothing will force its records from the buffer pool. Dave Griffen

Has anyone used a Ram-Disk based volume for Temp DB Space? I don't have a lot of data, just a lot of queries that select into temp. What do you think? Bad idea? Jonathan B. Smaby Pomona College phone: (909) 621-8506 email: jonathan.smaby@pomona.edu -- "Just because nobody complains doesn't mean all parachutes are perfect." - Benny Hill (1924 - 1992) ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

Jonathan Smaby wrote: > Has anyone used a Ram-Disk based volume for Temp DB Space? I don't have > a lot of data, just a lot of queries that select into temp. > > What do you think? Bad idea? In a production environment? Not sure I'd go there. Woould be pretty nippy, though! -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

I think it´s not pretty on our environments, ´cause any system failure would disappear with some data... but thanks a lot for your ideas... heheheheh Alexandre Marini Analista de Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / UIMP / Sistemas: Fronteiras e SIG-DW Obnoxio The Clown escreveu: > Jonathan Smaby wrote: > >> Has anyone used a Ram-Disk based volume for Temp DB Space? I don't have >> a lot of data, just a lot of queries that select into temp. >> >> What do you think? Bad idea? >> > > In a production environment? Not sure I'd go there. Woould be pretty > nippy, though! > >