opengauss教程FG067-openGauss系统表与数据字典查询生产实战

本文档详细介绍openGauss数据库系统表与数据字典的查询方法，包括会话信息、表结构信息、性能统计等内容，风哥教程参考openGauss官方文档系统目录与视图、系统表等内容，适合DBA人员进行数据库诊断和优化时参考。

Part01-基础概念与理论知识

1.1 openGauss系统表基本概念

openGauss系统表特点：

元数据存储：存储数据库对象的定义信息
动态更新：随数据库操作实时更新
只读视图：部分系统表以只读视图形式提供
性能统计：包含丰富的性能统计数据

1.2 openGauss信息schema

Information Schema是SQL标准定义的系统表集合：

# Information Schema

1. 常用信息schema视图
– information_schema.tables：表信息
– information_schema.columns：列信息
– information_schema.views：视图信息
– information_schema.routines：存储过程信息
– information_schema.table_constraints：约束信息
– information_schema.key_column_usage：主键外键信息

2. 查询示例
# 查询fgedu用户的表
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = ‘fgedu’;

# 查询表字段信息
SELECT
column_name,
data_type,
character_maximum_length,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = ‘fgedu’
AND table_name = ‘users’
ORDER BY ordinal_position;

3. Information Schema优点
– 符合SQL标准，跨数据库兼容性好
– 查询语法统一，易于记忆
– 提供一致的数据类型和格式

4. Information Schema缺点
– 性能相对较低
– 部分openGauss特有信息未包含
– 无法获取实时性能统计数据

1.3 openGauss pg_catalog体系

pg_catalog是openGauss的核心系统表集合：

# pg_catalog体系

1. 对象定义表
– pg_class：表、视图、序列等对象信息
– pg_attribute：表字段属性信息
– pg_type：数据类型定义
– pg_namespace：模式空间定义
– pg_database：数据库定义

2. 用户与权限表
– pg_roles：角色信息
– pg_authid：认证信息
– pg_permission：权限信息
– pg_default_acl：默认权限

3. 索引相关表
– pg_index：索引定义
– pg_indexes：索引详情
– pg_stat_user_indexes：索引使用统计

4. 约束相关表
– pg_constraint：约束定义
– pg_depend：对象依赖关系

5. 统计信息表
– pg_stat_activity：会话活动统计
– pg_stat_database：数据库统计
– pg_stat_user_tables：表使用统计
– pg_stat_user_indexes：索引使用统计
– pg_stat_replication：复制状态统计

6. 锁相关表
– pg_locks：当前锁信息
– pg_blocking_pids：阻塞会话信息

# pg_catalog优势
– 包含openGauss特有的统计信息
– 性能数据更详细准确
– 支持实时监控和诊断

Part02-生产环境规划与建议

2.1 openGauss系统表架构

系统表架构设计：

# 系统表架构

2. 统计信息表关系
pg_stat_activity
|风哥提示：
+—-> pg_locks
|
+—-> pg_blocking_pids

pg_stat_database
|
+—-> pg_stat_user_tables
|
+—-> pg_stat_user_indexes

3. 系统表访问层次
– 底层：pg_catalog系统表
– 中层：information_schema视图
– 顶层：gs工具和系统函数

4. 系统表更新机制
– DDL操作：实时更新
– 统计数据：周期性收集
– 性能数据：实时更新
– 复制状态：实时更新

2.2 openGauss常用查询场景

常用查询场景：

# 常用查询场景

1. 会话相关查询
# 查看当前所有会话
SELECT 学习交流加群风哥微信: itpux-com
pid,
usename,
application_name,
client_addr,
state,
query_start,
state_change,
wait_event_type,
wait_event,
LEFT(query, 100) as query_text
FROM pg_stat_activity
ORDER BY query_start;

