MySQL Tens data Performance tuning configuration

Background:

The author's source data a table about 7000多万条, data size 36G, index 6G, add up table space has 40g+, similar table has 4, a total of more than 200 million

Database MySQL, engine for InnoDB, version 5.7, server memory 256G, physical memory several T, hardware parameters of the leverage, but processing these data trampled a lot of pits, because

For not doing this work before, now record the cleaning process, detailed business cleaning process and rules are recorded in the Https://gitee.com/yanb618/zhirong/wikis

Feel:

Cleaning from the table name, field names, field types, field values, index creation and deletion, often see that the number of seconds slowly up to thousands of seconds, the heart of a cool singing up

Assuming this speed, one day down even only to deal with a table, before the work experience has never done performance tuning, just this time to taste the pain, record

In the description before inserting a sentence: must first do the experiment, do the experiment! Look at the execution time, such a large table, the processing is not good to carry out the hours to remember, even cancel all have to wait long

Prepare:

First, the reader to distinguish between Chu InnoDB and MyISAM two kinds of engine difference: InnoDB has a complete thing support, row lock, B-tree/hash index; MyISAM Support table lock,

Do not support things, there is fulltext index, suitable for fast reading;

Second, the temporary data can read and write in memory, do not read and write to the disk

Specific:

Configure temp_table_size and max_heap_table_size, here is the official website description

Tmp_table_sizecommand-Line Format--tmp-table-size=#System Variable Name tmp_table_sizescope Global, sessiondynamic yespermittedValuesTypeintegerDefault 16777216Minimum1024x768Maximum18446744073709551615The maximum size ofInternalin-MemoryTemporaryTables. This variable does notApply to User-created MEMORY tables. The actual limit isDetermined fromWhichever ofTheValues  ofTmp_table_size andMax_heap_table_size issmaller.IfAnin-MemoryTemporary Tableexceeds the limit, MySQL automatically converts it toAn on-DiskMyISAMTable. Increase the value ofTmp_table_size ( andMax_heap_table_sizeifnecessary)ifMany advancedGROUP  byQueries andYou have lots ofmemory. You can compare the Number  ofInternal on-Disk TemporaryTables created toThe total Number  ofInternalTemporaryTables created bycomparing theValues  ofThe Created_tmp_disk_tables andcreated_tmp_tables variables. See also sections8.4.4, "InternalTemporary Table  Use inchMySQL ".

Max_heap_table_sizecommand-Line Format--max-heap-table-size=#System Variable Name max_heap_table_sizescope Global, sessiondynamic yespermittedValues( +-bitPlatforms) TypeintegerDefault 16777216Minimum16384Maximum4294967295permittedValues( --bitPlatforms) TypeintegerDefault 16777216Minimum16384Maximum1844674407370954752This variable sets the maximum size towhichUser-Created MEMORY tables is permitted togrow. The value ofThe variable isUsed toCalculate MEMORYTableMax_RowsValues. Setting This variable have no effect on  anyExisting MEMORYTable, unless theTable  isRe-Created withA statement such as CREATE TABLE orAltered with ALTER TABLE or TRUNCATE TABLE. A Server Restart also sets the maximum size ofExisting MEMORY Tables toThe global max_heap_table_size value. This variable isAlso usedinchConjunction withTmp_table_size toLimit the size ofInternalin-memory tables. See section8.4.4, "InternalTemporary Table  Use inchMySQL ". Max_heap_table_size is  notreplicated. See section17.4.1.20, “Replication  andMEMORY Tables ", andSection17.4.1.38, “Replication  andVariables ", forMore information.

Typically, when you perform a lengthy Update table operation, you can see the following information when you view the show Processlist command

Copying to TMP table Copying to TMP table on disk

The latter indicates that the memory temporary table space is not enough, need to write to the hard disk, this is very deadly, temporary query data part in the memory portion on the hard disk, read and write speed dips

Adjust the size of these two parameters of 40G or more, your server memory is large enough to continue to increase, the author because there is a SELECT into the reconfiguration table requirements,

The data is read very large, so you need to increase the default value

Innodb_buffer_pool_size

InnoDB buffer pool, here's his duty scene.

* Data Caching – the most important purpose
* Index Cache – Using the same buffer pool
* Buffering – Changed data (often called dirty data) is stored in the buffer before being flushed to the hard disk
* Storage internal structure – some structures such as adaptive hash indexes or row locks are also stored in the InnoDB buffer pool

Fortunately our server is separate to MySQL, based on anecdotal experience set to 80% of total available memory, where total memory 256G, complete this cleaning

Set up 100G is enough, we need this space to save the temporary query data, the large data volume to avoid the temporary data in memory and inter-disk exchange caused by the IO performance impact

MySQL Tens data Performance tuning configuration