Mysql optimization configuration

Mysql optimization configuration Mysql Configuration Optimization

I. environment introduction

Mysql version: 5.5.27

II. Content Optimization

Field	Introduction	Recommended value
Skip-locking	Avoid MySQL external locks, reduce the chance of errors, and enhance stability
Back_log	Possible MySQL connections (less than 512 in linux)	384
Key_buffer_size	Key_buffer_size specifies the size of the buffer for the index. increasing the size can improve the index processing performance. This parameter can be set to 384 M or M for servers with around 4 GB of memory. Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!	4G Server 256 M
Max_allowed_packet	When the MySQL client or mysqld server receives an information packet greater than the value of max_allowed_packet, the "information packet is too large" error is reported and the connection is closed.	4 M
Thread_stack	It is mainly used to store the identity information of each thread, such as the thread id and basic information during thread running. we can use the thread_stack parameter to set the size of memory allocated to each thread stack.	192kb
Table_cache	Table cache quantity	512
Sort_buffer_size	N when this buffer is used for the first time, the configured memory is allocated at one time.	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	Sort the buffer allocated by the MyISAM INDEX during repair table, create index, or alter table.	64 M
Thread_cache_size	Indicates that the number of threads stored in the cache can be reused. when the connection is disconnected, if there is space in the cache, the client thread will be placed in the cache. if the thread is requested again, then the request will be read from the cache (the memory above 3 GB is recommended to be 64)	64
Query_cache_size	Maximum length of the query cache	64 M
Tmp_table_size	If a temporary table exceeds this size, MySQL generates an error in The table tbl_name is full format.	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. The value of max_connect_errors does not have much to do with performance.	10000000
Wait_timeout	Specify the maximum connection time of a request. for servers with around 4 GB of memory, you can set it to 5-10.	10
Thread_concurrency	This parameter is set to the number of server logical CPUs × 2	4
Innodb_log_file_size	If a large number of write operations are performed on The Innodb data table, it is important to select the innodb_log_file_size value to improve MySQL Performance.	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 disk operations of the log file. Truly ACID. Slow speed. 2) = 2. when each transaction is committed, the log buffer is written to the file, but the disk operation is not refreshed. 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 the log file is refreshed by disk operations. Any crash of the mysqld process will delete the last second of the transaction before the crash	2
Innodb_buffer_pool_size	Innodb_buffer_pool_size defines the maximum memory buffer size for table data and index data of the InnoDB storage engine. On a dedicated database server, consider setting this value to 60%-80% of the physical memory size.	1G
Innodb_additional_mem_pool_size	In addition to caching table data and indexes, you can allocate cache for other internal items required for the operation to improve InnoDB performance. These memories can be allocated through this parameter. We recommend that you set this parameter to at least 2 MB.	2 M

III. Optimization Focus

1: max_connections

We often encounter "MySQL: ERROR 1040: 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 the value of Threads_created is large, you can set the value of thread_cache_size to a greater value.

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 locks

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 scanning

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 is the access allowed by the host, and the % symbol indicates that all hosts are allowed.
Example of the command to add a user authorized IP address:

Use myuser/mypassword to connect to the mysql server from a host with the ip address 61.129.51.8:

Grant all privileges on *. * TO '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.