# 查看活跃会话
SELECT
pid,
usename,
query_start,
LEFT(query, 200) as query
FROM pg_stat_activity
WHERE state = ‘active’
AND state_change < now() - interval '5 minutes'; 2. 锁相关查询 # 查看当前锁 SELECT l.locktype, l.relation::regclass, l.mode, l.granted, l.pid, a.usename, a.query FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid ORDER BY l.granted, l.pid; # 查看阻塞会话 SELECT blocked_locks.pid AS blocked_pid,学习交流加群风哥QQ113257174 blocked_activity.usename AS blocked_user, blocked_activity.query AS blocked_query, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocking_activity.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted; 3. 表相关查询 # 查看表大小 SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size FROM pg_tables WHERE schemaname = 'fgedu' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC; 4. 索引相关查询 # 查看索引使用情况 SELECT schemaname, tablename, indexname,更多视频教程www.fgedu.net.cn idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelid)) as index_size FROM pg_stat_user_indexes ORDER BY idx_scan;

2.3 openGauss查询优化建议

系统表查询优化：

# 系统表查询优化

1. 性能优化原则
– 避免全表扫描：使用WHERE条件过滤
– 使用索引：系统表通常有索引
– 限制返回：使用LIMIT限制结果集
– 避免函数：减少不必要的函数调用

2. 常用优化技巧
# 低效写法
SELECT * FROM pg_stat_activity;

# 高效写法
SELECT pid, usename, state
FROM pg_stat_activity
WHERE state = ‘active’;

3. 监控查询优化
# 频繁执行的监控查询应该简单高效
# 避免使用复杂的JOIN
# 使用物化视图缓存结果

# 创建监控物化视图
CREATE MATERIALIZED VIEW mv_active_sessions AS
SELECT
pid,
usename,
application_name,
state,更多学习教程公众号风哥教程itpux_com
query_start
FROM pg_stat_activity
WHERE state = ‘active’;

CREATE UNIQUE INDEX idx_mv_active_sessions_pid ON mv_active_sessions(pid);

# 刷新物化视图
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_active_sessions;

4. 批量查询优化
# 使用分页查询
SELECT *
FROM pg_stat_activity
ORDER BY pid
LIMIT 100 OFFSET 0;

Part03-生产环境项目实施方案

3.1 openGauss会话查询

# 会话查询

#!/bin/bash
# session_query.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`from DB视频:www.itpux.com

DB_IP=”192.168.1.10″

echo “=== openGauss会话信息查询 ===”
echo “查询时间: $(date)”

# 1. 查看所有会话
echo “”
echo “— 当前所有会话 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
pid,
usename,
application_name,
client_addr,
state,
TO_CHAR(query_start, ‘YYYY-MM-DD HH24:MI:SS’) as query_start,
TO_CHAR(state_change, ‘YYYY-MM-DD HH24:MI:SS’) as state_change,
EXTRACT(EPOCH FROM (now() – state_change)) as idle_seconds
FROM pg_stat_activity
ORDER BY state_change;
”

# 2. 查看活跃会话
echo “”
echo “— 活跃会话 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
pid,
usename,
application_name,
wait_event_type,
wait_event,
TO_CHAR(query_start, ‘YYYY-MM-DD HH24:MI:SS’) as query_start,
EXTRACT(EPOCH FROM (now() – query_start)) as duration_seconds,
LEFT(query, 200) as query_text
FROM pg_stat_activity
WHERE state = ‘active’
ORDER BY query_start;
”

# 3. 查看空闲会话
echo “”
echo “— 空闲会话 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
pid,
usename,
application_name,
client_addr,
EXTRACT(EPOCH FROM (now() – state_change)) as idle_seconds
FROM pg_stat_activity
WHERE state = ‘idle’
ORDER BY state_change;
”

# 4. 查看长事务会话
echo “”
echo “— 长事务会话 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
pid,
usename,
application_name,
TO_CHAR(xact_start, ‘YYYY-MM-DD HH24:MI:SS’) as xact_start,
TO_CHAR(query_start, ‘YYYY-MM-DD HH24:MI:SS’) as query_start,
EXTRACT(EPOCH FROM (now() – xact_start)) as transaction_duration
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now() – xact_start > interval ‘1 hour’
ORDER BY xact_start;
”

