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

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

Q: how to monitor the InnoDB log buffer and whether it is set properly

When changes are made to the InnoDB table in MySQL, the changes are first stored in memory in the InnoDB log buffer and then written to InnoDB log files, which are often referred to as redo logs. If there are signs that the innodb_log_buffer_size variable is set too low, you can adjust it to optimize performance.

Before adjusting, first of all, we need to know how much innodb log buffer is currently used. We can use the following methods to monitor

Select count from information_schema.innodb_metrics where name = 'log_lsn_current'

Select count from information_schema.innodb_metrics where name = 'log_lsn_last_flush'

Subtract the two values to get how much lsn is not currently flushed to disk, and you get innodb log buffer usage.

In terms of the usage above, you can see whether the corresponding configuration of this server is appropriate by conducting an evaluation over a period of time.

Q: after taking over a MYSQL system (halfway here), how do you quickly know about the page merge page splits through monitoring, to the end the overall system design is very OK? (used for reference and problem discovery only)

First of all, we have to have a common consensus that the database processes the data in memory, and the data in memory is processed not in ROW but in page. When organizing data with tables and rows, InnoDB organizes data with branches, pages, and records. It is important to remember that InnoDB cannot work on a single line basis. InnoDB always operates on the page. After the page is loaded, it scans the page for the requested row / record.

According to the configuration of the value of MERGE_THRESHOLD, we need to understand the overall page merging and page separation of mysql.

Generally speaking, 50% of the merge_threshold is set, and a too small merge_threshold will cause the overall data footprint to become larger when the overall data volume remains unchanged, compared with a larger memory footprint.

Therefore, the merge and split of monitoring are indicators to see that the current merge_threshold is very appropriate and the overall operation of the system (for example, this system is designed with physical deletions, no logical deletions, or often the fields of UPDATE are originally small, but become very large after UPDATE).

You need to see it.

Index page merge attempts & index page merge successful and splits

Frequent merge and splits itself is a performance impact on the database, and in the process of operation will affect the related operations of DML.

In addition, from a foreign article on the impact of primary key design on page segmentation, the design of defective INNODB primary key table, page splits operands are 2 times or more than normal.

Through this problem, it can also be derived that too many indexes in MYSQL and problems in the original design of the table will lead to worse performance problems, so the setting for MYSQL tables is more rigorous than other database designs.

Thank you for reading this article carefully. I hope the article "sample Analysis of Monitoring 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.