|
The original question was this: "Can I improve mlsrv16.exe performance by
This is a high-volume application with one single SA 16 remote database and one single ASE 15.7 consolidated database. Second-hand reports claim client applications on the consolidated database are frequently "deadlocked"... that's the word being used, although I would not be suprised if the problem was blocking (which is both violent and persistent) rather than deadlocking which may also be violent but only for a moment :) Here's an excerpt from mlsrv16 -o log; as you can see there has already been some "adjusting the -w* options": I. 2016-06-12 15:04:04. SQL Anywhere MobiLink Server Version 16.0.0.2095
...
I. 2016-06-12 15:04:04. Running Windows 2008R2 Build 7601 Service Pack 1 on X86_64
I. 2016-06-12 15:04:04. Server built for X86_64 processor architecture
I. 2016-06-12 15:04:04. <main> Option 1: -c
I. 2016-06-12 15:04:04. <main> Option 2: DSN=xxx
I. 2016-06-12 15:04:04. <main> Option 3: -o
I. 2016-06-12 15:04:04. <main> Option 4: xxx
I. 2016-06-12 15:04:04. <main> Option 5: -os
I. 2016-06-12 15:04:04. <main> Option 6: 10M
I. 2016-06-12 15:04:04. <main> Option 7: -vcefhkmnopstuU
I. 2016-06-12 15:04:04. <main> Option 8: -zu+
I. 2016-06-12 15:04:04. <main> Option 9: -zs
I. 2016-06-12 15:04:04. <main> Option 10: xxx
I. 2016-06-12 15:04:04. <main> Option 11: -w
I. 2016-06-12 15:04:04. <main> Option 12: 10
I. 2016-06-12 15:04:04. <main> Option 13: -wu
I. 2016-06-12 15:04:04. <main> Option 14: 10
I. 2016-06-12 15:04:04. <main> Option 15: -x
I. 2016-06-12 15:04:04. <main> Option 16: tcpip{host=xxx;port=xxx}
I. 2016-06-12 15:04:04. <main> Verbose logging: show script names when invoked
I. 2016-06-12 15:04:04. <main> Verbose logging: show script contents when invoked
I. 2016-06-12 15:04:04. <main> Verbose logging: show schema for each table
I. 2016-06-12 15:04:04. <main> Verbose logging: show an error when the first read of a synchronization fails
I. 2016-06-12 15:04:04. <main> Verbose logging: show translated SQL for prepared statements
I. 2016-06-12 15:04:04. <main> Verbose logging: show rowcount values
I. 2016-06-12 15:04:04. <main> Cache size: 52428800 bytes
I. 2016-06-12 15:04:04. <main> Download cache directory size: 10485760 bytes
I. 2016-06-12 15:04:04. <main> Local file for remote synchronization logs: 'mlsrv.mle'
I. 2016-06-12 15:04:04. <main> Starting cache sizes: min of 52428800 bytes; max of 6009691340 bytes; initial size of 52142080 bytes
I. 2016-06-12 15:04:04. <main> Individual database connections will be closed after synchronization errors
I. 2016-06-12 15:04:04. <main> Maximum number of BLOB bytes to compare: 4294967295
I. 2016-06-12 15:04:04. <main> Maximum number of database connections: 10
I. 2016-06-12 15:04:04. <main> Maximum number of deadlock retries: 10
I. 2016-06-12 15:04:04. <main> Timeout for inactive database connections: 60 minutes
I. 2016-06-12 15:04:04. <main> Maximum delay between retries after deadlock: 30 seconds
I. 2016-06-12 15:04:04. <main> Rowset size: 10
I. 2016-06-12 15:04:04. <main> Number of database worker threads: 10
I. 2016-06-12 15:04:04. <main> Maximum number of threads uploading concurrently: 10
W. 2016-06-12 15:04:04. <main> [10064] Unknown users will be added automatically (when there is no authenticate_user script)
I. 2016-06-12 15:04:04. <main> ODBC DBMS Name: SQL Server
I. 2016-06-12 15:04:04. <main> ODBC DBMS Version: 15.7.0
I. 2016-06-12 15:04:04. <main> ODBC DBMS Driver Version: 15.7.0.104
I. 2016-06-12 15:04:04. <main> ODBC Version supported by the driver: 3.51
I. 2016-06-12 15:04:04. <main> Collation sequence of the consolidated database is ' '
|
|
There is really not enough to go on here. Clearly you will not be focussing on any of those earlier swtiches for a situation where this is just 1 remote. Can I take it your (possibly earlier) performance problem has resisted earlier attempts to 'tune for the masses' and reducing the problem down to a single sync still shows a problem? If blocking is a major concern then understanding your transaction size and complexity is going to be factors you'll want to understand the impacts of and probably reduce. Because the remote sends up all changes which will get applied as one as 1 bigger transaction this can be a concern in some settings. (The -tu switch aside.) More frequent synchronizations may help. So might redesigning the (remote side) application to commit more frequently in addition to that. But some (consolidated side) applications are just too exposed to contention issues even before MobiLink enters the picture and, so, your MobiLink instance may just be 'falling in with a bad crowd' there. Do keep an eye open for basic things like page-level locking, lock escalation and exceeding the number of configured locks on the ASE side. MobiLink may be holding a large number of locks and may not function as well in a touchy, locking sensitive environment without a deeper analysis of the latter. If deadlocking is the issue then an altered table order may be of some help; especially if there are a lot of rollback/retry cycles involved. As always, deadlocking needs to be understood from a schema, lock acquisition and usage pattern stand point, and may require a larger system 'rethink' to address. MobiLink's lock acquisition order may interact with a system that is (possibly implicitly) dependant upon all application acquiring locks in a specific order. {given that switching to 'prescribed lock order' is a common workaround to some of the tougher deadlock issues you may need to parot the same order} But beyound the above guessuggestions, the only other thing I could think of is the size of the upload/downloads. Smaller (ie. more frequent) syncs can be faster and more robust. Anywhere close to what you are looking for? Oh, yes, it is better than "close" to what I was looking for, it is exactly what I wanted :)
(20 Jun '16, 15:32)
Breck Carter
Replies hidden
1
Breck, if you improve what you wanted to improve, could you please post the solution/answer here? Please :)
(20 Jun '16, 15:55)
Vlad
Working remotely is sometimes like the movie The Martian. I'm in that movie, not as the Matt Damon character, but one of the nameless engineers back on Earth trying to figure out what to do :) So sure, if I hear about improvements, I'll post them here. One thing I did not report is that dbmlsync -tu (transactional upload) was used for a while when it should not have been, and it caused great havoc... a story for another day :)
(21 Jun '16, 05:53)
Breck Carter
|
