PolarDB:Usage

Prerequisites

Your PolarDB cluster uses one of the following versions:

• PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.30 or later.

• PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.12 or later.

For information about how to view the version of your cluster, see Query the engine version.

Limits

The SQL Trace feature does not track operations that are related to accounts, such as CREATE USER, DROP USER and GRANT.

Parameters

Usage

• Add SQL statements to be tracked.

  You can use one of the following methods to add SQL statements that need to be tracked and use the SQL Trace feature to track the execution of the SQL statements:

  • Set the loose_sql_trace_type parameter to ALL to track all SQL statements.

  • Set the loose_sql_trace_type parameter to DEMAND and add the SQL statements to be traced by using the dbms_sql.add_trace stored procedure.

• Obtain information about SQL statements that are traced by the SQL Trace feature.

  You can access information_schema.sql_sharing tables to view the execution information of SQL statements and top SQL statements that are tracked by the SQL Trace feature. Sample syntax:

  • Obtain the execution information and execution plan of a specified SQL statement.

    SELECT * FROM information_schema.sql_sharing WHERE sql_id = polar_sql_id('select * from t');

  • Obtain the top 10 SQL statements in terms of the total execution time, average execution time, and total number of scanned rows.

    SELECT * FROM information_schema.sql_sharing WHERE TYPE='sql' ORDER BY SUM_EXEC_TIME DESC LIMIT 10;
    SELECT * FROM information_schema.sql_sharing WHERE TYPE='sql' ORDER BY SUM_EXEC_TIME/EXECUTIONS DESC LIMIT 10;
    SELECT * FROM information_schema.sql_sharing WHERE TYPE='sql' ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10;

Other operations

• If you no longer need to track SQL statements that are added by using the dbms_sql.add_trace stored procedure, you can use the dbms_sql.delete_trace stored procedure to delete templated SQL statements based on specific SQL statements, or use the dbms_sql.delete_trace_by_sqlid stored procedure to delete templated SQL statements based on SQL IDs.

• You can reset all statistics in the information_schema.sql_sharing table by using the dbms_sql.reset_trace_stats stored procedure.

• You can clear all statistics in the information_schema.sql_sharing table by using the dbms_sql.flush_trace stored procedure. After all statistics in the table are cleared, you can use the dbms_sql.reload_trace stored procedure to reload templated SQL statements from the mysql.sql_sharing table to the information_schema.sql_sharing table if you want to re-collect statistics on the execution of SQL statements that are added by using the dbms_sql.add_trace stored procedure.

Performance test

The SQL Trace feature uses many lock-free designs to ensure database performance in various scenarios where high concurrency and a large number of SQL templates are required.

The following test compares the performance of database in clusters when the sql_trace_type parameter is set to OFF and ALL in the same scenario.

In the Sysbench test, 2,000 tables are used. Each table contains 10,000 rows of data. A cluster of 4 cores and 8 GB of memory and a cluster of 8 cores and 32 GB of memory are used for testing.

• The following figures show the performance comparison for the cluster of 4 cores and 8 GB of memory in different scenarios.

  • db-ps-mode=disable oltp_read_only

  • db-ps-mode=auto oltp_read_write

• The following figures show the performance comparison for the cluster of 8 cores and 32 GB of memory in different scenarios.

  • db-ps-mode=disable oltp_read_only

  • db-ps-mode=auto oltp_read_write

Conclusions

The preceding test data indicates that the SQL Trace feature degrades the database performance by less than 3% in the oltp_read_only and oltp_read_write scenarios.