MySQL Forums
Forum List  »  Memory Storage Engine

Using Memory table as cache in HIGH WRITE environment
Posted by: Dewey Gaedcke
Date: January 01, 2007 09:06PM

I have some data intensive aggregate calculations that are run PER USER. The results of this aggregation is needed constantly, but does NOT have to be perfect (ie the latest result). They can be several hours or even days old. Therefore, to reduce load on my system, I'm writting the latest aggregate values to a memory table with an "Insert Date/Time" for proper aging and replacement.

The query to load these values always checks the cache first and in some cases "ages" the Insert time to make the cache expire faster. If the value is not found in the cache, then we run the heavy aggregate calculations and tax my server more than I like.

It's working fine in development/test, and seems like a good solution. But I just discovered that MEMORY tables to TABLE LOCKS for Inserts, Updates & Deletes.

Since this cache is getting constantly updated (Inserts, Updates (for aging) and deletes (for old records), I'm afraid that the table locks will block my Reads and cause me a bigger bottleneck than I would have with the aggregate calculations??

Any advice or experience would be most appreciated!!

Options: ReplyQuote

Written By
Using Memory table as cache in HIGH WRITE environment
January 01, 2007 09:06PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.