Is InnoDB Engine up to speed against Memory Engine?

Question

I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows:

SELECT P.col1, P.col2, P.col3, P.col4, P.col5, P.col6, P.col7, P.col8, P.col9
 , P.col10, P.col10 * R.col3 as 'combi' 
FROM PRODUCT P INNER JOIN RATE R ON R.col2 = P.col2 
WHERE P.col3 = 'y' 
 AND P.col4 >= 1000 
 AND P.col5 >= 5 
 AND P.col6 BETWEEN 10 AND 100 
 AND P.col7 >= 0 
 AND P.col8 >= 7 
 AND P.col9 >= NOW() 
 AND P.col10 * R.col3 BETWEEN 50 AND 80
ORDER BY P.col8 DESC LIMIT 100;

Based on some discussion on Stackoverflow, I learnt that I may be able to load the database into the RAM by setting InnoDB parameter innodb_buffer_pool_size larger than the size of the dataset. Following the adjustment of this parameter, I am disappointed that the speed of the query wasn't even faster than MyISAM on most occasions (average of 3s versus 0.3s) and 100 times slower than Memory.

Upon further examination of the MySQL manual, it stated the following two points:

• InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory.
• The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads.

It seems to me that innodb_buffer_pool_size is more for caching the results of repeated queries than to provide a hot copy of the database from which to run queries. Is my understanding correct or am I missing out something that would allow InnoDB Engine to match Memory Engine, performance-wise for non-repeating range queries such as the one above?

UPDATE:

Frederick Cheung pointed out correctly that InnoDB buffer pool does cache a hot copy of the database. What I missed out was a loading procedure found in the link given by Rolando:

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.

Upon further testing, I am able to bring down the query times (0.3 to 1.0s) to a level comparable to that of MyISAM Engine. But this is still about seven times slower than that obtained with Memory Engine.

Since both engines are querying the database from RAM, can anyone tell me why InnoDB Engine is still not up to speed against Memory Engine?

Answer 1

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?

Answer 2

MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables along with many others. MySQL does this through their Pluggable Storage Engine Architecture. Both have some pro and cons. We can divide up the core functionality/Performence into four areas;

1. The supported field and data types
2. locking types
3. indexing and
4. transactions

Some engines have unique functionality that can also drive your decision.

The MEMORY storage engine stores all data in memory; once the MySQL server has been shut down any information stored in a MEMORY database will have been lost. However, the format of the individual tables is kept and this enables you to create temporary tables that can be used to store information for quick access without having to recreate the tables each time the database server is started.

Long term use of the MEMORY storage engine is not generally a good idea, because the data could so easily be lost. However, providing you have the RAM to support the databases you are working on, use of MEMORY based tables is an efficient way of running complex queries on large data sets and benefitting from the performance gains.

The best way to use MEMORY tables is to use a SELECT statement to select a larger data set from your original, disk-based, tables and then sub-analyse that information for the specific elements you want.

The InnoDB Engine supports all of the database functionality (and more) of MyISAM engine and also adds full transaction capabilities (with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance) and row level locking of data.

The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk. This enables very fast recovery, and works even on very large data sets. By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.

If you are willing (and able) to configure the InnoDB settings for your server, then I would recommend that you spend the time to optimize your server configuration and then use the InnoDB engine as the default.

Refer this article for more details: MySQL Storage Engines – their limitations and an attempt for comparison