Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from serverfault.com Jan 16 '12 at 14:48

This question came from our site for professional system and network administrators.

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
1  
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? –  Mr.Boon Jan 16 '12 at 16:40
    
@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). –  Nick Chammas Jan 17 '12 at 1:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.