# 执行结果
=== openGauss会话信息查询 ===
查询时间: Thu Apr 9 10:00:00 CST 2026

— 当前所有会话 —
pid | usename | application_name | client_addr | state | query_start | state_change | idle_seconds
——+———+——————+—————-+——–+———————+———————+————–
1234 | fgedu | app1 | 192.168.1.20 | active | 2026-04-09 09:55:00 | 2026-04-09 09:55:00 | 0
1235 | fgedu | app2 | 192.168.1.21 | idle | 2026-04-09 09:50:00 | 2026-04-09 09:55:30 | 30
1236 | fgedu | app3 | 192.168.1.22 | idle in transaction | 2026-04-09 09:30:00 | 2026-04-09 09:40:00 | 1200
(3 rows)

3.2 openGauss对象查询

# 对象查询

#!/bin/bash
# object_query.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

DB_IP=”192.168.1.10″

echo “=== openGauss对象信息查询 ===”
echo “查询时间: $(date)”

# 1. 查看数据库列表
echo “”
echo “— 数据库列表 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) as size,
numbackends as connections,
xact_commit,
xact_rollback,
blks_hit,
blks_read
FROM pg_stat_database
WHERE datname NOT IN (‘template0’, ‘template1′)
ORDER BY pg_database_size(datname) DESC;
”

# 2. 查看表列表
echo “”
echo “— fgedu用户下的表 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||’.’||tablename)) as total_size,
n_live_tup as row_count,
n_dead_tup as dead_rows,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = ‘fgedu’
ORDER BY pg_total_relation_size(schemaname||’.’||tablename) DESC
LIMIT 20;
”

# 3. 查看表结构
echo “”
echo “— fgedu.users表结构 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = ‘fgedu’
AND table_name = ‘users’
ORDER BY ordinal_position;
”

# 4. 查看索引信息
echo “”
echo “— fgedu.users表索引 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE schemaname = ‘fgedu’
AND tablename = ‘users’;
”

# 5. 查看约束信息
echo “”
echo “— fgedu.users表约束 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
conname,
conrelid::regclass as table_name,
pg_get_constraintdef(oid) as constraint_def
FROM pg_constraint
WHERE conrelid = ‘fgedu.users’::regclass;
”

# 执行结果
=== openGauss对象信息查询 ===
查询时间: Thu Apr 9 10:05:00 CST 2026

— 数据库列表 —
datname | size | connections | xact_commit | xact_rollback | blks_hit | blks_read
———-+———+————-+————-+—————+———–+———–
fgedudb | 500 GB | 50 | 15000000 | 1500 | 15000000 | 150000
(1 row)

— fgedu用户下的表 —
schemaname | tablename | total_size | row_count | dead_rows | last_vacuum | last_autovacuum | last_analyze
————+—————+————+———–+———–+————-+—————–+————-
fgedu | orders | 200 GB | 50000000 | 10000 | | 2026-04-09 | 2026-04-09
fgedu | order_items | 150 GB | 200000000 | 20000 | | 2026-04-09 | 2026-04-09
fgedu | users | 50 GB | 10000000 | 5000 | | 2026-04-09 | 2026-04-09
(3 rows)

3.3 openGauss性能查询

# 性能查询

#!/bin/bash
# performance_query.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

DB_IP=”192.168.1.10″

echo “=== openGauss性能统计查询 ===”
echo “查询时间: $(date)”

# 1. 查看QPS统计
echo “”
echo “— QPS统计 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
datname,
sum(xact_commit + xact_rollback) as total_transactions,
sum(xact_commit) as commits,
sum(xact_rollback) as rollbacks,
round(sum(xact_commit + xact_rollback)::numeric /
EXTRACT(EPOCH FROM (now() – stats_reset)), 2) as tps
FROM pg_stat_database
WHERE datname = ‘fgedudb’
GROUP BY datname, stats_reset;
”

