MySQL - How to determine if my table is stored in RAM?

Question

I'm running:

MySQL v5.0.67 
InnoDB engine 
innodb_buffer_pool_size = 70MB 

Question: What command can I run to ensure that my entire 50 MB database is stored entirely in RAM?

Answer 1

I am curious about why you want to store the entire table in memory. My guess is that you are not. The most important thing for me is if your queries are running well and if you are tied up on disk access. It is also possible that the OS has cached disk blocks that you need if there is memory available. In this case, even though MySQL might not have it in memory, the OS will. If your queries are not running well, and you can do it, I highly recommend adding more memory if you want it all in RAM. If you have slowdowns it is more likely that you are running into contention.

show table status

will show you some of the information.

If you get the server IO/buffer/cache statistics from

show server status

and then run a query that requires each row to be accessed (say sum the non empty values from each row using a column that is not indexed) and check to see if any IO has occurred.

I doubt you are caching the entire thing in memory though with only 70MB. You have to take out a lot of cache, temp, and index buffers from that total.

Jacob