October 2011 Edition
Using Request Logging To Monitor Application Concurrency
Request logging has been available in SQL Anywhere since version 8.0. It has been enhanced over the years with more functionality which was designed to help diagnose database performance problems. Although the information gathered with 'request logging' doesn't log as much information as 'database tracing' (introduced in version 10), it provides a quick and simple way to gather enough information for concurrency analysis without service interruption. This is particularly important for systems running 24/7 when we don't want to affect the end users and wish to minimize the performance impact on the system.
With request logging, a database administrator can identify all SQL requests that are being sent to the database, their duration with query plan information, as well as blocking information which is required to help diagnose performance issues.
It is particularly important to monitor for blocking patterns after application upgrades. With changes in the executed SQL code or changes to the database schema there is always a chance that there is an increase in contention for database resources. This is when request logging may become helpful, while minimizing the impact on the system’s performance.
Below is an example that demonstrates how to identify blocking requests, using the request logging feature with SQL Anywhere 12.0.1.
Turn request logging on
or start the server with -zr <options> -zo <request.log> switches,
<request.log> - file where request logging information is stored
PLAN - enables logging of execution plans (short form).
HOSTVARS - enables logging of host variable values.
PROCEDURES - enables logging of statements executed from within procedures.
TRIGGERS - enables logging of statements executed from within triggers.
OTHER - enables logging of additional request types not included by SQL, such as FETCH and PREFETCH. .
BLOCKS - enables logging of details showing when a connection is blocked and unblocked
REPLACE - at the start of logging, the existing request log is replaced.
ALL logs all supported information.
NO or NONE turns off logging to the request log.
e.g. outputs logging information to c:\req.txt, with sql+plan+blocks+replace’ options:
Note: Since the request-level log records all SQL statements that were executed, be aware that <request.log> file may contain sensitive data (passwords etc.)
Test your Application
In our example we are going to simulate blocking with the following using SQL Anywhere 12 installed:
Open cmd Window and start the server:
Create a test table and test procedures:
From the same dbisql Windows, start request logging by executing:
Now, open 2 additional dbisql windows, and then from the first window run:
then, without waiting for completion , from the second window run:
Let both of them to finish and close both of them.
Go back to the first dbisql Window and stop request logging by executing:
Analyzing blocked statements
Using the same dbisql Window, load the request-level log data into temporary tables using a system stored procedure:
To see which connections were blocked and for how long, execute:
Then, see which actual SQL requests where blocked with e.g:
And, finally for a particular blocked statement (req_id: 130) see which statements were executing at the same time to cause the blocking. Looking at 4000 ms timeframe you can see that as soon as conn_id=4 executed ‘commit work’ conn_id=2 started processing again.