MySQL – huge Dictionary memory allocated

It is ironic I answered a question earlier about InnoDB vs MEMORY storage engines.

There is something very weird about the MEMORY Storage Engine you must consider.

MEMORY tables perform full table locks each time there is an INSERT, UPDATE, and DELETE.

MEMORY tables still trigger a little disk I/O because the .frm file of the MEMORY table is a disk file that must be referenced with each query as far the table's existence goes and query parsing thereafter.

The default index type for a MEMORY table is the HASH index not BTREE. If you forget to declare USING BTREE, all range searches become table scans. HASH indexes are poor candidates for indexes to fulfill range queries. The query you have in the question body would quickly be victimized by this.

Even if you create indexing in the MEMORY table with the USING BTREE clause, BTREE indexes in RAM grow at a pace of O(log n) so expect disk I/O again for checking the index definition in the .frm file plus O(log n) running time on page access.

Another crazy thing to think about when using the MEMORY storage engine is this : If you try to join a MEMORY table and an InnoDB table, the resulting lock behavior defaults to the worst one, which in this case is full table locking.

CAVEAT

Others have answered question like this back in March 2011

• David Splllett's answer to What are reasons *not* to use the MEMORY storage engine in MySQL?
• Morgan Tocker's answer to What are reasons *not* to use the MEMORY storage engine in MySQL?

Here is one on why an all memory Database is good or bad : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.