# 2. 查看缓存命中率
echo “”
echo “— 缓存命中率 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
datname,
blks_hit,
blks_read,
round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) as cache_hit_ratio
FROM pg_stat_database
WHERE datname = ‘fgedudb’;
”

# 3. 查看表扫描统计
echo “”
echo “— 表扫描统计 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_tup_hot_upd
FROM pg_stat_user_tables
WHERE schemaname = ‘fgedu’
ORDER BY seq_scan + idx_scan DESC
LIMIT 10;
”

# 4. 查看索引使用统计
echo “”
echo “— 索引使用统计 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE schemaname = ‘fgedu’
ORDER BY idx_scan DESC
LIMIT 10;
”

# 5. 查看等待事件
echo “”
echo “— 等待事件统计 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
wait_event_type,
wait_event,
count(*) as count
FROM pg_stat_activity
WHERE state = ‘active’
AND wait_event_type IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count DESC;
”

# 执行结果
=== openGauss性能统计查询 ===
查询时间: Thu Apr 9 10:10:00 CST 2026

— QPS统计 —
datname | total_transactions | commits | rollbacks | tps
———-+——————–+———-+———–+———
fgedudb | 15000000 | 14998500 | 1500 | 5000.00
(1 row)

— 缓存命中率 —
datname | blks_hit | blks_read | cache_hit_ratio
———-+———-+———–+—————–
fgedudb | 15000000 | 150000 | 99.01
(1 row)

— 表扫描统计 —
schemaname | tablename | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
————+—————+———-+—————+———-+—————+————+————+————
fgedu | orders | 10 | 1000 | 5000000 | 5000000 | 5000000 | 10000000 | 50000
fgedu | order_items | 5 | 500 | 20000000 | 20000000 | 20000000 | 15000000 | 20000
fgedu | users | 2 | 200 | 10000000 | 10000000 | 10000000 | 5000000 | 10000
(3 rows)

Part04-生产案例与实战讲解

4.1 openGauss DBA日常查询案例

4.1.1 案例背景

DBA日常需要定期检查数据库状态、健康情况、性能指标等。

4.1.2 完整查询脚本

# DBA日常查询脚本

#!/bin/bash
# dba_daily_check.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

DB_IP=”192.168.1.10″
DB_NAME=”fgedudb”
LOG_DIR=”/opengauss/log”
DATE=$(date +%Y%m%d)

# 创建日志目录
mkdir -p $LOG_DIR

echo “=========================================”
echo “openGauss DBA日常检查”
echo “检查时间: $(date)”
echo “=========================================”

# 1. 数据库基本状态
echo “”
echo “— 1. 数据库基本状态 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) as size,
numbackends,
xact_commit,
xact_rollback
FROM pg_stat_database
WHERE datname = ‘$DB_NAME’;
”

# 2. 连接状态
echo “”
echo “— 2. 连接状态 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
state,
count(*) as count
FROM pg_stat_activity
GROUP BY state;
”

# 3. 锁状态
echo “”
echo “— 3. 锁等待情况 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
count(*) as lock_wait_count
FROM pg_locks
WHERE NOT granted;
”

# 4. 复制状态
echo “”
echo “— 4. 复制状态 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
application_name,
client_addr,
state,
sync_state,
pg_wal_lsn_diff(sent_lsn, replay_lsn) as replication_lag
FROM pg_stat_replication;
”

# 5. 大表统计
echo “”
echo “— 5. 大表统计 —”
gsql -h $DB_IP -d $DB_NAME -c ”
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||’.’||tablename)) as total_size,
n_live_tup as row_count
FROM pg_stat_user_tables
WHERE schemaname = ‘fgedu’
ORDER BY pg_total_relation_size(schemaname||’.’||tablename) DESC
LIMIT 5;
”

# 6. 慢查询统计
echo “”
echo “— 6. 慢查询（超过5秒） —”
gsql -h $DB_IP -d postgres -c ”
SELECT
pid,
usename,
EXTRACT(EPOCH FROM (now() – query_start)) as duration_seconds,
LEFT(query, 100) as query_text
FROM pg_stat_activity
WHERE state = ‘active’
AND now() – query_start > interval ‘5 seconds’
ORDER BY query_start;
”

