Re: memory resident tables

In article <913020553snz@kontron.demon.co.uk>, andy@kontron.demon.co.uk wrote: > In article <74c6st$ukq$1@nnrp1.dejanews.com> awallner@eb.com writes: > > > I am trying to use the not well documented command: > > set table <tablename> memory_resident; > > to force tables to stay in main memory. > > The command executes ok, but I don't see the expected performance > > improvement. > > Has anyone successfully used this and obtained significantly better > > response times ? > > Alfred Wallner > > I'm not sure how I'd quantify performance improvement for this.. > If a table sees 'heavy' use then it will effectively be 'memory resident' > because the info will all be present in the LRUs, dic etc. Forcing a table > to be memory resident will not help in this case. yes, it's a heavily used table which we access from our web server. I would like to avoid any disk access when querying this table. We have tons of main memory and given that this table is getting accessed constantly, I feel there is potential for significant response time improvement if the entire table and it's indices were cached in memory. But when I do load testing and look at vmstat outputs, I still see the same number of disk accesses and measure the same average response time. If this command worked the way I think it should, there should be no more disk reads and therefore improved response times, right, since going to disk is the costly operation ? Or am I overlooking some other important factor ? > > For a lightly used table, forcing it to be resident may help in those > 'rare' occasions when the table is accessed, and give snappier > results for the 'once a day' user. (but presumably to the detriment of > other users whose tables are 'coming and going' normally). > > Real improvement would also depend on the number of rows to be returned > from this table. Sure, the first row may come back faster, but if there > are another 20,000 following it then that may be the major factor. > > Perhaps the poor documentation reflects the poor gains to be achieved.... > that is the impression that I got as well. I have spent much time talking to informix tech support, but I haven't found a single support person who is actually familiar with this option. > Andy. > > -- > Andy Lennard andy@kontron.demon.co.uk > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own