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

This article mainly introduces how to optimize MySQL in Linux. It is very detailed and has a certain reference value. Friends who are interested must finish reading it.

Detailed explanation of MySQL optimization examples in Linux

Vim / etc/my.cnf below lists only the contents of the [mysqld] paragraph in the my.cnf file, and the contents of other paragraphs have little impact on the performance of MySQL, so ignore it for the time being.

[mysqld] port = 3306serverid = 1socket = / tmp/mysql.sockskip-locking

Avoid external locking of MySQL, reduce the probability of error and enhance stability.

Skip-name-resolve

Disable DNS parsing of external connections by MySQL, which eliminates 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!

Back_log = 384

The value of the back_log parameter indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops responding to new requests. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have their own limits on the queue size. Trying to set the limit that back_log is higher than your operating system will not be valid. The default value is 50. It is recommended to set an integer less than 512 for Linux systems.

Key_buffer_size = 256m

Key_buffer_size specifies the size of the buffer used for indexing, and increasing it results in better index processing performance. For servers with memory around 4GB, this parameter can be set to 256m or 384m. Note: the excessive setting of this parameter value will reduce the overall efficiency of the server!

Max_allowed_packet = 4Mthread_stack = 256Ktable_cache = 128Ksort_buffer_size = 6m

The size of the buffer that can be used when sorting queries. Note: the allocated memory for this parameter is exclusive per connection. If there are 100 connections, the actual total sort buffer size allocated is 100x6 = 600MB. Therefore, it is recommended to set it to 6-8m for servers with about 4GB memory.

Read_buffer_size = 4m

The size of the buffer that can be used by read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

Join_buffer_size = 8m

The size of the buffer that can be used by federated query operations is the same as sort_buffer_size, and the allocated memory corresponding to this parameter is exclusive per connection.

Myisam_sort_buffer_size = 64Mtable_cache = 512thread_cache_size = 64query_cache_size = 64m

Specifies the size of the MySQL query buffer. You can observe in the MySQL console that if the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient; if the value of Qcache_hits is very large, query buffering is used very frequently, and if the value is small but affects efficiency, then you can consider not query buffering; Qcache_free_blocks, if the value is very large, it indicates that there are a lot of fragments in the buffer.

Tmp_table_size = 256Mmax_connections = 768

Specifies the maximum number of connected processes allowed by MySQL. If there are frequent Too ManyConnections errors when visiting the forum, you need to increase the value of this parameter.

Max_connect_errors = 10000000wait_timeout = 10

Specify the maximum connection time for a request, which can be set to 5-10 for servers with memory around 4GB.

Thread_concurrency = 8

The value of this parameter is the number of server logical CPU * 2. In this example, the server has two physical CPU, and each physical CPU supports H.T hyperthreading, so the actual value is 4 / 2 / 8.

Skip-networking

Turn on this option to completely turn off MySQL's TCP/IP connection mode, but do not turn on this option if the WEB server accesses the MySQL database server remotely! Otherwise, you will not be able to connect properly!

Table_cache=1024

The larger the physical memory, the larger the setting. The default is 2402, which is best adjusted to 512-1024.

Innodb_additional_mem_pool_size=4M

The default is 2m

Innodb_flush_log_at_trx_commit=1

Set to 0 means to wait until the innodb_log_buffer_size queue is full and then store it uniformly. The default is 1.

Innodb_log_buffer_size=2M

Default is 1m

Innodb_thread_concurrency=8

Your server CPU is set to several. It is recommended that the default is 8.

Key_buffer_size=256M# defaults to 218m, the best tmp_table_size=64M # defaults to 16m, and the most popular read_buffer_size=4M # defaults to 64Kread_rnd_buffer_size=16M # defaults to 256Ksort_buffer_size=32M # defaults to 256Kthread_cache_size=120 # defaults to 60query_cache_size=32M

It is worth noting that:

Many situations need to be analyzed in a specific way.

First, if the Key_reads is too large, you should make the Key_buffer_size in the my.cnf larger and keep the Key_reads/Key_read_requests at least 1max above 100. The smaller the better.

Second, if the Qcache_lowmem_prunes is very large, increase the value of Query_cache_size.

The above is all the contents of the article "how to optimize MySQL in Linux". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.