MySQL database high concurrency optimized configuration

[CSharp]View PlainCopyPrint?

2. Let's look at some of the MySQL optimization basics, MySQL optimization I divided into two parts, one is the optimization of the server physical hardware, and the second is the MySQL itself (my.cnf) optimization.
6. First, the impact of server hardware on MySQL performance
10. ① 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 characteristics, there is no way to change. MySQL every second in a large number of complex query operations, the amount of read and write disk can be imagined. Therefore, the disk I/O is generally considered to be one of the biggest constraints on MySQL performance, for the average daily access to more than 1 million PV discuz! forum, due to disk I/O constraints, MySQL performance will be very low! To address this constraint, consider the following solutions: Using the raid-0+1 disk array, it is important not to try to use Raid-5,mysql on the RAID-5 disk array as quickly as you might expect.
14. ②CPU for MySQL applications, it is recommended to use S.M.P. Architecture's multi-symmetric CPU, for example: two Intel Xeon 3.6GHz CPUs can be used, now I would rather recommend 4U servers dedicated to database server, not just for MySQL.
18. ③ physical memory for a database server using MySQL, the server memory is recommended not less than 2GB, recommended to use more than 4GB of physical memory, but memory for the current server can be said to be a negligible problem, At work encountered high-end servers, basically more than 16G of memory.
22. Second, MySQL self-factors
26. When the above server hardware constraints are addressed, let's look at how MySQL's own optimizations are being manipulated. The optimization of MySQL itself is mainly to optimize and adjust the parameters in the configuration file MY.CNF.  Let's 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 an imaginary server hardware environment:
30. Below, we describe the above hardware configuration in conjunction with an optimized MY.CNF:
36. #vim/ETC/MY.CNF The following lists only the contents of the [MYSQLD] paragraph in the my.cnf file, and the other paragraphs have little impact on MySQL performance, so ignore them.   
38. Code to copy code as follows
40. [Mysqld]
42. Port = 3306
44. ServerID = 1
46. Socket =/tmp/mysql.sock
48. Skip-locking
50. #避免MySQL的外部锁定, reducing the chance of error increases stability.   
52. Skip-name-resolve
54. #禁止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!   
56. Back_log = 384
58. the value of the #back_log parameter indicates how many requests can be present in the stack for a short period of 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 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.   
60. Key_buffer_size = 256M
62. #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!   
64. Max_allowed_packet = 4M
66. Thread_stack = 256K
68. Table_cache = 128K
70. Sort_buffer_size = 6M
72. #查询排序时所能使用的缓冲区大小. 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.   
74. Read_buffer_size = 4M
76. #读查询操作所能使用的缓冲区大小. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.   
78. Join_buffer_size = 8M
80. #联合查询操作所能使用的缓冲区大小, like Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.   
82. Myisam_sort_buffer_size = 64M
84. Table_cache = 512
86. Thread_cache_size = 64
88. Query_cache_size = 64M
90. # Specifies the size of the MySQL query buffer. 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.   
92. Tmp_table_size = 256M
94. Max_connections = 768
96. #指定MySQL允许的最大连接进程数. If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.   
98. Max_connect_errors = 10000000
100. Wait_timeout = 10
102. #指定一个请求的最大连接时间, the server with about 4GB of memory can be set to 5-10.   
104. Thread_concurrency = 8
106. #该参数取值为服务器逻辑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   
108. Skip-networking
110. #开启该选项可以彻底关闭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!   
112. table_cache=1024
114. #物理内存越大, the larger the setting. Default is 2402, set to 512-1024 best   
116. innodb_additional_mem_pool_size=4m
118. #默认为2M   
120. Innodb_flush_log_at_trx_commit=1
122. #设置为0就是等到innodb_log_buffer_size列队满后再统一储存, default is 1   
124. Innodb_log_buffer_size=2m
126. #默认为1M   
128. Innodb_thread_concurrency=8
130. #你的服务器CPU有几个就设置为几, the recommended default is typically 8   
132. key_buffer_size=256m
134. #默认为218, tune to 128 best   
136. tmp_table_size=64m
138. #默认为16M, tune to 64-256 most hanging   
140. read_buffer_size=4m
142. #默认为64K   
144. read_rnd_buffer_size=16m
146. #默认为256K   
148. sort_buffer_size=32m
150. #默认为256K   
152. thread_cache_size=120
154. #默认为60   
156. query_cache_size=32m
162. If I start from the database platform application, I will still prefer MyISAM.
166. PS: Some people may say that you myisam can not resist too much write operation, but I can make up by the architecture, say I have a database platform capacity: The total number of master and slave data in more than hundreds of T, more than 1 billion PV dynamic page per day, there are several large items are called by the data interface method is 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 queries per day). My overall database server load averaged around 0.5-1.
170. MyISAM and InnoDB optimizations:
174. key_buffer_size– This is very important for the MyISAM table. If you use 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, the MyISAM table caches the data using the operating system's cache, so you need to set aside some memory for them, and in many cases the data peso is mostly. Nonetheless, it is always necessary to check that all key_buffer are exploited-. MYI files are only 1GB, while Key_buffer is set to 4GB is very rare. This is a waste of time. If you rarely use the MyISAM table, you also keep the key_buffer_size below 16-32MB to accommodate the temporary table index that is given to the disk.
178. innodb_buffer_pool_size– This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory. Some of the rules that apply to Key_buffer are-if you have a small amount of data and do not burst, you do not need to set the innodb_buffer_pool_size too large.
182. innodb_additional_pool_size– This option has little impact on performance, at least on operating systems that have almost enough memory to allocate. But if you still want to set it to 20MB (or larger), you need to look at how much memory InnoDB other needs to allocate.
186. 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 the recovery time may be increased. I often set it to 64-512MB, depending on the size of the server.
190. Innodb_log_buffer_size default settings in the case of moderate-strength write loads and shorter transactions, server performance is also possible. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, memory may be wasted-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 less its value.
194. Innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head big? You may have forgotten to modify this parameter. The default value is 1, which means that each committed update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource intensive, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set its value to 2, which is to not flush the log to disk, but only to the operating system's cache. Logs are still flushed to disk every second, so there is usually no loss of 1-2 updates per second. If you set it to 0, it's a lot faster, but it's also relatively insecure. When the-mysql server crashes, some transactions are lost. Set to 2 command to lose the part of the transaction that was flushed to the operating system cache.
198. 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 happen too often, so it's often necessary to increase the number of caches to maximize the cache of open tables. It needs to use 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 increase its value if the number of connections is larger. I've seen cases set to 100,000.
202. The cost of creating and destroying thread_cache-threads can be significant, as each thread is required to connect/disconnect. I usually set it at least to 16. If there is a large number of hops concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is intended to create no new threads in the usual operation.
206. Query_cache-This is useful if your application has a lot of read and no application-level caching. Do not set it too big, because it also requires a lot of overhead to maintain it, which can cause MySQL to become slower. Typically set to 32-512MB. It's a good idea to track a period of time to see if it's working well. Under a certain load pressure, if the cache hit rate is too low, enable it.
210. sort_buffer_size – If you have only a few simple queries, you don't need to increase its value, even though you have  64GB  memory. Maybe it will degrade performance.   

 Let's look at some of the MySQL optimization basics, MySQL optimization I divided into two parts, one is the optimization of the server physical hardware, and the second is the MySQL itself (my.cnf) optimization. First, the impact of server hardware on MySQL performance ① disk seek capability (disk I/O), with the current high-speed SCSI hard disk (7200 RPM) For example, the hard drive theoretically 7,200 times per second, this is the physical characteristics of the decision, there is no way to change. MySQL every second in a large number of complex query operations, the amount of read and write disk can be imagined. Therefore, the disk I/O is generally considered to be one of the biggest constraints on MySQL performance, for the average daily access to more than 1 million PV discuz! forum, due to disk I/O constraints, MySQL performance will be very low! To address this constraint, consider the following solutions: Using the raid-0+1 disk array, it is important not to try to use Raid-5,mysql on the RAID-5 disk array as quickly as you might expect. ②CPU for MySQL applications, it is recommended to use S.M.P. Architecture's multi-symmetric CPU, for example: two Intel Xeon 3.6GHz CPUs can be used, now I would rather recommend 4U servers dedicated to database server, not just for MySQL. ③ physical memory for a database server using MySQL, the server memory is recommended not less than 2GB, recommended to use more than 4GB of physical memory, but memory for the current server can be said to be a negligible problem, At work encountered high-end servers, basically more than 16G of memory. Second, MySQL self-factor when the above server hardware constraints are resolved, let's look at how MySQL's own optimizations are being manipulated. The optimization of MySQL itself is mainly to optimize and adjust the parameters in the configuration file MY.CNF.  Let's 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 an imaginary server hardware environment: Below, we are based on the above hardware configuration combined with an optimized my.cnf for illustration: #vim/etc/  MY.CNF The following lists only the contents of the [MYSQLD] paragraph in the my.cnf file, while the rest of the paragraphs have little impact on MySQL performance, so ignore them. The code below copies the code [MYSQLD] Port = 3306 ServerID = 1 socket =/tmp/mysql.sock skip-locking #避免MySQL的外部锁定, reducing the chance of error to enhance stability. Skip-name-resolve #禁止MySQL对外部连接进DNS resolution, use this option 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 The value of the #back_log parameter indicates how many requests can be present in the stack for a short period of 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 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 #联合查询操作所能使用的缓冲区大小, as with Sort_buffer_size, the allocated memory for this parameter is also exclusive for each connection. Myisam_sort_buffer_size = 64M Table_cache = thread_cache_size = Query_cache_size = 64M # Specifies the size of the MySQL query buffer. 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 you can consider notquery buffering; 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 = Ten #指定一个请求的最大连接时间, the server with about 4GB 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 # Turn on this option to completely turn off MySQL's TCP/IP connection and do not turn on this option if the Web server is accessing the MySQL database server remotely. 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有几个就设置为几, it is recommended to use the default is generally 8 key_buffer_size=256m #默认为218, tune to 128 the best tmp_table_size=64m #默认为16M, Transfer 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 I start with the database platform application, I would prefer myisam.ps: someone might say that you myisam not be able to write too much, but I can make it up by architecture, Tell me about my existing database platform capacity: Master-slave data totals at hundreds of T toOn the more than 1 billion PV dynamic pages per day, there are several major items that are not counted into the PV total by means of data interface calls (including a large project because the initial memcached was not deployed, resulting in a single database processing 90 million of queries per 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 use 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, the MyISAM table caches the data using the operating system's cache, so you need to set aside some memory for them, and in many cases the data peso is mostly. Nonetheless, it is always necessary to check that all key_buffer are exploited-. MYI files are only 1GB, while Key_buffer is set to 4GB is very rare. This is a waste of time. If you rarely use the MyISAM table, you also keep the key_buffer_size below 16-32MB to accommodate the temporary table index that is given to the disk. innodb_buffer_pool_size– This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory. Some of the rules that apply to Key_buffer are-if you have a small amount of data and do not burst, you do not need to set the innodb_buffer_pool_size too large. innodb_additional_pool_size– This option has little impact on performance, at least on operating systems that have almost enough memory to allocate. But if you still want to set it to 20MB (or larger), you need to look at how much memory InnoDB other needs 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 the recovery time may be increased. I often set it to 64-512MB, depending on the size of the server. Innodb_log_buffer_size default settings in the case of medium-strength write loads and shorter transactions, server performance can alsoTo. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, memory may be wasted-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 less its value. Innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head big? You may have forgotten to modify this parameter. The default value is 1, which means that each committed update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource intensive, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set its value to 2, which is to not flush the log to disk, but only to the operating system's cache. Logs are still flushed to disk every second, so there is usually no loss of 1-2 updates per second. If you set it to 0, it's a lot faster, but it's also relatively insecure. When the-mysql server crashes, some transactions are lost. Set to 2 command to lose the part of the transaction that was 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 happen too often, so it's often necessary to increase the number of caches to maximize the cache of open tables. It needs to use 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 increase its value if the number of connections is larger. I've seen cases set to 100,000. The cost of creating and destroying thread_cache-threads can be significant, as each thread is required to connect/disconnect. I usually set it at least to 16. If there is a large number of hops concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is intended to create no new threads in the usual operation. Query_cache-This is useful if your application has a lot of read and no application-level caching. Do not set it too big, because it also requires a lot of overhead to maintain it, which can cause MySQL to become slower. Typically set to 32-512MB. It's a good idea to track a period of time to see if it's working well. Under a certain load pressure, if the cache hit rate is too low, enable it. sort_buffer_size– If you have only a few simple queries, then you don't need to increase its value, even though you have 64GB of memory. Maybe it will degrade performance.

MySQL database high concurrency optimization configuration