MySQL Forums
Forum List  »  Memory Storage Engine

What table shall I use? MEMORY table or InnoDB with BIG CACHE?
Posted by: Vrabie Cristian
Date: March 09, 2007 01:49PM

Hello,

I've been having a dilemma lately and since I'm not an mysql expert but an average user I hoped you can help me with some opinions.

I'm developing an website (portal actually) that will have a high number of hits. Some worst case scenarios include about 5000 - 10.000 users being logged the same time, navigaitng the portal.

The main layout will be organized on "modules" (squares on the main page). For example we have a module that shows one users horoscope, other the weather from his city, other the news he's interested in, another his/hers latest mails and so on (something like yahoo).

Even though this will run on it's own server (test server has 2 x dual core duo at 2GHz and 2GB of ram, and i have free hand to upgrade it up to 16GB of ram when we put this on the web) I am considering the traffic will rise very much in the next years and don't want to have trouble about it.

So i developed an caching system that will cache not the whole page but individual modules. For example, we don't need to query the mail server unless new mail has arrived but we can simply display the last html generated when we queried the mail server. The horoscope however needs daily updating, so we can have cache with one day lifetime.

The problem I’m confronting is the following: the caching table

CREATE TABLE `cache` (
`module` char(8) NOT NULL default '' COMMENT '',
`user` mediumint(8) unsigned NOT NULL default '0' COMMENT '',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '',
`cache` text NOT NULL COMMENT '',
PRIMARY KEY (`module`,`user`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

will be very stressed with lots and lots of selects and considerable number of inserts. I've decided to use InnoDB because of it's locking system. However i think the stress will generate a lot of overhead which, even with a daemon that optimizes the table everyday, is bad.

Now another idea came to me. I can use memory tables. The problem is that i can't predict the size of the cache (i now use text field), so in many cases I would have to spread the data on several records.

My question is: is this worth it? I know it would be considerably fast but I think that this method might generate some problems.

I would also appreciate very very much if you could give me some other advices. I've read lots from mysql documentation but I lack the experience with large systems so I could use any tip or peace of advice (no matter how small) you can give me. Once again, I thank you very much.

Options: ReplyQuote


Subject
Views
Written By
Posted
What table shall I use? MEMORY table or InnoDB with BIG CACHE?
6669
March 09, 2007 01:49PM


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.