SQLdiag: Slow SQL Discovery
Tags: opengauss
Published: 2023-04-13 06:56:55

SQLdiag is a tool for predicting the execution duration of SQL statements in openGauss. Existing prediction technologies are mainly based on evaluations of the execution plans. This method can only be used in OLAP scenarios where the execution plans can be obtained. They are not very useful for quick simple OLTP or HTAP queries. SQLdiag, in contrast, examines the database SQL statement history and analyzes the performance of these historical statements to predict the performance of new, unknown services. The execution duration of SQL statements in the database does not differ greatly in a short period of time. SQLdiag can detect the statement result set similar to the executed SQL statements from historical data and predict the execution duration of SQL statements based on the SQL vectorization technology through a SQL template or deep learning. This tool has the following advantages:

1. Execution plans do not require SQL statements. This has no impact on database performance.
2. SQLdiag is widely used, unlike many other algorithms with limitations in the industry, for example, they are applicable only to OLTP or OLAP.
3. It is easy to understand the tool. You can train your own prediction model by performing simple operations.
A typical application scenario of this tool is to perform a perspective analysis on a batch of SQL statements to be brought online to identify risks in advance.

**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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8iumqndv3u66le8y9snn.png)

The prediction data format is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y60mtdb4z0mx2ti54k4w.png)

**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**).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g4cccmquc3cedd8nnh5c.png)

**Procedure**
1. Provide historical logs for model training.

2. Perform training and prediction.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jvih0py2wt4im5f2n5j2.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wv5wijvqsjptasbura91.png)

Use the provided test data for template-based prediction:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qu5or632ztam4xawy2xc.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0i2pzce8kfskx7fcyyr6.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/khropcyy4gy2prrwnvfn.png)

Use the provided test data for DNN-based prediction:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/guyl2nq29omp63mojtv8.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lbwa2k9tx4im333wa7ac.png)

**Obtaining Help Information**
Before using the SQLdiag tool, run the following command to obtain help information:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/msc8g4qb9ri5iv1xwer0.png)

The command output is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sgetxns4jb3zwr9fxcjf.png)

**Command Reference**
Table 1 Command-line options

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8mxzbti93dfid729ujkz.png)

**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.