How to Store Table in RAM in Informix

Hello All, I want to know that is there any funtionality of Informix that would allow me to store some of the tables in the RAM. I want to do this to increase the query performance? Is there any way, is it possible? I will be waiting for the response. Thanks. Omer Saeed Khan

Specific table I dont know, but is possible do , informix reserve a amount of RAM memory in onconfig configuration file, in section Shared Memory. Regards, Willy Calderon R. IT Consultant 2008/7/17, OMER KHAN <oskhan@i2cinc.com>: > > Hello All, > > I want to know that is there any funtionality of Informix that would allow > me > to store some of the tables in the RAM. > > I want to do this to increase the query performance? > > Is there any way, is it possible? > > I will be waiting for the response. Thanks. > Omer Saeed Khan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

2008/7/17 OMER KHAN <oskhan@i2cinc.com>: > Hello All, > > I want to know that is there any funtionality of Informix that would allow me > to store some of the tables in the RAM. > > I want to do this to increase the query performance? > > Is there any way, is it possible? > > I will be waiting for the response. Thanks. > Omer Saeed Khan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Omer Why do you think RAW tables will increase query performance? You shold be looking at indexes, update statistics, number of buffers, ReadAhead settings, possibly LRU settings, and at a lower level disk layout, disk controllers and/or system memory. Post your O/S, IDS version and some stats, these will be of more use than a nebulous question that will not provide the solution you need. Keith

> I want to know that is there any funtionality of Informix that would allow me > to store some of the tables in the RAM. SET TABLE ${TABLE} MEMORY_RESIDENT You can also set fragments of a table memory resident which often makes more sense. SET TABLE ${TABLE} ( ${DBSPACE}, ${DBSPACE} ) MEMORY_RESIDENT; But I think this feature was disabled as of 9.4? > I want to do this to increase the query performance? It is better to analyze queries and see what can be done to reduce their cost. > Is there any way, is it possible? > I will be waiting for the response. Thanks.

I don't think there is direct feature which will do this for you in the= current IDS release. But, I remember it was there either in XPS or some earlier versions of IDS.. and it was called. . memory residency.. Manoj = "OMER KHAN" = <oskhan@i2cinc.co = m> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect How to Store Table in RAM in = 07/17/2008 10:06 Informix [12744] = AM = = = Please respond to = ids@iiug.org = = = Hello All, I want to know that is there any funtionality of Informix that would al= low me to store some of the tables in the RAM. I want to do this to increase the query performance? Is there any way, is it possible? I will be waiting for the response. Thanks. Omer Saeed Khan ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

2008/7/17 Keith Simmons <smiley73@googlemail.com>: > 2008/7/17 OMER KHAN <oskhan@i2cinc.com>: >> Hello All, >> >> I want to know that is there any funtionality of Informix that would allow > me >> to store some of the tables in the RAM. >> >> I want to do this to increase the query performance? >> >> Is there any way, is it possible? >> >> I will be waiting for the response. Thanks. >> Omer Saeed Khan >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > Omer > > Why do you think RAW tables will increase query performance? You shold > be looking at indexes, update statistics, number of buffers, ReadAhead > settings, possibly LRU settings, and at a lower level disk layout, > disk controllers and/or system memory. > Post your O/S, IDS version and some stats, these will be of more use > than a nebulous question that will not provide the solution you need. > > Keith > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > My apologies, I misread RAM for RAW, but some of the comments still apply. However if your tables are small and frequently used thay will tend to remain in RAM (buffers) anyway. If you are getting better than 99% Read Buffer hits from onstat -p, then most of you reads are from buffers and will not get much faster. Let the engine sort out the memory allocatio, just give it as much memory as possible to play with. Keith

Earlier releases had a SET TABLE <tablename> RESIDENT; (7.25-7.30 & 9.14-9.21 IB) but that's been deprecated because the new cache aging algorithms are so much better that it was deemed unnecessary and usually hurt over all performance anyway. Remember that IDS ONLY accesses data in memory, so for all intents and purposes it is an in-memory database and for many applications it can keep up with dedicated in-memory DBs like TimesTen. If you need more performance than that there are two more stages available: - Use a RAM drive for those high performance tables (there's a bank with many TB of memory drives installed as IDS chunks) - Solid (an in-memory database that competes with TimesTen) now works with IDS 11.50 as its persistent backend. Since IBM bought Solid they're tightening the integration, but it works now. WHOOSH. Art On Thu, Jul 17, 2008 at 11:06 AM, OMER KHAN <oskhan@i2cinc.com> wrote: > Hello All, > > I want to know that is there any funtionality of Informix that would allow > me > to store some of the tables in the RAM. > > I want to do this to increase the query performance? > > Is there any way, is it possible? > > I will be waiting for the response. Thanks. > Omer Saeed Khan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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.