# 7. 磁盘空间
echo “”
echo “— 7. 磁盘空间使用 —”
df -h /opengauss/fgdata

echo “”
echo “=========================================”
echo “检查完成”
echo “=========================================”

# 执行结果
=========================================
openGauss DBA日常检查
检查时间: Thu Apr 9 10:30:00 CST 2026
=========================================

— 1. 数据库基本状态 —
datname | size | numbackends | xact_commit | xact_rollback
———-+———+————-+————-+—————
fgedudb | 500 GB | 50 | 15000000 | 1500
(1 row)

— 2. 连接状态 —
state | count
———-+——-
active | 10
idle | 40
(2 rows)

— 3. 锁等待情况 —
lock_wait_count
—————–
0
(1 row)

— 6. 慢查询（超过5秒） —
pid | usename | duration_seconds | query_text
——+———+——————+————
(0 rows)

— 7. 磁盘空间使用 —
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 2.0T 1.3T 700G 65% /opengauss/fgdata
=========================================
检查完成
=========================================

4.2 openGauss故障排查案例

4.2.1 问题描述

应用反馈查询变慢，怀疑是数据库性能问题。

4.2.2 排查步骤

# 故障排查脚本

#!/bin/bash
# troubleshooting.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

DB_IP=”192.168.1.10″

echo “=== openGauss故障排查 ===”
echo “排查时间: $(date)”

# 1. 检查活跃会话
echo “”
echo “— 1. 检查活跃会话 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
pid,
usename,
application_name,
wait_event_type,
wait_event,
query_start,
LEFT(query, 200) as query
FROM pg_stat_activity
WHERE state = ‘active’
ORDER BY query_start;
”

# 2. 检查锁等待
echo “”
echo “— 2. 检查锁等待 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
l.locktype,
l.relation::regclass,
l.mode,
l.granted,
a.usename,
a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY l.relation;
”

# 3. 检查长事务
echo “”
echo “— 3. 检查长事务 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
pid,
usename,
xact_start,
query_start,
EXTRACT(EPOCH FROM (now() – xact_start)) as duration
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now() – xact_start > interval ’10 minutes’
ORDER BY xact_start;
”

# 4. 检查表膨胀
echo “”
echo “— 4. 检查表膨胀 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
schemaname,
tablename,
n_live_tup,
n_dead_tup,
ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_tuple_ratio
FROM pg_stat_user_tables
WHERE schemaname = ‘fgedu’
AND n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
”

# 5. 检查索引使用
echo “”
echo “— 5. 检查未使用索引 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = ‘fgedu’
ORDER BY pg_relation_size(indexrelid) DESC;
”

# 执行结果
=== openGauss故障排查 ===
排查时间: Thu Apr 9 11:00:00 CST 2026

4.3 openGauss优化分析案例

4.3.1 问题描述

数据库整体性能下降，需要分析原因。

4.3.2 分析方案

# 优化分析脚本

#!/bin/bash
# optimization_analysis.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

DB_IP=”192.168.1.10″

echo “=== openGauss性能优化分析 ===”
echo “分析时间: $(date)”

# 1. 缓存命中率分析
echo “”
echo “— 1. 缓存命中率分析 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
datname,
blks_hit,
blks_read,
round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) as cache_hit_ratio,
pg_size_pretty(shared_buffers) as shared_buffers
FROM pg_stat_database, pg_settings
WHERE pg_settings.name = ‘shared_buffers’
AND datname = ‘fgedudb’;
”

# 2. TOP SQL分析
echo “”
echo “— 2. TOP SQL分析 —”
gsql -h $DB_IP -d postgres -c ”
SELECT
LEFT(query, 200) as query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
”

