InnoDB table vs MEMORY table for a mysql environment with many inserts

Question

I run my sites all on InnoDB tables which is working really well so far. Now I like to know what is going on in real-time on my sites, so I store each pageview (page, referrer, IP, hostname, etc) in an InnoDB table. There are about 100 inserts per second, and this table is only read once in a while when i'm browsing the logs.

I clean out the table every minute with a cron that removes old items. This leaves about 35.000 rows in that table on average, with a size of about 5MB.

Would it be easier on the server if I were to transfer the InnoDB table to a MEMORY table? As far as I can see this would save a lot of disk IO right? Restarting Mysql would result in a loss of data, but this does not matter in my case.

Question: In my case, would you recommend a Memory table over a InnoDB table?

Answer 1

Clearly, it will be faster and more efficient to write/read to/from memory. In your case, losing the data is not a big issue as you stated. However, you need to be careful about the memory usage and limit the number of rows stored.

• Mr.Boon: I've switched to a MEMORY table, and performance is good. I did notice that my Lock Wait Ratio is kinda bad now. Current Lock Wait ratio = 1 : 570 is what Tuning-primer says. Anything I can do about that?
• Nick Chammas: @Godius - Does it have any visible impact on your query times? Regardless, this probably merits a question of its own right (with more detail, of course).