Re: memory resident tables

awallner@eb.com wrote: > 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 ? How do you know that the table wasn't cached in memory before you set it memory_resident? As has been pointed out, because of the way the LRU queues work, heavily used tables tend to stay in memory, regardless of the memory_resident setting. Therefore, there would be no difference between the performance before and after setting it. > 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. Probably because it's new and isn't that useful. My impression is that it's one of those things that was added because enough people who were used to "another database product" requested it. I was hard-pressed to come up with any example where it would actually help (actually, I never did come up with one). Maybe I should save Art's post to remind me, but even that, I think, is a bit contrived. June -- june_t@hotmail.com Grounded in Palo Alto, living on chocolate chip cookies

In article <36707D9B.5116F322@hotmail.com>, June Tong <june_t@hotmail.com> writes >> > <Someone else writes> >> > 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. >> 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. > >Probably because it's new and isn't that useful. My impression is that it's one >of those things that was added because enough people who were used to "another >database product" requested it. I was hard-pressed to come up with any example >where it would actually help (actually, I never did come up with one). Maybe I >should save Art's post to remind me, but even that, I think, is a bit contrived. > Many users all accessing small 'reference data' tables every few tens of seconds. One user doing a sequential scan of several large tables and hence keeps pushing the pages from the small tables out of the buffer cache. E.g. large report which runs for several hours. The 1 user will not revisit the rows once they have been scanned hence caching them does not make sense, but Online will still keep them in the cache. The many users will reuse the pages but not immediately hence they are not MRU and get pushed out of the cache. i.e. where the MRU is not the best caching policy... >June >-- >june_t@hotmail.com >Grounded in Palo Alto, living on chocolate chip cookies > > -- David Williams