centos7配置yum源
sudo cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
sudo vim /etc/yum.repos.d/CentOS-Base.repo
[base]
name=CentOS-$releasever - Base - mirrors.aliyun.com
baseurl=http://mirrors.aliyun.com/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7
[updates]
name=CentOS-$releasever - Updates - mirrors.aliyun.com
baseurl=http://mirrors.aliyun.com/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7
[extras]
name=CentOS-$releasever - Extras - mirrors.aliyun.com
baseurl=http://mirrors.aliyun.com/centos/$releasever/extras/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7
[centosplus]
name=CentOS-$releasever - CentOSPlus - mirrors.aliyun.com
baseurl=http://mirrors.aliyun.com/centos/$releasever/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7
sudo yum clean all
sudo yum makecache
在线扩容根目录
sudo yum -y install cloud-utils-growpart
sudo growpart /dev/sda 3
sudo xfs_growfs /dev/sda3
PG分析会话的CPU占用
pids=$(top -b -n 1 | awk 'NR>7 {print $1}' | head -n 10 | tr '\n' ',' | sed 's/,$//')
psql -d postgres -c "SELECT * FROM pg_stat_activity WHERE pid IN ($pids)"
--分析每个postgres子进程的资源占用
#!/bin/bash
# 查询当前活动连接并提取 PID
pids=$(psql -d postgres -p 6543 -c "SELECT pid FROM pg_stat_activity where state = 'active'" -t)
# 遍历所有 PID 并获取对应的 CPU和内存 使用情况
for pid in $pids
do
ps -o pid,%cpu,vsz,rss,comm -p $pid
done
每日产生的wal文件数
# /XXOO/替换为实际wal文件目录
ls -lh --time-style="+%Y%m%d" /XXOO/ | grep '^-' | awk '{x[$6]++;} END{for(i in x) print(i "|" x[i])}'
检查系统资源
top ##cpu和内存
iostat -ctx 2 30 ##磁盘IO
数据库连接
select state,count(*) from pg_stat_activity group by state order by 2;
while true;do psql -c 'select state,count(*) from pg_stat_activity group by state order by 2;'; date;sleep 5; done
数据库内存占用
select * from pg_total_memory_detail;
查看连接的内存占用
select a.client_addr,a.usename,a.state,a.query_start,a.pid,left(a.query,50),sum(d.totalsize/1000/1000)||'MB' from dbe_perf.session_memory_detail d,pg_stat_activity a where a.pid=right(d.sessid,14) group by 1,2,3,4,5,6 order by 7 desc;
长事务
select
datname,pid,usename,client_addr,application_name,client_hostname,
xact_start, now() - xact_start xact_duration,
query_start, now() - query_start query_duration,
STATE,query
from pg_stat_activity
where STATE <> 'idle'
and now()-xact_start > interval '2 minutes'
order by xact_start;
阻塞
WITH t_wait AS
(SELECT
a.mode,a.locktype, a.database,a.relation,
a.page,a.classid, a.objid,a.objsubid,
a.pid,a.virtualtransaction, a.virtualxid,a.transactionid,
b.query,b.xact_start, b.query_start,b.usename,b.datname
FROM pg_locks a,pg_stat_activity b
WHERE a.pid = b.pid AND NOT a.granted),
t_run AS
(SELECT a.mode,a.locktype,
a.database,a.relation, a.page,a.tuple,
a.classid,a.objid, a.objsubid,a.pid,
a.virtualtransaction,a.virtualxid, a.transactionid,b.query,
b.xact_start,b.query_start, b.usename,b.datname
FROM pg_locks a,pg_stat_activity b
WHERE a.pid = b.pid AND a.granted)
SELECT
r.locktype,r.mode r_mode,
r.usename r_user,r.datname r_db,
r.relation::regclass::regclass,r.pid r_pid,
r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,
w.mode w_mode,w.pid w_pid,
w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
FROM t_wait w, t_run r
WHERE r.locktype IS NOT DISTINCT FROM w.locktype
AND r.database IS NOT DISTINCT FROM w.database
AND r.relation IS NOT DISTINCT FROM w.relation
AND r.page IS NOT DISTINCT FROM w.page
AND r.tuple IS NOT DISTINCT FROM w.classid
AND r.classid IS NOT DISTINCT FROM w.classid
AND r.objid IS NOT DISTINCT FROM w.objid
AND r.objsubid IS NOT DISTINCT FROM w.objsubid
AND r.transactionid IS NOT DISTINCT FROM w.transactionid
AND r.pid <> w.pid;
--阻塞信息,只有dml
select
waiting1.pid as waiting_pid,
waiting2.usename as waiting_user,
waiting2.query as waiting_statement,
blocking1.pid as blocking_pid,
blocking2.usename as blocking_user,
blocking2.query as blocking_statement
from
pg_locks waiting1
join pg_Stat_activity waiting2 on
waiting1.pid = waiting2.pid
join pg_locks blocking1 on
waiting1.transactionid = blocking1.transactionid
and waiting1.pid != blocking1.pid
join pg_stat_activity blocking2 on
blocking1.pid = blocking2.pid
where
not waiting1.granted;
查看日志
grep -E 'FATAL|ERROR' postgresql.log
缓冲区命中率
select
round(sum(blks_hit)*100/sum(blks_hit+blks_read),2)::numeric
from pg_stat_database where datname = current_database();
重复索引
SELECT indrelid::regclass table_name,
att.attname column_name,
amname index_method
FROM pg_index i,
pg_class c,
pg_opclass o,
pg_am a,
pg_attribute att
WHERE o.oid = ALL (indclass)
AND att.attnum = ANY(i.indkey)
AND a.oid = o.opcmethod
AND att.attrelid = c.oid
AND c.oid = i.indrelid
GROUP BY table_name,
att.attname,
indclass,
amname, indkey
HAVING count(*) > 1;
# 详细版
SELECT indrelid::regclass::text AS table
, idx_columns
, indexrelid::regclass::text AS index
FROM pg_index i
, LATERAL (
SELECT string_agg(attname, ', ') AS idx_columns
FROM pg_attribute
WHERE attrelid = i.indrelid
AND attnum = ANY(i.indkey) -- 0 excluded by: indexprs IS NULL
) a
WHERE EXISTS (
SELECT FROM pg_index
WHERE indrelid = i.indrelid
AND indkey = i.indkey
AND indexrelid <> i.indexrelid -- exclude self
)
AND indexprs IS NULL -- exclude expression indexes
ORDER BY 1, 2, 3;
vacuum时间与死元组占比
select schemaname,relname,n_live_tup,n_dead_tup,n_dead_tup::float/(n_dead_tup::float+n_live_tup::float+100)*100 as percent_of_dead_tuples,last_autovacuum from pg_stat_user_tables;
表膨胀
--查看膨胀表
--otta 评估出的表实际需要页数,
--iotta 评估出的索引实际需要页数
--bs 数据库的块大小
--tbloat表膨胀倍数
--ibloat索引膨胀倍数
--wastedpages表浪费了多少个数据块
--wastedipages索引浪费了多少个数据块
--wastedbytes表浪费了多少字节
--wastedibytes索引浪费了多少字节
SELECT
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::text || $$ bytes$$ END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::text || $$ bytes$$ END AS wastedisize,
CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting($$block_size$$)::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)
IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind=$$r$$
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml order by wastedbytes desc limit 5;
索引膨胀
--查看膨胀索引
--otta 评估出的表实际需要页数,
--iotta 评估出的索引实际需要页数
--bs 数据库的块大小
--tbloat表膨胀倍数
--ibloat索引膨胀倍数
--wastedpages表浪费了多少个数据块
--wastedipages索引浪费了多少个数据块
--wastedbytes表浪费了多少字节
--wastedibytes索引浪费了多少字节
SELECT
schemaname, tablename, reltuples::bigint, relpages::bigint, otta,
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
relpages::bigint - otta AS wastedpages,
bs*(sml.relpages-otta)::bigint AS wastedbytes,
pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,
iname, ituples::bigint, ipages::bigint, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN pg_size_pretty(0::bigint) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint)
END AS wastedisize
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples,
COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta
-- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
WHERE sml.relpages - otta > 0 OR ipages - iotta > 10
ORDER BY wastedbytes DESC, wastedibytes DESC limit 10;
垃圾数据top5
select current_database(),schemaname,relname,n_dead_tup
from pg_stat_all_tables
where n_live_tup>0
and n_dead_tup/n_live_tup>0.2
and schemaname not in ($$pg_toast$$,$$pg_catalog$$)
order by n_dead_tup desc limit 5;
年龄
--查看数据库剩余年龄
select datname,age(datfrozenxid),2^31-age(datfrozenxid) age_remain from pg_database order by age(datfrozenxid);
--查看表年龄
SELECT
current_database(),
rolname,
nspname
relkind,
relname,
age(relfrozenxid),
2^31-age(relfrozenxid) age_remain
from pg_authid tl
JOIN pg_class t2 ON tl.oid = t2.relowner
JOIN pg_namespace t3 ON t2.relnamespace = t3.oid
WHERE t2.relkind IN ('t','r')
ORDER BY age(relfrozenxid) DESC
limit 5;
磁盘读写
混合随机读写（7/3），8k，100并发、压力100%（模拟贴近数据库读写压力）
fio --direct=1 --iodepth=10 --bs=8k --size=1G --numjobs=100 --runtime=300 --group_reporting --rw=randrw --rwmixread=70 --name=test --filename=/vbback/randrw.file
--dd 测试磁盘随机生成数据内容 顺序
dd if=/dev/urandom of=/dev/sdc bs=5G count=1 oflag=dsync
--dd 测试磁盘顺序生成数据内容 顺序
dd if=/dev/zero of=/dev/zero bs=5G count=1 oflag=dsync
锁等待
with t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.virtualxid is not distinct from w.virtualxid and
r.transactionid is not distinct from w.transactionid and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.pid <> w.pid
)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||
'SQL (Current SQL in Transaction): '||chr(10)||
case when query is null then 'NULL' else query::text end,
chr(10)||'--------'||chr(10)
order by
( case mode
when 'INVALID' then 0
when 'AccessShareLock' then 1
when 'RowShareLock' then 2
when 'RowExclusiveLock' then 3
when 'ShareUpdateExclusiveLock' then 4
when 'ShareLock' then 5
when 'ShareRowExclusiveLock' then 6
when 'ExclusiveLock' then 7
when 'AccessExclusiveLock' then 8
else 0
end ) desc,
(case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;
慢SQL
# 平均执行耗时最长的SQL
# for pg10/11/12
select replace(query,chr(10), ' ') as query, calls, total_time, (total_time/calls) as average ,rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
from pg_stat_statements
order by average desc LIMIT 10;
# for pg 13+
select replace(query,chr(10), ' ') as query, calls, total_exec_time, (total_exec_time/calls) as average ,rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
from pg_stat_statements
order by average desc LIMIT 10;
# 消耗CPU最长的SQL
# for pg 10/11/12
--消耗cpu时间超过所有SQL语句消耗的总CPU时间的5%的SQL语句
--设置显示sql长度,重启生效
alter system set track_activity_query_size=4096；
--先清除累计的统计信息
select pg_stat_statements_reset();
--然后等一段时间后再查询如下的语句
with total as
(select sum(total_time) as total_exec_time,
sum(blk_read_time + blk_write_time) as io_time,
sum(total_time - blk_read_time - blk_write_time) as cpu_time,
sum(calls) as ncalls,
sum(rows) as total_rows
from pg_stat_statements
where dbid in
(select oid from pg_database where datname = current_database()))
select userid::regrole,dbid,calls,pss.total_time,min_time,max_time,rows,shared_blks_hit,shared_blks_read,
shared_blks_dirtied,shared_blks_written,local_blks_hit,local_blks_read,local_blks_dirtied,local_blks_written,
temp_blks_read,temp_blks_written,blk_read_time,blk_write_time,io_time,cpu_time,total_rows,
(pss.total_time - pss.blk_read_time - pss.blk_write_time) / total.cpu_time * 100 as cpu_pct,
replace(query,chr(10), ' ') as query
from pg_stat_statements pss, total
where (pss.total_time - pss.blk_read_time - pss.blk_write_time) / total.cpu_time >= 0.1
order by pss.total_time - pss.blk_read_time - pss.blk_write_time desc;
# for pg13+
--消耗cpu时间超过所有SQL语句消耗的总CPU时间的5%的SQL语句
--设置显示sql长度,重启生效
alter system set track_activity_query_size=4096；
--先清除累计的统计信息
select pg_stat_statements_reset();
--然后等一段时间后再查询如下的语句
with total as
(select sum(total_exec_time) as total_exec_time,
sum(blk_read_time + blk_write_time) as io_time,
sum(total_exec_time - blk_read_time - blk_write_time) as cpu_time,
sum(calls) as ncalls,
sum(rows) as total_rows
from pg_stat_statements
where dbid in
(select oid from pg_database where datname = current_database()))
select userid::regrole,dbid,calls,pss.total_exec_time,min_exec_time,max_exec_time,rows,shared_blks_hit,shared_blks_read,
shared_blks_dirtied,shared_blks_written,local_blks_hit,local_blks_read,local_blks_dirtied,local_blks_written,
temp_blks_read,temp_blks_written,blk_read_time,blk_write_time,io_time,cpu_time,total_rows,
(pss.total_exec_time - pss.blk_read_time - pss.blk_write_time) / total.cpu_time * 100 as cpu_pct,
replace(query,chr(10), ' ') as query
from pg_stat_statements pss, total
where (pss.total_exec_time - pss.blk_read_time - pss.blk_write_time) / total.cpu_time >= 0.1
order by pss.total_exec_time - pss.blk_read_time - pss.blk_write_time desc;
# 消耗IO时间超过所有SQL语句消耗的总IO时间的5%的SQL语句--for pg10+
with total as
(select sum(total_time) as total_time,
sum(blk_read_time + blk_write_time) as io_time,
sum(total_time - blk_read_time - blk_write_time) as cpu_time,
sum(calls) as ncalls,
sum(rows) as total_rows
from pg_stat_statements
where dbid in
(select oid from pg_database where datname = current_database()))
select *,
(pss.blk_read_time + pss.blk_write_time) / total.io_time * 100 as io_pct
from pg_stat_statements pss, total
where (pss.blk_read_time + pss.blk_write_time) / total.io_time >= 0.05
and total.io_time > 0
order by pss.blk_read_time + pss.blk_write_time desc;
计算内核参数
echo "kernel.sem= `cat /proc/sys/kernel/sem`" && cat /proc/meminfo|grep MemTotal|awk {'print $2'*1024*0.8/$(getconf PAGE_SIZE)}|awk '{printf "kernel.shmall=%d\n",$1}' && cat /proc/meminfo|grep MemTotal| awk '{printf "kernel.shmmax=%d\n",$2*1024*0.8}' && echo "kernel.shmmni =`cat /proc/sys/kernel/shmmni`"
复制槽
-- 1. 查看所有逻辑复制槽（slot_type = 'logical'）
select * from pg_replication_slots where slot_type = 'logical';
-- 2. 判断复制槽是否存在（替换 'your_slot_name'）
SELECT EXISTS (
SELECT 1
FROM pg_replication_slots
WHERE slot_type = 'logical' AND slot_name = 'your_slot_name'
) AS slot_exists;
-- 3. 查看逻辑复制槽是否活跃（active 表示是否被订阅端占用）
SELECT slot_name, active
FROM pg_replication_slots
WHERE slot_type = 'logical';
-- 4. 检查逻辑槽延迟（WAL 堆积字节数），判断是否可能失效或卡住
SELECT
slot_name,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS wal_delay,
pg_current_wal_lsn() AS current_lsn,
confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';
-- 5. 查看逻辑订阅延迟（订阅端使用，查询 pg_stat_subscription）
SELECT
subname,
received_lsn,
latest_end_lsn,
latest_end_time,
now() AS now,
EXTRACT(EPOCH FROM now() - latest_end_time)::int AS delay_seconds
FROM pg_stat_subscription;
数据导出
######将数据导出到txt文件*************************************************
su - vastbase
vsql -r -d dbname -U username -W password -c "\copy (select * from t_t1) to '/tmp/t_t1_v1.txt' with delimiter ','"
pg_freespacemap
# 计算表的平均空间空闲率
create extension pg_freespacemap;
select count(*) as "number of pages",
pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
from pg_freespace('test');
# 查询检查特定表中每个页面的自由空间占比
select *,round(100 * avail/8192 ,2) as "freespace ratio" from pg_freespace('test');
pageinspect
# 查看表在页面的实际存储
create extension pageinspect;
select * from heap_page_items(get_raw_page('test',0));
# 查看页头
SELECT * from page_header(get_raw_page('test', 0));
# 一个页面有多少行数据
select count(*) from heap_page_items(get_raw_page('test',0));
# 一个表有多少行数据
select count(*) from test;
# 两者相除，可大致估算出一个表有多少页
计算某个schema下有多少张表
select count(*) from information_schema.tables where table_schema='sjzx';
VB查数据库年龄
select datname,age(datfrozenxid64),2^31-age(datfrozenxid64) age_remain from pg_database order by age(datfrozenxid64);
SELECT age(pgo.ctime) FROM pg_object pgo,pg_class pgc WHERE pgc.oid = pgo.object_oid AND pgc.relname = 'table_name';
VB查阻塞
WITH t_wait AS
(SELECT
a.mode,a.locktype, a.database,a.relation,
a.page,a.classid, a.objid,a.objsubid,
a.pid,a.virtualtransaction, a.virtualxid,a.transactionid,
b.query,b.xact_start, b.query_start,b.usename,b.datname
FROM pg_locks a,pg_stat_activity b
WHERE a.pid = b.pid AND NOT a.granted),
t_run AS
(SELECT a.mode,a.locktype,
a.database,a.relation, a.page,a.tuple,
a.classid,a.objid, a.objsubid,a.pid,
a.virtualtransaction,a.virtualxid, a.transactionid,b.query,
b.xact_start,b.query_start, b.usename,b.datname
FROM pg_locks a,pg_stat_activity b
WHERE a.pid = b.pid AND a.granted)
SELECT
r.locktype,r.mode r_mode,
r.usename r_user,r.datname r_db,
r.relation::regclass::regclass,r.pid r_pid,
r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,
w.mode w_mode,w.pid w_pid,
w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
FROM t_wait w, t_run r
WHERE r.locktype IS NOT DISTINCT FROM w.locktype
AND r.database IS NOT DISTINCT FROM w.database
AND r.relation IS NOT DISTINCT FROM w.relation
AND r.page IS NOT DISTINCT FROM w.page
AND r.tuple IS NOT DISTINCT FROM w.classid
AND r.classid IS NOT DISTINCT FROM w.classid
AND r.objid IS NOT DISTINCT FROM w.objid
AND r.objsubid IS NOT DISTINCT FROM w.objsubid
AND r.transactionid IS NOT DISTINCT FROM w.transactionid
AND r.pid <> w.pid;
查看表结构
select pg_get_tabledef('表名');
切换vastbase用户时自动加载数据库环境变量
echo -e "\nsource /home/vastbase/.Vastbase" >> /home/vastbase/.bashrc
VB查数据库内存
select case when
memorytype='max_process_memory' then 'VastbaseG100实例允许占用的最大内存'
when memorytype='process_used_memory' then '数据库进程占用的内存'
when memorytype='max_dynamic_memory' then '最大动态内存'
when memorytype='dynamic_used_memory' then '已使用的动态内存'
when memorytype='dynamic_peak_memory' then '内存的动态峰值'
when memorytype='dynamic_used_shrctx' then '最大动态共享内存上下文'
when memorytype='dynamic_peak_shrctx' then '共享内存上下文的动态峰值'
when memorytype='max_shared_memory' then '最大共享内存'
when memorytype='shared_used_memory' then '已使用的共享内存'
when memorytype='max_cstore_memory' then '列存所允许使用的最大内存'
when memorytype='cstore_used_memory' then '列存已使用的内存大小'
when memorytype='max_sctpcomm_memory' then 'sctp通信所允许使用的最大内存'
when memorytype='sctpcomm_used_memory' then 'sctp通信已使用的内存大小'
when memorytype='sctpcomm_peak_memory' then 'sctp通信的内存峰值'
when memorytype='other_used_memory' then '其他已使用的内存大小'
when memorytype='gpu_max_dynamic_memory' then 'GPU最大动态内存'
when memorytype='gpu_dynamic_used_memory' then 'GPU已使用的动态内存'
when memorytype='gpu_dynamic_peak_memory' then 'GPU内存的动态峰值'
when memorytype='pooler_conn_memory' then '链接池申请内存计数'
when memorytype='pooler_freeconn_memory' then '链接池空闲连接的内存计数'
when memorytype='storage_compress_memory' then '存储模块压缩使用的内存大小'
when memorytype='udf_reserved_memory' then 'UDF预留的内存大小'
else memorytype end as memorytype
,memorymbytes from pg_total_memory_detail;
--查看内存耗在哪里
select sessid,contextname,totalsize/1024/1024 as totalsize,freesize/1024/1024 freesize,usedsize/1024/1024 as usedsize from dbe_perf.session_memory_detail group by sessid,contextname order by 3 desc limit 5;
select sessid,contextname,sum(totalsize)/1024/1024 sum,sum(freesize)/1024/1024 freesize,count(*) count from gs_session_memory_detail group by sessid,contextname order by sum desc limit 10;
with t1 as (select sessid, sum(totalsize) totalsize, sum(usedsize) usedsize from gs_session_memory_detail group by 1) select a.sessionid, a.datname, a.usename, a.application_name, a.client_addr, pg_size_pretty(totalsize) total_size, pg_size_pretty(usedsize) used_size, a.query from pg_stat_activity a, t1 where a.sessionid = split_part(sessid,'.',2) order by usedsize desc;
查询阻塞
-- 查询阻塞会话的SQL
--简化版
select ws.sessionid, --被阻塞会话ID
ws.wait_status, -- 等待状态
ws.wait_event, -- 等待事件
ws.lockmode, -- 锁模式
substr(at.query, 0, 50) as query, -- 被阻塞语句
ws.block_sessionid -- 造成阻塞的会话ID
from pg_thread_wait_status as ws, pg_stat_activity as at
where ws.sessionid = at.pid
and ws.wait_status = 'acquire lock';
--详情版
with t_lock as (select psa.pid as ssid,psa.*,pl.* from pg_stat_activity psa,pg_locks pl where psa.pid=pl.pid and psa.pid<>pg_backend_pid())
select
l.relation::regclass, -- 被锁定的表名
l.locktype as l_locktype, -- 锁类型（持有锁的会话）
l.mode as l_mode, -- 锁模式（持有锁的会话）
l.usename as l_user, -- 用户名（持有锁的会话）
l.datname as l_db, -- 数据库名（持有锁的会话）
l.ssid as l_pid, -- 会话ID（持有锁的会话）
l.state as l_state, -- 会话状态（持有锁的会话）
l.xact_start as l_xact_start, -- 事务开始时间（持有锁的会话）
l.query_start as l_query_start, -- 查询开始时间（持有锁的会话）
now() - l.query_start as l_locktime, -- 锁持有时间（持有锁的会话）
l.query as l_query, -- 持有锁的查询语句
b.locktype as b_locktype, -- 锁类型（被阻塞的会话）
b.mode as b_mode, -- 锁模式（被阻塞的会话）
b.ssid as b_pid, -- 会话ID（被阻塞的会话）
b.state as b_state, -- 会话状态（被阻塞的会话）
b.xact_start as b_xact_start, -- 事务开始时间（被阻塞的会话）
b.query_start as b_query_start, -- 查询开始时间（被阻塞的会话）
now() - b.query_start as b_locktime, -- 锁等待时间（被阻塞的会话）
b.query as b_query -- 被阻塞的查询语句
from t_lock l, t_lock b
where l.locktype is not distinct from b.locktype
and l.database is not distinct from b.database
and l.relation is not distinct from b.relation
and l.page is not distinct from b.page
and l.tuple is not distinct from b.classid
and l.classid is not distinct from b.classid
and l.objid is not distinct from b.objid
and l.objsubid is not distinct from b.objsubid
and l.transactionid is not distinct from b.transactionid
and l.granted = true
and b.granted = false
and l.ssid <> b.ssid;
postgres查询所有用户及其状态
SELECT rolname AS 用户名,
CASE WHEN rolcanlogin THEN '允许登录' ELSE '禁止登录' END AS 登录权限,
CASE WHEN rolsuper THEN '超级用户' ELSE '普通用户' END AS 用户类型,
CASE WHEN rolvaliduntil IS NULL THEN '永久有效'
WHEN rolvaliduntil > now() THEN '生效中 '
ELSE '已过期' END AS 账号状态,
rolconnlimit AS 最大连接数,
pg_user.usename IS NOT NULL AS 当前已连接
FROM pg_roles
LEFT JOIN pg_user ON pg_user.usename = pg_roles.rolname
WHERE rolcanlogin -- 只看能登录的账号
ORDER BY rolname;
查询一段时间开始的慢sql
select unique_sql_id as sqlid,user_name as "用户名称",query as "SQL文本",n_calls as 执行次数, last_updated as "最近一次执行时间",min_elapse_time/1000 as "最快执行耗时ms",max_elapse_time/1000 as "最慢执行耗时ms",n_returned_rows as "结果集行数",n_tuples_fetched as "读取行数",n_tuples_inserted as "写入行数",n_tuples_updated as "更新行数",n_tuples_deleted as "删除行数",n_blocks_fetched as "读取块数",n_blocks_hit as "缓存命中块数",data_io_time as "硬盘IO时间",sort_time as "排序耗时"
from dbe_perf.statement
where last_updated >= '2025-10-30 00:00:00'
and user_name <> 'vastbase'
and "最慢执行耗时ms" > 500
order by min_elapse_time desc;
查询等待事件
select wait_status,wait_event ,count(1) from pg_thread_wait_status group by 1,2 order by 3 desc;
查询慢sql
select start_time,round(db_time/1000,0) as db_time,
round(cpu_time/1000,0) as db_time,
round(execution_time/1000,0) as exec_time,
round(parse_time/1000,0) as parse_time,
round(plan_time/1000,0) as plan_time,
query,query_plan
from statement_history
where
start_time>='2025-10-30 07:15:18'
and start_time<='2025-10-30 18:00:00'
and user_name='u8c'
and application_name='Vastbase JDBC Driver'
order by start_time desc;
Comments NOTHING