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

For nearly half a year, I have been dealing with log file sync. Every time I check the TOP 5 waiting events during the system stress test, log file sync steadily ranks in the first place, and the average response time has reached 10ms. With the step-by-step optimization, the process of upgrading the TPS of the system stress test from 4000 to 6000 to 8000 TPS is also the process of optimizing the log file sync waiting events. This chapter mainly introduces the principles and optimization techniques related to log file sync. I hope the content of this chapter can help you more or less.

The background of log file sync

When a transaction commits, Lgwr needs to flush the log generated by the transaction to disk to achieve persistence, so as to achieve the D requirements of ACID. The foreground process waits for log file sync to wait for the event from issuing the commit instruction to receiving the notification of the completion of the Lgwr process log. The foreground process is blocked while waiting for log file sync and cannot do anything else. When the Lgwr refreshes the log to the online log, it will inform the foreground process that the log writing is complete. After receiving the notification, the foreground process will continue to do other things. Once the log is flushed to disk, it marks the end of transaction persistence. It has become a general rule for all databases to achieve transaction persistence through logs. Speed is the most important reason why logs are used to achieve transaction persistence. We can imagine that a transaction inserts 10 records on a table with three indexes, if it is persisted by refreshing dirty blocks of data. How many data blocks need to be refreshed? due to the characteristics of the heap table, 10 records can be considered to be clustered on one data block, but 10 records for the index, it is very likely that the number of index blocks can be 20 or 30, because the index is ordered, unlike the heap table, which can be clustered together without distinction. Of course, after using ASSM management, when inserting the record, the inserted data block will be selected according to the pid of the process. So to some extent, for 10 records, if it is inserted by multiple processes, it is also very likely to be in different data blocks, but we are talking about a transaction, of course, we are referring to a process. According to our calculation, we have only inserted 10 records, but there may be about 30 dirty blocks involved, and each transaction commit needs to refresh 30 discrete data blocks, which is really too low. the log is ordered on disk, so the refresh efficiency is much higher, and the log also provides the basis for the database to achieve other functions, such as providing instance recovery after the instance suddenly downloads. It provides the foundation for the realization of log mining, the foundation for the recovery of preparation database, and so on.

Log file sync is one of the most common waiting events in Oracle databases. Generally, the waiting time of log file sync is very short 1-8ms, which depends on the hardware configuration of your database, the load of the host, and so on. In general, if you put the log disk on high-end storage, the log file sync time should be maintained between 1-5ms. If you are using pc and have a raid card, the log file sync time should be maintained at 3-15ms. But these data are not absolute, and it depends on the configuration and load of your database. For example, I have encountered a situation where the cache of raid card is close to 30ms with 512m log file sync. After analysis, due to the large amount of log and dirty data blocks, the cache of raid card was broken down, and the read cache of raid card was not turned off at that time. Keep the default read-write ratio of 7:3. Generally, if you are not using raid 5 and other raid settings that need to be verified, such as raid1 or raid10, you can use all the cache policies of the raid card to write cache. But you can't do this for storage. You can't turn off all the read cache and only write cache, because compared to the cache of raid card, the size of its own cache is relatively small, so it is particularly valuable, but the storage cache is generally larger, such as an interrupt storage head cache can also be 8G or 16G, to a certain extent, they can not only be used to write cache, but also a continuation of host memory.

The meaning of the parameter P1 to P2 and P3 for the log file sync wait event:

P1: buffer#, the number of buffer in log buffer

P2PowerP3 is not in use

The common scenarios that generate log file sync are as follows:

Commit operation

Rollback operation

DDL operation (commit is performed before DDL operation is implemented)

Log file sync resulting from data dictionary modification caused by DDL operation

Some operations that can recursively modify the data dictionary, such as querying the next value of SEQ, may cause the data dictionary to be modified. A typical situation is that the cache property of SEQ is set to nocache, which causes the data dictionary to be modified every time SEQ is called, resulting in a recursive log file sync.

In a normal system, the vast majority of log file sync waits should be log file sync waits caused by commit operations. Some abnormal systems, such as frequent rollback and systems with seq's cache set to nocache, may also cause more log file sync waits.

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.