Monday, March 9, 2009

MobiLink Xtreme Edition

If you're using SQL Anywhere 11's version of the MobiLink synchronization server, you may have good reason to upgrade to 11.0.1.

That's because in addition to adding support for Microsoft SQL Server 2008 at the consolidated database side of things, they have added a SACKLOAD of logging and monitoring options.

First of all, there is the venerable "verbosity" option, mlsrv11 -v.

You can use mlsrv11 -v+ for maximum verbosity, or, wait for it, you can code everything:

mlsrv11 -vcefhimnopqrstu
Here's the full list of mlsrv11 -v codes:
+   Turn on all logging options that increase verbosity.
c Show scripts.
e Show system event scripts.
f Show first-read errors.
h Show the remote schema.
i Show uploaded column values.
m Show the duration of each synch phase.
n Show row-count summaries.
o Show SQL passthrough activity.
p Show progress offsets.
q Show downloaded column values.
r Show uploaded and downloaded column values.
s Show script names.
t Show the translated SQL scripts.
u Show undefined table scripts.
The dreaded -vr option (show all column values) has been supplemented by two new options that will help cut the size of the mlsrv11 -o text file: -vi just shows uploaded column values, and -vq just the downloaded values. In many situations the volume of uploaded data is much lower than downloaded data, and it is the uploaded data that's often more interesting for debugging, so -vi is going to be popular.

But it gets better: you can turn on mlsrv11 verbose logging for individual sync sessions started by specific remote databases. Here's an excerpt from this Help topic:
Log verbosity for targeted MobiLink users and remote IDs

The MobiLink server can be set to use different log verbosity for a targeted MobiLink user or remote ID. The MobiLink server checks the ml_property table every five minutes and looks for verbose settings for a MobiLink user or remote ID. If verbose settings exist, then it uses the new setting to log output messages for the given MobiLink user or remote ID. This enables you to see the details for a specific user or remote ID without the need for high verbosity settings that would negatively impact the server farm, and without requiring a restart of each server in the farm.

