MySQL performance tuning related parameters in large data volume

Excerpted from http://blog.csdn.net/nightelve/article/details/17393631

One, thread_concurrency, default of 8

The correctness of the value of the thread_concurrency has a significant impact on MySQL performance, in the case of multiple CPUs (or multicore), the error setting the value of thread_concurrency will cause MySQL to not take full advantage of multi-CPU (or multicore), Occurs at the same moment only one CPU (or core) is in the working condition.
The thread_concurrency should be set to twice times the number of CPU cores. For example, there is a dual-core CPU, that thread_concurrency should be 4; 2 Dual-core CPUs, the value of thread_concurrency should be 8.
For example: According to the above describes our current system configuration, can be known as 4 CPUs, each CPU is 8 cores, according to the above calculation rule, here should be: 4*8*2=64
To view the system's current thread_concurrency default configuration command:

 like ' thread_concurrency ';

Second, key_buffer_size, default is 384M

Key_buffer_size is the buffer size used for index blocks, increasing the index (for all read and multiple writes) that can be better processed, and one parameter that has the greatest impact on the performance of MyISAM (a type of MySQL table storage that can be viewed by Baidu, etc.). If you make it too big, the system will start to change pages and really become slow. Strictly speaking, it determines the speed of database index processing, especially the speed of index reading. The parameter can be set to 256M or 384M for a server that has around 4GB.
How to know key_buffer_size settings is reasonable, generally can check the status value Key_read_requests and Key_reads, the proportion of key_reads/key_read_requests should be as low as possible, such as 1 : 100,1:1000, 1:10,000. The value can be checked with the following command:

 like ' key_read% ';

For example, check the system current Key_read and Key_read_request values are:

+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| key_read_requests | 28535 |
| Key_reads | 269 |
+-------------------+-------+
It is known that there are 28,535 requests, and 269 requests are not found in memory directly from the hard disk to read the index.
The probability of the missing cache is: 0.94%=269/28535*100%. The general Miss probability is better under 0.1. It is far more than 0.1 and proves to be not effective. If the hit rate is below 0.01, it is recommended to modify the Key_buffer_size value appropriately.

Third, innodb_buffer_pool_size (default 128M)

Innodb_buffer_pool_size: One of the most important parameters for InnoDB table performance. Functions are the same as key_buffer_size. InnoDB occupied memory, in addition to innodb_buffer_pool_size used to store page cache data, in addition to the normal situation there is about 8% overhead, mainly used in each cache page frame description, Adaptive hash data structure, if not safe shutdown, At startup, you also need to open about 12% of the memory for recovery, which adds up to almost 21% of the overhead. Assume: 12G innodb_buffer_pool_size, the most time InnoDB may occupy 14.5G of memory. If the system is only 16G and only runs MySQL, and MySQL only uses InnoDB,
So for MySQL to open 12G, is to maximize the use of memory.
In addition to the INNODB and MyISAM storage engines, MyISAM's key_buffer_size can only cache index keys, while innodb_buffer_pool_size caches data blocks and index keys. An appropriate increase in the size of this parameter can effectively reduce the disk I/O of the InnoDB type table.
When we manipulate a InnoDB table, all the data returned or any index block used in the data process will go through this memory area.
Cache hit ratio can be calculated by (innodb_buffer_pool_read_requests–innodb_buffer_pool_reads)/innodb_buffer_pool_read_requests * 100%, and adjust the innodb_buffer_pool_size parameter size according to the hit ratio to optimize. The value can be checked with the following command:

 like ' innodb_buffer_pool_read% ';

such as viewing the system in the current system

+---------------------------------------+---------+

| innodb_buffer_pool_read_requests | 1283826 |
| Innodb_buffer_pool_reads | 519 |
+---------------------------------------+---------+
Its hit ratio 99.959%= (1283826-519)/1283826*100% the higher the better.

Iv. innodb_additional_mem_pool_size (Default 8M)

Innodb_additional_mem_pool_size sets the amount of memory space that the InnoDB storage engine uses to hold data dictionary information and some internal data structures, so when we have a very large number of database objects in MySQL instance, It is necessary to adjust the size of this parameter appropriately to ensure that all data can be stored in memory for increased access efficiency.
It's easy to know if this parameter size is enough, because when it's too small, MySQL logs the warning information into the database's error log, and you know it's time to adjust the parameter size.
View the error log of the current system MySQL cat/var/lib/mysql/machine name. Error found there are many waring warnings. So it needs to be 20M.
According to the MySQL manual, the recommended value for 2G memory machines is 20M.
100M of 32G Memory

MySQL performance tuning related parameters in large data volume