You can call the DescribeSlowLogs operation to query the summary of the slow query logs of an ApsaraDB RDS instance.

Before you call this operation, make sure that the instance runs one of the following database engine versions:

  • All MySQL versions except MySQL 5.7 that is used with RDS Basic edition
  • SQL Server 2008 R2
  • MariaDB 10.3
Note Slow query logs are not collected in real time and may show a latency of 6 hours to 8 hours.

Debugging

OpenAPI Explorer automatically calculates the signature value. For your convenience, we recommend that you call this operation in OpenAPI Explorer. OpenAPI Explorer dynamically generates the sample code of the operation for different SDKs.

Request parameters

Parameter Type Required Example Description
Action String Yes DescribeSlowLogs

The operation that you want to perform. Set the value to DescribeSlowLogs.

DBInstanceId String Yes rm-uf6wjk5xxxxxxx

The ID of the instance.

EndTime String Yes 2011-05-30Z

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 the end time cannot exceed 31 days. Specify the time in the yyyy-MM-ddZ format. The time must be in UTC.

StartTime String Yes 2011-05-01Z

The beginning of the time range to query. Specify the time in the yyyy-MM-ddZ format. The time must be in UTC.

DBName String No RDS_MySQL

The name of the database that you want to query.

SortKey String No TotalExecutionCounts

The dimension based on which ApsaraDB RDS sorts the returned entries. Valid values:

  • TotalExecutionCounts: ApsaraDB RDS sorts the returned entries based on the total number of SQL statements that are executed.
  • TotalQueryTimes: ApsaraDB RDS sorts the returned entries based on the total execution duration.
  • TotalLogicalReads: ApsaraDB RDS sorts the returned entries based on the total number of logical reads.
  • TotalPhysicalReads: ApsaraDB RDS sorts the returned entries based on the total number of physical reads.
Note This parameter is supported only for instances that run SQL Server 2008 R2.
PageSize Integer No 30

The number of entries to return on each page. Valid values: 30 to 100. Default value: 30.

PageNumber Integer No 1

The number of the page to return. Valid values: any non-zero positive integer.

Default value: 1.

Response parameters

Parameter Type Example Description
Engine String MySQL

The database engine of the instance.

StartTime String 2011-05-30Z

The start date of the time range that was queried.

EndTime String 2011-05-30Z

The end date of the time range that was queried.

TotalRecordCount Integer 5

The total number of entries that are returned.

PageNumber Integer 1

The page number of the returned page.

PageRecordCount Integer 10

The number of SQL statements that are returned on the current page.

Items Array of SQLSlowLog

An array that consists of the information about each slow query.

SQLSlowLog
DBName String RDS_MySQL

The name of the database that was queried.

SQLText String select id,name from tb_table

The SQL statement that was executed in the query.

SQLServerTotalExecutionCounts Long 1

The total number of SQL statements that were executed in the query. This parameter is returned only for instances that run SQL Server.

MySQLTotalExecutionCounts Long 1

The total number of SQL statements that were executed in the query. This parameter is returned only for instances that run MySQL.

SQLServerTotalExecutionTimes Long 1000

The total execution duration of all SQL statements in the query. This parameter is returned only for instances that run SQL Server. Unit: milliseconds.

MySQLTotalExecutionTimes Long 1

The total execution duration of all SQL statements in the query. This parameter is returned only for instances that run MySQL. Unit: seconds.

MaxExecutionTime Long 60

The longest execution duration of a specific SQL statement in the query. Unit: seconds.

ReportTime String 2011-05-30Z

The date on which the data report was generated.

TotalLockTimes Long 0

The total lock duration that was caused by all SQL statements in the query. Unit: seconds.

MaxLockTime Long 0

The longest lock duration that was caused by a specific SQL statement in the query. Unit: seconds.

ParseTotalRowCounts Long 1

The total number of rows that were parsed by all SQL statements in the query.

ParseMaxRowCount Long 1

The largest number of rows that were parsed by a specific SQL statement in the query.

ReturnTotalRowCounts Long 1

The total number of rows that were returned by all SQL statements in the query.

ReturnMaxRowCount Long 1

The largest number of rows that were returned by a specific SQL statement in the query.

CreateTime String 2011-05-30Z

The date when the data was generated.

AvgExecutionTime Long 1

The average execution duration per SQL statement in the query. Unit: seconds.

Note This parameter is returned only for instances that run SQL Server.
AvgIOWriteCounts Long 0

The average number of I/O writes per SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
AvgLastRowsAffectedCounts Long 0

The average number of rows that are affected by the last SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
AvgLogicalReadCounts Long 0

The average number of logical reads per SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
AvgPhysicalReadCounts Long 0

The average number of physical reads per SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
AvgRowsAffectedCounts Long 0

The average number of rows that were affected per SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MaxIOWriteCounts Long 0

The largest number of I/O writes that were performed by a specific SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MaxLastRowsAffectedCounts Long 0

The largest number of rows that were affected by the last SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MaxLogicalReadCounts Long 0

The largest number of logical reads that were performed by a specific SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MaxPhysicalReadCounts Long 0

The largest number of physical reads that were performed by a specific SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MaxRowsAffectedCounts Long 0

The largest number of rows that were affected by a specific SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MinIOWriteCounts Long 0

The smallest number of I/O writes that were performed by a specific SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MinLastRowsAffectedCounts Long 0

