Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of my.cnf parameters in MYSQL, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

Introduction and usage of common configuration file parameters for mysql:

● max_conecctions: the maximum number of connections allowed for the entire MySQL

This parameter mainly affects the concurrent processing ability of the whole MySQL application. When the actual number of connections needed in the system is greater than max_conecctions, due to the setting limitation of MySQL, then the waiting of connection requests will inevitably occur in the application, thus limiting the corresponding concurrency. So generally speaking, as long as the performance of the MySQL host allows, set this parameter as large as possible. Generally speaking, about 500 to 800 is a more appropriate reference value.

● max_user_connections: the maximum number of connections allowed per user; the above parameter limits the number of connections for the entire MySQL, while max_user_connections is the connection limit for a single user. In general, we may use this restriction less, only in some applications that specialize in providing MySQL data storage services or web hosting services. Except for the restricted object differences, other aspects are the same as max_connections. The setting of this parameter depends entirely on the number of connected users of the application. For ordinary applications, there are not too many restrictions at all, so you can try to open them up as much as possible.

● net_buffer_length: in network packet transmission, the initialization size of net buffer before the message is transmitted; this parameter may mainly affect the efficiency of network transmission. Since this parameter is only set to the initialization size of the message buffer, the main impact is that MySQL always needs to apply for expansion of the buffer size multiple times when each of our messages is very large. The default size of the system is 16KB, which can generally meet most scenarios. Of course, if our query is very small, the amount of network transmission is very small, and the system memory is scarce, you can also reduce this value to 8KB appropriately.

● max_allowed_packet: in network transmission, the maximum amount of a message transmitted at a time; this parameter corresponds to net_buffer_length, but is only the maximum value of net buffer. When our message traffic is greater than the net_buffer_length setting, MySQL automatically increases the net buffer size until the buffer size reaches the value set by max_allowed_packet. The system default value is 1MB, and the maximum value is 1GB, which must be set to a multiple of 1024 in bytes.

● back_log: the maximum number of connection requests allowed to be stored in the MySQL's connection request waiting queue. The connection request waiting queue actually means that when the number of connection requests from the client is too large at a certain time, when the MySQL main thread is unable to assign (or create) a connection thread to each new connection request in time, all requests that have not been assigned to the connection thread will be stored in a waiting queue, which is the connection request queue of MySQL. When there are a large number of instantaneous connection requests in our system, we should pay attention to the setting of the back_log parameter. The system default is 50, and the maximum can be set to 65535. When we increase the back_log setting, we also need to impose restrictions on network snooping queues at the OS level, because it makes no sense for us to increase the "back_log" setting if OS's network snooping setting is smaller than MySQL's back_log setting.

In MySQL, in order to improve the performance of the process in which the client requests to create a connection, a Thread Cache pool is implemented, in which idle connection threads are stored instead of being destroyed after completing the request. In this way, when there is a new connection request, MySQL will first check whether there is a free connection thread in the Thread Cache pool, if so, take it out and use it directly, and create a new connection thread if there is no free connection thread. The system parameters and state variables related to the connection thread in MySQL are described as follows:

The number of connected threads that should be stored in the ● thread_cache_size:Thread Cache pool.

When the system is initially started, it will not immediately create the number of connection threads set by thread_cache_size and store them in the Thread Cache pool, but slowly store the used connection threads in it as the connection threads are created and used. When the stored connection thread reaches the thread_cache_size value, MySQL will not continue to save the used connection thread. If our application uses short connections, Thread Cache pooling is most effective. Because in database applications with short connections, database connections are created and destroyed very frequently, so if MySQL needs to create and destroy corresponding connection threads every time, then the resource consumption is actually very large, and when we use Thread Cache, because most of the connection threads are creating a state waiting to be accessed, they do not need to be recreated every time. It does not need to be destroyed after use, so a large amount of department funding can be saved. Therefore, in the short-connected application system, the value of thread_cache_size should be set relatively large and should not be less than the actual number of concurrent requests from the application system to the database. If we use long connections, Thread Cache may not be as effective as short connections, but

