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

This article mainly introduces how to optimize the 4G memory server configuration in MySQL, which has a certain reference value. Interested friends can refer to it. I hope you can learn a lot after reading this article.

With the increasing number of visits to the company's website (over 100000 PV per day), MySQL has naturally become a bottleneck. The most basic thing about the optimization of MySQL is the optimization of MySQL system parameters. MySQL has the greatest impact on the performance of web architecture and is a key core part. Let's take a look at some of the basics of MySQL optimization, the optimization of MySQL itself (my.cnf).

MySQL has the greatest impact on the performance of web architecture and is a key core part. Whether the setting of MySQL is reasonably optimized or not directly affects the speed and carrying capacity of web! At the same time, MySQL is also the most difficult part of optimization, not only need to understand some MySQL professional knowledge, but also need to observe statistics for a long time and judge according to experience, and then set reasonable parameters. Let's take a look at some of the basics of MySQL optimization, the optimization of MySQL itself (my.cnf).

We introduce some parameters that have a great impact on performance. Since the optimized settings of my.cnf files are closely related to the server hardware configuration, we specify a common 2U server hardware environment: CPU: 2 Intel Xeon 2.4GHz memory: 4GB DDR hard disk: SCSI.

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

[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 # prohibits MySQL from DNS parsing external connections, and using this option 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! The value of the back_log = 384 # 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 = 4m thread_stack = 256K table_cache = 128K sort_buffer_size = 6m # the size of the buffer that can be used for 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 # buffer size 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 for federated query operations, and like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive per connection. Myisam_sort_buffer_size = 64m table_cache = 512 thread_cache_size = 64 query_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 = 256m max_connections = 768 # specifies the maximum number of connected 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 # specifies 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 disable the TCP/IP connection mode of MySQL. Do not enable this option if the WEB server accesses the MySQL database 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, and the best innodb_additional_mem_pool_size=4M # is set to 2m innodb_flush_log_at_trx_commit=1 # by default to 2m innodb_flush_log_at_trx_commit=1 #, which means wait until the innodb_log_buffer_size queue is full before unified storage. 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 to default to 8 key_buffer_size=256M # default to 218 The best tmp_table_size=64M # defaults to 16m and the most hanging read_buffer_size=4M # defaults to 64K read_rnd_buffer_size=16M # defaults to 256k sort_buffer_size=32M # defaults to 256k thread_cache_size=120 # defaults to 60 query_cache_size=32M

Note:

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.

Thank you for reading this article carefully. I hope the article "how to optimize 4G memory server configuration in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.