Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM? I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that one has a server, with this kind of memory? I should mention that the queries towards the table are mostly ad-hoc queries.

One issue I believe I have found that would arise is that queries towards the MEMORY table could cause temporary tables to get created. Temporary tables have a hard limit of utilizing up to 4 GB of RAM before they get converted to a MyISAM table. This would of course kill performance completely. The memory limit of temporary tables could be worked around by setting the tempdir to a ramdisk.

What other kinds of problems do you foresee?
The MEMORY storage engine can either be a blessing or a curse depending on


what you intend to store
how often you plan to perform DML
how much RAM you leaving for the

Database caches (MyISAM Key Cache, InnoDB Buffer Pool)
OS caches
OS operation



The MEMORY storage engine


uses full table locking for INSERTs, UPDATEs, and DELETEs
Cannot perform concurrent INSERTs
uses the hash indexes instead of BTREE indexes by default
can use BTREEs indexes, but must be explicitly specified at CREATE TABLE time
has no transaction support
Single row queries are just great against MEMORY tables, especially using HASH indexes -- Ranged queries and sequential access are just horrific unless you use BTREE explicitly (more memory consumption required)


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.