How to capture slow query logs in wso2 stream processor-based products.

2 min readJun 25, 2020

We may need to capture slow query logs to debug various issues in wso2 products.
In this documentation, we have explained the steps to capture the slow query logs of carbon-5 based wso2 products such as stream processor, APIM-Analytics-2.6.0 and above, IS-Analytics 5.7 and above, etc

By using this JDBC SPY logs, we can analyze the DB related queries, timing, resultsets, and connections.

You can enable the JDBC Spy logs for carbon-5 based WSO2 Servers as below.

Download the log4jdbc driver from [1].
Then convert the downloaded log4jdbc driver into OSGI bundle by using the jartobundle.sh script provided in the <Analytics_Home>/bin directory.

Sample command

sh <Analytics_Home>/bin/jartobundle.sh log4jdbc4–1.2.jar <Analytics_Home>/lib/

Please refer the documentation [2] for the detailed information of adding third party Non-OSGI libraries.

Add the converted driver into the <Analytics_Home>/lib/ directory. Make sure you have added the corresponding JDBC driver to your database server in the same directory.
Modify the data source configuration of the related database as below in the deployment.yaml file ( Eg:- IS_Analytics_DB, APIM_Analytics_DB, etc). For an example, we have provided the configuration related to the APIM_ANALYTICS_DB here.

I. Change JDBC URL like below by adding :log4jdbc part.

jdbcUrl: 'jdbc:log4jdbc:mysql://localhost:3306/APIM_ANALYTICS_DB?useSSL=false'

II. Set JDBC driver class like this.

driverClassName: net.sf.log4jdbc.DriverSpy

Add the following properties under the <Loggers> tag in the log4j2.xml file which exists in the <Analytics_Home>/conf/<Profile> directory.

  <Loggers>
        <Logger name="AUDIT_LOG" level="info" additivity="false">
            <AppenderRef ref="AUDIT_LOGFILE"/>
        </Logger>
        <Logger name="com.zaxxer.hikari" level="error"/>
        <Logger name="jdbc.sqlonly" level="INFO"/>
        <Logger name="jdbc.sqltiming" level="INFO"/>
        <Logger name="jdbc.audit" level="OFF"/>
        <Logger name="jdbc.resultset" level="error"/>
        <Logger name="jdbc.connection" level="error"/>        <Root level="info">
            <AppenderRef ref="CARBON_CONSOLE"/>
            <AppenderRef ref="CARBON_LOGFILE"/>
        </Root>
    </Loggers>

Once you restart the server, you could see the JDBC Spy logs in the carbon.log file as below.

[2020-06-25 22:37:17,741]  INFO {jdbc.sqltiming} - SELECT * FROM SessionInformationTable WHERE (((SessionInformationTable.duration > 900000 )AND 
(SessionInformationTable.rememberMeFlag = 0 ))AND (SessionInformationTable.isActive = 1 )) 
 {executed in 60 msec}
[2020-06-25 22:37:17,744]  INFO {jdbc.sqlonly} - SELECT * FROM SessionInformationTable WHERE (((SessionInformationTable.duration > 900000 )AND 
(SessionInformationTable.rememberMeFlag = 0 ))AND (SessionInformationTable.isActive = 1 ))

[1] https://code.google.com/archive/p/log4jdbc/downloads
[2] https://docs.wso2.com/display/SP430/Adding+Third+Party+Non+OSGi+Libraries

How to capture slow query logs in wso2 stream processor-based products.

Sample command

Written by Rizmeer Shafeek