Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to carry out the MySQL Memory storage engine analysis, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, do not say much, follow the editor to have a look.

Preface

The requirements are derived from the MemCache requirements in the project, and at first I want to use MemCached (official site:), but this unofficially supported Windows version of open source software widely used under Linux. Later, I saw that the blog Park was using NorthScale Memcached Server (official site:), which seemed to share the fee, and hesitated again. In fact, the requirements in the project are very simple, but also want to use .net Cache to achieve, but the stability is difficult to assess, development and maintenance costs seem to be too large, there is no way, My SQL Memory Storage has become the only choice, because there is little need to write code.

Official documentation (translated from version 5.5 of The Memory Storage Engine)

The Memory storage engine stores the table's data in memory. Memory replaces the previous Heap as the preferred, but at the same time backward compatibility, Heap is still supported.

Memory storage engine features:

Comparison between Memory and MySQL Cluster

Developers who want to deploy a memory engine will consider whether MySQL Cluster is a better choice. Refer to the following usage scenarios and features of Memory engine:

It can be operated and managed as easily as session (Session) or cache (Caching).

Give full play to the characteristics of memory engine: high speed, low latency.

Read-only or read-based access mode (not suitable for frequent writes).

However, the performance of memory tables is limited by the execution efficiency of single thread and the table lock overhead of write operations, which limits the scalability of memory tables under high load, especially the concurrent processing of mixed write operations. In addition, the data in the memory table is lost after the server is restarted.

MySQL Cluster (clustering) supports the same functionality as the Memory engine and provides higher performance, while having more other features that Memory does not support:

The row locking mechanism can better support multi-thread and multi-user concurrency.

Better support for read-write mixed statements and extensions.

Disk storage media can be selected to permanently save data.

Shared-nothing and distributed architecture guarantee no single point of failure and 99.999% availability.

Data is automatically distributed in each node, and application developers do not need to consider partitioning or sharding solutions.

Supports variable length data types (including BLOB and TEXT) that are not supported in MEMORY.

For a more detailed comparison between MySQL clustering and the Memory engine, see Scaling Web Services with MySQL Cluster: An Alternative to the MySQL Memory Storage Engine, a white paper that includes performance studies of 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 starts with the name of the table and a .frm extension indicates the table definition it stores. To specify that you want a Memory table, use the ENGINE option to specify:

CREATE TABLE t (I INT) ENGINE = MEMORY

As their names indicate, the Memory table is stored in memory and the hash index is used by default. This makes them very fast and useful for creating temporary tables. However, when the server shuts down, all data stored in the Memory table is lost. Because the definition of the table is stored in the .frm file on disk, the tables themselves continue to exist and are empty when the server is restarted.

This example shows how you can create, use, and delete a Memory table:

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

The MEMORY table has the following characteristics:

The space given to the Memory table is allocated in small blocks. The table uses 100% dynamic hashes for inserts. No overflow area or extra key space is required. The free list has no additional space requirements. Deleted rows are placed in a linked list and reused when you insert new data into the table. The Memory table also does not have the usual problems associated with deleting and inserting in a hash table.

MEMORY tables 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 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 key of an 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. Repetition is proportional to speed, and you can use BTREE indexes to avoid this problem.

MEMORY tables can use non-unique keys. (implementation of hash index, which is an uncommonly used feature)

Index 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 and stored in the MEMORY table.

MEMORY cannot contain BLOB or TEXT columns.

MEMORY supports AUTO_ element columns

MEMORY table supports INSERT DELAYED

Non-temporary MEMORY tables are shared among all clients, just like any other non-temporary table.

The contents of the MEMORY table are stored in memory and serve as a shared medium for dynamic query queues to create internal temporary tables, but the difference between the two types of tables is that MEMORY tables do not encounter storage transformations, while internal tables:

1. MEMORY tables are not converted to disk tables, while internal temporary tables are automatically converted to disk tables if they are too large.

2. The maximum value of the MEMORY table is limited by the system variable max_heap_table_size, and the default is 16MB. To change the size limit of the MEMORY table, you need to change the value of max_heap_table_size. This value takes effect on CREATE TABLE and follows the life cycle of the table. (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 of 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 you delete a row, the memory table will not reclaim memory, and memory will be reclaimed only if the entire table is 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 free up the memory space occupied by deleted rows, you can use ALTER TABLE ENGINE=MEMORY to forcibly rebuild the table. When the content expires to free the entire memory table, you can execute DELETE or TRUNCATE TABLE to clear all rows, or use DROP TABLE to delete the table.

When the MySQL server starts, if you want to populate the MEMORY table, you can use the-- init-file option. For example, you can put INSERT INTO... Statements such as SELECT or LOAD DATA INFILE are put into this file to load tables from persistent data sources.

If you are using replication, the MEMORY table of the primary server becomes empty when the primary server is shut down and restarted. But the server doesn't realize that these tables are empty, so if you select data from them, it returns outdated content. Since the server was started, when a MEMORY table is first used on the master server, a DELETE FROM statement is automatically written to the master server's binary log, so the slave server is synchronized with the master server again. Note that even with this strategy, the slave server still has obsolete data in the table between the restart of the master server and the first time it uses the table. However, if you use the-- init-file option to push the MEMORY table on the primary server when it starts. It ensures that the interval is zero.

In the MEMORY table, the memory required for a row is calculated using the following formula:

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 length of the row an exact multiple of the char pointer size. Sizeof (char*) is 4 on 32-bit machines and 8 on 64-bit machines.

As mentioned earlier, the system variable max_heap_table_size is used to set the upper limit of the 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 plan to use this value for all memory tables created by the client.)

The following example creates two memory tables with size limits of 1MB and 2MB, respectively:

SET max_heap_table_size = 1024024

/ * 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 limits of the two tables are restored using the global max_heap_table_size value.

You can also set the maximum number of rows in the table through the MAX_ROWS option of CREATE TABLE, but max_heap_table_size takes precedence over MAX_ROWS. For maximum compatibility when both exist, you need to set max_heap_table_size to a reasonable value.

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

Performance testing

The Insert and Select performance of MySQL's InnoDB, MyIsam and Memory engines are tested and compared with .net DataTable (bar chart shows its consumption time, unit BNSs, innodb_flush_log_at_trx_commit parameter is set to 1, and MySQL is restarted to avoid Query Cache each test). The results are as follows:

Write 10000 records for comparison.

Read 1000 record comparisons.

Test script:

/ *

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 the code:

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; iTunes +)

{

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; iTunes +)

{

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; iTunes +)

{

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; iTunes +)

{

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; iTunes +)

{

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; iTunes +)

{

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; iTunes +)

{

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; iTunes +)

{

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 ()

Www.2cto.com DataTableInsertAndSelect ()

}

} / / end class

}

There is no doubt that the read and write performance of .net Cache is much better than the engine.

InnoDB takes about 5 times more time to write than MyIsam and Memory, and its row locking mechanism must determine more performance overhead when writing, and its strength lies in multithreaded concurrent processing, but this test does not show its advantage.

The three database engines are almost the same in SELECT performance, Memory is slightly superior, and the comparison under the same high concurrency needs to be further tested.

The above is how to analyze the MySQL Memory storage engine. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.