The smallest number of rows that were affected by the last SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MinLogicalReadCounts Long 0

The smallest number of logical reads that were performed by a specific SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MinPhysicalReadCounts Long 0

The smallest number of physical reads that were performed by a specific SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
MinRowsAffectedCounts Long 0

The smallest number of rows that were affected by a specific SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
SQLHASH String U2FsdGVkxxxx

The unique ID of the SQL statement. The ID is used to obtain the slow query logs of the SQL statement.

SQLIdStr String 521584

The ID of the SQL statement in the statistical template of slow query logs. This parameter is replaced by the SQLHASH parameter.

SQLServerAvgCpuTime Long 0

The average amount of CPU time per SQL statement in the query. Unit: seconds.

Note This parameter is returned only for instances that run SQL Server.
SQLServerAvgExecutionTime Long 0

The average execution duration per SQL statement in the query. Unit: seconds.

Note This parameter is returned only for instances that run SQL Server.
SQLServerMaxCpuTime Long 0

The largest amount of CPU time that was used by a specific SQL statement in the query. Unit: seconds.

Note This parameter is returned only for instances that run SQL Server.
SQLServerMinCpuTime Long 0

The smallest amount of CPU time that was used by a specific SQL statement. Unit: seconds.

Note This parameter is returned only for instances that run SQL Server.
SQLServerMinExecutionTime Long 0

The smallest execution duration of a specific SQL statement in the query. Unit: seconds.

Note This parameter is returned only for instances that run SQL Server.
SQLServerTotalCpuTime Long 0

The total amount of CPU time that was used by all SQL statements in the query. Unit: seconds.

Note This parameter is returned only for instances that run SQL Server.
SlowLogId Long 26584213

The ID of the slow query log summary.

TotalIOWriteCounts Long 0

The total number of I/O writes that were performed by all SQL statements in the query.

Note This parameter is returned only for instances that run SQL Server.
TotalLastRowsAffectedCounts Long 0

The total number of rows that were affected by the last SQL statement in the query.

Note This parameter is returned only for instances that run SQL Server.
TotalLogicalReadCounts Long 1

The total number of logical reads that were performed by all SQL statements in the query.

TotalPhysicalReadCounts Long 1

The total number of physical reads that were performed by all SQL statements in the query .

TotalPhysicalReadCounts Long 1

The total number of physical reads that were performed by all SQL statements in the query.

TotalRowsAffectedCounts Long 0

The total number of rows that were affected by all SQL statements in the query.

DBInstanceId String rm-uf6wjk5xxxxxxx

The ID of the instance.

RequestId String 2553A660-E4EB-4AF4-A402-8AFF70A49143

The ID of the request.

Examples

Sample requests

http(s)://rds.aliyuncs.com/?Action=DescribeSlowLogs
&DBInstanceId=rm-uf6wjk5xxxxxxx
&StartTime=2011-05-01Z
&EndTime=2011-05-30Z
&<Common request parameters>

Sample success responses

XML format

<DescribeSlowLogsResponse> 
    <RequestId>A5409D02-D661-4BF3-8F3D-0A814D0574E7</RequestId>
    <DBInstanceID>rm-uf6wjk5xxxxxxx</DBInstanceID> 
    <Engine>SQLServer</Engine>
    <StartTime>2011-06-11Z</StartTime> 
    <EndTime>2011-12-11Z</EndTime> 
    <TotalRecordCount>1</TotalRecordCount>
    <PageNumber>1</PageNumber>
    <PageRecordCount>1</PageRecordCount>
    <Items>
        <SQLSlowLog>
          <SQLText>update test.zxb set id=0 limit 1</SQLText>
          <SQLServerTotalExecutionCounts>178</SQLServerTotalExecutionCounts>
          <SQLServerTotalExecutionTimes>189</SQLServerTotalExecutionTimes>
          <TotalLogicalReadcounts>89</TotalLogicalReadcounts>
          <TotalPhysicalReadcounts>90</TotalPhysicalReadcounts>
          <ReportTime>2013-11-12Z</ReportTime>
       </SQLSlowLog>
    </Items>
</DescribeSlowLogsResponse>

JSON format

{
    "DescribeSlowLogs": {
        "RequestId": "A5409D02-D661-4BF3-8F3D-0A814D0574E7",
        "DBInstanceID": "rm-uf6wjk5xxxxxxx",
        "Engine": "SQLServer",
        "StartTime": "2011-06-11Z",
        "EndTime": "2011-12-11Z",
        "TotalRecordCount": "1",
        "PageNumber": "1",
        "PageRecordCount": "1",
        "Items": {
            "SQLSlowLog": {
                "SQLText": "update test.zxb set id=0 limit 1",
                "SQLServerTotalExecutionCounts": "178",
                "SQLServerTotalExecutionTimes": "189",
                "TotalLogicalReadcounts": "89",
                "TotalPhysicalReadcounts": "90",
                "ReportTime": "2013-11-12Z"
            }
        }
    }
}

Error codes

HTTP status code Error code Error message Description
400 InvalidSearchTimeRange search time range cannot be longer than a month. The error message returned because the time span between the start time and the end time exceeds 31 days.
400 IO.Exception IO exception, retry later. The error message returned because I/O errors occurred.

For a list of error codes, visit the API Error Center.