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

LOG FILE SYNC tuning

As a general method of log file sync diagnosis and tuning, we can generally judge what is wrong by diagnosing the IO delay of the system and whether the CPU resources are sufficient.

Diagnosis and tuning of IO delay

Poor IO performance is often the main culprit for high log file sync, and DBA should try to keep redo logs on separate IO devices. Although lgwr can use asynchronous IO, the log file is opened with the DSYNC flag, and the log must be flushed to disk before the commit operation can be returned (if the host has a raid card, it should be flushed to the CACHE of the raid card, if storage is used, it should be flushed to the stored CACHE). You can use the log file parallel write as a background process wait event to help diagnose the problem of excessive log file sync wait time. If the log file parallel write wait time is too long, then there is probably a problem with your system's IO, but as I mentioned earlier, too much log file parallel write may also be caused by the large number of logs that need to be written. The means to optimize IO are generally as follows: do not use RAID5 in the way of RAID, preferably RAID10. If you use PC locally, then close the read CACHE of raid card and use it as a write CACHE. If you use storage, you can use 2-4 disks to do raid10 as a log disk group. For medium and high-end storage, the CACHE of the general storage head is also relatively large, and IO performance can be basically guaranteed.

You can try to use SSD to store Oracle's redo log files. Although it is not recommended in the industry, with the optimization of SSD's write magnification algorithm and the maturity of Wear leveling, Over-provisioning and GC strategies, DBA can try to put redo logs on SSD devices to improve IO performance. When using SSD to store redo logs, it is best to use SSD products from large vendors. If there is no budget problem, it is recommended to select SSD of SLC type, and reserve enough free space for logs on SSD disk, because for SSD products, the more space reserved, the longer the life of SSD and the better performance.

Diagnosis and tuning of CPU Resources

If CPU resources are very scarce and Lgwr cannot get CPU resources, it will slow down the system call and increase the waiting time of log file sync. As we mentioned above, system call, semaphores and IO call all rely on CPU resources. If the time difference between log file sync and log file parallel write is too large, the CPU resources of the system may be insufficient. Under solaris, the cpu scheduling delay time of lgwr processes can also be diagnosed by the operating system tool prstat. Other platforms cannot track and diagnose the process directly, but can assist the diagnosis through the system LOAD,CPU utilization. For example, if the CPU utilization exceeds 60%, it may cause scheduling delay to a certain extent, and there is a risk of scheduling delay if the CPU running queue exceeds the number of CORE of the logical CPU, and so on. It is tricky to have a bottleneck in the CPU resources of the system, because it is not troublesome to change the hard disk, but it is different to change the CPU. It is generally more difficult, and the final result may be to change the host. However, there are some methods that can be tried, such as raising the priority of LGWR through the database parameter _ high_priority_processes, or the operating system command renice command (the former may be better). If the system CPU is very rich, you can also consider using tools such as taskset to set an exclusive CPU (core) for the Lgwr process.

Tuning application

Sometimes the more effective means may not be desperately tuning the database and tuning the hardware, such as whether transactions can be merged, which reduces the number of LOG FILE SYNC and improves the efficiency of system transactions in disguise. But in order to improve the number of transactions in the system at the expense of business integrity, you need to think carefully about whether it is necessary and risky to do so. If some transactions can be merged without sacrificing business integrity, the effect is immediate.

Database tuning

L reduce the workload of lgwr by reducing the log of transactions, such as reducing unnecessary field updates, reducing unnecessary indexes, not using CLOB fields, and so on.

L reduce the number of member in the log group. In general, a production environment keeps 2 copies of each member in a log group, which are stored on different disks or storage. If possible, you can reduce the number of member in the log group and reduce the workload of Lgwr.

The transport log of the image library is in asynchronous mode.

Memory tuning

Under AIX, if memory read-ahead is enabled, it is also very obvious for TPS to improve dscrctl-n-b-s 1. Also pay close attention to the memory usage of the system, if the system appears SWAP,lgwr is very likely to be paged out, will also cause the lgwr response is very slow.

The above provides some ideas for tuning log file sync, but with the increase in the number of concurrent transactions in the system, lgwr may not be fast enough to notify a large number of processes waiting to commit. Although there is no shortage of CPU resources at this time, and IO is fast enough, lgwr is still slow to work. As I mentioned above, after the lgwr has finished its work, you need to notify the process waiting for submission, telling them that the submission has been completed and that they can do something else. If there are too many processes waiting to be committed, the more work lgwr will do and the greater the corresponding delay. And if there are a large number of processes waiting for log file sync, once lgwr is finished, it will notify these processes to wake up and make them re-enter the CPU run queue, in this case, because lgwr has been working for some time (it has taken up a lot of CPU time), and the foreground process has been waiting for some time (waiting for LOG FILE SYNC), according to the operating system's default scheduling policy, in this case The foreground process will have a higher priority to obtain CPU resources, while lgwr may not get CPU resources because of the sudden shortage of CPU resources, resulting in a jitter in the number of transactions in the system, which suddenly decreases greatly. So the method I suggested earlier to increase the priority of the LGWR process is worth trying.

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.