4G memory server MySQL configuration optimization, 4G memory server mysql

4G memory server MySQL configuration optimization, 4G memory server mysql

As the company's website traffic increases (more than 0.1 million PVS per day), MySQL naturally becomes a bottleneck. The most basic optimization of MySQL system parameters is about MySQL.

MySQL has the greatest impact on the performance of the web architecture and is also a key core component. Whether the MySQL settings are reasonably optimized directly affects the web speed and load capacity! At the same time, MySQL is also the most difficult part of optimization. It not only needs to understand some MySQL professional knowledge, but also requires a long period of observation statistics and judgment based on experience, and then set reasonable parameters. Next, let's take a look at some of the basics of MySQL optimization, including the optimization of MySQL itself (my. cnf.

We will introduce some parameters that have a great impact on performance. The optimization settings of the my. cnf file are closely related to the server hardware configuration,Therefore, we specify a common 2U server hardware environment: CPU: 2 Intel Xeon 2.4 GHz memory: 4 gb ddr Hard Disk: SCSI.

Next, we will describe the optimized my. cnf based on the above hardware configuration:

[Mysqld]
Port = 3306
Serverid = 1
Socket =/tmp/mysql. sock
Skip-locking
# Avoid external locks of MySQL to reduce the chance of errors and enhance stability.
Skip-name-resolve
# Prohibit MySQL from performing DNS resolution on external connections. Using this option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally!
Back_log = 384
# The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. 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 listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.
Key_buffer_size = 256 M
# Key_buffer_size specifies the buffer size used 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!
Max_allowed_packet = 4 M
Thread_stack = 256 K
Table_cache = 128 K
Sort_buffer_size = 6 M
# The buffer size that can be used for sorting. Note: The allocated memory for this parameter is exclusive to each connection. If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.
Read_buffer_size = 4 M
# The buffer size that can be used by the read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.
Join_buffer_size = 8 M
# The size of the buffer that can be used by the Joint query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.
Myisam_sort_buffer_size = 64 M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64 M
# Specify the size of the MySQL Query Buffer. You can observe on the MySQL console that if the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the Qcache_hits value is very large, it indicates that the query buffer is frequently used, if this value is small, it will affect the efficiency, you can consider not to query the buffer; Qcache_free_blocks, if this value is very large, it indicates that there are many fragments in the buffer.
Tmp_table_size = 256 M
Max_connections = 768
# Specify the maximum number of connection processes allowed by MySQL. If the Too connector Connections error is frequently reported during Forum access, you need to increase the value of this parameter.
Max_connect_errorrs = 10000000
Wait_timeout = 10
# Specify the maximum connection time of a request. For servers with around 4 GB of memory, you can set it to 5-10.
Thread_concurrency = 8
# The value of this parameter is the number of logical CPUs of the server x 2. In this example, the server has two physical CPUs, and each physical CPU supports H.T hyper-threading, therefore, the actual value is 4*2 = 8.
Skip-networking
# Enabling this option can completely disable the MySQL TCP/IP connection mode. If the WEB server accesses the MySQL database server remotely, do not enable this option! Otherwise, the connection will fail!
Table_cache = 1024
# The larger the physical memory is, the larger the setting will be. The default value is 2402, and the optimal value is adjusted.
Innodb_additional_mem_pool_size = 4 M
# The default value is 2 MB.
Innodb_flush_log_at_trx_commit = 1
# If it is set to 0, innodb_log_buffer_size is stored after the queue is full. The default value is 1.
Innodb_log_buffer_size = 2 M
# The default value is 1 MB.
Innodb_thread_concurrency = 8
# Set the number of CPUs on your server to 8 by default.
Key_buffer_size = 256 M
# The default value is 218. The optimal value is 128.
Tmp_table_size = 64 M
# The default value is 16 MB, and the maximum value is adjusted to 64-256.
Read_buffer_size = 4 M
# The default value is 64 KB.
Read_rnd_buffer_size = 16 M
#256 kb by default
Sort_buffer_size = 32 M
#256 kb by default
Thread _ cache_size = 120
# The default value is 60.
Query_cache_size = 32 M

Note:

1. If Key_reads is too large, increase Key_buffer_size in my. cnf to keep Key_reads/Key_read_requests above 1/100. The smaller the value, the better.

2. If Qcache_lowmem_prunes is large, you need to increase the value of Query_cache_size.