All Products
Search
Document Center

PolarDB:Overview

Last Updated:Aug 29, 2023

PolarDB-X and Simple Log Service jointly provide the SQL audit and analysis feature to deliver SQL audit logs to Simple Log Service. This feature allows you to perform operations including queries, visualized analysis, alert generation, deliveries, and processing of logs in real time. This topic describes the SQL audit and analysis features of PolarDB-X.

Benefits

  • Ease of use: You can enable the SQL audit and analysis feature in the PolarDB-X console to audit and analyze the logs of SQL queries in real time.

  • Zero performance loss: Simple 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: Simple Log Service provides real-time log analysis, out-of-the-box reports, and drill-down analysis. These features help you obtain information about your database, including the status, performance, and security risks.

  • Real-time alerts: The feature supports custom monitoring and alerting for specific metrics in real time. You can receive alerts through multiple notification methods when exceptions in critical workloads occur.

Limits

  • Only existing instances and new instances created by users of PolarDB-X 5.4.16-16717637 or earlier support this feature. Instances created by users of later versions do not support this feature. We recommend that you use the SQL insight and stress testing feature.

  • By default, audit log entries are retained for 45 days.

  • Simple Log Service provides frequent updates and upgrades for the SQL audit feature. Simple 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 in the Simple Log Service console.

  • The SQL audit and analysis feature of PolarDB-X is free of charge. However, Simple Log Service charges you for the storage space, read traffic, number of requests, data processing, and data transmission.

  • By default, the SQL audit and analysis feature is disabled. You can enable the feature at any time 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 Simple 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 Simple Log Service to disable Simple Log Service. After Simple Log Service is disabled, you are no longer charged. How do I deactivate Simple Log Service or stop being billed for Simple Log Service?

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.

  • 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 time" , 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 query result includes the SQL template ID, execution duration ratio, number of executions, average execution time, average number of affected rows, and sample SQL. 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 feature of PolarDB-X provides the out-of-the-box log report feature based on Simple 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.