> The fact that it's taking you 9ms to do each index lookup
> suggests to me that
> it's going to disk each time. Does that sound plausible, or do
> you think you
> have enough RAM to cache your large indexes?
I'm sure we don't have enough RAM to cache all of our large indexes, so your
supposition makes sense. We have 1GB on this machine. In responding to the
performance problems we're having, one of the questions has been adding
memory vs crafting "helper" tables to speed things up. The issue is that
this database needs to be able to scale easily to about 10 times the size,
so although we could easily triple the memory at reasonable expense, we'd
eventually hit a wall.
Is there any solid method to insure that a particular index always resides
in memory? A hybrid approach that might scale reliably would be to bump up
our memory and then make sure key indexes are cached. however, I'm concerned
that if we didn't have a way to ensure that the indexes that we choose
remain cached, we would have very inconsistent responses.
Thanks for your ideas!
-Nick