# 3. 表I/O分析
echo “”
echo “— 3. 表I/O分析 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
schemaname,
tablename,
seq_scan,
idx_scan,
seq_tup_read,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del,
heap_blks_read,
heap_blks_hit,
round(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) as heap_hit_ratio
FROM pg_statio_user_tables
WHERE schemaname = ‘fgedu’
ORDER BY heap_blks_read DESC
LIMIT 10;
”

# 4. 索引I/O分析
echo “”
echo “— 4. 索引I/O分析 —”
gsql -h $DB_IP -d fgedudb -c ”
SELECT
schemaname,
tablename,
indexname,
idx_blks_read,
idx_blks_hit,
round(100.0 * idx_blks_hit / NULLIF(idx_blks_hit + idx_blks_read, 0), 2) as idx_hit_ratio
FROM pg_statio_user_indexes
WHERE schemaname = ‘fgedu’
ORDER BY idx_blks_read DESC
LIMIT 10;
”

# 执行结果
=== openGauss性能优化分析 ===
分析时间: Thu Apr 9 11:30:00 CST 2026

— 2. TOP SQL分析 —
query | calls | total_exec_time | mean_exec_time | max_exec_time | rows
——————————+———+—————–+—————-+—————-+——–
INSERT INTO fgedu.orders… | 500000 | 500000.00 | 1.00 | 10.00 | 500000
UPDATE fgedu.orders SET… | 300000 | 450000.00 | 1.50 | 20.00 | 300000
SELECT * FROM fgedu.users… | 100000 | 300000.00 | 3.00 | 30.00 | 100000
(3 rows)

Part05-风哥经验总结与分享

5.1 openGauss系统表最佳实践

系统表使用最佳实践：

定期检查：建立定期检查机制，及时发现问题
脚本化：将常用查询封装成脚本，提高效率
监控告警：基于系统表建立监控和告警
性能注意：避免频繁查询大结果集的系统表
权限控制：合理分配系统表查询权限

5.2 openGauss常用脚本集

# 常用脚本集

1. 会话管理脚本
#!/bin/bash
# kill_session.sh
gsql -h $DB_IP -d postgres -c “SELECT pg_terminate_backend($PID);
”

2. 锁管理脚本
gsql -h $DB_IP -d postgres -c ”
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE wait_event_type = ‘Lock’
AND query_start < now() - interval '1 hour'; " 3. VACUUM优化脚本 gsql -h $DB_IP -d fgedudb -c " VACUUM (VERBOSE, ANALYZE) fgedu.orders; " 4. 索引重建脚本 gsql -h $DB_IP -d fgedudb -c " REINDEX TABLE fgedu.orders; " 5. 统计信息更新脚本 gsql -h $DB_IP -d fgedudb -c " ANALYZE fgedu.orders; "

5.3 openGauss常见问题处理

常见问题处理：

会话数过多：查询pg_stat_activity，分析连接来源
锁等待严重：查询pg_locks，定位阻塞会话
性能下降：分析pg_stat_statements，找出TOP SQL
表膨胀：检查pg_stat_user_tables的n_dead_tup
索引失效：检查pg_stat_user_indexes的idx_scan

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

opengauss教程FG067-openGauss系统表与数据字典查询生产实战

Part01-基础概念与理论知识

1.1 openGauss系统表基本概念

1.2 openGauss信息schema

1.3 openGauss pg_catalog体系

Part02-生产环境规划与建议

2.1 openGauss系统表架构

2.2 openGauss常用查询场景

2.3 openGauss查询优化建议

Part03-生产环境项目实施方案

3.1 openGauss会话查询

3.2 openGauss对象查询

3.3 openGauss性能查询

Part04-生产案例与实战讲解

4.1 openGauss DBA日常查询案例

4.1.1 案例背景

4.1.2 完整查询脚本

4.2 openGauss故障排查案例

4.2.1 问题描述

4.2.2 排查步骤

4.3 openGauss优化分析案例

4.3.1 问题描述

4.3.2 分析方案

Part05-风哥经验总结与分享

5.1 openGauss系统表最佳实践

5.2 openGauss常用脚本集

5.3 openGauss常见问题处理

相关推荐

联系我们