You do not specify what version you use. As somebody told before the set residency feature was disabled around 9.XX version. There is another way to try to keep some pages on memory provided by the variable page size capability. Create a dbspace with another pagesize than your other tables. Put your tables or indexes you want to stay longer on memory on that dbspace. You also have to define a buffer pool for that dbspace which will be the resident portion that will keep in memory all those pages you read. In our environment, heavily OLTP, we put only some indexes on those dbspaces. Don't forget that Informix use LRU mechanism, so if you have a page on memory used a lot, most likely that page will remain in memory. And then residency becomes a moot point. Walter Milan DBA -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith Simmons Sent: Thursday, July 17, 2008 10:53 AM To: ids@iiug.org Subject: Re: How to Store Table in RAM in Informix [12754] 2008/7/17 Keith Simmons <smiley73@googlemail.com>: > 2008/7/17 OMER KHAN <oskhan@i2cinc.com>: >> Hello All, >> >> I want to know that is there any funtionality of Informix that would allow > me >> to store some of the tables in the RAM. >> >> I want to do this to increase the query performance? >> >> Is there any way, is it possible? >> >> I will be waiting for the response. Thanks. >> Omer Saeed Khan >> >> >> > ************************************************************************ ******* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > Omer > > Why do you think RAW tables will increase query performance? You shold > be looking at indexes, update statistics, number of buffers, ReadAhead > settings, possibly LRU settings, and at a lower level disk layout, > disk controllers and/or system memory. > Post your O/S, IDS version and some stats, these will be of more use > than a nebulous question that will not provide the solution you need. > > Keith > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > My apologies, I misread RAM for RAW, but some of the comments still apply. However if your tables are small and frequently used thay will tend to remain in RAM (buffers) anyway. If you are getting better than 99% Read Buffer hits from onstat -p, then most of you reads are from buffers and will not get much faster. Let the engine sort out the memory allocatio, just give it as much memory as possible to play with. Keith ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Hello Art, Thanks for all the responses! I will certaininly use the RAM disk and would create a dbspace in the RAM, please let me know that do i also have to follow any particular guidelines for creating the dbchunks in the RAM or its the same as normal? what page size do i have to set? or any thing else? Also i want to know a bit more about the usage of SolidDb within IDS 11.50, can you send me some sort of link or information about how to use SolidDb within IDS 11.50. Rightnow we are using IDS 10.0 FC4, but we are planning and preparing for the upgrade to IDS11.50 and want to use all the newly provided features. Also please comment that would you suggest to upgrade the production load to IDS11.50 or we should wait for more stable versions (somebody told me to waiting until more stable versions arrive), to avoid any problem. Please comment! I will be waiting for your response. Thanks. Regards, Omer Saeed Khan

