【GBase 8c V5_3.0.0 分布式数据库常用几个SQL】

1.检查应用连接数

以管理员用户 gbase，登录数据库主节点。
接数据库，并执行如下 SQL 语句查看连接数。

SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

2.查看空闲连接

查看空闲(state 字段为”idle”)且长时间没有更新过的连接信息，使用如下命令。

SELECT * FROM pg_stat_activity where state='idle' order by state_change;

3.查看慢SQL

查看数据库实例中慢SQL语句执行信息，语法格式:

select * from dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp, end_timestamp);

例如

select * from DBE_PERF.get_global_full_sql_by_timestamp('2024-08-30 09:25:22', '2024-08-30 23:54:41');

4.查看历史SQL

查看当前主节点SQL语句执行信息

 select * from statement_history;

表的结构为

postgres=# \d statement_historyUnlogged table "pg_catalog.statement_history"Column        |           Type           | Modifiers 
----------------------+--------------------------+-----------db_name              | name                     | schema_name          | name                     | origin_node          | integer                  | user_name            | name                     | application_name     | text                     | client_addr          | text                     | client_port          | integer                  | unique_query_id      | bigint                   | debug_query_id       | bigint                   | query                | text                     | start_time           | timestamp with time zone | finish_time          | timestamp with time zone | slow_sql_threshold   | bigint                   | transaction_id       | bigint                   | thread_id            | bigint                   | session_id           | bigint                   | n_soft_parse         | bigint                   | n_hard_parse         | bigint                   | query_plan           | text                     | n_returned_rows      | bigint                   | n_tuples_fetched     | bigint                   | n_tuples_returned    | bigint                   | n_tuples_inserted    | bigint                   | n_tuples_updated     | bigint                   | n_tuples_deleted     | bigint                   | n_blocks_fetched     | bigint                   | n_blocks_hit         | bigint                   | db_time              | bigint                   | cpu_time             | bigint                   | execution_time       | bigint                   | parse_time           | bigint                   | plan_time            | bigint                   | rewrite_time         | bigint                   | pl_execution_time    | bigint                   | pl_compilation_time  | bigint                   | data_io_time         | bigint                   | net_send_info        | text                     | net_recv_info        | text                     | net_stream_send_info | text                     | net_stream_recv_info | text                     | lock_count           | bigint                   | lock_time            | bigint                   | lock_wait_count      | bigint                   | lock_wait_time       | bigint                   | lock_max_count       | bigint                   | lwlock_count         | bigint                   | lwlock_wait_count    | bigint                   | lwlock_time          | bigint                   | lwlock_wait_time     | bigint                   | details              | bytea                    | is_slow_sql          | boolean                  | trace_id             | text                     | 
Indexes:"statement_history_time_idx" btree (start_time, is_slow_sql) TABLESPACE pg_default
Replica Identity: NOTHING

备库

select * from dbe_perf.standby_statement_history(true, '2024-08-30 09:25:22', '2024-08-30 23:54:41');

5.数据库实例中SQL语句执行信息

查看数据库实例中SQL语句执行信息，语法格式:

select * from dbe_perf.get_global_full_sql_by_timestamp('2024-08-30 09:25:22', '2024-08-30 23:54:41');

6.执行加载配置文件命令

select pg_reload_cong();