Analysis of MySQL Memory storage engine

Later saw the blog Park in the use of NorthScale Memcached Server (Official Site: http://www.couchbase.com/products-and-services/memcached), it seems to share the charges, and hesitated. In fact, the requirements in the project are very simple and you also want to use them on your own. net Cache, but the stability is difficult to assess, the development and maintenance costs seem too high, there is no way, My SQL Memory Storage becomes the only choice, because almost no code needs to be written.

First read the official manual and then write a simple performance test. Because the latest official documents are in English, MySQL Memory Storage 5.5 is translated.

Official documentation (translated from Version 5.5 of The Memory Storage Engine)
The Memory storage engine Stores Table data in the Memory. Memory replaces the previous Heap as a preference, but it is also backward compatible, and Heap is still supported.

Memory storage engine features:

Storage limits	RAM	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial data type support	No	Geospatial indexing support	No
B-tree indexes	Yes	Hash indexes	Yes	Full-text search indexes	No
Clustered indexes	No	Data caches	N/	Index caches	N/
Compressed data	No	Encrypted data	Yes	Cluster database support	No
Replication support	Yes	Foreign key support	No	Backup/point-in-time recoveryc	Yes
Query cache support	Yes	Update statistics for data dictionary	Yes

Comparison between Memory and MySQL Cluster

Developers who want to deploy the Memory engine will consider whether MySQL Cluster is a better choice. refer to the following Memory Engine Use scenarios and features:

It is as convenient for operation and management as Session or cache.
Make full use of the features of the memory engine: high speed and low latency.
Read-only or read-dominated access mode (not suitable for frequent writes ).
However, the performance of the memory table is subject to the execution efficiency of a single thread and the table lock overhead during write operations. This limits the scalability of the memory table when the load is high, especially the concurrent processing of mixed write operations. In addition, data in the memory table is lost after the server is restarted.

MySQL Cluster supports the same features as the Memory engine and provides higher performance. It also has more features not supported by Memory:

The row lock mechanism better supports multi-thread multi-user concurrency.
Better support for read/write hybrid statements and extensions.
You can choose a disk storage medium to permanently save data.
Shared-nothing and distributed architecture ensure no spof and 99.999% availability.
Data is automatically distributed across nodes, so application developers do not need to consider partitioning or partitioning solutions.
Supports variable-length data types (including BLOB and TEXT) that are not supported in MEMORY ).
For more details about the comparison between the MySQL Cluster and the Memory Engine, see Scaling Web Services with MySQL Cluster: An Alternative to the MySQL Memory Storage Engine, this White Paper includes performance research on these two technologies and provides step-by-step guidance on migrating Memory users to MySQL clusters.

Each Memory table is associated with a disk file. The file name starts with the table name and is defined by a. frm extension. You can use the ENGINE option to specify a Memory table:

Create table t (I INT) ENGINE = MEMORY;
As indicated by their names, Memory tables are stored in the Memory and hash indexes are used by default. This makes them very fast and useful for creating temporary tables. However, when the server is shut down, all data stored in the Memory table is lost. Because the table definition is stored in the. frm file on the disk, the table itself continues to exist and is empty when the server restarts.

This example shows how you can create, use, and delete a Memory table:
Copy codeThe Code is as follows:
Create table test ENGINE = MEMORY;
SELECT ip, SUM (downloads) AS down FROM log_table group by ip;
Select count (ip), AVG (down) FROM test;
Drop table test;

MEMORY tables have the following features:

The space for the Memory table is allocated in small blocks. 100% dynamic hash is used for table insertion. No overflow zone or extra key space is required. The Free List does not require additional space. Deleted rows are placed in a linked list and used again when you insert new data into the table. Memory tables do not usually have problems related to deleting and inserting hash tables.
A memory table can contain up to 64 indexes, 16 columns for each index, and a maximum key length of 3072 bytes.
The MEMORY storage engine supports HASH and BTREE indexes. You can specify one or another for a given index by adding a USING clause as follows:
Copy codeThe Code is as follows:
Create table lookup
(Id INT, index using hash (id ))
ENGINE = MEMORY;
Create table lookup
(Id INT, index using btree (id ))
ENGINE = MEMORY;

