Slow Query Diagnosis: Root Cause Analysis for Slow SQL Statements
Tags: opengauss
Published: 2023-04-13 03:33:49

**Overview**

Slow SQL statements have always been a pain point in data O&M. How to effectively diagnose the root causes of slow SQL statements is a big challenge. Based on the characteristics of openGauss and the slow SQL diagnosis experience of DBAs on the live network, this tool supports more than 25 root causes of slow SQL statements, outputs multiple root causes based on the possibility, and provides specific solutions.

**Environment Deployment**

**·** The database is working properly.
**·** The metric collection system is running properly.

**Usage Guide**

Assume that the confpath configuration file directory has been initialized.

**·** Run the following command to start only the slow SQL diagnosis function and output the top 3 root causes (for details, see the description of the service subcommand):

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

**·** Run the following command to diagnose slow SQL statements in interactive mode:

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

**·** Run the following command to manually clear historical prediction results:

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

**·** Run the following command to stop the services that have been started:

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

**Obtaining Help Information**

You can run the –help command to obtain the help information. For example:

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

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

**Command Reference**

Table 1 gs_dbmind component slow_query_diagnosis parameters

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

**Troubleshooting**

**·** If you run the interactive diagnosis command for a slow SQL statement that has not been executed, no diagnosis result is provided.
**·** If the exporter metric collection function is not enabled, the slow SQL diagnosis function is not available.
**·** After the parameters in the configuration file are reset, you need to restart the service process for the settings to take effect.
**·** When the interactive diagnosis function of slow SQL statements is used, the tool obtains necessary data based on the RPC and data collection services. Therefore, if the RPC and data collection services are not started, the diagnosis cannot be performed.
**·** When the diagnosis function is used for interactive diagnosis, the tool checks the entered SQL and database. If the entered SQL and database are invalid, the diagnosis cannot be performed.
**·** During slow SQL diagnosis, SMALL_SHARED_BUFFER needs to collect column information of related tables. Therefore, ensure that the opengauss_exporter connection user has the permission on the schema to which the table belongs.