It's not totally worthless. Because even if applications use persistent connections, it is difficult to ensure that all the connections they manage are in a stable state, and there will still be a lot of connection closures and new operations. In some cases, the complication is higher, so it should be

In systems with a large number of servers, it is common to create and close connections about ten times a minute. And if the connection pool management of the application server is not very good, and it is easy to produce connection pool jitter, the resulting connection creation and destruction operations will

There will be more. So even in an application environment that uses persistent connections, the use of Thread Cache mechanisms is still very helpful to performance. It's just that in a long-connected environment, we don't need to set the thread_cache_size parameter too large, generally speaking.

Maybe between 50 and 100 should be fine.

● thread_stack: the amount of memory that MySQL allocates to each connection thread when it is created.

When MySQL creates a new connection thread, it needs to allocate a certain amount of memory stack space to store the client's request Query as well as its own state and processing information. But generally speaking, if it's not a cable for MySQL,

If the program processing mechanism is very familiar, you should not easily adjust the size of this parameter, using the default value of the system (192KB) can basically be used in all common application environments. If this value is set too small, it will affect the size of the Query content that the MySQL connection thread can handle the client request, as well as the Procedures and Functions created by the user.

Calculate the Thread of the new connection of the system

The Cache hit rate, that is, the ratio of the number of connection threads obtained through the Thread Cache pool to the total number of connections received by the system, is as follows:

Threads_Cache_Hit = (Connections-Threads_created) / Connections * 100% We can use the above formula to calculate the Thread Cache hit rate in the above environment: Thread_Cache_Hit= (127-12) / 127 * 100% = 90.55% generally speaking, after the system has been running steadily for a period of time, our Thread Cache hit rate should be kept at around 90% or higher. It can be seen that the Thread Cache hit ratio in the above environment is basically normal. For the optimization related to Table Cache, let's first take a look at the mechanism by which MySQL opens the table. Due to the implementation mechanism of multithreading, in order to improve performance as much as possible, each thread in MySQL independently opens the file descriptor of the table it needs, rather than through the mechanism of sharing the file descriptor of the opened table. Of course, there may be different ways to deal with different storage engines. Such as MyISAM table, every client thread needs to open a file descriptor to open any data file of MyISAM table, but if it is an index file, multiple threads can share the descriptor of the same index file. For Innodb's storage engine, if we use shared tablespaces to store data, then

We need to open fewer file descriptors, and if we use exclusive tablespaces to store data, we will also open a lot of table file descriptors because there are more data files for storing table data. In addition to the actual table or index opening of the database, temporary files also need to use file descriptors, which will also take up the set limit of open_files_limit in the system. In order to solve the problem of opening table file descriptors too frequently, MySQL implements a Table Cache mechanism in the system, which is somewhat similar to the Thread Cache mechanism described earlier, mainly the descriptors of all table files opened by Cache, which do not need to be reopened when there is a new request, and do not need to be closed immediately when the use is finished. In this way, the resource consumption caused by frequently opening and closing file descriptors is reduced. Let's first take a look at the system parameters and state variables related to Table Cache. In MySQL we set the number of open table file descriptors in the system for our Cache through table_cache (starting with MySQL5.1.3 and changing to table_open_cache). According to the official manual of MySQL, when we set the table_cache size, it should be calculated by the max_connections parameter. The formula is as follows:

Table_cache = max_connections * N, where N represents the maximum number of Table contained in a single Query statement. But I personally understand that such a calculation is not very accurate. The analysis is as follows:

First of all, max_connections is the maximum number of connections that the system can accept at the same time, but these connections are not necessarily in active state, that is, many connections may be in Sleep state. It is not possible for a connection in the Sleep state to open any Table. Second, the number of Table contained in the Query that executes the most Table in the Query is not appropriate, because we cannot ignore the opening of the index file. Although index files can share open connection descriptors among connection threads, they are always needed. Moreover, if the access to each table in my Query is retrieved through the current index location, perhaps even through multiple indexes, then the execution of the Query requires more file descriptors to be opened, perhaps twice or even three times as much as N. Finally, this formula can only calculate the maximum number of descriptors we need to open at the same time, and the setting of table_cache does not necessarily have to be set according to this limit, because table_cache sets only the number of descriptors opened by Cache, not the maximum number of descriptors that can be opened.

