【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();