GBase 8c Slow SQL Queries and Optimization
Tags: database
Published: 2024-07-01 08:25:06

GBase 8c database supports slow SQL diagnostics and provides several parameter interfaces for developers.

## 1. Slow SQL Related Parameters

### `track_stmt_stat_level`
Controls the level of statement execution tracking. This parameter has two parts, formatted as `'full sql stat level, slow sql stat level'`.

**full sql stat level**: Full SQL tracking level, with values OFF, L0, L1, L2.
**slow sql stat level**: Slow SQL tracking level, with values OFF, L0, L1, L2.

When the full SQL tracking level is not OFF, the current SQL tracking level is the higher level between the full SQL and slow SQL levels (L2 > L1 > L0).

### `log_min_duration_statement`
This parameter controls the logging of the duration of each completed statement when the duration is greater than or equal to a specified number of milliseconds.

When used together with `log_statement`, statements already logged by `log_statement` will not be logged again. Without syslog, it's recommended to use `log_line_prefix` to log PID or session ID, making it easier to connect the current statement message to the final duration message.

### `instr_unique_sql_count`
Increase this parameter to avoid the "missing SQL statement, GUC instr_unique_sql_count is too small." message.

## 2. Enabling Slow SQL

```bash
gs_guc reload -N all -I all -c "log_min_duration_statement=5s"
gs_guc reload -N all -I all -c "track_stmt_stat_level='OFF,L0'"
gs_guc reload -N all -I all -c "instr_unique_sql_count=2000"
```

## 3. Slow SQL Queries

Here are some example queries to detect slow SQL:

```sql
select * from dbe_perf.get_global_slow_sql_by_timestamp('2024-01-17 00:00:00', '2024-04-17 23:00:00');

select pid, now(), now() - query_start as query_duration, query 
from pg_stat_activity 
where datname = 'cloudx' 
  and pid != pg_backend_pid() 
  and state != 'idle' 
order by query_duration desc;
```

### Causes of Slow SQL

#### (1) Lack of Indexes

In such cases, it is recommended to create indexes:

- **a.** Create indexes on columns used in join conditions and `WHERE` clauses.
- **b.** Create composite indexes for multiple column conditions in `WHERE`.
- **c.** Create indexes on columns with high selectivity.
- **d.** Create indexes on columns that require sorting.

#### (2) Index Invalidation

- **a.** Leftmost prefix rule for composite indexes is invalidated.
- **b.** `SELECT *` queries unnecessary fields.
- **c.** Predicates involved in calculations or functions.
- **d.** Use of `LIKE` for fuzzy search, e.g., `c1 LIKE '%aa'`.
- **e.** Use of `!=`, `NOT IN`, `NOT EXISTS`, `IS NOT NULL`, etc.

### System Configuration

#### (1) Memory-Related Parameters Need Optimization

- **`max_process_memory`**: Used with `enable_memory_limit`, limits the maximum memory available to the GBase instance. Recommended to be 70%-80% of system memory.
- **`shared_buffers`**: Size of the database system cache pool. If set too low, insufficient cache will cause excessive disk I/O. Recommended to be 25% of system memory.
- **`work_mem`**: Specifies the amount of memory for internal sorting and hash tables. If set too low, more temporary files will be written to disk.

#### (2) Optimizer-Related Configurations

Below are some commonly used parameters:

- **`enable_bitmapscan`**
- **`enable_mergejoin`**
- **`enable_sort`**
- **`enable_nestloop`**
- **`enable_hashjoin`**
- **`enable_seqscan`**

#### (3) Use of Hints

Specify the use of nestloop join method:

```sql
select /*+ nestloop(t2 t1) */
```

Specify query parallelism:

```sql
select /*+ set(query_dop 24) */
```