SQLdiag: Slow SQL Discovery ​

SQLdiag is a framework for predicting the execution duration of SQL statements in openGauss. The existing prediction technologies are mainly based on model prediction of execution plans. These prediction solutions are applicable only to jobs whose execution plans can be obtained in the OLAP scenarios, and are not useful for quick query such as OLTP or HTAP. Different from the preceding solutions, SQLdiag focuses on the historical SQL statements of databases. Because the execution duration of the database SQL statements in a short time does not vary greatly, SQLdiag can detect instruction sets similar to the entered instructions from the historical data, and predict the SQL statement execution duration based on the SQL vectorization technology and the time series prediction algorithm. This framework has the following benefits:

1. Execution plans do not require instructions. This has no impact on database performance.
2. The framework is widely used, unlike many other well-targeted algorithms in the industry, for example, they may applicable only to OLTP or OLAP.
3. The framework is robust and easy to understand. Users can design their own prediction models by simply modifying the framework.

Overview ​

SQLdiag is an SQL statement execution time prediction tool. It predicts the execution time of SQL statements based on the statement logic similarity and historical execution records without obtaining the SQL statement execution plan using a template or deep learning. Abnormal SQL statements can also be detected with this tool.

Usage Guide ​

Prerequisites ​

• You have obtained training data.
• If you use the provided tool to collect training data, you need to enable the WDR function. The involved parameters are track_stmt_stat_level and log_min_duration_statement. For details, see the following sections.
• To ensure the prediction accuracy, the historical statement logs provided by users should be as comprehensive and representative as possible.

Collecting SQL Statements ​

This tool requires users to prepare data in advance. Each sample is separated by a newline character. The training data format is as follows:

SQL,EXECUTION_TIME

The prediction data format is as follows:

SQL

SQL indicates the text of an SQL statement, and EXECUTION_TIME indicates the execution time of the SQL statement. For details about the sample data, see train.csv and predict.csv in sample_data.

You can collect training data in the required format. The tool also provides the load_sql_from_rd script for automatic collection. The script obtains SQL information based on the WDR report. The involved parameters are log_min_duration_statement and track_stmt_stat_level:

• log_min_duration_statement indicates the slow SQL threshold. If the value is 0, full collection is performed. The unit is millisecond.
• track_stmt_stat_level indicates the information capture level. You are advised to set it to 'L0,L0'.

After this parameter is set, a certain amount of system resources may be occupied but the usage is generally low. In continuous high-concurrency scenarios, this may cause a performance loss less than 5%. If the database concurrency is low, the performance loss can be ignored. The following script is stored in the sqldiag root directory ($GAUSSHOME/bin/components/sqldiag).

Use a script to obtain the training set:
load_sql_from_wdr.py [-h] --port PORT --start_time START_TIME
                            --finish_time FINISH_TIME [--save_path SAVE_PATH]
Example:
    python load_sql_from_wdr.py --start_time "2021-04-25 00:00:00" --finish_time "2021-04-26 14:00:00" --port 5432  --save_path ./data.csv

Procedure ​

1. Provide historical logs for model training.

2. Perform training and prediction.

   Template-based training and prediction:
      gs_dbmind component sqldiag [train, predict] -f FILE --model template --model-path template_model_path 
   DNN-based training and prediction:
      gs_dbmind component sqldiag [train, predict] -f FILE --model dnn --model-path dnn_model_path

Examples ​

Use the provided test data to perform template-based training:

gs_dbmind component sqldiag train -f ./sample_data/train.csv --model template --model-path ./template

Use the provided test data for template-based prediction:

gs_dbmind component sqldiag predict -f ./sample_data/predict.csv --model template --model-path ./template --predicted-file ./result/t_result

Use the provided test data to update the template-based model:

gs_dbmind component sqldiag finetune -f ./sample_data/train.csv --model template --model-path ./template

Use the provided test data to perform DNN-based training:

gs_dbmind component sqldiag train -f ./sample_data/train.csv --model dnn --model-path ./dnn_model

Use the provided test data for DNN-based prediction:

gs_dbmind component sqldiag predict -f ./sample_data/predict.csv --model dnn --model-path ./dnn_model --predicted-file

Use the provided test data to update the DNN-based model:

gs_dbmind component sqldiag finetune -f ./sample_data/train.csv --model dnn --model-path ./dnn_model

Obtaining Help Information ​

Before using the SQLdiag tool, run the following command to obtain help information:

gs_dbmind component sqldiag --help

The command output is as follows:

usage:   [-h] [-f CSV_FILE] [--predicted-file PREDICTED_FILE]
               [--model {template,dnn}] --model-path MODEL_PATH
               [--config-file CONFIG_FILE]
               {train,predict,finetune}

SQLdiag integrated by openGauss.

positional arguments:
  {train,predict,finetune}
                        The training mode is to perform feature extraction and
                        model training based on historical SQL statements. The
                        prediction mode is to predict the execution time of a
                        new SQL statement through the trained model.

optional arguments:
  -h, --help            show this help message and exit
  -f CSV_FILE, --csv-file CSV_FILE
                        The data set for training or prediction. The file
                        format is CSV. If it is two columns, the format is
                        (SQL statement, duration time). If it is three
                        columns, the format is (timestamp of SQL statement
                        execution time, SQL statement, duration time).
  --predicted-file PREDICTED_FILE
                        The file path to save the predicted result.
  --model {template,dnn}
                        Choose the model to use.
  --model-path MODEL_PATH
                        The storage path of the model file, used to read or
                        save the model file.
  --config-file CONFIG_FILE

Command Reference ​

Table 1 Command-line options

Troubleshooting ​

• Failure in the training scenario: Check whether the file path of historical logs is correct and whether the file format meets the requirements.

• Failure in the prediction scenario: Check whether the model path is correct. Ensure that the format of the load file to be predicted is correct.