PolarDB-X allows you to collect SQL audit logs and send the log entries to Log Service. You can query logs in real time and transform the log entries. You can also visualize log information for analysis and configure alerts based on the log entries. This topic describes the SQL audit and analysis features of PolarDB-X.

Benefits

  • Ease of use: You can enable the SQL audit and analysis features in the PolarDB-X console to analyze SQL audit log entries in real time.
  • Lossless performance: Log Service pulls SQL query log entries from all databases that are deployed in a PolarDB-X instance in real time. This does not compromise the instance performance.
  • Real-time analysis: Log Service provides real-time log analysis, out-of-the-box log reports, and drill-down analysis. These features help you obtain information about your database, including the status, performance, and security risks.
  • Real-time alerts: You can configure monitoring and alert rules based on specific metrics. This way, you can receive alerts through multiple notification methods when exception errors occur on your key business.

Note

  • By default, audit log entries are retained for 45 days.
  • Log Service provides frequent updates and upgrades for the SQL audit feature. Log Service also automatically updates the indexes and default reports of dedicated Logstores. Take note of the precautions before you delete or modify the default project, Logstore, indexes, and dashboards that are created for PolarDB-X by Log Service.
  • You are not charged for using the SQL audit and analysis features of PolarDB-X. You are charged for the storage space, read traffic, number of requests, data processing, and data transmission by Log Service.
  • By default, the SQL audit and analysis features are disabled. You can enable the feature in the PolarDB-X console. For more information, see Enable SQL audit and analysis.
  • After the SQL audit and analysis features are disabled, data is not written to Log Service. You can view historical data. You are charged for the storage and indexing of historical data. You can delete all data that is stored in Log Service to disable Log Service. After Log Service is disabled, you are no longer charged.

Scenarios

  • Troubleshoot problematic SQL queries

    After the SQL audit and analysis features are enabled, you can quickly retrieve SQL log entries for troubleshooting. For example, run the following command to query all SQL statements that take more than 1s to return results.

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

    The query results include information such as the execution duration of the SQL statement and the IP address that the client used to send the request, as shown in the preceding figure.

  • Analyze SQL templates that consume large amounts of resources

    In most applications, SQL statements are dynamically generated based on templates. SQL statements that are generated based on the same template contain different parameter values. For example, run the following command to obtain information about SQL templates that consume large amounts of resources 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 "Execution duration ratio (%)" ,execute_times as "Number of executions", round(avg_time) as "Average execution duration",round(avg_rows) as "Average number of 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 "Execution duration ratio (%)" desc limit 10

    The returned information includes the SQL template ID, ratio of the execution duration of the template-based SQL statement to the execution duration of all SQL statements, number of queries, average execution duration, average number of rows on which SQL statements are run, and a sample SQL statement, as shown in the preceding figure. You can identify and optimize the SQL templates that consume large amounts of resources in the application based on the query result.

  • Log report statistics

    The SQL audit and analysis features of PolarDB-X provide the out-of-the-box log report feature based on Log Service. Log reports help you analyze the status, performance, and potential security risks for your database in real time. For more information, see Log reports.