GBase 8c Slow SQL Queries and Optimization

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

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:

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:

select /*+ nestloop(t2 t1) */

Specify query parallelism:

select /*+ set(query_dop 24) */