If the hash index key of a MEMORY table is highly duplicated (many index entries contain the same value), updates related to the index key and all deletions will be significantly slower. The repeatability is proportional to the speed. You can use the BTREE index to avoid this problem.
The MEMORY table can use a non-unique key. (Implementation of hash indexes, which is not commonly used)
Index of a column that can contain NULL values
The MEMORY table uses a fixed record length format. variable-length types such as VARCHAR are converted to fixed-length types and stored in the MEMORY table.
MEMORY cannot contain BLOB or TEXT columns.
MEMORY supports AUTO_INCREMENT Columns
The MEMORY table supports insert delayed.
Non-Temporary MEMORY tables are shared among all clients, just like any other non-temporary tables.
The MEMORY table content is stored in the MEMORY. It is used as the shared medium for creating internal temporary tables in the dynamic query queue. However, the difference between the two types of tables is that MEMORY tables do not encounter storage conversion, the internal table will:
1. MEMORY tables are not converted to disk tables, but internal temporary tables are automatically converted to disk tables if they are too large.
2. The maximum value of a MEMORY table is limited by the system variable max_heap_table_size. The default value is 16 MB. To change the MEMORY table size, you need to change the value of max_heap_table_size. This value takes effect at create table and is accompanied by the TABLE's lifecycle. (when you use the alter table or truncate table command, the maximum limit of the TABLE will change, or when you restart the MYSQL service, the maximum limit for all existing MEMORY tables will be reset using the value of max_heap_table_size .)
The server needs enough MEMORY to maintain all MEMORY tables used at the same time.
If a row is deleted, memory is not recycled from the memory table. Memory is recycled only when all the tables are deleted. At the same time, the memory space of the previously deleted row is used only when a new row is inserted in the same table. To release the MEMORY space occupied by deleted rows, use alter table engine = MEMORY to forcibly recreate the TABLE. When the content expires, you can DELETE or truncate table to clear all rows, or use drop table to DELETE the TABLE.
When the MySQL server is started, if you want to fill the MEMORY table, you can use the -- init-file option. For example, you can put statements such as insert into... SELECT or load data infile into this file to LOAD tables from a persistent and stable DATA source.
If you are using replication, the MEMORY table of the master server becomes empty when the master server is shut down and restarted. However, the server does not realize that these tables have become empty, so if you select data from them, it will return outdated content. After the server is started, when a MEMORY table is used for the first time on the master server, a delete from statement is automatically written into the binary log of the master server, so synchronize the slave server with the master server again. Note that even if this policy is used, the slave server still has outdated data in the table at the interval between the restart of the master server and the first time it uses the table. However, if you use the -- init-file option to implement the MEMORY table on the master server when it is started. It ensures that the interval is zero.
In the MEMORY table, the MEMORY required for a row is calculated using the following formula:
Copy codeThe Code is as follows:
SUM_OVER_ALL_BTREE_KEYS (max_length_of_key + sizeof (char *) * 4)
+ SUM_OVER_ALL_HASH_KEYS (sizeof (char *) * 2)
+ ALIGN (length_of_row + 1, sizeof (char *))

ALIGN () represents the round-up factor, which makes the row length an exact multiple of the char pointer size. Sizeof (char *) is 4 on 32-bit machines and 8 on 64-bit machines.
As mentioned above, the system variable max_heap_table_size is used to set the maximum size of the memory table. To control the maximum value of a single table, you must set the session variable before creating the table. (Do not set the value of global max_heap_table_size unless you want to use this value for all memory tables created on the client)
The following example creates two memory tables with a size limit of 1 MB and 2 MB respectively:
Copy codeThe Code is as follows:
SET max_heap_table_size = 1024*1024;
/* Query OK, 0 rows affected (0.00 sec )*/

