MySQL Optimization Configuration

MySQLConfiguration Optimization

I. Environment Introduction

MySQLVersion: 5.5.27

Ii. Content Optimization

Field	Introduction	Recommended Value
Skip-locking	AvoidMySQLTo reduce the probability of errors and enhance stability.
Back_log	MySQLPossible connection count(LinuxLess512)	384
key_buffer_size	key_buffer_size specifies the buffer size used for indexing, increase it to get better index processing performance. for 4 GB left and right servers. This parameter can be set to 256 m or 384 m . Note: if the value of this parameter is too large, the overall efficiency of the server is reduced. !	4G server 256 m
max_allowed_packet	when mysql client or mysqld the server receives a message greater than max_allowed_packet In the byte information package, " the information package is too large " error and close connection	4 m
Thread_stack	It is mainly used to store the identity information of each thread, such as the threadID, The basic information of the thread runtime, etc.Thread_stackParameter to set the size of memory allocated for each thread Stack	192kb
Table_cache	Table cache quantity	512
Sort_buffer_size	NThe first time you need to use thisBufferOne-time allocationSet memory	512 K
Read_buffer_size	Buffer size available for read query operations	4 m
Join_buffer_size	The buffer size that can be used by the Joint query operation.	8 m
Myisam_sort_buffer_size	WhenRepair tableOr useCreate IndexCreate an index orALTER TABLESort in processMyISAMThe buffer allocated by the index.	64 m
Thread_cache_size	Indicates the number of threads that can be reused and saved in the cache.,If there is space in the cache when the connection is disconnected,Then the client thread will be placed in the cache,If the thread is requested again, the request will be read from the cache,(3GThe above memory is recommended64)	64
Query_cache_size	Maximum length of the query Cache	64 m
Tmp_table_size	If a temporary table exceeds this size,MySQLGenerateThe table tbl_name is fullFormat Error	256 m
Max_connections	Maximum number of user connections The maximum number of connections accounts for about 85% of the maximum number of connections	3000
Max_connect_errors	It is responsible for blocking clients that fail too many attempts to prevent brute force password cracking.Max_connect_errorsThe value does not have much to do with performance.	10000000
Wait_timeout	Specifies the maximum connection time of a request.4 GBThe left and right memory servers can be set5-10.	10
Thread_concurrency	This parameter is set to server logic.CPUQuantity× 2	4
Innodb_log_file_size	IfInnoDBData Tables have a large number of write operations, so select the appropriateInnodb_log_file_sizeIncrease in value pairsMySQLPerformance is important	256 m
Innodb_log_buffer_size	Maximum length of the cache for write operations on transaction log files	8 m
innodb_flush_logs_at_trx_commit	1) = 1 when each transaction is committed, the log buffer is written to the log file to refresh the log file disk operations. truly acid . Slow speed. 2) = 2 when each transaction is committed, the log buffer is written to a file, however, disk operations are not refreshed for log files. Transactions in the last second will be deleted only when the operating system crashes or power is down. Otherwise, transactions will not be lost. 3) = 0 the log buffer is written to the log file once per second, and refresh the disk operations of log files. Any mysqld the crash of the Process deletes the last second of the transaction before the crash	2
innodb_buffer_pool_size	innodb_buffer_pool_size defined InnoDB maximum memory buffer size of table data and index data of the storage engine on the dedicated database server , consider the 60%-80%	1G
Innodb_additional_mem_pool_size	Besides caching table data and indexes,You can allocate cache for other internal items required for the operation to improveInnoDBPerformance. These memories can be allocated through this parameter. We recommend that you set this parameter to at least2 MB	2 m

 

Iii. Optimization focus

1: max_connections

We often encounter "MYSQL: Error 1040: Too too connections". One is that the access traffic is indeed high and the MySQL server cannot resist it. In this case, we need to consider increasing the load on distributed reads from the server, the value of max_connections In the MySQL configuration file is too small:

The ideal setting is
Max_used_connections/max_connections * 100% ≈ 85%.
The maximum number of connections accounts for about 85% of the maximum number of connections. If the percentage is less than 10%, the maximum number of connections of the MySQL server is too high.

2:Key_buffer_size

Key_buffer_size is one of the most influential parameters on MyISAM Table performance:

 

Key_cache_miss_rate = key_reads/key_read_requests * 100%

Key_cache_miss_rate is good at lower than 0.1% (each 1000 requests have a direct read hard disk). If key_cache_miss_rate is lower than 0.01%, too many key_buffer_size allocations can be reduced as appropriate.

3:Temporary table

 

Ideal Configuration:
Created_tmp_disk_tables/created_tmp_tables * 100% <= 25%

4: Open Table

 

Open_tables/opened_tables * 100%> = 85%
Open_tables/table_cache * 100% <= 95%

5:Process usage

If you findThreads_createdThe value is relatively large.,You can considerThread_cache_sizeThe value is larger.

6:Query Cache

 

 

 

Query cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%

If the query cache utilization is below 25%, the query_cache_size setting is too large and can be appropriately reduced. If the query cache utilization is above 80% and qcache_lowmem_prunes> 50, the query_cache_size may be small, or too many fragments.
Query cache hit rate = (qcache_hits-qcache_inserts)/qcache_hits * 100%
The query cache fragmentation rate is 20.46%, the query cache utilization rate is 62.26%, the query cache hit rate is 1.94%, And the hit rate is very low. It is possible that the write operations are frequent and there may be some fragments.

7:Number of opened files

 

 

Suitable settings: open_files/open_files_limit * 100% <= 75%

8:Table lock status

 

Table_locks_immediate indicates the number of table locks to be released immediately, table_locks_waited indicates the number of table locks to wait. If table_locks_immediate/table_locks_waited> 5000, InnoDB engine is recommended because InnoDB is a row lock and MyISAM, innoDB provides better performance for applications with high concurrent writes.

9:Table Scan

 

 

Calculate the table scan rate:
Table scan rate = handler_read_rnd_next/com_select
If the scanning rate of a table exceeds 4000, too many table scans are performed. It is very likely that the index has not been created. Increasing the value of read_buffer_size may be advantageous, but it is best not to exceed 8 Mb.

4. access from a specified IP address in MySQL

 

The host part indicates the allowed host access., %Symbol indicates that all hosts are allowed.
Add user authorizationIPCommand example:

UseMyuser/mypasswordSlaveIPIs61.129.51.8Host connectionMySQLServer:

Grant all privileges on *. *'Myuser' @ '61. 129.0.0'Identified by 'mypassword' with grant option;
Flush privileges;

V. Summary

In actual configuration, the performance of each server is different. Therefore, the MySQL Optimization Configuration is mainly based on the third part. First, test and run the game for a period of time, and then go to MySQL to view the values of various variables, then, the values of various variables are calculated based on the formula. If the values are within the standard range and are not within the standard range, they are all transferred up and down accordingly.