Shulou(Shulou.com)05/31 Report--

Storage cache problem produces a large number of log file leading to slow database solutions. In view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

One day, I received a case. Specifically, the user reported that the front desk business was very slow, and the user would pause for about 3-4 seconds when doing business at the front desk.

However, there are a lot of log file waits and ENQ:HW waits in the database.

The wait event for the database with Awr information as follows:

At that time, the read and write of the database was not large, and the write of redo was 1618396/1024/1024=1.5M/S.

The awr above shows that both 'log file sync'' and 'log file parallel write' have high waiting times.

The average log file parallel write time is 43 milliseconds

The average log file sync time is 74 milliseconds

It is described in the oracle support document as follows:

If a high proportion of 'log file sync'' time is spent on 'log file parallel write', then most of the wait time is due to IO (waiting for redo writes). You should check the performance of LGWR in terms of IO. As a rule of thumb, the average log file parallel write' time is more than 20 milliseconds, which means there is something wrong with the IO subsystem.

(this value is a description of oracle official documentation {troubleshooting: "log file sync" wait (document ID 1626301.1)})

The tablespace IO for further analysis of the database is as follows:

As indicated by awr Av Rd (ms) above, if it is higher than 20ms and has a lot of read operations at the same time, we should start to investigate whether there are potential IO problems from the perspective of OS.

The official documentation is as follows:

(

How to Tell if the I of the Database is Slow O of the Database is Slow (document ID 1275596.1)

Check that the storage message in the host layer did report an error, mainly because the stored cache version reported an error.

Wait for the storage repair to complete. The database is back to normal.

This is the solution to the problem of storage cache that produces a large number of log file which leads to slow database. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.