Shulou(Shulou.com)06/01 Report--

This article is mainly about the detailed steps of Mysql parameter optimization, if you are interested, let's take a look at this article. I believe it is of some reference value to you after reading the detailed steps of Mysql parameter optimization.

Connection optimization

Back_log=500 (default 50)

When the connection of MySql reaches max_connections, the new request will be stored in the stack to wait for a connection to release resources, the number of which is back_log. If the number of waiting connections exceeds back_log, connection resources will not be granted. It will be reported that: unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | the back_log value of the process to be connected to NULL cannot exceed the size of the listening queue for TCP/IP connections. If it is invalid, check the size of the listening queue for the TCP/IP connection of the current system: cat / proc/sys/net/ipv4/tcp_max_syn_backlog the current system is 1024. It is recommended to set an integer less than 512 for Linux systems.

Wait_timeout=1800 (default 8 hours, unit second)

The maximum amount of time that the database connection of the MySQL client is idle; this needs to be modified if there are a large number of sleep taking up memory and the number of connections for a long time.

Max_connections=3000 (default 151s)

The maximum number of connections for MySql. If the number of concurrent connection requests for CVM is relatively large, it is recommended to increase this value. The more connections, the more memory is consumed.

Max_connect_errors = 10000000

Set it as large as possible to prevent too many incorrect links from being unable to write data.

CPU optimization

Thread_concurrency=16 (default 8)

The correct setting can make full use of the multicore CPU,thread_concurrency should be set to twice the number of CPU cores.

Network optimization

Skip-name-resolve (commented by default)

Disable DNS parsing of external connections by MySQL. Use this option to avoid the time it takes for MySQL to parse DNS. It should be noted, however, that if this option is turned on, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to handle connection requests properly!

IO optimization

Key_buffer_size=400M (MyISAM)

The size of the buffer used for the index block, a parameter that has a greater impact on MyISAM. Key_reads / key_read_requests under 0.1 is better.

Key_cache_miss_rate = Key_reads / Key_read_requests * 100%show status like 'key_read%' +-+-+ | Variable_name | Value | +-+-+ | Key_read_requests | 28535 | | Key_reads | 269 | +-+-+ above key_cache_miss_rate greater than 0.1% You need to increase the parameters. It is good that the key_cache_miss_rate is below 0.1% (there is one direct read hard disk for every 1000 requests). If the key_cache_miss_rate is less than 0.01%, the key_buffer_size allocation is too much and can be reduced appropriately. Mysql > show global status like 'key_blocks_u%' +-- + | Variable_name | Value | +-- + | Key_blocks_unused | 0 | Key_blocks_used | 413543 | +-+ Key_blocks_unused indicates the number of unused cache clusters (blocks) Key_blocks_used indicates the largest number of blocks ever used, such as this server, where all the cache is used, either adding key_buffer_size or overriding the index, filling up the cache. Ideal setting: Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

Innodb_buffer_pool_size = 2048m (default: 128m focus InnoDB)

Used to cache data blocks and index keys. One of the parameters that has the greatest impact on the performance of InnoDB tables. Increasing the size of this parameter can effectively reduce the disk IBO of InnoDB tables.

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%' +-- +-+ | Variable_name | Value | +-+-+ | Innodb_buffer_pool_pages_data | 129037 | | Innodb_buffer _ pool_pages_dirty | 362 | Innodb_buffer_pool_pages_flushed | 9998 | | Innodb_buffer_pool_pages_free | 0 |! | Innodb_buffer_pool_pages_misc | 2035 | | Innodb_buffer_pool_pages_total | 131072 | +-+-+ 6 Rows in set (0.00 sec) Innodb_buffer_pool_pages_free is 0 It means that buffer pool has been used up and needs to be increased by 75% of all memory recommended by innodb_buffer_pool_size (if the remaining memory can ensure the normal operation of the system and other services)

Innodb_additional_mem_pool_size=100M (default 8m)

Set the memory space used by the InnoDB storage engine to store data dictionary information and some internal data structures, so when we have a lot of database objects in a MySQL Instance, we need to properly adjust the size of this parameter to ensure that all data can be stored in memory to improve access efficiency. If the setting is too small, the log will appear warning

2G memory recommendation 20m, 32G memory recommendation 100m

Innodb_log_buffer_size=20M (default 8m)

The buffer used by the transaction log of the InnoDB storage engine. Similar to when Binlog Buffer,InnoDB writes transaction logs, in order to improve performance, it also writes information to Innofb Log Buffer first. Ideally 8m, it is not recommended to exceed 32MB, and increasing this cache segment can reduce the number of times the database writes data files.

Innodb_flush_log_trx_commit=0 (default 1)

0 log buffer will be brushed to disk every second, and nothing will be done when the transaction is committed

1 data from log buffer is written to log file each time the transaction is committed, and the synchronization of the file system to disk is triggered.

Transaction commit triggers log buffer-to-log file refresh, but does not trigger disk file system-to-disk synchronization. In addition, there is a file system-to-disk synchronization operation every second.

A setting of 1 is of course the safest, but the performance page is the worst (compared to the other two parameters, but not unacceptable). If the requirements for data consistency and integrity are not high, you can set it to 2. If you only want the most performance, such as log cloud servers with high concurrent writes, set it to 0 to get higher performance.

Innodb_log_file_size = 128m (default 8m)

This configuration item sets the size of the UNDO logs of the innodb database engine, thereby reducing database checkpoint operations.

Query_cache_size = 128m (default 32m)

Cache the ResultSet in MySQL, that is, the result set of the execution of a SQL statement, so only for select statements

Show status like 'Qcache_%'; | Qcache_hits | 1892463 | | Qcache_inserts | 35627 | hit rate of 98.17% / (1892463 + 35627) * 100

Thread_cache_size = 128 (default 8)

If there is still space in the cache when disconnected, the client thread will be placed in the cache, if the thread is re-requested, the request will be read from the cache, and if the cache is empty or a new request, then the thread will be recreated

Mysql > show status like 'thread%';+-+---+ | Variable_name | Value | +-+-- + | Threads_cached | 0 | 16; 3G-> 32; > 3G-> 64

Innodb_autoextend_increment=128 (default 8m)

The main purpose of this configuration item is that when the tablespace space is full, how much space the MySQL system needs to expand automatically, and each tablespace expansion will make each SQL wait. Increasing the automatic extension Size can reduce the number of tablespace automatic expansion.

Table structure optimization

Innodb_file_per_table = on (default off before 5.6)

This setting tells InnoDB whether the data and indexes of all tables need to be stored in a shared tablespace (innodb_file_per_table = OFF) or in a separate .ibd file (innodb_file_per_table = ON) for each table. One file per table allows you to reclaim disk space in drop, truncate, or rebuild tables. This is also necessary for some advanced features, such as data compression. But it doesn't bring any performance benefits. The main scenario where you don't want one file per table is that there are a lot of tables (such as 10k +).

In MySQL 5.6, the default value for this property is ON, so in most cases you don't need to do anything. For previous versions, you must set this property to ON before loading the data, because it only affects newly created tables.

The above detailed steps on the optimization of Mysql parameters are helpful to you. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.