# csu_query_limit

**Repository Path**: csugp/csu_query_limit

## Basic Information

- **Project Name**: csu_query_limit
- **Description**: 对Greenplum的复杂SQL的并发执行做一些限制，防止把数据库给搞死。
- **Primary Language**: C
- **License**: PostgreSQL
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2024-09-08
- **Last Updated**: 2024-09-25

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# csu_query_limit
`csu_query_limit`  is a Greenplum extension which to limit sql execute.


# Installation
## Compiling

This module can be built using the standard PGXS infrastructure. For this to work, the pg_config program must be available in your $PATH:
  
`git clone https://gitee.com/csudata/csu_query_limit.git` <br>
`cd csu_query_limit` <br>
`make` <br>
`make install` <br>

## Greenplum setup

Extension can be loaded at session level with `session_preload_libraries` parameter: <br> 
`session_preload_libraries = 'csu_query_limit'` <br>

Following SQL statement must be run in each database: <br>
`create extension csu_query_limit;`

 `csu_query_limit` has been successfully tested with Greenplum6.X.

## Usage

GUC:

* csu_query_limit.max_parallel_slow_sql: The maximum number of parallelism for slow SQL.
* csu_query_limit.slow_sql_timemout: The maximum waiting time for slow SQL in the queue, , unit is seconds.
* csu_query_limit.max_queue_net_plan_size: the size(gp_plan_size * slices) of the SQL， Exceeds this parameter value will queue the slow SQL.
* csu_query_limit.max_abort_net_plan_size: the size(gp_plan_size * slices) of the SQL， Exceeds this parameter value will abort the slow SQL.
* csu_query_limit.queue_policy: Slow SQL report error when it set to 0, else slow SQL will queue.
* csu_query_limit.limit_user_name： only for this db user.

Function:

* csuql_get_slow_conns(): Get current number of slow SQL.
* csuql_set_slow_conns(): Adjust number of slow SQL


Show slow query:
```
postgres=# select pid, waiting_reason, state, query from pg_stat_activity;
  pid  | waiting_reason | state  |                                                  query                                                   
-------+----------------+--------+----------------------------------------------------------------------------------------------------------
 13394 |                | active | /*CSUQL_RUNNING*/WITH cte0 AS (                                                                         +
       |                |        |     SELECT                                                                                              +
 13414 |                | active | /*CSUQL_WAITING*/WITH cte0 AS (                                                                         +
       |                |        |     SELECT                                                                                              +
       |                |        |         end_user AS "最终用户",                                                                         +
```

* /\*CSUQL_RUNNING\*/: that slow sql is running.
* /\*CSUQL_WAITING\*/: that slow sql is in queue.



## Install

In postgresql.conf:

```
session_preload_libraries = 'csu_query_limit'
csu_query_limit.max_parallel_slow_sql = 1
csu_query_limit.slow_sql_timemout = 60
csu_query_limit.max_net_plan_size = 20
csu_query_limit.queue_policy = 1
csu_query_limit.limit_user_name = 'u01'
```

Run with psql:

```
CREATE EXTENSION csu_query_limit;
```

## Limitations