PG/Gauss 性能问题相关表

长运行SQL：

SELECT current_timestamp - query_start AS runtime, datname, usename, query, unique_sql_id FROM pg_stat_activity where state != 'idle' ORDER BY 1 desc;
——设置参数track_activities为on 当此参数为on时，数据库系统才会收集当前活动查询的运行信息

Analyze：

ANALYZE检查的行数取决于300乘以default_statistics_target。

set default_statistics_target=10000;

show default_statistics_target;

set work_mem ='1GB';

show work_mem;

analyze table_name;

select tablename, attname, correlation, n_distinct, null_frac, most_common_vals, most_common_freqs, histogram_bounds from pg_stats where tablename = 'table_name' and attname = 'column_name';

如果default_statistics_target值为100，则ANALYZE最多可以扫描300 * 100 = 30,000行以收集准确的统计信息。

索引信息

select * from my_indexes t where t.table_name='table_name';

select * from my_ind_columns t where t.table_name='table_name' order by t.index_name,t.column_position;

select * from db_ind_columns t where t.table_name='table_name' order by t.index_name,t.column_position;

 

SELECT pg_get_functiondef('get_hostname()'::regprocedure);

SELECT pg_get_functiondef(f.oid) FROM pg_catalog.pg_proc f INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid) WHERE n.nspname = 'pg_catalog' and f.oid=5015;

select current_query(),pg_current_sessionid(),pg_current_sessid(),inet_client_addr(),inet_server_addr();

select t.plan_time/t.db_time,t.plan_time,t.db_time,t.n_calls,t.*  from dbe_perf."statement" t  where t.db_time> 0 and t.plan_time/t.db_time > 0.3 order by 1 desc;-- 计划生成使用占用总时间过大

ASP等session采样

select * from pg_stat_activity where state != 'idle' ; -- gv$session  information_schema.processlist

select * from dbe_perf.local_active_session; -- gv$active_session_history

select * from gs_asp; --dba_hist_active_sess_history

select n.nspname,c.* from pg_class c,pg_namespace n where relname ='gs_asp' and c.relnamespace=n.oid;

select w.query as waiting_query, w.pid as w_pid, w.usename as w_user,l.query as locking_query,l.pid as l_pid,l.usename as l_user,t.schemaname || '.' || t.relname as tablename from pg_stat_activity w join pg_locks l1 on w.pid = l1.pid and not l1.granted join pg_locks l2 on l1.relation = l2.relation and l2.granted join pg_stat_activity l on l2.pid = l.pid join pg_stat_user_tables t on l1.relation = t.relid where w.waiting = true;

 慢SQL分析

-- 必须在postgres库查询
--statement_history 记录了执行时间超过阀值（默认3s，log_min_duration_statement）的SQL详细情况
select extract(epoch from(t.finish_time-t.start_time)),start_time,finish_time,query,client_addr,t.* from dbe_perf.statement_history t where t.unique_query_id=2506848261 and start_time > '2024-05-17 12:00:00' and start_time < '2024-05-17 13:00:00'; --注意 只有start_time 有索引，所以需要加时间条件

-- 单个SQL 运行情况，执行次数、总的执行时间、访问数据量等
select * from dbe_perf.statement  t where t.unique_sql_id=1894315162;

--收集慢SQL计划
select * from dynamic_func_control('LOCAL', 'STMT', 'TRACK', '{"3267119089", "L2"}'); -- 抓此SQL的FULLSQL L2
--打开之后看一下statement_history里面有没有抓到，抓到之后，执行下面三个语句给关掉
select * from dynamic_func_control('LOCAL', 'STMT', 'UNTRACK', '{"3267119089"}'); -- 取消抓取
select * from dynamic_func_control('LOCAL', 'STMT', 'LIST', '{}');
select * from dynamic_func_control('LOCAL', 'STMT', 'CLEAN', '{}');

-- 添加SQL Patch 是在应用数据库中
select * from gs_sql_patch where unique_sql_id = 1709163987;
select * from dbe_sql_util.drop_sql_patch('20220630_cplan');
select * from dbe_sql_util.create_hint_sql_patch('20220630_cplan', 1709163987, 'indexscan(orderLine_ hord_order_line_t_contract_number_cfg_instance_id_idx)');


select extract(epoch from(t.finish_time-t.start_time)),t.* from dbe_perf.statement_history t where t.unique_query_id=2506848261;

统计信息

select pg_catalog.pg_stat_get_session_wlmstat(45708); --返回当前会话负载信息

select * from pg_stat_all_tables;--当前表相关的统计信息

select * from pg_stat_user_indexes;--索引的统计信息

select * from pg_stats t where t.tablename='table_name';--pg_stats视图提供对存储在pg_statistic表里面的列维度统计信息的访问

 select oid, relname, reltuples, relpages, relallvisible from pg_class where relname in( 'table_name', 'index_name');


set default_statistics_target =1000; -- 设置当前session内 表分析的采样数，实际行数 值*300

show default_statistics_target;

set work_mem='10GB'; -- 设置采样使用的内存，会限制实际的采样行数

show work_mem;

analyze table_name; -- 做表分析

select * from pg_stats where tablename = 'table_name' and attname = 'column_name'; -- 看统计信息是否有刷新

select * from pg_class where relname in( 'table_name');

select * from pg_stat_user_tables where relname = 'table_name' ;

SQL Patch

--查看级别
show track_stmt_stat_level;
--OFF,L0
--采集执行信息开启
select * from dynamic_func_control('GLOBAL', 'STMT', 'TRACK', '{"952624811"}');
--关闭
select * from dynamic_func_control('GLOBAL', 'STMT', 'UNTRACK', '{"952624811"}');
--绑定sql patch , 就是hint的内容
select * from dbe_sql_util.create_hint_sql_patch('20241104_gplan2', 1206660899 , 'use_gplan'); 
select * from dbe_sql_util.drop_sql_patch('20241104_gplan2'); 
--查看有哪些绑定过
select * from gs_sql_patch;

--对于绑定变量的sql
prepare p1 as(SQL);
--说明：where 条件中的参数使用$1，$2，$3 等替换，根据参数个数依次递增
explain execute p1('parameter1','parameter2',parameter3);
--说明：()中的值依次对应$1，$2，$3，注意顺序
--执行
explain analyze excute p1('parameter1','parameter2',parameter3);