Shulou(Shulou.com)06/03 Report--

This article mainly introduces "MySQL performance tuning skills and Monyog thread cache monitoring". In daily operation, I believe many people have doubts about MySQL performance tuning skills and Monyog thread cache monitoring. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL performance tuning skills and Monyog thread cache monitoring". Next, please follow the editor to study!

Tip # 1: determine the maximum number of connections for MySQL

For the maximum number of connections to MySQL, it is best to send five requests to the Web server at a time. Some of the five requests to the Web server will be used for resources such as CSS stylesheets, images, and scripts. For reasons such as browser caching, it can be difficult to get an accurate MySQL-to-Web server request ratio; to get an exact number, you need to analyze the log files of the Web server. For example, you can access Apache's "access_log" log file manually or through utilities such as Analog or Webalizer.

Once you have an accurate estimate of a specific usage case, multiply the ratio by the maximum number of connections to the Web server. For example, if the Web server is configured to serve up to 256 clients, and the ratio of MySQL requests to Web requests is 1 to 8, it is best to set the maximum number of database connections to 32. Also consider leaving a safety margin and multiplying this number by 2 to get the final quantity. Try to match the maximum number of database connections to the client limit of the Web server only if the infrastructure supports it. In most cases, it's best to keep it close to 32.

View MySQL connections in Monyog

In the MySQL database, the maximum number of concurrent connections for MySQL is stored in the global variable max_connections. The Monyog report variable "max_connections" is used as the maximum allowable metric in the current connection monitoring group. It also divides this number by the number of open connections to generate the percentage of connection usage:

There is also a connection history monitor that can help calculate the optimal maximum number of concurrent connections. It includes the number of attempts, rejections, and successful connections. In addition, the percentage of the maximum allowable metrics is displayed as a progress bar, which allows you to quickly assess the maximum number of concurrent connections reached by the server in the past:

Tip # 2: allocate enough memory for temporary tables

In some cases, the server creates internal temporary tables when processing statements. Temporary tables are used for internal operations such as GROUP BY and distinct, as well as some ORDER BY queries and subqueries in the UNION and FROM clauses (derived tables). These are memory tables created in memory. The maximum size of temporary tables in memory is determined by smaller values in tmp_table_size and max_heap_table_size. If the size of the temporary table exceeds this threshold, it is converted to an InnoDB or MyISAM table on disk. In addition, if the query involves BLOB or TEXT columns that cannot be stored in a memory table, the temporary table always points directly to disk.

This conversion is costly, so consider increasing the size of the max_heap_table_size and tmp_table_size variables to help reduce the number of temporary tables created on disk. Keep in mind that this will require a lot of memory because the size of the temporary table in memory is based on the "worst-case". For example, memory tables always use fixed-length columns, so character columns use VARCHAR. This makes the temporary tables in memory much larger than you think-in fact, this is many times larger than the total size of the query table! When increasing the size of max_heap_table_size and tmp_table_sizevariables, be sure to monitor the server's memory usage, as temporary tables in memory may increase the risk of reaching the server's memory capacity.

In general, 32m to 64m are recommended values, starting with these two variables and tuning as needed.

Temporary Table Monitoring in Monyog

Temporary table monitoring is one of many predefined Monyog monitoring. It provides a number of indicators used by temporary tables, including:

Maximum allowed: displays the value of the tmp_table_size server variable, which defines the maximum size of the temporary table created in memory. Along with max_heap_table_size, this value defines the maximum size of temporary tables that can be created in memory. If the temporary memory table is larger than this size, it is stored on disk.

Maximum size of the memory table: displays the value of the max_heap_table_size server variable that defines the maximum size of the MEMORY storage engine table that is explicitly created.

Total number of temporary tables created: displays the value of the created_tmp_tables server variable, which defines the number of temporary tables created in memory.

Temporary tables created on disk: displays the value of the created_tmp_disk_tables server variable, which defines the number of temporary tables created on disk. If this value is high, you should consider increasing the values of tmp_table_size and max_heap_table_size to increase the number of in-memory temporary tables created, thereby reducing the number of temporary tables created on disk.

Disk: total ratio: calculated based on created_tmp_disk_tables divided by created_tmp_tables. The percentage of temporary tables created on disk due to insufficient tmp_table_size or max_heap_table_size. Monyog displays this number as a progress bar and a percentage to quickly determine how many disks are used for temporary tables rather than memory.

The trend chart can be used for the total table created, the total ratio of the table created on disk to the disk. These show us how they evolve over time:

Tip # 3: increase the thread cache size

The connection manager thread handles client connection requests on the network interface that the server listens to. The connection manager thread associates each client connection with a thread dedicated to it, which handles the authentication of the connection and all request processing. Therefore, there is an one-to-one ratio between the thread and the client to which it is currently connected. It is important to ensure that the thread cache is large enough to accommodate all incoming requests.

MySQL provides a number of server variables related to the connection thread:

The thread cache size is determined by the thread_cache_size system variable. The default value is 0 (no cache), which causes a thread to be set for each new connection and needs to be processed when the connection terminates. If you want the server to receive hundreds of connection requests per second, you should set the thread_cache_size high enough so that most new connections can use cached threads. You can set the value of max_connections when the server is up or running.

You should also monitor the number of threads in the cache (Threads_cached) and how many threads have been created because threads cannot be obtained from the cache (Threads_created). For the latter, if Threads_created continues to grow at more than several threads per minute, consider increasing the value of thread_cache_size.

Use the MySQL show status command to display variable and state information for MySQL. Here are a few examples:

Monyog thread cache monitoring

Monyog provides a screen called "Thread" to monitor the thread cache. The server variables associated with MySQL threads map to the following Monyog metrics:

Thread_cache_size: the number of threads that can be cached.

Threads_cached: the number of threads in the cache.

Threads_created: create a thread to process the connection.

The Monyog thread screen also includes the Thread Cache hit ratio indicator. This is an indicator of the thread cache hit ratio. If the value is low, you should consider increasing the thread cache. Display the value as a percentage in the status bar; the closer it is to 100%, the better.

If the values of these metrics are equal to or exceed the specified values, each metric can be configured to issue warnings and / or critical alerts.

Other related server variables

In addition to the above indicators, the following should be monitored:

InnoDB buffer pool size: InnoDB buffer pool size plays a critical role in MySQL databases using InnoDB. The buffer pool caches both data and indexes. Its value should be as large as possible to ensure that the database uses memory instead of the hard drive for read operations.

Temporary table size: MySQL uses the smaller of max_heap_table_size and tmp_table_size to limit the size of temporary tables in memory. Having a large value can help reduce the number of temporary tables created on disk, but it also increases the risk of server memory capacity because this metric applies to each client. In general, 32m to 64m are the recommended values, starting with these two variables and tuning as needed.

InnoDB log buffer size: every time MySQL writes to a log file, it takes advantage of important system resources that can be used to process sales data. Therefore, it makes sense to set the InnoDB log buffer size to a large value. In this way, the number of times the server writes to disk in large transactions is reduced, thus minimizing these time-consuming operations. 64m is a good starting point for this variable.

At this point, the study on "MySQL performance tuning techniques and Monyog thread cache monitoring" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.