PolarDB-X SQL audit and analysis (legacy)

Benefits

• Easy to use: Enable the feature in the console to start auditing and analyzing SQL logs in real time.

• No performance impact: Log Service pulls SQL audit log files in real time from each database in your PolarDB-X instance, without affecting instance performance.

• Real-time analysis: Powered by Log Service, this feature provides real-time log analysis, out-of-the-box dashboards, and drill-down analysis for precise insights into database execution status, performance, and potential security issues.

• Real-time alerts: You can create custom alerts based on specific metrics. When an anomaly is detected in your key services, alerts are sent through multiple notification channels.

Limitations

This feature is available only for instances that run PolarDB-X Enterprise Edition V5.4.16-16717637 or earlier. For instances that run Enterprise Edition or Standard Edition later than V5.4.16-16717637, use the SQL insight and stress testing (new) feature.

Usage notes

• By default, SQL audit logs are stored for 45 days.

• Log Service periodically updates the SQL audit and analysis feature. The indexes and default dashboards in your dedicated Logstore are also updated automatically. To ensure proper functionality, do not delete or modify configurations such as the PolarDB-X Project, Logstore, index, or dashboard in the Log Service console.

• If you previously enabled the pay-by-data-traffic billing method, the system creates a dedicated Logstore with this billing method by default. If you want to switch to the pay-by-feature billing method, you can modify the Logstore configuration. For more information, see Manage a Logstore.

• The SQL audit and analysis feature is disabled by default. You can enable SQL audit and analysis at any time in the PolarDB-X console.

• You can still view historical data, but it continues to incur storage and indexing fees. To stop billing, delete the data and disable Log Service.

Use cases

• Troubleshoot problematic SQL statements

  After you enable the SQL audit and analysis feature, you can quickly search SQL logs to identify and troubleshoot issues. For example, you can run the following command to query all SQL statements with a response time of 1,000 ms or longer:

  __topic__:polardbx_sqlaudit and instance_id:pxc-*************** and response_time >= 1000 | select sql, response_time 
  

  The query results show the execution time of the SQL statement and the IP address of the client that executed it.

• Analyze high-cost SQL templates

  In most applications, SQL statements are dynamically generated from a set of templates, with only parameter values changing. For example, you can run the following command to identify high-cost SQL templates in a database.

  __topic__:polardbx_sqlaudit and instance_id:pxc-**************** | SELECT sql_code as "SQL Template ID", round(total_time * 1.0 /sum(total_time) over() * 100, 2) as "Percentage of total time (%)" ,execute_times as "Executions", round(avg_time) as "Average execution time",round(avg_rows) as "Average affected rows", CASE WHEN length(sql) > 200 THEN  concat(substr(sql, 1, 200), '......') ELSE trim(lpad(sql, 200, ' ')) end as "Sample SQL" FROM  (SELECT sql_code, count(1) as execute_times, sum(response_time) as total_time, avg(response_time) as avg_time, avg(affect_rows) as avg_rows, arbitrary(sql) as sql FROM log GROUP BY sql_code) ORDER BY "Percentage of total time (%)" desc limit 10

  The results include the SQL template ID, the percentage of total time, the number of executions, the average execution time, the average affected rows, and a sample SQL statement. Based on this analysis, you can identify the highest-cost SQL templates in your application and optimize them.

• Use log reports for statistical analysis

  To simplify problem analysis, the PolarDB-X SQL audit and analysis feature works with Log Service to provide out-of-the-box log reports. These reports help you diagnose and analyze database execution status, performance, and potential security risks in real time. For more information, see Log reports.

Billing

• Currently, PolarDB-X does not charge extra for the SQL audit and analysis feature.

• When the Logstore's billing method is pay-by-feature, Log Service charges for usage after logs are delivered. Billable items include storage space, read traffic, number of requests, data processing, and data shipping. For more information, see Pay-by-feature billing items.

• When the Logstore's billing method is pay-by-data-traffic, Log Service charges are based on the volume of raw data written. For more information, see Pay-by-data-traffic billing items.