OOOOOOOOOOOOOOO MAJOR MISUNDERSTANDING and my fault! My language was imprecise. You cannot use software RAM DISKS for chunks (there might be some discussion of using them for temp dbspaces but I don't remember - see the performance guide). they are not persistent and if the system crashes your database is hosed and you'll have to restore from archive and roll forwards logical logs. What I was discussing are those Solid State Disk units that pretend to be disk drives but are really made up of very high speed flash memory. Someone actually reported to me that they used a 16x 4GB memory card for that purpose and it was FAST, haven't tried it by 4GB HSD card is only 2x or 4x speed. Sandisk among others market flash based drives. Throughput is impressive for reads and access latency is measurably zero, but write times are not good and don't scale well. However, a RAID0 or N units (or RAID10 or N pairs of units) will scale read throughput linearly so N time single unit throughput. Art On Thu, Jul 17, 2008 at 1:23 PM, OMER KHAN <oskhan@i2cinc.com> wrote: > Hello Art, > > Thanks for all the responses! > > I will certaininly use the RAM disk and would create a dbspace in the RAM, > please let me know that do i also have to follow any particular guidelines > for > creating the dbchunks in the RAM or its the same as normal? what page size > do > i have to set? or any thing else? > > Also i want to know a bit more about the usage of SolidDb within IDS 11.50, > can you send me some sort of link or information about how to use SolidDb > within IDS 11.50. > > Rightnow we are using IDS 10.0 FC4, but we are planning and preparing for > the > upgrade to IDS11.50 and want to use all the newly provided features. Also > please comment that would you suggest to upgrade the production load to > IDS11.50 or we should wait for more stable versions (somebody told me to > waiting until more stable versions arrive), to avoid any problem. Please > comment! > > I will be waiting for your response. Thanks. > > Regards, > Omer Saeed Khan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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.

OMER KHAN said: > Hello All, > > I want to know that is there any funtionality of Informix that would allow > me > to store some of the tables in the RAM. > > I want to do this to increase the query performance? > > Is there any way, is it possible? > > I will be waiting for the response. Thanks. Whenever I see this kind of thing, my initial reaction is always to ask: "Why?" Why do you think it will help? -- Bye now, Obnoxio http://obotheclown.blogspot.com/

How about using solidDB Cache for IDS? http://www-306.ibm.com/software/data/soliddb/ HTH Hrvoje -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of OMER KHAN Sent: Thursday, July 17, 2008 5:06 PM To: ids@iiug.org Subject: How to Store Table in RAM in Informix [12744] Hello All, I want to know that is there any funtionality of Informix that would allow me to store some of the tables in the RAM. I want to do this to increase the query performance? Is there any way, is it possible? I will be waiting for the response. Thanks. Omer Saeed Khan **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Tables are stored in pages, pages are stored in chunks, chunks have to live in disks, SAN, or some physical disk media. Unless you want to try using Solid State Disks for a $$$ premium, your I/O performance is still limited by the IDE, SCSI, SATA, or SAS bus max through put. I you really need some I/O, I would suggest a nice little Fibre-Channel SCSI enclosure @ 4GB with a 4GB HBA card, setup for Raid 10. That ought to be pretty fast for your table fragment pages/chunks. Jonathan Smaby Pomona College ITS Department "Buffered is great for Aspirin, but sucks for Logging", "Friends don't let friends use RAID 5". > From: OMER KHAN <oskhan@i2cinc.com> > Reply-To: <ids@iiug.org> > Date: Thu, 17 Jul 2008 11:06:17 -0400 (EDT) > To: <ids@iiug.org> > Subject: How to Store Table in RAM in Informix [12744] > > Hello All, > > I want to know that is there any funtionality of Informix that would allow me > to store some of the tables in the RAM. > > I want to do this to increase the query performance? > > Is there any way, is it possible? > > I will be waiting for the response. Thanks. > Omer Saeed Khan > > > ****************************************************************************** > * > Forum Note: Use "Reply" to post a response in the discussion forum. > ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

Hi ... I think you can use RAM disk as the chunk , IDS access shared memory to satisfy your read/write requests , and those data in shared memory will [read from/flush to] chunk , so if your chunk is a RAM disk , then you don't have a Hard Disk I/O and you get the performance ~~~ I've considered to use RAM disk recently in my case, I'd like to create 5 to 10 tables ,each one will have only several data pages in that no-logging database whose chunk is in RAM disk,if the IDS crush abnormally for any reason , I don't know whether I can still oninit to start engine .... because while I oninit , the database created in RAM disk already not there , Can I oninit the engine ?!...can I drop the Database after I can start the engine?! if those 2 questions are "yes" ... then RAM disk might get a good performance ~~

One solution that has been raised before on this topic is to configure a space with a different sized page from the rest of your databases on the server. You can configure a BUFFERPOOL for this page size large enough to hold all your data. Put the target table on this space. The first time data from the table gets read, it will be read into memory. If your BUFFERPOOL is set large enough, the data should stay there. An interesting project that may give you the same results though would be to develop some bladelets - however this is a lot harder to achieve. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan Smaby Sent: 18 July 2008 12:33 AM To: ids@iiug.org Subject: Re: How to Store Table in RAM in Informix [12766] Tables are stored in pages, pages are stored in chunks, chunks have to live in disks, SAN, or some physical disk media. Unless you want to try using Solid State Disks for a $$$ premium, your I/O performance is still limited by the IDE, SCSI, SATA, or SAS bus max through put. I you really need some I/O, I would suggest a nice little Fibre-Channel SCSI enclosure @ 4GB with a 4GB HBA card, setup for Raid 10. That ought to be pretty fast for your table fragment pages/chunks. Jonathan Smaby Pomona College ITS Department "Buffered is great for Aspirin, but sucks for Logging", "Friends don't let friends use RAID 5". > From: OMER KHAN <oskhan@i2cinc.com> > Reply-To: <ids@iiug.org> > Date: Thu, 17 Jul 2008 11:06:17 -0400 (EDT) > To: <ids@iiug.org> > Subject: How to Store Table in RAM in Informix [12744] > > Hello All, > > I want to know that is there any funtionality of Informix that would > allow me > to store some of the tables in the RAM. > > I want to do this to increase the query performance? > > Is there any way, is it possible? > > I will be waiting for the response. Thanks. > Omer Saeed Khan > > > **************************************************************************** ** > * > Forum Note: Use "Reply" to post a response in the discussion forum. > ------------------------------------------------------------- This message has been scanned by Postini anti-virus software. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.