SQLAnywhere High Availability/Mirror: Mode=Page risk/reward

[former_SQLA_member1694902]

Hi all, I have a db mirroring as mode=sync and we've been having a bit of trouble with large UPDATE statements locking out all other connections until the statements and connections has finished. Does anyone think it will be worth the risk of moving to mode=page to improve performance or this there something else I can do?

SQL Anywhere 11.0.1.2837

[ian_mchardy · Accepted solution]

Choosing async or page mode is a big change from sync mode. In other modes than sync, you can loose committed transactions if your primary goes down.

Is your mirror computer as powerful as your primary computer? If not, you may want to upgrade your mirror computer. But even if they are equally powerful machines this problem can still occur.

The undocumented lagtime option may be causing your problem. This option defaults to 60 seconds. If the mirror is more than this number of seconds behind the primary in apply transactions, then the mirror will block the primary to allow it to catch up. This behavior can cause other connections that are attempting to write to the transaction log on the primary to appear to hang or be locked out.

You may be able to workaround this behavior by setting the lagtime option to a higher value, or by setting it to 0 to disable it completely. You can use "-xp partner=(...);auth=abc123;lagtime=0" to disable the lagtime completely.

Note if you use this option, it can cause failover to take much longer. If the mirror is not applying transactions as fast as the primary, then if the primary fails, the mirror needs to apply all transactions before it can take over as the new primary.

[Former Member]

Large single transactions can be problematic for many different features. In this regard database mirroring is just one example of such a technology. Going to an Asynchronous mode may be able to delay the effect a little but will not completely eliminate it. But that would need testing in your test environment to verify that the trade-offs are acceptable to your usage.

You should consider other approaches as well. Scheduling your large transaction runs at the quieter (ie. more off-hours) periods so the impact will be less is one option. Another would be to break up the large transaction into multiple smaller units of work; that way spreading the smaller impacts across a number of steps so that other transactions can have an opportunity to complete 'in between' those and not have to wait as long even when they do block.

[former_SQLA_member1694902]

This is great, guys. Thanks very much. I really don't want to be a in a position to lose transactions but I also can't really go on having large updates lock out all the users. The mirror is a virtual machine (not my choice) and might be under a more strain recently. A major factor could also be that the server applications are using DDE to talk to each other which can lock up and delay the large updates from happening until someone closes some of the stalled processes.

Thanks again, Sam