|
Hello guys, could somebody help me with this: We have a read-only scale-out database mirroring system (root node and only one copy node) on Windows, separate VM's. It's working fine but when a very long transaction arrives, the mirror server starts to run behind the primary server in applying the transaction log (aprox 200MB size) and it looks to take forever. The mirror message log shown this kind of messages: I. 05/15 09:05:47. Parallel recovery enabled I. 05/15 09:05:47. Parallel recovery disabled to accomodate a very long transaction I. 05/15 09:05:48. Parallel recovery enabled I. 05/15 09:05:49. Parallel recovery disabled to accomodate a very long transaction I. 05/15 09:05:49. Parallel recovery enabled I. 05/15 09:05:49. Parallel recovery disabled to accomodate a very long transaction I. 05/15 09:05:49. Parallel recovery enabled I. 05/15 09:05:49. Parallel recovery disabled to accomodate a very long transaction Any idea why is disabling the parallel recovery? We are monitoring the latency with this SQL: SELECT (log_written - log_applied) AS VARIANCE FROM sa_mirror_server_status() where server_name like '%Mirror'; It's applying the transactions but incredibly slow. The mirror server has 8 logical CPU's but it's using only 1 CPU 100% (the license allows to use all CPU's). Why is not using more than 1 CPU? The number of workers in the mirror database is: SELECT PROPERTY ( 'CurrentMirrorBackgroundWorkers' ) = 3 SELECT PROPERTY ( 'MaxMirrorBackgroundWorkers' ) = 4 We are running SQL Anywhere 17 (17.0.9.4857). I'm trying to see how to speed up the application of transactions in the mirror. Appreciate any clue. Thanks for your help. |
|
Parallel recovery creates a dependency graph between operations across multiple transactions. It then uses the graph to figure out a way to replay operations in these transactions such that it does not introduce incorrect ordering of updates. When replaying a large transaction, the dependency graph can quickly grow and consume all server memory. For this reason, parallel recovery is trying to be conservative by temporarily switching to sequential recovery only for the duration of replaying the large transaction. Parallel recovery is also disabled when we encounter DDL statements. Based on the snippet of log that you posted, it seems these large transactions are appearing frequently in your transaction log. If you are able to make changes to the application, then one thing you can try is breaking up these large transactions into smaller ones. For the purpose of parallel recovery, the size of a transaction is measured by the number of rows it modifies. Is there any way to disable background mirror processing? ...it is not unheard of for brand-new features to have flaws.
(16 May '19, 07:32)
Breck Carter
Replies hidden
Not sure if I get your question right. Database mirroring hardly is a brand-new feature. It can be disabled by starting the database server without the corresponding cmd options. But that of course means that it has to be re-initialized before operating in mirror mode again. However, VL xacts are a pain for every store-and-forward- technology with the spec to maintain transactional consistency.
(16 May '19, 12:13)
Volker DB-TecKy
2
According to "What's new in SQL Anywhere Server 17.0 - Performance enhancements changes to parallel recovery of the mirror database are indeed new in SQL Anywhere 17: "The mirror server can now more quickly catch up to changes happening on the primary server because log operations are applied in parallel instead of sequentially." The OP's question seems to show that the server is rapidly cycling (thrashing?) between "Parallel recovery enabled" and "Parallel recovery disabled", and my concern is that the overhead of deciding and changing behavior might be the bottleneck, not the sequential processing. If parallel recovery of the mirror database is anything like Dynamic Tuning of the Multiprogramming Level was in the recent past, turning off parallel recovery might be a worthwhile experiment. FWIW, the CurrentMirrorBackgroundWorkers and MaxMirrorBackgroundWorkers properties have existed since SQL Anywhere 12, but not mentioned until SQL Anywhere 17... ...the word "mentioned" is used, since the material doesn't come close to "documentation" :)
(16 May '19, 13:39)
Breck Carter
2
There is no thrashing when switching between parallel and sequential recovery. The thread reading operations from the transaction log (reader) is a different thread from the ones that apply the changes to the database (writers). All that happens when we switch to sequential mode is that the reader thread waits for the writer threads to catch up. In our experiments, parallel recovery always had equal or better performance to sequential recovery. Depending on the workload, turning off Parallel recovery might be the wrong choice as it will make all transactions sequential and will not allow us to use the many cores available on the system when there is an opportunity to do so. If large transactions is a common pattern for this system then parallel recovery will not gain you any benefit. It also shouldn't be an overhead.
(16 May '19, 16:09)
Mohammed Abo...
Thank you so much for your comments, I'll continue to monitor and investigate to see what happens on the mirror side.
(20 May '19, 09:38)
lferreira
|
