Slow SQL Analysis

Scenarios

Slow SQL Analysis 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

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page, choose Databases > GaussDB(for MySQL).
4. On the Instances page, click the DB instance name to go to the Basic Information page.
5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
6. Click the Slow Query Log tab.
   

   Slow SQL Analysis 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.

7. Click Subscribe. In the displayed dialog box, you can learn about Intelligent O&M functions and pricing.
   Figure 1 Subscribe to Intelligent O&M
   

8. After subscribing to Intelligent O&M, view slow queries over time of your instance.
9. View slow queries and slow log archive history for the last 1 hour, last 3 hours, last 12 hours, or a custom time period (spanning no more than one day).

   In the Top 5 Slow Query Logs area, logs can be displayed by the usernames and client IP addresses.

   

10. In the Details area, click next to Collect Slow Query Logs to view slow query log details and template statistics. If you want to disable it, click Manage Log in the upper right corner and toggle off the switch.
    Figure 2 Viewing slow query log details (Collect Slow Query Logs disabled)
    
    Figure 3 Viewing slow query log details (Collect Slow Query Logs enabled)
    
    • In the Details area:
      • Filter slow query logs by database, client IP address, or user.
      • Locate a SQL statement and click Concurrency Control in the Operation column. For details, see Concurrency Control.
      • Click . 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.
    • In the Statistics area:
      • Click View Sample in the Operation to view the sample of the SQL template.
      • Click . 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.

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 4 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 5 Log storage and archiving (Intelligent O&M not subscribed)