This topic describes how to use Alibaba Cloud SDK for Java to query the slow query logs of an ApsaraDB for RDS instance.

Prerequisites

Before you begin, ensure that the following requirements are met:
  • You have obtained the ID of the target ApsaraDB for RDS instance in the ApsaraDB for RDS console or by using an API operation. For more information about the API operation, see DescribeDBInstances.
  • You must have an Alibaba Cloud account and an AccessKey pair (AccessKey ID and AccessKey secret) to use Alibaba Cloud SDK for Java. You can create and view your AccessKey pair on the AccessKey Management page in the Alibaba Cloud Management Console.
  • You have installed Alibaba Cloud SDK for Java. For more information about SDK versions, see Alibaba Cloud SDK.
    <project>
        <modelVersion>4.0.0</modelVersion>
        <groupId>java.demo</groupId>
        <artifactId>test</artifactId>
        <version>1.0-SNAPSHOT</version>
        <dependencies>
            <! -- https://mvnrepository.com/artifact/com.aliyun/aliyun-java-sdk-core -->
            <dependency>
                <groupId>com.aliyun</groupId>
                <artifactId>aliyun-java-sdk-core</artifactId>
                <version>4.4.3</version>
            </dependency>
            <! -- https://mvnrepository.com/artifact/com.aliyun/aliyun-java-sdk-rds -->
            <dependency>
                <groupId>com.aliyun</groupId>
                <artifactId>aliyun-java-sdk-rds</artifactId>
                <version>2.3.9</version>
            </dependency>
        </dependencies>
    </project>

Sample code

import com.aliyuncs.DefaultAcsClient;
import com.aliyuncs.IAcsClient;
import com.aliyuncs.exceptions.ClientException;
import com.aliyuncs.exceptions.ServerException;
import com.aliyuncs.profile.DefaultProfile;
import com.aliyuncs.rds.model.v20140815.DescribeSlowLogRecordsRequest;
import com.aliyuncs.rds.model.v20140815.DescribeSlowLogRecordsResponse;
import com.aliyuncs.rds.model.v20140815.DescribeSlowLogsRequest;
import com.aliyuncs.rds.model.v20140815.DescribeSlowLogsResponse;
import com.google.gson.Gson;
import java.util.List;

/**
 * DescribeSlowLogRecords    Queries the slow query log details of the instance.
 * DescribeSlowLogs            Queries the slow query log statistics.
 */
public class TestDescribeSlowLog {

    public static void main(String[] args) {
        IAcsClient client = Initialization();
        List<DescribeSlowLogsResponse.SQLSlowLog> logs = DescribeSlowLogs(client, "YOUR_RDS_DBINSTANCE_ID");
        for (DescribeSlowLogsResponse.SQLSlowLog sqlSlowLog : logs) {
            // The unique identifier of an SQL statement (SQLHASH)
            String sqlHash = sqlSlowLog.getSQLHASH();
            String logRecords = DescribeSlowLogRecords(client, "YOUR_RDS_DBINSTANCE_ID", sqlHash);
            System.out.println(logRecords);
        }
    }

    private static IAcsClient Initialization() {
        // Initialize request parameters.
        DefaultProfile profile = DefaultProfile.getProfile(
                "<your-region-id>", // The ID of the region.
                "<your-access-key-id>", // The AccessKey ID.
                "<your-access-key-secret>"); // The AccessKey secret.
        return new DefaultAcsClient(profile);
    }

    private static List<DescribeSlowLogsResponse.SQLSlowLog> DescribeSlowLogs(IAcsClient client, String id) {
        DescribeSlowLogsRequest request = new DescribeSlowLogsRequest();
        // The ID of the ApsaraDB for RDS instance.
        request.setDBInstanceId(id);
        // The beginning of the time range to query.
        request.setStartTime("2019-06-01Z");
        // The end of the time range to query. The end time must be later than the start time. The time span between the start time and end time must be less than 31 days.
        request.setEndTime("2019-06-20Z");
        // The database name.
        request.setDBName("test11221");
        // The sorting order. This parameter is supported only by SQL Server 2008 R2 instances. Valid values:
        // TotalExecutionCounts: sort by the total execution count in descending order
        // TotalQueryTimes: sort by the total query time in descending order
        // TotalLogicalReads: sort by the total logical reads in descending order
        // TotalPhysicalReads: sort by the total physical reads in descending order
        request.setSortKey("TotalExecutionCounts");
        try {
            DescribeSlowLogsResponse response = client.getAcsResponse(request);
            return response.getItems();
        } catch (ServerException e) {
            e.printStackTrace();
        } catch (ClientException e) {
            System.out.println("ErrCode:" + e.getErrCode());
            System.out.println("ErrMsg:" + e.getErrMsg());
            System.out.println("RequestId:" + e.getRequestId());
        }
        return null;
    }

    /**
     * DescribeSlowLogRecords    Queries the slow query log details of the instance.
     */
    private static String DescribeSlowLogRecords(IAcsClient client, String id, String SQLHASH) {
        DescribeSlowLogRecordsRequest request = new DescribeSlowLogRecordsRequest();
        // The ID of the instance.
        request.setDBInstanceId(id);
        // The beginning of the time range to query.
        request.setStartTime("2019-06-01T16:00Z");
        // The end of the time range to query. The end time must be later than the start time. The time span between the start time and end time must be less than 31 days.
        request.setEndTime("2019-06-20T16:00Z");
        // The database name.
        request.setDBName("test11221");
        // The unique identifier of an SQL statement in slow query log statistics. You can use this parameter to obtain the slow query log details of the SQL statement.
        request.setSQLHASH(SQLHASH);
        // The number of entries to return on each page.
        request.setPageSize(50);
        // The number of the page to return.
        request.setPageNumber(1);
        try {
            DescribeSlowLogRecordsResponse response = client.getAcsResponse(request);
            return new Gson().toJson(response);
        } catch (ServerException e) {
            e.printStackTrace();
        } catch (ClientException e) {
            System.out.println("ErrCode:" + e.getErrCode());
            System.out.println("ErrMsg:" + e.getErrMsg());
            System.out.println("RequestId:" + e.getRequestId());
        }
        return null;
    }
}

Output

This section shows the sample success response:
{
    "DescribeSlowLogRecordsResponse": {
        "Items": {
            "SQLSlowRecord": {
                "ReturnRowCounts": "1", 
                "HostAddress": "192.101.2.11", 
                "SQLText": "update test.zxb set id=0 limit 1", 
                "LockTimes": "12", 
                "ExecutionStartTime": "2011-06-11T15:00:08Z", 
                "ParseRowCounts": "125", 
                "QueryTimes": "123", 
                "DBName": "test"
            }
        }, 
        "PageNumber": "1", 
        "TotalRecordCount": "1", 
        "DBInstanceID": "rm-uf6wjk5xxxxxxx", 
        "RequestId": "542BB8D6-4268-45CC-A557-B03EFD7AB30A", 
        "Engine": "MySQL", 
        "PageRecordCount": "1"
    }
}