Join_buffer_size: the Buffer; we use when our Join is ALL, index, rang or index_merge is actually called Full Join. Virtually every table that participates in Join requires a Join Buffer, so when Join appears, there are at least two. The setting of Join Buffer is the largest for 4GB before MySQL version 5.1.23, but starting with version 5.1.23, the 4BG limit can be exceeded on 64-bit platforms other than Windows. The system defaults to 128KB.

● sort_buffer_size: the Buffer;Sort Buffer used when sorting data in the system is also for a single Thread, so when multiple Thread are sorted at the same time, multiple Sort Buffer will appear in the system. Generally, we can improve the processing performance of ORDER BY or GROUP BY by increasing the size of Sort Buffer. The default size of the system is 2MB, the maximum limit is the same as Join Buffer, the maximum 4GB before MySQL version 5.1.23, starting with version 5.1.23, the limit of 4GB can be exceeded on 64-bit platforms except Windows. If there are few Join statements in the application system, you can not care too much about the size of the join_buffer_size parameter, but if the Join statement is not very few, I suggest that you can appropriately increase the setting of join_buffer_size to around 1MB, or even set it to 2MB if there is enough memory. For the sort_buffer_size parameter, it is generally set to between 2MB and 4MB to satisfy most

The requirements of the application. Of course, if the sorting in the application system is relatively large, the memory is sufficient and the concurrency is not particularly large, you can continue to increase the setting of sort_buffer_size. In these two Buffer settings, the most important thing to pay attention to is not to forget that each Thread will create its own independent Buffer, rather than the Buffer shared by the whole system, and do not run out of memory because the setting is too large.

Innodb_thread_concurrendy: this parameter is the current-limiting operation of innodb to ensure the normal operation of the service. Setting it to 0 means it is controlled by innodb itself. It is generally recommended to set it to the number of cores of the server CPU (excluding hyperthreading), which will cause the service hang to die and other unavailable cases.

Innodb_io_capacity: the data page on which the background process processes IO operations is online per second. Generally, it can be set to 75% of the total IO capacity. In general, when the IO performance is good, this parameter is recommended to be 1000.

Innodb_buffer_pool_instance: divide instances in innodb_buffer_pool. Each instance contains a list of flush, LRU and free. It is recommended to configure multiple innodb_buffer_pool_instance for large memory.

The ratio of innodb_max_dirty_pages_pct:innodb refreshing dirty pages to disk from innodb_buffer_pool. Setting too high will have a great impact on IO. When this parameter is used in combination with innodb_io_capacity, it can be set to 75% when IO performance is good.

Innodb_flush_method: when set to O_DIRECT, flush memory data directly to disk to avoid caching on raid devices

Innodb_file_per_table: set to 1, each table has a separate data file, which can be placed on other disks to make soft connections to improve IO performance; on the other hand, it can reduce the IO competition of shared tablespaces and avoid excessive ibdata1

Innodb_flush_log_at_trx_commit: set to 0: flush the contents of log buffur to disk every second; set to 1: flush the contents of log buffur to disk before each transaction commit; set to 2: write the contents of log buffur to the transaction log, but it may not be flushed to disk because caches such as the operating system may exist

Sync_binlog: the number of binlog refreshes. The non-core system is set to 1000, which means that it will only be refreshed to disk when 1000 binlog records are accumulated. The core system can be set to 1 to ensure data synchronization between the master and slave servers. Double 1 mode: that is, both innodb_flush_log_at_trx_commit and sync_binlog are set to 1, so that the data between the master and standby are consistent and will not be lost.

Thank you for reading this article carefully. I hope the article "sample Analysis of my.cnf parameters in MYSQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.