Mysql Performance Tuning Path---Modify the configuration file My.cnf_mysql

In the Apache, PHP, MySQL architecture, MySQL has the greatest impact on performance and the key core. For the discuz! Forum program is also the case, MySQL settings are reasonable optimization, directly affect the speed and load of the forum! At the same time, MySQL is also the most difficult part of optimization, not only need to understand some MySQL expertise, but also need a long time to observe statistics and based on experience to judge, and then set reasonable parameters. Below we understand some MySQL optimization foundation, MySQL optimization I divide into two parts, one is the server physical hardware optimization, the second is the MySQL itself (my.cnf) optimization.

The impact of server hardware on MySQL performance

① disk seek capability (disk I/O), with the current high speed SCSI hard drive (7200 rpm), for example, the hard drive theoretically seeks 7,200 times per second, which is determined by the physical properties and has no way to change. MySQL every second in a large number of complex query operations, on the disk read and write volume imaginable. As a result, disk I/O is generally considered to be one of the biggest constraints on MySQL performance, and for discuz! forums with daily average visits above 1 million PV, MySQL performance will be very low due to disk I/O constraints! To address this constraint, consider the following solutions: Use a raid-0+1 disk array, and be careful not to attempt to use Raid-5,mysql on the RAID-5 disk array not as fast as you expect.

②CPU for MySQL applications, it is recommended to use S.M.P. Architecture of the multi-channel symmetric CPU, for example: You can use two Intel Xeon 3.6GHz CPU, now I recommend using 4U server to specialize in database server, not only for MySQL.

③ physical memory for a database server that uses MySQL, server memory is not recommended to be less than 2GB, it is recommended to use more than 4GB of physical memory, but memory for the current server can be said to be an overlooked problem, The work encountered a high-end server basically memory is more than 16G.

Second, MySQL's own factors when the above server hardware constraints are resolved, let's see how MySQL's own optimization is operating. The optimization of MySQL itself is mainly to adjust the parameters of its configuration file MY.CNF. Here are some of the 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 an imaginary server hardware environment: Cpu:2 Intel Xeon 2.4GHz Memory: 4GB DDR hard drive: SCSI 73GB (a very common 2U server).

Below, we are based on the above hardware configuration combined with a MY.CNF has been optimized to explain:

[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-locking
#避免MySQL的外部锁定, reduce the chance of error increase stability.
Skip-name-resolve
#禁止MySQL对外部连接进行DNS解析, use this option to eliminate the time that MySQL makes DNS resolution. Note, however, that if this option is turned on, all remote host connection authorizations should be in the IP address mode, otherwise MySQL will not handle the connection request properly!
Back_log = 384
#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中. If the system has many connections in a short time, you need to increase the value of the parameter, which specifies the size of the incoming TCP/IP connection's listening queue. The different operating systems have their own limits on the size of this queue. Attempting to set Back_log above your operating system will be ineffective. The default value is 50. For Linux systems It is recommended to set to an integer less than 512.
Key_buffer_size = 256M
#key_buffer_size指定用于索引的缓冲区大小, increase it to get better index processing performance. This parameter can be set to 256M or 384M for servers with around 4GB. Note: The value of this parameter set too large will 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, the total sorted buffer size that is actually allocated is 100x6 = 600MB. Therefore, for the existence of around 4GB server recommended set to 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
#联合查询操作所能使用的缓冲区大小, as with 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查询缓冲区的大小. Can be observed in the MySQL console, if the value of the qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient, if the value of qcache_hits is very large, it indicates that the query buffer use is very frequent, if the value is small but will affect efficiency, Then consider not querying the buffer; 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
#指定MySQL允许的最大连接进程数. You need to increase the value of a too Many connections error message when you visit the forum frequently.
Max_connect_errors = 10000000
Wait_timeout = 10
#指定一个请求的最大连接时间, a server with about 4GB of memory can be set to 5-10.
Thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量 *2, 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 this option on if the Web server is accessing the MySQL database server in a remote connection way! Otherwise you will not be able to connect properly!
table_cache=1024
#物理内存越大, the larger the setting. Defaults to 2402, to 512-1024 best
innodb_additional_mem_pool_size=4m
#默认为2M
Innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存, defaults to 1
Innodb_log_buffer_size=2m
#默认为1M
Innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几, it is recommended to use the default generally 8
key_buffer_size=256m
#默认为218, tune to 128 best
tmp_table_size=64m
#默认为16M, tune to 64-256.
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
※ It is important to note that:

Many situations require concrete analysis

First, if the key_reads is too big, then should my.cnf in Key_buffer_size, keep key_reads/key_read_requests at least 1/100, the smaller the better.

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