Building Database Applications with Remote Support in Mind
Identification and Diagnosis of Problems
Problem Diagnosis in the On-Site Database
Many times, the support call comes in, and the only information is "the system is slow"... It's up to you to diagnose exactly what and where the problem is. This section presents some quick tips and non-invasive queries that can be used against a production database that provide excellent visibility into the inner workings of the database engine, helping pinpoint common causes associated with degrading performance.
Setting the "Remember_Last_Statement" engine property
The .zl engine switch enables this feature, which "remembers" the last statement executed by any active connection. Turn this feature on, and leave it on. It costs very little in terms of execution overhead, and lets you see the exact SQL statements that each connection is using. This option can also be enabled and disabled dynamically, with the following command:
call sa_server_option( 'remember_last_statement', 'ON' );
Note: use of this feature can add significant overhead to the database engine, and should be used judiciously. If the output is redirected to a file, the file can grow rather rapidly.
This feature is enabled with the following commands:
call sa_server_option( 'request_level_logging', 'SQL' ); call sa_server_option( 'request_level_log_file', "<output file>' );
After the desired results are captured to the output file, turn request_level_logging off with:
call sa_server_option( 'request_level_logging', 'none' ); call sa_server_option( 'request_level_log_file', '' );
Finally, the output file that is generated by request_level_logging can be analyzed with two system stored procedures:
call sa_get_request_profile( 'request_level_log_file' [,connection-id ] );
sa_get_request_profile() processes the generated log file, and populates the temporary table SATMP_REQUEST_PROFILE. If an optional connection_id argument is used, the results will be filtered down to that specific connection_id. Once the satmp_request_profile table is populated, it can be queried to identify potentially poor-performing queries.
call sa_get_request_times( 'request_level_log_file' [,connection-id ] );
sa_get_request_times() is a similar procedure to sa_get_request_profile(), but it populates the satmp_request_times table.
Detecting Blocked Connections
As you've seen, SQL Anywhere has the ability to perform SELECT operations across the results of stored procedures. With this capability as a foundation, it's possible to write queries that can detect all kinds of anomalous conditions.
For example, here's a query that finds any connections that are blocked on another connection, and the statements that both connections are executing.
Note: This query is much more effective when the "remember_last_statement" engine property has been enabled.
SELECT blockedActivity.number blockedConn, blockedActivity.userID blockedUser, datediff( mi, blockedActivity.lastReqTime, now()) as blockedInMinutes, blockedActivity.lastStatement blockedStatement, blockedInfo.nodeAddr blockedIPAddr, blockerActivity.number blockingConn, blockerActivity.userID blockingUser, datediff( mi, blockerActivity.lastReqTime, now()) blockingSince, blockerActivity.lastStatement blockingStatement, blockerInfo.nodeAddr blockingIPAddr FROM sa_conn_activity() blockedActivity JOIN sa_conn_info() blockedInfo ON blockedInfo.number = blockedActivity.number JOIN sa_db_info() dbInfo ON blockedActivity.DBNumber = dbInfo.Number JOIN sa_conn_activity() blockerActivity ON blockedInfo.blockedOn = blockerActivity.number JOIN sa_conn_info() blockerInfo ON blockerActivity.number = blockerInfo.number ORDER BY blockedInMinutes DESC ;
This query uses the sa_conn_activity() and sa_conn_info() procedures to report the current elapsed time of all active queries being processed by the SQL Anywhere engine.
SELECT Activity.number, Activity.userID, datediff( ms, Activity.lastReqTime, now()) / 1000.0 as duration, Activity.lastStatement, Info.nodeAddr IPAddr, Info.reqType requestType FROM sa_conn_activity() Activity JOIN sa_conn_info() Info ON Info.number = Activity.number WHERE requestType not in ('COMMIT', 'CURSOR_CLOSE') ORDER BY Activity.lastReqTime;
This particular query was especially helpful recently at one of our customer sites. The CHUM Television group hosts our application client software on a cluster of CitrixTM servers, and the users were complaining of degrading performance. The diagnostic query above identified the long-running queries, but we noticed that the majority of these had a requestType of 'CURSOR_FETCH'. This indicated that the database engine had completed the execution of the queries and was trying to send the result sets back to their requestors, which were hosted client sessions on the Citrix servers. An examination of the Citrix servers revealed that they were significantly underpowered, and were trying to service three times more client sessions than originally specified. By adding more Citrix servers into the cluster, they were able to relieve the client-side bottlenecks, and our application performance returned to normal.
Retrieving a Local Copy of the Remote Database
There are some diagnostic tests that simply cannot be executed against a production database with active users. One example is the sa_table_page_usage() stored procedure, which requires exclusive access to the database during its execution, and can take an hour or more to complete. Consequently, there are times when the best recourse is to bring a backup of the production database into your labs, mount it on a local server, and execute the diagnostic tests on that machine. This allows you to stop and restart the database at will, step through procedures, triggers and events in Sybase Central's integrated debugger, and generally execute any test that might require exclusive access to the database.
SQL Anywhere databases can be transferred and mounted on a different server with no prior setup required because they are completely binary-compatible across platforms. In addition, SQL Anywhere .DB files are highly compressible, and utilities like WinZipTM, WinRarTM, can be used to shrink the .db and .log files down prior to transmission. If the zipped file will exceed 4 Gb in size, tools such as WinRar can partition the archive automatically into multiple 700 Mb slices, which are small enough to be burned onto a CD.
Remember that a SQL Anywhere database may consist of one or more physical .DB files (if multiple DBSPACES are used), and one .LOG file. If "image" backups are used, it will be necessary to transfer all of these files before the database can be mounted on a local server.
The alternative is to use the Archive backup method, which creates a single archive file containing all of the necessary database components. Once this file has been zipped and transferred locally, the individual files can be extracted with a single RESTORE DATABASE operation.
Supporting remote embedded applications can a difficult and time consuming endeavor. The hard work and effort that went into developing the application suite can be all for naught with one lost production database.
So, develop your plan to remotely support customers while still in the development phase. In particular, plan to:
- Prepare the Site: The first step is careful planning and adequate preparation of the remote site.
- Automate routine tasks: Build in functionality so routine tasks are completed even when no IT administrator works at the client site.
- Diagnose the problem: Identify key tools and techniques that could be used to diagnose problems in a production database.
- Retrieve a local copy of the database: Prepare for those instances where a database may need to brought into the lab to properly diagnose and repair the problem.
The costs associated with software support tasks are not often directly visible, but certainly have a direct impact on the bottom line for companies such as VCI. With now well over one hundred remote customer installations of STARSII+, the four strategies listed above have been critical for us in minimizing these hidden costs, and maintaining a low TCO for our customers.
About the Author
Paul Horan is a senior consultant with Washington, D.C.-based Cynergy Systems. Prior to joining Cynergy, Paul was the Senior Architect at Video Communications, Inc., of Springfield, MA. He has over 15 years of experiencedeveloping client/server and distributed applications. Paul's areas of expertise include relational database design and implementation, and he has been working with the Watcom/SQL Anywhere database platform since 1993. Paul serves on the iAnywhere Customer Advisory Panel, has been a member of TeamSybase since 2000, and is a frequent speaker at the annual Sybase TechWave User Conference. Currently residing in Buffalo, NY, Paul can be reached via e-mail at email@example.com.
Page 3 of 3