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

This article will explain in detail how to analyze the REDO AHI latch lock in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Text

Q: there are big transactions in my system, how can I identify the problems that may arise?

There is a common point in MYSQL, that is, it is not recommended to deal with more complex and holistic transactions at one time, but to deal with them separately to reduce the relevance of a large transaction and turn it into multiple transactions. When a very large transaction occurs in MYSQL, it is not good for the system, but how to explain it clearly is a problem.

1 Checkpoint, it is well known that dirty pages will be refreshed if dirty page reaches a value-triggered ratio. Of course, checkpoint itself also has four modes to refresh data to disk.

The complete stages of a transaction are as follows

Creation phase: the transaction creates a log

Log flush: log files written to disk

Data flushing: the dirty page data corresponding to the log is written to the data file on disk

Write CKP: logs are written to log files as Checkpoint

There are a few points to pay attention to.

1. The position of 7 checkpoint 8 in the log space. If the log is written to this location, it will start to perform the log asynchronously without blocking the transaction.

If triggered at this point, some current transactions will be stopped and the disk will be flushed.

3 reach the position of 31x32 and start to do last checkpoint

4 to reach the size of the log space, stop some transactions and do last checkpoint

Therefore, when the number of one-time writes to a large transaction is large and persistent, when it reaches the position between 7Universe 8 and 15Universe 16, the whole system will be in a situation where I am busy brushing the disk, and when it reaches 15Universe 16, the whole system will no longer accept operations.

So we have to monitor the log occupancy in the end, using the following ways to monitor

Select count/1000000 from innodb_metrics where name like'% innodb_check%'

View the overall percentage occupied by checkpoint.

Q: what is the status of log writes to innodb in the current database, is there no waiting state, and is there a bottleneck?

This refers to whether there is a bottleneck in the writing of redo log. We can monitor whether the Innodb_os_log_pending_writes parameter is growing in Thai style. If it continues to grow, it means that the writing of the above logs has a performance bottleneck. The number of bytes written by the relevant log can be obtained through the Innodb_os_log_written parameter. To determine whether the current log is written as a whole.

Q: what is the current latch lock in MYSQL system, is there a bottleneck, and how can it be improved?

First of all, latch is a memory lock, the main role is to protect shared resources to support concurrency, these two things are contradictory, resources to be exclusive, but also to support concurrency, there must be locks to ensure.

(note: the above lock does not directly refer to the concept of database row lock, page lock, table lock). For related theory, please refer to mysql latch lock, which is not expanded here.

By periodically recording and comparing the following parameters, you can find out whether there is serious latch contention in the system during the check period. In addition to looking at the current execution of SQL statements, you can also adjust the number of mysql instance according to other situations to alleviate.

Select name,count from INNODB_METRICS where name in ('innodb_rwlock_s_spin_rounds','innodb_rwlock_x_spin_rounds','innodb_rwlock_sx_spin_rounds')

Q: how does adaptive hash indexing work? It is all carried out by MYSQL itself, how to monitor it?

Briefly talk about HASH, in fact, this method can also be designed into the business table to achieve certain goals and speed up the query, the adaptive HASH provided by MYSQL.

For database queries, queries through primary keys and indexes are normal, MYSQL AHI, for more than 3 corresponding queries =, > =

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.