Slow Query Logs

Scenarios

Slow Query Logs 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.

Constraints

• 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 Subscribing to Intelligent O&M.
• After Collect Slow Query Logs is enabled, SQL text content will be stored in OBS.

Viewing Slow Queries over Time

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > TaurusDB.
4. On the Instances page, click the instance name.
5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
6. Click the Slow Query Logs tab.
7. Select a time range, and view slow queries over time by instance or node.

   You can view slow query logs in the last 1 hour, 3 hours, 12 hours, or a custom time period (no longer than one day).

   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.

Viewing Top 5 Slow Query Logs

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > TaurusDB.
4. On the Instances page, click the instance name.
5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
6. Click the Slow Query Logs tab.
7. Select an instance, node, and time range. In the Top 5 Slow Query Logs area, view the top 5 slow SQL statements sorted by user or client IP address.

   You can view slow query logs in the last 1 hour, 3 hours, 12 hours, or a custom time period (no longer than one day).

Viewing Slow Query Log Details

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > TaurusDB.
4. On the Instances page, click the instance name.
5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
6. Click the Slow Query Logs tab.
7. Select an instance, node, and time range, and view the slow query log details. The details include the SQL statement, execution start time, database, client, user, execution duration, lock wait duration, and scanned and returned rows.

   You can view slow query logs in the last 1 hour, 3 hours, 12 hours, or a custom time period (no longer than one day).

   • To export slow query log details to an OBS bucket, click Export. In the displayed dialog box, select an OBS bucket and click OK. Up to 100,000 records can be exported.

     If no OBS bucket is available, click Create. In the displayed dialog box, enter an OBS bucket name, and click OK.

     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, click View Export List to view export records. You can also download the details to your local PC for analysis.
   • To add a concurrency control rule, click Concurrency Control in the Operation column. In the displayed dialog box, specify SQL Type, Keyword, and Max. Concurrency. For details, see Concurrency Control.
   • To view the SQL diagnosis results, click Diagnose in the Operation column.

Viewing Template Statistics

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > TaurusDB.
4. On the Instances page, click the instance name.
5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
6. Click the Slow Query Logs tab.
7. Select an instance, node, and time range, and view the template statistics.
   • Click View Sample in the Operation to view the sample of the SQL template.
   • Export slow query log details.
     1. Click Export. In the displayed dialog box, select an OBS bucket and click OK to export the log details to the OBS bucket. Up to 100,000 records can be exported.
     2. If no OBS bucket is available, click Create. In the displayed dialog box, enter an OBS bucket name, and click OK.
        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 View Export List to view export records. You can also download the details to your local PC for analysis.