Create table t1 (id INT, UNIQUE (id) ENGINE = MEMORY;
/* Query OK, 0 rows affected (0.01 sec )*/

SET max_heap_table_size = 1024*1024*2;
/* Query OK, 0 rows affected (0.00 sec )*/

Create table t2 (id INT, UNIQUE (id) ENGINE = MEMORY;
/* Query OK, 0 rows affected (0.00 sec )*/

If the service is restarted, the size limit of the two tables will be restored using the Global max_heap_table_size value.
You can also use the MAX_ROWS option of create table to set the maximum number of rows in the TABLE. However, the priority of max_heap_table_size is higher than that of MAX_ROWS. To ensure maximum compatibility, you need to set a reasonable value for max_heap_table_size.

Memory storage engine Forum: http://forums.mysql.com/list.php? 92

Performance Testing

The InnoDB, MyIsam, and Memory engines of MySQL are tested and compared respectively. net DataTable Insert and Select performance (the bar chart shows the time consumed, measured in S. The innodb_flush_log_at_trx_commit parameter is set to 1, and MySQL is restarted in each test to avoid Query Cache). The result is as follows:

Write 10000 records for comparison.

Read 1000 records for comparison.

Test script:

Copy codeThe Code is as follows:
/*************************************** ***************
MYSQL STORAGE ENGINE TEST
Http://wu-jian.cnblogs.com/
2011-11-29
**************************************** **************/
Create database if not exists test
Character set 'utf8'
COLLATE 'utf8 _ general_ci ';
USE test;
/*************************************** ***************
1. INNODB
**************************************** **************/
Drop table if exists test_innodb;
Create table if not exists test_innodb (
Id int unsigned AUTO_INCREMENT COMMENT 'pk ',
Obj CHAR (255) not null default ''comment 'object ',
Primary key (id)
) ENGINE = INNODB;
/*************************************** ***************
2. MYISAM
**************************************** **************/
Drop table if exists test_myisam;
Create table if not exists test_myisam (
Id int unsigned AUTO_INCREMENT COMMENT 'pk ',
Obj CHAR (255) not null default ''comment 'object ',
Primary key (id)
) ENGINE = MYISAM;
/*************************************** ***************
1. MEMORY
**************************************** **************/
Drop table if exists test_memory;
Create table if not exists test_memory (
Id int unsigned AUTO_INCREMENT COMMENT 'pk ',
Obj CHAR (255) not null default ''comment 'object ',
Primary key (id)
) ENGINE = MEMORY;

Test code:
Copy codeThe Code is as follows:
Using System;
Using System. Data;
Using MySql. Data. MySqlClient;
Namespace MySqlEngineTest
{
Class Program
{
Const string OBJ = "The MEMORY storage engine creates tables with contents that are stored in memory. formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility. ";
Const string SQL _CONN = "Data Source = 127.0.0.1; Port = 3308; User ID = root; Password = root; DataBase = test; Allow Zero Datetime = true; Charset = utf8; pooling = true ;";
Const int LOOP_TOTAL = 10000;
Const int LOOP_BEGIN = 8000;
Const int loop_end= 9000;
# Region Database Functions
Public static bool DB_InnoDBInsert (string obj)
{
String commandText = "insert into test_innodb (obj) VALUES (? Obj )";
MySqlParameter [] parameters = {
New MySqlParameter ("? Obj ", MySqlDbType. VarChar, 255)
};
Parameters [0]. Value = obj;
If (DBUtility. MySqlHelper. ExecuteNonQuery (SQL _CONN, CommandType. Text, commandText, parameters)> 0)
Return true;
Else
Return false;
}
Public static string DB_InnoDBSelect (int id)
{
String commandText = "SELECT obj FROM test_innodb WHERE id =? Id ";
MySqlParameter [] parameters = {
New MySqlParameter ("? Id ", MySqlDbType. Int32)
};
Parameters [0]. Value = id;
Return DBUtility. MySqlHelper. ExecuteScalar (SQL _CONN, CommandType. Text, commandText, parameters). ToString ();
}
Public static bool DB_MyIsamInsert (string obj)
{
String commandText = "insert into test_myisam (obj) VALUES (? Obj )";
MySqlParameter [] parameters = {
New MySqlParameter ("? Obj ", MySqlDbType. VarChar, 255)
};
Parameters [0]. Value = obj;
If (DBUtility. MySqlHelper. ExecuteNonQuery (SQL _CONN, CommandType. Text, commandText, parameters)> 0)
Return true;
Else
Return false;
}
Public static string DB_MyIsamSelect (int id)
{
String commandText = "SELECT obj FROM test_myisam WHERE id =? Id ";
MySqlParameter [] parameters = {
New MySqlParameter ("? Id ", MySqlDbType. Int32)
};
Parameters [0]. Value = id;
Return DBUtility. MySqlHelper. ExecuteScalar (SQL _CONN, CommandType. Text, commandText, parameters). ToString ();
}
Public static bool DB_MemoryInsert (string obj)
{
String commandText = "insert into test_memory (obj) VALUES (? Obj )";
MySqlParameter [] parameters = {
New MySqlParameter ("? Obj ", MySqlDbType. VarChar, 255)
};
Parameters [0]. Value = obj;
If (DBUtility. MySqlHelper. ExecuteNonQuery (SQL _CONN, CommandType. Text, commandText, parameters)> 0)
Return true;
Else
Return false;
}
Public static string DB_MemorySelect (int id)
{
String commandText = "SELECT obj FROM test_memory WHERE id =? Id ";
MySqlParameter [] parameters = {
New MySqlParameter ("? Id ", MySqlDbType. Int32)
};
Parameters [0]. Value = id;
Return DBUtility. MySqlHelper. ExecuteScalar (SQL _CONN, CommandType. Text, commandText, parameters). ToString ();
}
# Endregion
# Region Test Functions InnoDB
Static void InnoDBInsert ()
{
Long begin = DateTime. Now. Ticks;
For (int I = 0; I <LOOP_TOTAL; I ++)
{
DB_InnoDBInsert (OBJ );
}
Console. WriteLine ("InnoDB Insert Result: {0}", DateTime. Now. Ticks-begin );
}
Static void InnoDBSelect ()
{
Long begin = DateTime. Now. Ticks;
For (int I = LOOP_BEGIN; I <LOOP_END; I ++)
{
DB_InnoDBSelect (I );
}
Console. WriteLine ("InnoDB SELECT Result: {0}", DateTime. Now. Ticks-begin );
}
Static void MyIsamInsert ()
{
Long begin = DateTime. Now. Ticks;
For (int I = 0; I <LOOP_TOTAL; I ++)
{
DB_MyIsamInsert (OBJ );
}
Console. WriteLine ("MyIsam Insert Result: {0}", DateTime. Now. Ticks-begin );
}
Static void MyIsamSelect ()
{
Long begin = DateTime. Now. Ticks;
For (int I = LOOP_BEGIN; I <LOOP_END; I ++)
{
DB_MyIsamSelect (I );
}
Console. WriteLine ("MyIsam SELECT Result: {0}", DateTime. Now. Ticks-begin );
}
Static void MemoryInsert ()
{
Long begin = DateTime. Now. Ticks;
For (int I = 0; I <LOOP_TOTAL; I ++)
{
DB_MemoryInsert (OBJ );
}
Console. WriteLine ("Memory Insert Result: {0}", DateTime. Now. Ticks-begin );
}
Static void MemorySelect ()
{
Long begin = DateTime. Now. Ticks;
For (int I = LOOP_BEGIN; I <LOOP_END; I ++)
{
DB_MemorySelect (I );
}
Console. WriteLine ("Memory SELECT Result: {0}", DateTime. Now. Ticks-begin );
}
Static void DataTableInsertAndSelect ()
{
// Insert
DataTable dt = new DataTable ();
Dt. Columns. Add ("id", Type. GetType ("System. Int32 "));
Dt. Columns ["id"]. AutoIncrement = true;
Dt. Columns. Add ("obj", Type. GetType ("System. String "));
DataRow dr = null;
Long begin = DateTime. Now. Ticks;
For (int I = 0; I <LOOP_TOTAL; I ++)
{
Dr = null;
Dr = dt. NewRow ();
Dr ["obj"] = OBJ;
Dt. Rows. Add (dr );
}
Console. WriteLine ("DataTable Insert Result: {0}", DateTime. Now. Ticks-begin );
// Select
Long begin1 = DateTime. Now. Ticks;
For (int I = LOOP_BEGIN; I <LOOP_END; I ++)
{
Dt. Select ("id =" + I );
}
Console. WriteLine ("DataTable Select Result: {0}", DateTime. Now. Ticks-begin1 );
}
# Endregion
Static void Main (string [] args)
{
InnoDBInsert ();
InnoDBSelect ();
// Restart mysql to avoid query cache
MyIsamInsert ();
MyIsamSelect ();
// Restart mysql to avoid query cache
MemoryInsert ();
MemorySelect ();
DataTableInsertAndSelect ();
}
} // End class
}

Summary
. Net Cache read/write performance is undoubtedly far ahead of the Database Engine
The InnoDB write time is about five times that of MyIsam and Memory. Its row lock mechanism inevitably determines more performance overhead during write operations. Its strength lies in the concurrent processing of multiple threads, this test does not reflect its advantages.
The performance of the three database engines is similar to that of the SELECT statement, and Memory is slightly superior. The comparison of the three database engines with the same high concurrency needs to be further tested.