Workload-level Index Recommendation
Tags: opengauss
Published: 2023-04-12 10:39:18

For workload-level indexes, you can run scripts outside the database to use this function. This function uses the workload of multiple DML statements as the input to generate a batch of indexes that can optimize the overall workload execution performance. In addition, it provides the function of extracting service data SQL statements from logs or system catalogs.

**Prerequisites**
**·** The database is normal, and the client can be connected properly.
**·** The gsql tool has been installed by the current user, and the tool path has been added to the _PATH _environment variable.

**Service Data Extraction**

**Collecting SQL Statements in Logs**
1. Set GUC parameters. log_min_duration_statement = 0 log_statement= 'all'

2. Run the following command to extract SQL statements based on logs:

gs_dbmind component extract_log [l LOG_DIRECTORY] [f OUTPUT_FILE] [p LOG_LINE_PREFIX] [-d DATABASE] [-U USERNAME][--start_time] [--sql_amount] [--statement] [--json] [--max_reserved_period] [--max_template_num]

The input parameters are as follows:

1) **LOG_DIRECTORY**: directory for storing pg_log.
2) **OUTPUT_PATH**: path for storing the output SQL statements, that is, path for storing the extracted service data.
3) **LOG_LINE_PREFIX**: specifies the prefix format of each log.
4) **DATABASE** (optional): database name. If this parameter is not specified, all databases are selected by default.
5) **USERNAME** (optional): username. If this parameter is not specified, all users are selected by default.
6) **start_time** (optional): start time for log collection. If this parameter is not specified, all files are collected by default.
7) **sql_amount** (optional): maximum number of SQL statements to be collected. If this parameter is not specified, all SQL statements are collected by default.
8) **statement** (optional): Collects the SQL statements starting with **statement** in **pg_log**. If this parameter is not specified, the SQL statements are not collected by default.
9) **json** (optional): specifies that the collected log files are stored in JSON format after SQL normalization. If no format is specified, each SQL statement occupies a line.
10) **max_reserved_period** (optional): specifies the maximum number of days of reserving the template in incremental log collection in JSON mode. If this parameter is not specified, the template is reserved by default. The unit is day.
11) **max_template_num** (optional): specifies the maximum number of templates that can be reserved in JSON mode. If this parameter is not specified, all templates are reserved by default.
An example is provided as follows.

An example is provided as follows.

gs_dbmind component extract_log $GAUSSLOG/pg_log/dn_6001 sql_log.txt '%m %c %d %p %a %x %n %e' -d postgres -U omm --start_time '2021-07-06 00:00:00' --statement

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

3. Change the GUC parameter values set in 1 to the values before the setting.

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

**Collecting SQL Statements in System Catalogs**
Run the following SQL statement to extract data from system catalogs:

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

The input parameters are as follows:

**·** **DB_PORT**: port number of the connected database.
**·** **DATABASE**: name of the connected database.
**·** **OUTPUT**: output file that contains SQL statements.
**·** **DB_HOST** (optional): name of the host that connects to the database.
**·** **DB_USER** (optional): name of the user that connects to the database. The user must have the sysadmin or monitor admin permission.
**·** **SCHEMA**: schema name. This parameter is used only when **statement-type** is set to **history**. The default value is **public**.
**·** **statement-type**: SQL statement type, which can be **asp**, **slow**, **history**, or **activity**.
**-** **asp**: extracts SQL statements from gs_asp. Ensure that the GUC parameter **enable_asp** is enabled.
**-** **slow**: extracts active slow SQL statements.
**-** **history**: extracts historical slow SQL statements.
**-** **activity**: extracts active SQL statements.
**·** **START_TIME**: specifies the start time. This parameter is mandatory and is used only when **statement-type** is set to **asp**.
**·** **END_TIME**: specifies the end time. This parameter is mandatory and is used only when statement-type is set to asp.
**·** **verify**: determines whether to verify the validity of SQL statements.
**·** **driver**: determines whether to use the Python driver to connect to the database. By default, **gsql** is used for the connection.

**Procedure for Using the Index Recommendation Script**
1. Prepare a file that contains multiple DML statements as the input workload. Each statement in the file occupies a line. You can obtain historical service statements from the offline logs of the database.

2. To enable this function, run the following command:

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

The input parameters are as follows:
1) **DB_PORT**: port number of the connected database.
2) **DATABASE**: name of the connected database.
3) **FILE**: file path that contains the workload statement.
4) **DB_HOST** (optional): ID of the host that connects to the database.
5) **DB_USER** (optional): username for connecting to the database.
6) **SCHEMA**: schema name.
7) **MAX_INDEX_NUM** (optional): maximum number of recommended indexes.
8) **MAX_INDEX_STORAGE** (optional): maximum size of the index set space.
9) **MAX_N_DISTINCT**: reciprocal of the number of **distinct** values. The default value is **0.01**.
10) **MIN_IMPROVED_RATE**: minimum improvement ratio. The default value is **0.1**.
11) **MAX_CANDIDATE_COLUMNS** (optional): maximum number of candidate columns in an index.
12) **MAX_INDEX_COLUMNS**: maximum number of columns in an index. The default value is **4**.
13) **MIN_RELTUPLES**: minimum number of records. The default value is **10000**.
14) **multi_node** (optional): specifies whether the current instance is a distributed database instance.
15) **multi_iter_mode** (optional): algorithm mode. You can switch the algorithm mode by setting this parameter.
16) **json** (optional): specifies the file path format of the workload statement as JSON after SQL normalization. By default, each SQL statement occupies one line.
17) **driver** (optional): Specifies whether to use the Python driver to connect to the database. By default, **gsql** is used for the connection.
18) **show-detail** (optional): specifies whether to display the detailed optimization information about the current recommended index set.
19) **show-benefits** (optional): determines whether to display index benefits.

The recommendation result is a batch of indexes, which are displayed on the screen in the format of multiple create index statements. The following is an example of the result.

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