MySQL database high concurrency optimization configuration

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 take a look at how MySQL's own optimization operates. 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 of the my.cnf file are closely related to the server hardware configuration, we specify an imaginary server hardware environment:

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

#vim/ETC/MY.CNF the contents of the [MYSQLD] paragraphs in the my.cnf file are listed below, and the other paragraphs have little impact on the performance of the MySQL operation, so ignore it.

The code is as follows

Copy Code

[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 The value of the #back_log parameter indicates how many requests can be on the stack in the short time before MySQL temporarily stops responding to the new request. 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 # Specifies the size of the MySQL query buffer. 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

If you start with the database platform application, I will still prefer MyISAM.

PS: Some people may say that you myisam can't resist too much writing, but I can make up through the structure, say a database platform capacity of my existing use: Master and subordinate data total in hundreds of t above, more than 1 billion PV dynamic page every day, there are several big items is through the data interface way call not counted into PV total, ( This includes a large project because the initial memcached was not deployed, resulting in a single database processing 90 million of queries a day. My overall database server load averaged around 0.5-1.

MyISAM and InnoDB optimization:

key_buffer_size– This is very important for the MyISAM table. If you are using only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load-remember that the MyISAM table uses the operating system's cache to cache the data, so you need to set aside some of the memory for them, and in many cases the data peso is much larger. Still, it is necessary to always check whether all the key_buffer are being exploited-. Myi files are only 1GB, and Key_buffer is set to 4GB is very rare. It's too wasteful to do so. If you rarely use MyISAM tables, then keep key_buffer_size below 16-32MB to accommodate the temporary table indexes that are given to the disk.

innodb_buffer_pool_size– This is very important for the InnoDB table. InnoDB is more sensitive to buffering than MyISAM tables. MyISAM can be run under the default Key_buffer_size settings, however InnoDB is like a snail in the default innodb_buffer_pool_size settings. Because InnoDB caches data and indexes without leaving the operating system with too much memory, you can set it up to 70-80% usable memory if you only need to use InnoDB. Some of the rules applied to Key_buffer are-if your data is small and not exploding, you don't have to set the innodb_buffer_pool_size too large.

innodb_additional_pool_size– This option does not have much impact on performance, at least on an operating system with almost enough memory to allocate. But if you still want to set it to 20MB (or more), you need to look at the amount of memory other InnoDB need to allocate.

Innodb_log_file_size is important in the case of high write loads, especially large data sets. The larger the value, the higher the performance, but note that recovery time may be increased. I often set it to 64-512MB, which varies according to the size of the server.

Innodb_log_buffer_size default settings for medium-intensity write load and shorter transactions, server performance is also available. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, it may waste memory-it refreshes every second, so there is no need to set the required memory space for more than 1 seconds. Usually the 8-16MB is enough. The smaller the system, the smaller its value.

Innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head is big? It seems that you have forgotten to revise this parameter. The default value is 1, which means that each submitted update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource-intensive, especially when there is no battery-standby cache. Many applications, especially those from MyISAM, set the value to 2, which means that the log is not flushed to disk, but only to the operating system's cache. Logs are still flushed to disk per second, so there is usually no loss of 1-2 updates per second. If set to 0 is much faster, but also relatively insecure-mysql server crashes will lose some transactions. Set to 2 command to lose that part of the transaction that is flushed to the operating system cache.

Table_cache-the cost of opening a table can be significant. For example MyISAM the Myi file header flag that the table is in use. You certainly don't want this to be too frequent, so it's usually a lot more cache, so it's enough to cache open tables. It needs the operating system's resources and memory, which is certainly not a problem for the current hardware configuration. If you have more than 200 tables, it might be appropriate to set it to 1024 (each thread needs to open the table), and if the number of connections is larger then increase its value. I have seen the case set to 100,000.

The cost of creating and destroying thread_cache-threads can be significant because each thread needs to be connected/disconnected. I'm usually at least set to 16. If there is a large number of jumps and concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is designed to not create a new thread in the usual operation.

Query_cache-This is useful if your application has a large number of reads, and there is no application-level caching. Don't set it too large, because it costs a lot to maintain it, which can cause MySQL to slow down. usually set to 32-512MB. After setting up, it's best to track for a while to see if it's working well. Under certain load pressure, if the cache hit rate is too low, enable it.

sort_buffer_size– If you only have a few simple queries, then you don't need to increase the value of it, although you have 64GB of RAM. Bad luck may degrade performance.