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

1. Recently, due to the poor performance of standby disk IO, the performance of Primary has been affected.

The main library is mainly waiting for "log file switch completion". Through ASH dump analysis, it is found that the actual waiting event is "LGWR-LNS wait on channel". This event basically attributes the problem to network performance and standby's IO performance, while the customer's transport mode is "MAXIMUM AVAILABILITY"

Finally, two solutions are proposed.

(1)。 Replace better performance standby Stora

(2)。 Change the transport mode to MAXIMUM performance and use the LGWR ASYNC transport mode

Here, by the way, I would like to emphasize the three transmission modes of standby and the corresponding available transmission modes.

Comparison term

Maximum

Protection

Maximum

Availability

Maximum

Performance

Redo write or transfer process

Lgwr

Lgwr

Lgwr or arch

Network transmission mode

Sync

Sync

Sync or async

IO write successful confirmation

Affirm

Affirm

Affirm or noaffirm

Standby redologs

Need

Need

Lgwr needs it, arch doesn't.

The root of the problem is the poor performance of standby IO, and the use of "MAXIMUM AVAILABILITY" mode of transmission, the use of sync mode, requires a confirmation of the success of disk IO writing, resulting in a drag on the performance of primary.

two。 Here is an introduction to SYNC and ASYNC

Http://docs.oracle.com/cd/B10501_01/server.920/a96653/log_arch_dest_param.htm#77394

SYNC=PARALLEL

SYNC=NOPARALLEL

The SYNC attribute specifies that network I/O is to be performed synchronously for the destination, which means that once the I/O is initiated, the archiving process waits for the I/O to complete before continuing. The SYNC attribute is one requirement for setting up a no-data-loss environment, because it ensures that the redo records were successfully transmitted to the standby site before continuing.

If the log writer process is defined to be the transmitter to multiple standby destinations that use the SYNC attribute, the user has the option of specifying SYNC=PARALLEL or SYNC=NOPARALLEL for each of those destinations.

-If SYNC=NOPARALLEL is used, the log writer process performs the network Imax O to each destination in series. In other words, the log writer process initiates an I/O to the first destination and waits until it completes before initiating the I/O to the next destination. Specifying the SYNC=NOPARALLEL attribute is the same as specifying the ASYNC=0 attribute.

-If SYNC=PARALLEL is used, the network I to multiple destinations can be initiated in parallel O is initiated asynchronously, so that I max O to multiple destinations can be initiated in parallel. However, once the I/O is initiated, the log writer process waits for each I/O operation to complete before continuing. This is, in effect, the same as performing multiple, synchronous I/O operations simultaneously. The use of SYNC=PARALLEL is likely to perform better than SYNC=NOPARALLEL.

Because the PARALLEL and NOPARALLEL qualifiers only make a difference if multiple destinations are involved, Oracle Corporation recommends that all destinations use the same value.

ASYNC [= blocks]

The ASYNC attribute specifies that network I/O is to be performed asynchronously for the destination. Once the I/O is initiated, the log writer continues processing the next request without waiting for the I/O to complete and without checking the completion status of the I/O. Use of the ASYNC attribute allows standby environments to be maintained with little or no performance effect on the primary database. The optional block count determines the size of the SGA network buffer to be used. In general, the slower the network connection, the larger the block count should be. Also, specifying the ASYNC=0 attribute is the same as specifying the SYNC=NOPARALLEL attribute.

Through careful interpretation of the document, we can summarize the following points

Sync, after the IO transfer is initiated, primary can proceed to the next step only after standby makes feedback on the success of IO confirmation. In this way, if the performance of standby IO is poor, it will affect the performance of the main library.

Async, there is no need to confirm the IO. After primary initializes the IO, proceed to the next step. The writing speed of the standby will not affect the primary.

3. After fully understanding these two concepts, go back to analyzing the customer's problems:

The customer has a total of three standby, but the performance of the standby server corresponding to LOG_ARCHIVE_DEST_3 is poor. During the relatively busy period of the system, you can find that the IO utilization of standby is 100% in oswatcher log.

At this point, the problem has been confirmed, that is, the performance gap between standby server and primary is relatively large, and due to the use of LGWR SYNC transmission mode, the IO pressure of standby is relatively high.

And primary must confirm that the transmission of log information is completed in standby before it can proceed to the next step, which greatly affects the performance of primary.

4. In conclusion, it is recommended that the performance of standby should not be too different from that of primary, and that it can at least achieve 70% of the performance of primary, otherwise standby will not be able to take over the business of primary in the case of switch and fail over.

And in the daily log transmission, it will also affect the performance of primary.

Maybe after reading this article, you will have a question? It is said that Maximum availability can be automatically switched to Maximum performance? How does it affect performance?

5. With the question, let's analyze it. Let's first look at the concept:

Maximum availability Thisprotection mode provides the highest level of data protection that is possiblewithout compromising the availability of the primary database. Like maximumprotection mode, a transaction will not commit until the redo needed to recoverthat transaction is written to the local online redo log and to the standbyredo log of at least one transactionally consistent standby database. Unlikemaximum protection mode, the primary database does not shut down if a faultprevents it from writing its redo stream to a remote standby redo log. Instead,the primary database operates in maximum performance mode until the fault iscorrected, and all gaps in redo log files are resolved. When all gaps areresolved, the primary database automatically resumes operating in maximumavailability mode.

This mode ensures that no data loss will occur if the primarydatabase fails, but only if a second fault does not prevent a complete set ofredo data from being sent from the primarydatabase to at least one standbydatabase.

Maximum availability mode-this protection mode provides the highest possible level of data protection without compromising the availability of the primary database. Like the maximum protection mode, the transaction will not commit until the redo required by the recovery transaction is written to the local online redo log and the standby redo log on at least one transaction consistency standby database. Unlike the maximum protection mode, if the failure prevents the primary database from writing the redo stream to the remote standby redo log, the primary database will not be shut down. Instead, the master database runs in maximum performance mode until the failure is eliminated, and interrupts in all redo log files are resolved. When all interrupts are resolved, the primary database automatically continues to run in maximum availability mode.

This mode ensures that if the primary database fails, but only if the second failure does not prevent the complete redo dataset from being sent from the primary database to at least one standby database, data loss does not occur.

In Maximum availability mode, if the connection with slave database is normal, the operation mode is the same as Maximum protection mode, and the transaction is also committed by master and slave database at the same time. If the standby database loses contact with the master database, the master library will automatically switch to Maximum performance mode to run, ensuring the maximum availability of the master library.

Did you find that "if the backup database loses contact with the main database", "losing contact" is very important. In the case of this article, it is precisely the normal contact, that is, the performance of IO is poor, not that it does not provide services at all.

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.