Shulou(Shulou.com)05/31 Report--

How to analyze the parameters of MySQL performance optimization, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The optimization of MySQL is divided into two parts, one is the optimization of the physical hardware of the server, the other is the optimization of MySQL itself (my.cnf). I. the influence of server hardware on the performance of MySQL

① disk seek capability (disk Imango O), take the current high-speed SCSI hard disk (7200 rpm / s) as an example, this kind of hard disk theoretically seeks 7200 times per second, which is determined by physical characteristics and cannot be changed. MySQL does a lot of complex query operations every second, so you can imagine the amount of reading and writing to the disk. Therefore, it is generally regarded as one of the biggest factors restricting the performance of MySQL. For Discuzables with an average daily visit of more than 1 million PV, the performance of MySQL will be very poor due to the restriction of disk iBandO. To address this constraint, consider the following solutions: use RAID-0+1 disk arrays and be careful not to try to use RAID-5,MySQL. The efficiency on RAID-5 disk arrays will not be as fast as you might expect.

For ② CPU, S.M.P is recommended for MySQL applications. Multi-channel symmetric CPU of the architecture, for example, you can use two Intel Xeon 3.6GHz CPU, now I recommend using a 4U server specifically as a database server, not just for mysql.

③ physical memory for a Database Server using MySQL, it is recommended that the server memory is not less than 2GB, and it is recommended to use physical memory above 4GB. However, memory is a negligible problem for current servers. When you encounter a high-end server, the memory is basically more than 16 GB.

Second, MySQL's own factors after solving the above server hardware constraints, let's take a look at how the optimization of MySQL itself operates. The optimization of MySQL itself is mainly to optimize and adjust the parameters in its configuration file my.cnf. Let's introduce some parameters that have a great impact on performance. Since the optimized settings of the my.cnf file are closely related to the server hardware configuration, we specify a hypothetical server hardware environment: CPU: 2 Intel Xeon 2.4GHz memory: 4GB DDR hard disk: SCSI 73GB (the most common 2U server).

Next, we will explain the above hardware configuration combined with an optimized my.cnf:

# vim / etc/my.cnf the following 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 = 3306

Serverid = 1

Socket = / tmp/mysql.sock

Skip-locking

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

Skip-name-resolve

# prohibit MySQL from parsing DNS for external connections. Use this option to eliminate the time 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 that the system be set to an integer less than 512.

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 = 4m

Thread_stack = 256K

Table_cache = 128K

Sort_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 in federated query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

Myisam_sort_buffer_size = 64m

Table_cache = 512

Thread_cache_size = 64

Query_cache_size = 64m

# specify 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 = 256m

Max_connections = 768

# specify the maximum number of connection processes allowed by MySQL. If there are frequent Too Many Connections errors when visiting the forum, you need to increase the value of this parameter.

Max_connect_errors = 10000000

Wait_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

# enable this option to completely turn off the TCP/IP connection mode of MySQL. Do not turn on this option if the WEB server accesses the MySQL server by remote connection. 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

# 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 can be set to several. It is recommended that the default is 8.

Key_buffer_size=256M

# default is 218, which is best set to 128

Tmp_table_size=64M

# default is 16m, set to 64-256 is the most popular

Read_buffer_size=4M

# default is 64K

Read_rnd_buffer_size=16M

# default is 256K

Sort_buffer_size=32M

# default is 256K

Thread_cache_size=120

# default is 60

Query_cache_size=32M

It is worth noting that:

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.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.