Let's say I have a MyISAM table with a data length of 4.8GB and an index length of 6.2GB. So, a total data size of eleven gig. How much memory would this require, were I to convert it to a
MEMORY table? 11 gig, or more?
The exact memory requirement of a row is calculated from the following formula:
So, on a 64-bit machine, replace
You can get an estimate of the
Then you add up all your BTREE keys and then HASH keys. Note that it might be worth it space-wise to convert any keys to HASH, as they require less memory.
I was going to mention the limitation of maximum MEMORY size dependent on
Here is yet another perspective to keep in mind:
The MEMORY storage engine
Even though you have data in RAM, mysqld will always hit the .frm file to check for the table existing as a reference point, thus always incurring a little disk I/O. Proportionally, heavy access to a MEMORY storage engine table will have noticeable disk I/O.
You must also remember to strike a proper balance of MEMORY table usage with
You can set this per table by setting max_heap_table_size per session.
But 12GB or so is a lot of memory for a caching table...