MySQL Memory table configuration and performance testing

CENTOS7 MySQL database installation and matching can refer to the article, the basic picture can be done (I choose the method of two):

Http://www.cnblogs.com/starof/p/4680083.html

When it comes to memory tables, the first two concepts are simple to distinguish:
1. temporary table; 2. memory table;

The distinction between a temporary table and a memory table:
Temporary tables are temporary tables created using create temprarytable. Temporary tables can use any storage engine, temporary tables are visible only in a single connection, and temporary tables disappear when the connection is broken.
MySQL initially creates the temporary table in memory and dumps it to disk when the data becomes too large.
An in-memory table is a table created with the memories engine. The table structure exists on the disk and the data is in memory.

Conditions for temporary table creation:
1, there is a sort (order by) and group by operation in the query
2, using filter repeating columns in sorting (DISTINCT)
3, query with SQL_SMALL_RESULT option

A temporary table in memory is written to disk when the following conditions are encountered:
1, the Blob and text fields are present in the table
2, grouped or filtered columns more than 512 bytes
3, the columns of the merged result set in the query are more than 512 bytes

If the temporary table created in memory initially becomes too large, MySQL automatically turns it into a temporary table on the disk.
The temporary tables in memory are determined by the tmp_table_size and max_heap_table_size two parameters. This is different from the table that created the memory engine. The table of the memory engine is determined by the max_heap_table_size parameter, And it does not turn into a format on disk.
When MySQL creates temporary tables (both in memory and on disk), it increases the created_tmp_tables state value, and if MySQL creates temporary tables on disk (including from memory), it increases the Created_tmp_disk_tables state value .

Two options for memory tables and TEMPORARY tables * * * *
# The maximum capacity allowed for a separate memory table.
# This option is used to prevent accidental creation of a large memory table that results in the exhaustion of all memory resources.
# Set Range 16KB-4GB
Max_heap_table_size = 64M

# The maximum size of the internal (in-memory) temporary table
# If a table grows larger than this value, it will be automatically converted to a disk-based table.
# This limit is for a single table, not a sum.
Tmp_table_size = 64M

The following specific memory table, the first to create a table is similar to the normal build table, just Engine=memory (after 5.5 is the engine, before the type, while the heap is memory, preferably using memory) can be created.

For our commonly used functions, the memory table has the following characteristics:

1. For the varchar variable-length type, the memory table is stored with a fixed length;

2. The memory table may have non-unique keys;

3. The memory table cannot contain blobs or text columns;

4. Memory table supports auto_increment column;

5. Memory table support insertion delay, make read priority;

6. The non-temporary memory table and other non-memory tables are shared directly on all clients;

Here are a few things to keep in mind when we use memory tables:

1. Server memory is large enough;

2. The memory table we created differs from the MySQL internal temp table:

3. When we delete from a memory table individually, the memory is not reclaimed and the memory is reclaimed only when the entire table is deleted;

4. On MySQL's master-slave server, the memory table can be copied

The MySQL memory table capacity is limited by two parameters: Max_heap_table_size and Max_Rows, where max_rows can add max_rows = 10000 similar parameters to the engine after the table is built.

Max_heap_table_size currently has three kinds of modification methods, recommend the second kind of measurement feasible (first did not try, third how can not succeed):

1. Changes in startup options

When you start MySQL, add the parameter-o max_heap_table_size=64m.

2, modify the MY.CNF

Add max_heap_table_size = 32M in the segment of [mysqld]

3. Modify in the MySQL client tool

Mysql>set max_heap_table_size=32777216;
Query OK, 0rows Affected (0.00 sec)

Mysql>show variables like '%heap% ';
+---------------------+----------+
| variable_name | Value |
+---------------------+----------+
|max_heap_table_size | 32777216 |
+---------------------+----------+
1 row in Set (0.00 sec)

The specific server/ETC/MY.CNF configuration is as follows, note the Red font location, special attention to modify the configuration file is added in the [mysqld] segment, there is D:

# for advice The change settings

#http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[Mysqld]

#

# Remove Leading # and set to the amount of RAM forthe most important data

# Cache in MySQL. Start at 70% of total RAM fordedicated server, Else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove Leading # to turn on a very important dataintegrity option:logging

# Changes to the binary log between backups.

# Log_bin

#

# Remove Leading # To set options mainly useful forreporting servers.

# The server defaults is faster for transactions Andfast selects.

# Adjust sizes as needed, experiment to find theoptimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

# Disabling Symbolic-links is recommended to preventassorted security risks

Symbolic-links=0

# Recommended in standard MySQL setup

Sql_mode=no_engine_substitution,strict_trans_tables

Max_heap_table_size = 64M

[Mysqld_safe]

Log-error=/var/log/mysqld.log

The configuration size adjusts according to the actual need, the configuration completes restarts the service, servicemysqld restart.

The performance test uses MySQL's own mysqlslap, you can use Mysqlslap–help to view the specific parameters, we test directly using the statement to modify the following parameters:
MYSQLSLAP--DEFAULTS-FILE=/ETC/MY.CNF--concurrency=200--iterations=100--create-schema=test--query=/home/hefj/ test.sql-uroot-pa747107c

Where concurrency is the concurrency, iterations is the number of iterations of the test execution, Create-schema is the database name, the query is a SQL file (test SQL written to the server can be executed), followed by the database link parameters, the results are as follows:

You can calculate the number of concurrent times per second by dividing by the average time by concurrency.

Common options refer to:
--concurrency number of concurrent, multiple can be separated by commas
--engines to test the engine, can have multiple, separated by delimiters, such as--engines=myisam,innodb
--iterations how many times to run these tests
--auto-generate-sql test with the SQL script generated by the system itself
--auto-generate-sql-load-type to test whether to read or write or to mix the two (read,write,update,mixed)
--number-of-queries how many queries to run in total. The number of queries that each client runs can be calculated using the total number of queries/concurrency
--debug-info additional output CPU and memory related information
--number-int-cols the number of int fields to create a test table
--number-char-cols number of chat fields to create a test table
--create-schema Testing the database
--query your own SQL script to perform the test
--only-print If you just want to print and see what the SQL statement is, you can use this option

MySQL Memory table configuration and performance testing