Slow Query Analysis

NineData's slow query analysis feature is used to locate slow SQL queries and optimize databases, assisting developers and database administrators in identifying abnormal queries that affect database performance and providing optimization recommendations. It mainly includes slow log collection, diagnosis, and optimization.

Background Information

Slow queries typically have a negative impact on enterprise databases, and their causes are diverse, including complex query statements, insufficient index design, large data processing, insufficient hardware resources, and more. This leads to the database server consuming more resources and time to execute queries, and can even lead to database crashes, making it unresponsive to business requests.

Slow query analysis is a key function in the field of database management and performance optimization. Its main purpose is to monitor abnormal queries that affect database performance in the database system to identify performance bottlenecks, improve query performance, and enhance system response time.

What Is NineData Slow Query Analysis?

NineData slow query analysis is a database optimization tool designed to help developers and database administrators identify queries that affect database performance and provide optimization recommendations with the aim of improving database efficiency and stability.

NineData slow query analysis has the following features:

• Slow Query Collection: Collecting and recording queries that affect database performance from the database. The records include detailed information about the query, such as the query statement, execution time, the number of returned rows, and more.
• Slow Query Diagnosis: In-depth analysis of collected slow queries to identify potential reasons for slower query execution. This includes factors like insufficient indexing, suboptimal table design, improper query statement writing, and more. Diagnosis helps to better understand why a particular query becomes slow, providing a basis for optimization.
• Slow Query Optimization: Building on the diagnosis, the system automatically analyzes optimization methods once the reasons for slow queries are identified. This may involve adding or modifying indexes, adjusting table structures, rewriting query statements, and more. The goal of optimization is to improve query execution speed and enhance the overall database performance.

Use Cases

• Database Performance Optimization: If your database performance is deteriorating or if some queries have excessively long execution times, you can use NineData's slow query analysis to identify and optimize these issues.
• SQL Development and Debugging: When developing or debugging SQL queries, you can use NineData's slow query analysis to assess query performance and identify potential areas for optimization.
• Database Operations: For database administrators, NineData's slow query analysis is a powerful operational tool that helps monitor database performance, promptly detect and resolve issues.

Prerequisites

Import Content from '../ref/_ref-precautions_1.md'

• You are using MySQL, PostgreSQL, DB2 and Oracle as your data source.
• You have added the data source you want to perform slow query analysis on to the NineData console. For more information, see Create Data Source.
• Slow query logging is enabled in your MySQL data source. See the Appendix for instructions on how to enable it.
• The pg_stat_statements plugin has been installed and configured in your PostgreSQL data source. Please refer to Appendix 2 for instructions on how to proceed.

Step 1: Enable Slow Query Collection

You need to enable the feature for your target data source to use the slow query analysis feature.

1. Log in to the NineData console.

2. Click on and then in the left navigation panel.

3. On the page, you will see all data sources that support slow query analysis. Click the toggle in the column for your target data source to enable this feature.

   tip

   If your data source has not enabled slow log functionality, the page will display an error. Please refer to the Appendix to enable it and then try again.

Step 2: View Slow Query Dashboard

The presentation of slow query counts in a report format helps you gain a clearer understanding of the trend in slow queries. Through the visualized data, you can quickly identify abnormal fluctuations in database performance, take timely measures to address issues, and further enhance system stability and performance.

1. Log in to the NineData Console.

2. Click on > in the left navigation bar.

3. On the tab, slow query trend charts for all data sources with slow query analysis enabled within the last 12 hours are displayed by default. You can perform the following actions:

   

   Number	Feature	Description
   ①	Time Range for Slow Queries	Choose the time range for displaying slow queries on the dashboard. Supports custom start and end times and preset options such as , , , , , .
   ②	Data Source Filtering	Quickly filter the data sources you want to view based on whether they are bookmarked, tag names, data source types, environment names, or data source names.
   ③	Group Display	Group slow queries based on tags, environments, data source types, or data source names for better organization.
   ④	Download Slow Query Report	Download a slow query report based on the filtered dashboard. The report is an Excel spreadsheet containing key information such as data source name, connection address, slow query count, and collection time. After clicking download, go to the tab, and click on the in the column of the target report to download it locally.
   ⑤	View Slow Query Details	Click on in the column of the target data source to navigate to the slow query details page. For more information, refer to Step 3.

Step 3: View Slow Query Details

1. Log in to the NineData Console.

2. Click on > in the left navigation bar.

3. On the tab, click on in the column of the target data source.

4. The page displays all slow query records generated by this data source within the last 3 days by default. You can adjust the time range as needed.

   tip

   Only slow query records within the last 3 days are displayed.

5. The slow query analysis consists of two layers: the outer layer is the SQL Template, which is the SQL framework without specific parameters. All slow queries using the same SQL template are recorded under this template. The inner layer contains the specific SQL statements. Click the + on the left of the target SQL Template to expand all SQL statements under this template for detailed information on libraries, execution time, returned rows, and executing users.

6. Click on to view information about performance diagnosis, rule checks, index suggestions, etc., for the slow query.

   tip

   The for the SQL Template displays the record for the SQL statement with the longest execution time under that template.

7. Click on the in the upper right corner of the section. Then, select either or to generate the report. Return to the previous page, click on the tab, and click the on the right side of the target report in the column to download the report locally.

Appendix: Enabling MySQL Slow Log Functionality

1. Log in to the MySQL database using an account with SYSTEM_VARIABLES_ADMIN or SUPER privileges. For more information about privileges, see System Variable Privileges.

2. Execute the following commands to enable the slow query log:

   SET GLOBAL slow_query_log=1; // Enable the slow query log.
   SET GLOBAL log_output='TABLE'; // Store the slow query log in the `slow_log` table within the `mysql` database.
   tip

   Pay attention to the size of the slow_log table. If the table becomes too large and affects your business, you can clear it using the TRUNCATE TABLE mysql.slow_log command.

3. (Optional) You can configure the following parameters as needed:

   SET GLOBAL long_query_time=1; // Queries taking more than 1 second will be defined as slow queries.
   SET GLOBAL log_queries_not_using_indexes=1; // Queries not utilizing indexes will be defined as slow queries.

4. To disable the slow query log in the MySQL database, execute SET GLOBAL slow_query_log=0;.

Appendix 2: Installing the pg_stat_statements Plugin

1. In your PostgreSQL database, execute CREATE EXTENSION IF NOT EXISTS pg_stat_statements; to create the pg_stat_statements plugin.

2. Open your postgresql.conf configuration file and set the following two parameters:

   python
   Copy code
   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

Appendix 3: Binding Tags to Data Sources

By configuring tags for specific data sources, you can flexibly group slow queries on the dashboard based on business logic, departments, or other requirements. This helps you more precisely identify and resolve performance bottlenecks.

1. Create tags.
2. Click on > in the left navigation bar.
3. Click on the tab, locate the data source you want to bind a tag to, hover over the column on the right side of the data source, and click the icon.
4. Click the drop-down menu for tags, select the target tag, and then click the icon to confirm the binding.