Looking at the feature availability list at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html two possible problems jump out:
- No transaction or FK support, meaning you will have to manage transactional integrity and referential integrity in your own code were needed (which could end up being a lot less efficient than letting the DB do this for you, though that very much depends on your app's expected behaviour patterns).
- Table level locking only: this could be a significant barrier to scalability if your app needs multiple concurrent writers to the same set of tables or in cases where your read operations use locks to ensure consistent data is read - in such cases a disk based table that supports much finer lock granularity will perform far better if enough of its content is currently cached in RAM.
Other than that, assuming you have enough RAM, a memory based table should be faster than a disk based one. Obviously you need to factor in taking snapshots to disk to address the issue of what happens when the server instance is reset, which is likely to completely negate the performance benefit overall if the data needs capturing often (if you can live with losing a day of data in such an instance you could just take a backup once per day, but in most cases that would not be acceptable).
An alternative might be to:
- Use disk based tables, but ensure that you have more than enough RAM to hold them all in RAM at any given time (and "enough RAM" might be more than you think as you need to account for any other processes on the machine, OS IO buffers/cache and so forth)
- Scan the entire contents (all data and index pages) of the table on each startup to preload the content into memory with
SELECT * FROM <table> ORDER BY <pkey fields>
for each table followed by SELECT <indexed fields> FROM <table> ORDER BY <index fields>
for each index
This way all your data is in RAM, you only have to worry about I/O performance for write operations. If your app's common working set is much smaller than the whole DB (which it usually the case - in most applications most users will only be looking at the most recent data most if the time) you might be better of being more selective about how much you scan to preload into memory, allowing the rest to be loaded from disk on demand.
I would not recommend the MEMORY storage engine
REASON #1 : No Redundancy
Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.
REASON #2 : Mild Disk I/O
No matter what Storage Engine you choose, the .frm
of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.
Please read past posts on the pros and cons of the MEMORY Storage Engine
RECOMMENDATION
Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?
Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:
STEP 01) Create the table like this:
CREATE TABLE blacklist
(
url VARCHAR(20),
dt DATETIME,
PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;
STEP 02) Create a dedicated 16MB MyISAM cache for that table:
cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist; >> init-file.sql
STEP 03) Add this to /etc/my.cnf
[mysqld]
init-file=/var/lib/mysql/init-file/sql
STEP 04) Restart MySQL
service mysql restart
That's it.
Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause
. What that does is speed up character search 20-25% (I wrote about this before).
Why not use InnoDB?
- The data and index pages would have the data twice in RAM.
- Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
- Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.
Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.
Best Answer
There are two situations you must address
SITUATION #1 : TABLES TO CREATE
You must add this to
my.cnf
Then, restart mysql (MariaDB)
CAVEAT: This will not convert already existing tables to MEMORY tables.
What can done for those existing tables ?
SITUATION #2 : TABLES ALREADY EXISTING
Here is the script to convert all tables to MEMORY tables:
If you are satistied with the contents of the SQL Script, login to MariaDB and run
EPILOGUE
While I gave you the two major things to do to have everything as MEMORY tables, you should read my old posts on the good, the bad, and the ugly of using MEMORY tables:
May 22, 2011
: I am using the MEMORY storage engine but MySQL still writes to my disk...Why?Sep 26, 2011
: Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?Jan 17, 2012
: Mysql Memory table getting many locksJan 21, 2012
: Is InnoDB Engine up to speed against Memory Engine?Jan 30, 2012
: How much memory will a MEMORY table take up?