How does MySQL 5.5 and InnoDB on Linux use RAM?

Question

Does MySQL 5.5 InnoDB keep indexes in memory and tables on disk? Does it ever do it's own in-memory caching of part or whole tables? Or does it completely rely on the OS page cache (I'm guessing that it does since Facebook's SSD cache that was built for MySQL was done at the OS-level: https://github.com/facebook/flashcache/)? Does Linux by default use all of the available RAM for the page cache? So if RAM size exceeds table size + memory used by processes, then when MySQL server starts and reads the whole table for the first time it will be from disk, and from that point on the whole table is in RAM? So using Alchemy Database (SQL on top of Redis, everything always in RAM: http://code.google.com/p/alchemydatabase/) shouldn't be much faster than MySQL, given the same size RAM and database?

Answer 1

Read up on innodb_buffer_pool_size.

That's how you tell innodb how much RAM to hold on to for caching. (not using OS page cache)

MySQL 5.5 docs - http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html

A Nice Blog to Follow - http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

Think of Flashcache as a L1 SSD cache sitting in front of your HDD.

I can't speak to the performance of Redis vs MySQL, but MySQL does have other performance overhead in processing statements, ACID compliance, things like that...

If you're all in RAM, performance will be "GOOD" on any system.