MySQL configuration optimization for 4G memory server

The company's website visits more and more (daily average of over 100,000 PV), MySQL naturally become a bottleneck, about MySQL optimization, the most basic is the MySQL system parameters optimization.

MySQL has the greatest impact on the performance of web architectures and is a key core part. MySQL settings are reasonable optimization, directly affect the speed and load of the web! At the same time, MySQL is also the most difficult part of optimization, not only need to understand some of the MySQL expertise, but also need to observe statistics for a long time and based on experience to judge, and then set reasonable parameters. Let's look at some of the basics of MySQL optimization, MySQL itself (my.cnf) optimization.

We introduce some parameters that have a greater impact on performance. Since the optimization settings for the my.cnf file 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 HDD: SCSI .

Below, we describe the above hardware configuration in conjunction with an optimized MY.CNF:

[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-locking
#避免MySQL的外部锁定, reducing the chance of error increases stability.
Skip-name-resolve
#禁止MySQL对外部连接进行DNS解析, this option can be used to eliminate the time for DNS resolution for MySQL. However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address mode, otherwise MySQL will not be able to handle the connection request properly!
Back_log = 384
#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中. 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 its own limitations on this queue size. Attempting to set a limit of back_log above your operating system will be invalid. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.
Key_buffer_size = 256M
#key_buffer_size指定用于索引的缓冲区大小, increase it to get better index processing performance. The parameter can be set to 256M or 384M for a server that has around 4GB. Note: This parameter value setting is too large to be the overall efficiency of the server down!
Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K
Sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小. Note: The allocated memory for this parameter is per-connection exclusive, and if there are 100 connections, then the actual allocated total sort buffer size is 100x6 = 600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.
Read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
Join_buffer_size = 8M
#联合查询操作所能使用的缓冲区大小, like Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
Myisam_sort_buffer_size = 64M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64M
#指定MySQL查询缓冲区的大小. It can be observed in the MySQL console that if the value of the qcache_lowmem_prunes is very large, there is often insufficient buffering, and if the value of qcache_hits is very large, the query buffer is used very frequently, and if the value is small it can affect efficiency. Then consider not querying the buffer; qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.
Tmp_table_size = 256M
Max_connections = 768
#指定MySQL允许的最大连接进程数. If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.
Max_connect_errors = 10000000
Wait_timeout = 10
#指定一个请求的最大连接时间, the server with about 4GB of memory can be set to 5-10.
Thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量 * *, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4*2=8
Skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP connection, do not turn on this option if the Web server accesses the MySQL database server as a remote connection! Otherwise it will not connect properly!
table_cache=1024
#物理内存越大, the larger the setting. Default is 2402, set to 512-1024 best
innodb_additional_mem_pool_size=4m
#默认为2M
Innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存, default is 1
Innodb_log_buffer_size=2m
#默认为1M
Innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几, the recommended default is typically 8
key_buffer_size=256m
#默认为218, tune to 128 best
tmp_table_size=64m
#默认为16M, tune to 64-256 most hanging
read_buffer_size=4m
#默认为64K
read_rnd_buffer_size=16m
#默认为256K
sort_buffer_size=32m
#默认为256K
thread_cache_size=120
#默认为60
query_cache_size=32m

Attention:

First, if the key_reads is too large, it should be my.cnf in the key_buffer_size to become larger, keep key_reads/key_read_requests at least 1/100, the smaller the better.

Second, if the qcache_lowmem_prunes is large, it is necessary to increase the value of query_cache_size.

MySQL configuration optimization for 4G memory server