Re: memory resident tables

In article <36707D9B.5116F322@hotmail.com>, June Tong <june_t@hotmail.com> wrote: > 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. ok, here is the sequence of commands I executed to test this: 1) onstat -z //zero out stats 2) onstat -p //check if all stats are zero 3) dbaccess db < cmd // cmd contains "set table A memory_resident;" 4) dbaccess db < cmd2 // cmd2 contains "select * from A;" 5) onstat -p // look at number of disk reads 6) onstat -z // zero out stats again 7) onstat -p // check if all stats are zero 8) dbaccess db < cmd2 9) onstat -p at this point, I expected to see a close to 0 number for disk reads, because the entire table should be in memory by now, but the number of disk reads is about the same as in 5) table A is about 40 MB, while we have 4 GIG of main memory, and here are my onconfig parameters for shared memory: LOCKS 500000 # Maximum number of locks BUFFERS 1000000 # Maximum numberof shared buffers NUMAIOVPS # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes) LOGSMAX 6 # Maximum number of logical log files CLEANERS 2 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 100000 # initial virtual shared memory segment size ##SHMADD8192 # Size of new shared memory segments (Kbytes) SHMADD 65536 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 3000 # Check point interval (in sec) LRUS 8 # Number of LRU queues LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water mark percentage LTXEHWM 60 # Longtransaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) if anyone could explain why the informix engine (btw, we use IDS 7.3) keeps going back to the disk to read that 40MB table, that would be great. > > > 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 my example is where the database is hooked up to a web server with lots of requests coming in. In that case, you would want the time spent accessing data from the database to be as short as possible in order to increase throughput. If you know all the requests are going against a few tables only, then you would want to keep those tables in memory always. Alfred > 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 > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own