A brief analysis of MySQL Memory storage engine _mysql

Later saw the blog park in the use of Northscale Memcached Server (official site: http://www.couchbase.com/products-and-services/memcached), seemingly shared charges, and hesitated. In fact, the requirements of the project is very simple, but also want to use the. Net cache to achieve, but the stability is difficult to assess, development and maintenance costs seem too large, no way, my SQL Memory storage became the only choice, because almost no need to write code.

Read the official manual first, and then write a simple performance test. Since the official latest documents are in English, the 5.5 version of the MySQL Memory storage Chapter is translated.

Official documentation (translated from the 5.5 version of the Memory Storage Engine)
The memory storage engine stores the table's data in memory. Memory replaces the previous heap as a preference, but at the same time backward-compatible, 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/A	Index Caches	N/A
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 of Memory and MySQL cluster

Developers who want to deploy the memory engine will consider whether MySQL cluster is a better choice, referring to the following memory engine usage scenarios and features:

Can be as easy to operate and manage as session or cache (Caching).
Give full play to the features of the memory engine: high speed, low latency.
Read-only or read-only access mode (not suitable for frequent writes).
However, the performance of the memory table is constrained by the efficiency of the single-threaded execution and the table lock overhead when the write operation, which limits the scalability of the memory table when it is high load, especially the concurrent processing of the mixed write operation. In addition, the data in the memory table is lost after the server restarts.

The MySQL Cluster (cluster) supports the same functionality as the memory engine and delivers higher performance, along with additional features that memory does not support:

The row lock mechanism better supports multi-threaded multiuser concurrency.
Better support for reading and writing mixed statements and extensions.
Disk storage media can be selected to permanently save data.
Shared-nothing and distributed architectures guarantee no single point of failure, 99.999% availability.
Data is automatically distributed across nodes, and application developers do not need to consider partitioning or slicing solutions.
Supports variable-length data types (including BLOBs and TEXT) that are not supported in memory.
For more details on the MySQL cluster and the Memory engine, you can view the scaling Web Services with MySQL Cluster:an alternative to the MySQL Memory Storage , this white paper includes performance research for both technologies and step-by-step instructions on how to migrate memory users to MySQL clusters.

Each memory table is associated with a disk file. The file name begins with the name of the table, and a. frm extension indicates the table definition it stores. To make it clear that you want a memory table, you can use the engine option to specify:

CREATE TABLE T (i INT) ENGINE = MEMORY;
As their name indicates, the memory table is stored in memory and the hash index is used by default. This makes them very fast and is useful for creating temporary tables. However, when the server shuts down, all data stored in the memory table is lost. Because the table definition is present 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 Code code as follows:

CREATE TABLE test engine=memory;
SELECT ip,sum (downloads) as down to log_table GROUP by IP;
SELECT COUNT (IP), AVG (down) from test;
DROP TABLE test;

The memory table has the following characteristics:

The space for the memory table is allocated in small chunks. Table pair inserts use 100% dynamic hash. No overflow area or extra key space is required. Free lists have no additional space requirements. Deleted rows are placed in a linked list and reused when you insert new data into the table. Memory tables also do not usually have problems with deleting inserts in the hash table.
The memory table can have up to 64 indexes per table, 16 columns per 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 shown below:

Copy Code code 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 a memory table's hash index key is highly repeated (many index entries contain the same value), updates related to the key index and all deletions are significantly slower. The degree of repetition is proportional to the speed at which you can use the Btree index to avoid this problem.
The memory table can use a non unique key. (Implementation of the hash index, which is an infrequently used feature)
Indexes on columns that can contain null values
The memory table uses a fixed record length format, and variable-length types such as varchar are converted to fixed-length types stored in the memory table.
Memory cannot contain blobs or text columns.
Memory supports auto_increment columns
Memory table supports insert delayed
Non-temporary memory tables are shared among all clients, just like any other non temp table.
The memory table content is stored in memory, and it creates the shared media for the internal temporary table as a dynamic query queue, but the difference between the two types of tables is that the memory table does not experience storage transformations, and the internal tables:
1. The memory table is not converted to a disk table, and an internal temporary table is automatically converted to a disk table if it is too general.
2, Memory table maximum by the system variable Max_heap_table_size limit, the default is 16MB, to change the memory table size limit, you need to change the max_heap_table_size value. This value takes effect at CREATE table and accompanies the life cycle of the table (when you use ALTER TABLE or the TRUNCATE Table command, the maximum limit for the table changes, or when you restart the MySQL service, the maximum limit for all existing memory tables will be used Max_ Resets the value of the heap_table_size. )
The server needs enough memory to maintain all the memory tables used at the same time.
If you delete a row, the memory table does not reclaim memory, and only when the entire table is deleted is memory reclaimed. At the same time, the memory space for deleted rows is used only if new rows are inserted in the same table. To release the memory space occupied by deleted rows, you can use ALTER TABLE engine=memory to force the table to be rebuilt. When the content expires to free the entire memory table, you can either perform a delete or TRUNCATE table to clear all rows, or use the drop table to delete the table.
When the MySQL server is started, if you want to populate the memory table, you can use the--init-file option. For example, you can insert into ... A statement such as SELECT or load data infile is placed in this file to load the table from a persistent, solid data source.
If you are using replication, the primary server's memory table becomes empty when the primary server is shut down and restarted. But since the server does not realize that these tables have been empty, if you select data from them, it returns obsolete content. Since server startup, when a memory table is used for the first time on the primary server, a delete from statement is automatically written to the primary server's binary log, so that it synchronizes from the server to the primary server again. Note that even with this policy, obsolete data is still available from the server in the table at the time of the primary server reboot and the first time it uses the table. However, if you use the--init-file option to implement the memory table on the primary server when it is started. It ensures that this time interval is zero.
In the memory table, the memory required by one row is calculated using the following formula:

Copy Code code 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 causes the length of the row to be the exact multiple of the size of the char pointer. sizeof (char*) on the 32-bit machine is 4, on 64-bit machine is 8.
As mentioned earlier, 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 need to set the session variable before creating the table. (Do not set the global Max_heap_table_size value unless you intend to use this value for all the client-created memory tables)
The following example creates two memory tables with a size limit of 1MB and 2MB respectively:

Copy Code code 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 restarts, the size limit for both tables is recovered using the global max_heap_table_size value.
You can also set the maximum number of rows for a table through the max_rows option of the CREATE TABLE, but the max_heap_table_size priority is higher than the max_rows, and for maximum compatibility when both exist, you need to Max_heap_table_ Size to set a reasonable value.

Memory Storage Engine Official forum: http://forums.mysql.com/list.php?92

Performance Test

Test compares MySQL's InnoDB, MyIsam, memory three engines with the. Net DataTable Insert and select Performance (the histogram reflects its consumption time, Unit hundred nanoseconds, Innodb_flush_log_at_trx The _commit parameter is configured to 1 and the MySQL is restarted each time the test is reset to avoid query Cache, as the result is as follows:

Write 10,000 record comparisons.

Read 1000 record comparisons.

Test script:

Copy Code code 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 Code code 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 this 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
}

Summarize
There is no doubt that. Net Cache read-write performance is significantly ahead of the database engine
InnoDB write time is about 5 times times that of MyISAM and memory, and its row lock mechanism inevitably determines the more performance overhead of writing, and its strength lies in multithreading concurrency, and this test does not reflect its advantages.
The three database engines are similar in select performance, memory slightly dominant, the same high concurrent comparison needs to be further tested.