To set maximum verbosity for a targeted MobiLink user, for example ml_user1, log into the consolidated database and issue the following SQL command:
call ml_add_property( 'MLS', 'ml_user_log_verbosity', 'ml_user1', '-v+' )
To set maximum verbosity for a targeted remote ID, for example rid_1, log into the consolidated database and issue the following SQL command:
call ml_add_property( 'MLS', 'ml_remote_id_log_versity', 'rid_1', '-v+' )
The mlsrv11 -vm option (show the duration of each synch phase) is also new. Here's the short description from the read-me file (there's more detail in this Help topic):
===============(Release Build  - Engineering Case #532825)===============

A new logging option has been added to the MobiLink server. The -vm command
line option will cause the server to print to the log the duration of each
sync and the duration of each sync phase whenever a sync completes. The sync
phases are the same as those displayed in the MobiLink monitor. Each value
is prefixed with "PHASE: " to aid searching for and printing the values.

Sample output follows:
I. 2008-06-05 14:48:36. <1> PHASE: start_time: 2008-06-05 14:48:36.048
I. 2008-06-05 14:48:36. <1> PHASE: duration: 175
I. 2008-06-05 14:48:36. <1> PHASE: sync_request: 0
I. 2008-06-05 14:48:36. <1> PHASE: receive_upload: 19
I. 2008-06-05 14:48:36. <1> PHASE: get_db_worker: 0
I. 2008-06-05 14:48:36. <1> PHASE: connect: 18
I. 2008-06-05 14:48:36. <1> PHASE: authenticate_user: 51
I. 2008-06-05 14:48:36. <1> PHASE: begin_sync: 69
I. 2008-06-05 14:48:36. <1> PHASE: apply_upload: 0
I. 2008-06-05 14:48:36. <1> PHASE: prepare_for_download: 1
I. 2008-06-05 14:48:36. <1> PHASE: fetch_download: 4
I. 2008-06-05 14:48:36. <1> PHASE: wait_for_download_ack: 0
I. 2008-06-05 14:48:36. <1> PHASE: end_sync: 0
I. 2008-06-05 14:48:36. <1> PHASE: send_download: 10
I. 2008-06-05 14:48:36. <1> PHASE: get_db_worker_for_download_ack: 0
I. 2008-06-05 14:48:36. <1> PHASE: connect_for_download_ack: 0
I. 2008-06-05 14:48:36. <1> PHASE: nonblocking_download_ack: 0
But wait, there's more... the mlsrv11 -ppv 60 option turns on the "print (log) periodic monitor value" feature every 60 seconds. Here's what the read-me says (again, there's more detail in this Help topic):
===============(Release Build  - Engineering Case #536143)===============

A new MobiLink server command line option, -ppv <period>, has been added
which will cause the server to print new, periodic monitoring values every <period>
seconds. The suggested period is 60s.

----- Note: You code it as -ppv 60, not -ppv 60s -----

If the period is set too small, the
log will grow very quickly. These values provide insight into the state of
the server, and are useful for determining the health and performance of
the MobiLink server. For example, one could look at the DB_CONNECTIONS and
LONGEST_DB_WAIT values to look for potential problems with the -w option
or in the synchronization scripts. They also provide an easy way to track
system wide throughput measures such as the number of rows uploaded or downloaded
per second and the number of successful syncs per second. Each row of output
is prefixed with "PERIODIC:" to aid searching for and filtering out the values.

The following table contains a description of the printed values:
Value Description
TCP_CONNECTIONS Number of TCP connections currently opened
PAGES_USED Number of cache pages used
PAGES_LOCKED Number of cache pages loaded into memory
TCP_CONNECTIONS_OPENED Total number of connections ever opened
TCP_CONNECTIONS_CLOSED Total number of connection ever closed
TCP_CONNECTIONS_REJECTED Total number of connection ever rejected
TCP_BYTES_READ Total number of bytes ever read
TCP_BYTES_WRITTEN Total number of bytes ever written
ML_NUM_CONNECTED_CLIENTS Number of connected sync client
PAGES_SWAPPED_OUT Total number of pages ever swapped to disk
PAGES_SWAPPED_IN Total number of pages ever read from disk
PAGES_IN_STREAMSTACK Number of pages held by the network streams
CPU_USAGE Amount of CPU time used by MobiLink server in microseconds
NUM_COMMITS Total number of commits
NUM_ROLLBACKS Total number of rollbacks
NUM_SUCCESS_SYNCS Total number of successful syncs
NUM_FAILED_SYNCS Total number of failed syncs
NUM_ERRORS Total number of errors
NUM_WARNINGS Total number of warnings
DB_CONNECTIONS Number of database connections in use
RAW_TCP_STAGE_LEN Length of the network work queue
STREAM_STAGE_LEN Length of the high level network processing queue
HEARTBEAT_STAGE_LEN Length of the queue for periodic, non-sync work
CMD_PROCESSOR_STAGE_LEN Length of the queue for sync work
NUM_ROWS_DOWNLOADED Total number of rows sent to remotes
NUM_ROWS_UPLOADED Total number of rows received from remotes
FREE_DISK_SPACE Disk space available on the temp disk in bytes
LONGEST_DB_WAIT Longest length of time an active sync has been waiting
for the database
LONGEST_SYNC Age of the oldest sync in ms
NUM_UNSUBMITTED_ERROR_RPTS Number of unsubmitted error reports
MEMORY_USED Bytes of RAM in use. Windows only.
SERVER_IS_PRIMARY 1 if the server is primary; 0 otherwise
Here's a "MobiLink Xtreme" version of the command line:
"%SQLANY11%\bin32\mlsrv11.exe" -c "DSN=cons;UID=dba;PWD=sql" -fr -o mlsrv11_log_cons.txt -os 10M -ppv 60 -vcefhimnopqrstu -zu+ 



Download "Upgrade of 11.0.0 to 11.0.1"
What's new in version 11.0.1
New Editions in SQL Anywhere 11.0.1
SQL Anywhere 11.0.1 FAQs
SQL Anywhere 11.0.1 Editions

No comments: