Viewing Slow SQL Logs

Scenarios

Slow Query Log displays a chart of SQL statements that are taking too long to execute and allows you to sort slow SQL statements by multiple dimensions, such as by user, host, or SQL template. It helps you quickly identify bottlenecks and improve instance performance.

Viewing Slow Query Logs

Log in to the management console.
Click in the upper left corner and select a region and project.
Click in the upper left corner of the page and choose Databases > GaussDB(for MySQL).
On the Instances page, click the instance name.
In the navigation pane, choose DBA Assistant > Historical Diagnosis.
Click the Slow Query Log tab.
- Slow Query Log needs to be purchased separately. To use this function, subscribe to Intelligent O&M first.
- Only the data of the last hour is displayed if Intelligent O&M is not subscribed. The data will be automatically deleted after one hour. After Intelligent O&M is subscribed, data can be stored for up to 30 days. For details, see Slow Query Log Storage.
Click Subscribe. In the displayed dialog box, you can learn about Intelligent O&M functions and pricing.
The Intelligent O&M fee for each instance consists of:
- Basic price: $0.075 USD/hour
- SQL storage space: $0.0072 USD/GB/hour
Get 5 GB of storage for free after your instance has subscribed to Intelligent O&M.

Figure 1 Subscribing to Intelligent O&M
Select "I have read and understand the billing rules." and click Subscribe.
Select a time range and view trends, details, and statistics of the slow query logs generated within the time range.

You can view information about slow query logs in the last 1 hour, 3 hours, 12 hours, or a custom time period (spanning no more than one day).
- Slow Queries over Time
  Above the chart, you can switch to another instance or node to view its slow queries.
  
  You can move the cursor to a point in time of the chart to view the number of slow query logs and CPU usage at the point in time.
  
  Figure 2 Slow Queries over Time
- Details
  View slow query log details in the lower part of the Slow Query Log page. The details include the SQL statement, execution start time, database, client, user, execution duration, lock wait duration, and scanned and returned rows.
  
  Figure 3 Details
  
  Click Export to export slow query log details to a specific OBS bucket. After the log details are exported, you can click View Export List to view export records.
  
  You can filter slow query log details by database, client IP address, or user.
  
  Locate an SQL statement and click Concurrency Control in the Operation column. For details, see Using SQL Statement Concurrency Control.
  
  Click Export. In the displayed dialog box, select an OBS bucket and click OK to export slow query logs to the OBS bucket. Up to 100,000 logs can be exported.
  
  If no OBS bucket is available, click Create. In the displayed dialog box, enter an OBS bucket name, and click OK.
  
  Creating an OBS bucket is free, but you will be billed for storing data in the bucket. For pricing details, see OBS Pricing Details.
  A bucket name:
  - Cannot be the same as that of any existing bucket.
  - Can contain 3 to 63 characters. Only lowercase letters, numbers, hyphens (-), and periods (.) are allowed.
  - Cannot start or end with a period (.) or hyphen (-), and cannot contain two consecutive periods (.) or contain a period (.) and a hyphen (-) adjacent to each other.
  - Cannot be an IP address.
  - If the bucket name contains a period (.), certificate-based verification is required when you use the name to access an OBS bucket or object.
  After the log details are exported, you can click View Export List to view export records. You can also download the details to your local PC for analysis.
- Statistics
  Figure 4 Statistics
  
  Click View Sample in the Operation to view the sample of the SQL template.
  
  Click Export. In the displayed dialog box, select an OBS bucket and click OK to export slow query logs to the OBS bucket. Up to 100,000 logs can be exported.
  
  If no OBS bucket is available, click Create. In the displayed dialog box, enter an OBS bucket name, and click OK.
  
  Creating an OBS bucket is free, but you will be billed for storing data in the bucket. For pricing details, see OBS Pricing Details.
  A bucket name:
  - Cannot be the same as that of any existing bucket.
  - Can contain 3 to 63 characters. Only lowercase letters, numbers, hyphens (-), and periods (.) are allowed.
  - Cannot start or end with a period (.) or hyphen (-), and cannot contain two consecutive periods (.) or contain a period (.) and a hyphen (-) adjacent to each other.
  - Cannot be an IP address.
  - If the bucket name contains a period (.), certificate-based verification is required when you use the name to access an OBS bucket or object.
  - After the templates are exported, you can click Export Slow Query Logs to view export records. You can also download the details to your local PC for analysis.
- Top 5 Slow Query Logs
  View the top 5 slow query logs by user or client IP address.
  
  Figure 5 Top 5 Slow Query Logs

Slow Query Log Storage

After Collect Slow Query Logs is enabled, SQL text content will be stored in OBS for analysis.

If you have subscribe intelligent O&M, click Log Settings in the upper right corner.
- Slow Query Log Period: The default value is 7. The value ranges from 1 to 30. After the period expires, the logs are automatically deleted.
- SQL Insights Retention Period: The default value is 7. The value ranges from 1 to 180.
- Log Size: Each paid instance can use 5 GB of storage for slow query logs for free. Any storage used in excess of 5 GB will be billed on a pay-per-use basis.
Figure 6 Log storage and archiving (Intelligent O&M subscribed)
If you do not subscribe intelligent O&M, click Log Settings in the upper right corner.
- Slow Query Log Period: The default value is 1 hour and cannot be changed. After the period expires, the logs are automatically deleted.
- SQL Insights Retention Period: 1 hour
Figure 7 Log storage and archiving (Intelligent O&M not subscribed)