巡检人:小麦苗　QQ:646634621　微信公众号：DB宝　提供OCP、OCM、高可用（rac+dg）、PostgreSQL和MySQL培训　BLOG地址: https://www.dbaup.com 若需要脚本可私聊我
CBDB数据库巡检报告

Copyright (c) 2015-2100 (https://www.dbaup.com) dbaup. All rights reserved.

巡 检 人：小麦苗([QQ：646634621] [微信公众号：DB宝] [提供OCP、OCM、高可用、MySQL和PostgreSQL最实用的培训])
版 本 号：v7.0.0
修改日期：2024-09-12

[转到页底]

目录
总体概况 实例信息数据库实例信息 数据库基本信息数据库的总体概况、版本、主机情况、数据库负载情况、数据库属性等 所有表空间所有表空间 当前客户端连接信息当前客户端连接信息 前几张大表前几张大表
当前库的所有数据库对象当前库的所有数据库对象
锁情况 所有进程
SQL部分 总执行时间最长的SQL 最耗IO的SQL 最耗共享内存
索引部分
高可用情况 standby master情况standby master情况 逻辑复制PG逻辑复制情况
数据库性能 参数配置 pg_hba参数文件 后台写进程统计信息
其它 用户和角色 所有插件 获取已安装的CBDB的信息获取已安装的CBDB的信息，表pg_config


数据库总体概况

● CBDB集群配置信息

● 按照实例排列
dbid content role preferred_role mode status port hostname address datadir warehouseid
1 -1 p p n u 5432 cbdb cbdb /opt/cloudberrydb/data/coordinator/gpseg-1 0
6 -1 m m s u 5433 cbdb cbdb /opt/cloudberrydb/data/coordinator_standby/gpseg-1 0
2 0 p p s u 6000 cbdb cbdb /opt/cloudberrydb/data/primary/gpseg0 0
4 0 m m s u 7000 cbdb cbdb /opt/cloudberrydb/data/mirror/gpseg0 0
3 1 p p s u 6001 cbdb cbdb /opt/cloudberrydb/data/primary/gpseg1 0
5 1 m m s u 7001 cbdb cbdb /opt/cloudberrydb/data/mirror/gpseg1 0

● p和m的数量
hostname role cnt
cbdb p 3
cbdb m 3

● 按照主机排列
dbid content role preferred_role mode status port hostname address datadir warehouseid
1 -1 p p n u 5432 cbdb cbdb /opt/cloudberrydb/data/coordinator/gpseg-1 0
6 -1 m m s u 5433 cbdb cbdb /opt/cloudberrydb/data/coordinator_standby/gpseg-1 0
2 0 p p s u 6000 cbdb cbdb /opt/cloudberrydb/data/primary/gpseg0 0
3 1 p p s u 6001 cbdb cbdb /opt/cloudberrydb/data/primary/gpseg1 0
4 0 m m s u 7000 cbdb cbdb /opt/cloudberrydb/data/mirror/gpseg0 0
5 1 m m s u 7001 cbdb cbdb /opt/cloudberrydb/data/mirror/gpseg1 0

● 按照p和m的对应关系
content p_hostname m_hostname p_datadir m_datadir
-1 cbdb cbdb /opt/cloudberrydb/data/coordinator/gpseg-1 /opt/cloudberrydb/data/coordinator_standby/gpseg-1
0 cbdb cbdb /opt/cloudberrydb/data/primary/gpseg0 /opt/cloudberrydb/data/mirror/gpseg0
1 cbdb cbdb /opt/cloudberrydb/data/primary/gpseg1 /opt/cloudberrydb/data/mirror/gpseg1

● CBDB复制情况
gp_segment_id pid usesysid usename application_name client_addr client_hostname client_port backend_start backend_xmin state sent_lsn write_lsn flush_lsn replay_lsn write_lag flush_lag replay_lag sync_priority sync_state reply_time spill_txns spill_count spill_bytes sync_error
-1 2827483 10 gpadmin gp_walreceiver 192.92.0.38   52882 2024-11-15 10:47:05.166163+08   streaming 0/C148A98 0/C148A98 0/C148A98 0/C148A98       1 sync 2024-11-15 11:03:30.418475+08       none
0 2827562 10 gpadmin gp_walreceiver 192.92.0.38   37508 2024-11-15 10:47:09.312031+08   streaming 0/C135478 0/C135478 0/C135478 0/C135478       1 sync 2024-11-15 11:03:30.707465+08       none
1 2827564 10 gpadmin gp_walreceiver 192.92.0.38   33002 2024-11-15 10:47:09.324208+08   streaming 0/C1354E8 0/C1354E8 0/C1354E8 0/C1354E8       1 sync 2024-11-15 11:03:30.732074+08       none

● 角色切换历史
time dbid desc
2024-07-08 13:27:24.230102+08 5 FTS: update role, status, and mode for dbid 5 with contentid 1 to m, u, and s
2024-07-08 13:27:24.230035+08 3 FTS: update role, status, and mode for dbid 3 with contentid 1 to p, u, and s
2024-07-08 13:27:24.229136+08 4 FTS: update role, status, and mode for dbid 4 with contentid 0 to m, u, and s
2024-07-08 13:27:24.228667+08 2 FTS: update role, status, and mode for dbid 2 with contentid 0 to p, u, and s

● CBDB基本信息
pg_start_time run_time config_file_load_time server_ip server_port client_ip client_port server_version primary_or_standby now_date all_db_hit
2024-11-15 10:47:04.511999+08 994.291231 2024-11-15 10:47:04.476801+08         PostgreSQL 14.4 (Cloudberry Database 1.0.0+ build dev) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.1 20210130 (Red Hat 10.2.1-11), 64-bit compiled on Jun 27 2024 14:41:00 primary 2024-11-15 11:03:38.80323+08 99.33%

● 数据库基本信息（数据库大小）
Name Owner Encoding Collate Ctype datconnlimit Access privileges Size Tablespace Description dbid create_user createtime
template0 gpadmin UTF8 en_US.UTF-8 en_US.UTF-8 -1 =c/gpadmin
gpadmin=CTc/gpadmin		 57 MB pg_default unmodifiable empty database      
template1 gpadmin UTF8 en_US.UTF-8 en_US.UTF-8 -1 =c/gpadmin
gpadmin=CTc/gpadmin		 58 MB pg_default default template for new databases      
lhrgpdb gpadmin UTF8 en_US.UTF-8 en_US.UTF-8 -1   58 MB pg_default   16384 gpadmin 2024-07-08 13:27:19.650974+08
postgres gpadmin UTF8 en_US.UTF-8 en_US.UTF-8 -1   58 MB pg_default default administrative connection database      
总计         -1   231 MB          

● 查看各数据库数据创建时间
datname size access modification change creation isdir
postgres 3 2024-07-08 13:27:09+08 2024-07-08 13:27:09+08 2024-07-08 13:36:11+08   f
template1 3 2024-07-08 13:27:09+08 2024-07-08 13:27:09+08 2024-07-08 13:36:11+08   f
template0 3 2024-07-08 13:27:09+08 2024-07-08 13:27:09+08 2024-07-08 13:36:11+08   f
lhrgpdb 3 2024-07-08 13:27:19+08 2024-07-08 13:27:19+08 2024-07-08 13:36:12+08   f

● 所有表空间
oid Name Owner Location Access privileges Options Size Description
1663 pg_default gpadmin       231 MB  
1664 pg_global gpadmin       15 MB  

● 当前客户端连接信息
current_user current_database pg_backend_pid
gpadmin postgres 2828982

● 当前库的前50张大表
db schemaname relname rowcount table_size indexes_size total_size
postgres pg_catalog pg_depend 10635 2304 kB 3456 kB 5760 kB
postgres pg_catalog pg_proc 3493 3264 kB 1536 kB 4800 kB
postgres pg_catalog pg_rewrite 198 4320 kB 384 kB 4704 kB
postgres pg_catalog pg_attribute 4217 2304 kB 1152 kB 3456 kB
postgres pg_catalog pg_description 4590 1440 kB 864 kB 2304 kB
postgres pg_catalog pg_statistic 488 1824 kB 192 kB 2016 kB
postgres pg_catalog pg_class 556 1056 kB 576 kB 1632 kB
postgres pg_catalog pg_amop 1279 672 kB 960 kB 1632 kB
postgres pg_catalog pg_authid 12 576 kB 960 kB 1536 kB
postgres pg_catalog pg_constraint 142 576 kB 960 kB 1536 kB
postgres pg_catalog pg_type 797 960 kB 384 kB 1344 kB
postgres pg_catalog pg_operator 822 768 kB 384 kB 1152 kB
postgres pg_catalog pg_resourcetype 6 480 kB 576 kB 1056 kB
postgres pg_catalog pg_conversion 128 480 kB 576 kB 1056 kB
postgres pg_catalog pg_profile 1 480 kB 576 kB 1056 kB
postgres pg_catalog pg_index 211 576 kB 384 kB 960 kB
postgres pg_catalog pg_tablespace 2 576 kB 384 kB 960 kB
postgres pg_catalog pg_namespace 8 576 kB 384 kB 960 kB
postgres pg_catalog pg_collation 3 576 kB 384 kB 960 kB
postgres pg_catalog pg_amproc 854 576 kB 384 kB 960 kB
postgres pg_catalog pg_language 4 576 kB 384 kB 960 kB
postgres pg_catalog pg_foreign_server 1 576 kB 384 kB 960 kB
postgres pg_catalog pg_foreign_data_wrapper 1 576 kB 384 kB 960 kB
postgres pg_catalog pg_extension 2 576 kB 384 kB 960 kB
postgres pg_catalog pg_ts_dict 29 576 kB 384 kB 960 kB
postgres pg_catalog pg_am 10 480 kB 384 kB 864 kB
postgres pg_catalog pg_ts_template 5 480 kB 384 kB 864 kB
postgres pg_catalog pg_ts_parser 1 480 kB 384 kB 864 kB
postgres pg_catalog pg_shdepend 22 480 kB 384 kB 864 kB
postgres pg_catalog pg_cast 238 480 kB 384 kB 864 kB
postgres pg_catalog pg_resgroupcapability 14 480 kB 384 kB 864 kB
postgres pg_catalog pg_resgroup 2 480 kB 384 kB 864 kB
postgres pg_catalog pg_auth_members 3 480 kB 384 kB 864 kB
postgres pg_catalog pg_resqueuecapability 2 480 kB 384 kB 864 kB
postgres pg_catalog pg_resqueue 1 480 kB 384 kB 864 kB
postgres pg_catalog pg_ts_config 29 480 kB 384 kB 864 kB
postgres pg_catalog pg_opclass 256 480 kB 384 kB 864 kB
postgres pg_catalog pg_opfamily 214 480 kB 384 kB 864 kB
postgres pg_catalog pg_range 6 480 kB 384 kB 864 kB
postgres pg_catalog pg_init_privs 241 576 kB 192 kB 768 kB
postgres pg_catalog pg_aggregate 162 576 kB 192 kB 768 kB
postgres pg_catalog pg_foreign_table 3 576 kB 192 kB 768 kB
postgres pg_catalog pg_shdescription 2 576 kB 192 kB 768 kB
postgres pg_catalog pg_ts_config_map 551 480 kB 192 kB 672 kB
postgres pg_catalog pg_compression 4 480 kB 192 kB 672 kB
postgres pg_catalog pg_database 2 192 kB 384 kB 576 kB
postgres pg_catalog pg_password_history 0 192 kB 384 kB 576 kB
postgres pg_catalog gp_version_at_initdb 1 480 kB 0 bytes 480 kB
postgres pg_catalog gp_id 1 480 kB 0 bytes 480 kB
postgres pg_catalog pg_statistic_ext 0 96 kB 288 kB 384 kB

● 当前库的所有数据库对象
schemaname objecttype cnt
session_state VIEW 1

● 查看当前库的所有schema占用的磁盘空间
sosdnsp schema_tb_size_mb schema_idx_size_mb
public 0.00 0.00
session_state 0.00 0.00

● 磁盘剩余空间查询

● segment剩余空间查询
address free_disk_gb
cbdb 1810.00

● 磁盘剩余空间查询
dfsegment dfhostname dfdevice free_disk_gb
0  cbdb  overlay 1810.00
1  cbdb  overlay 1810.00

● 详情
dfsegment dfhostname address dfdevice datadir free_disk_gb
0  cbdb cbdb  overlay /opt/cloudberrydb/data/primary/gpseg0 1810.00
1  cbdb cbdb  overlay /opt/cloudberrydb/data/primary/gpseg1 1810.00

[回到目录]

锁情况

● 所有进程

● 总计
最大连接数 当前连接数 剩余连接数
250 12 238

● 用户统计
usename cnt
  5
gpadmin 7

● 状态统计
state cnt
  6
active 2
idle 4

● 详情
datid datname pid sess_id leader_pid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change wait_event_type wait_event state backend_xid backend_xmin query_id query backend_type rsgid rsgname
    2827483 -1   10 gpadmin gp_walreceiver 192.92.0.38   52882 2024-11-15 10:47:05.166163+08   2024-11-15 10:47:05.169266+08 2024-11-15 10:47:05.169292+08 Activity WalSenderMain active       START_REPLICATION 0/C000000 TIMELINE 1 walsender 0 unknown
13289 postgres 2828982 81   10 gpadmin psql     -1 2024-11-15 11:03:38.757808+08 2024-11-15 11:03:39.450006+08 2024-11-15 11:03:39.450006+08 2024-11-15 11:03:39.450007+08     active   793   select a.* from pg_stat_activity a WHERE a."state" not in ('idle') order by a.state,query_start limit 100; client backend 0 unknown

● 锁
locktype database relation page tuple virtualxid transactionid classid objid objsubid virtualtransaction pid mode granted fastpath waitstart mppsessionid mppiswriter gp_segment_id
relation 13289 12375               8/312 2828982 AccessShareLock t t   81 t -1
virtualxid         8/312         8/312 2828982 ExclusiveLock t t   81 t -1
virtualxid         5/1054         5/1054 2828984 ExclusiveLock t t   81 t 0
virtualxid         5/1052         5/1052 2828985 ExclusiveLock t t   81 t 1

● 锁2
lorlocktype lordatabase lorrelname lorrelation lortransaction lorpid lormode lorgranted lorcurrentquery
relation 13289 gp_locks_on_relation 13185   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid 1260   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_oid_index 2677   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_rolname_index 2676   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_rolprofile_index 6441   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_rolresgroup_index 6440   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_rolresqueue_index 6029   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 13289 pg_class 1259   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 13289 pg_class_oid_index 2662   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 13289 pg_class_relname_nsp_index 2663   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 13289 pg_class_tblspc_relfilenode_index 3455   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_database 1262   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_database_datname_index 2671   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_database_oid_index 2672   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 13289 pg_locks 12375   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 13289 pg_stat_activity 12528   2828982 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;

● 锁详情
pid transaction_id db_name schemaname object_name lock_type lock_mode lock_satus query_start query_text

● state_change字段长时间没有更新过的连接信息
datid datname pid sess_id leader_pid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change wait_event_type wait_event state backend_xid backend_xmin query_id query backend_type rsgid rsgname
13289 postgres 2828499 55   10 gpadmin Navicat 192.168.27.112   49188 2024-11-15 10:57:10.098224+08   2024-11-15 10:57:15.233425+08 2024-11-15 10:57:15.233924+08 Client ClientRead idle       set client_encoding to 'UNICODE' client backend 0 unknown
16384 lhrgpdb 2828495 53   10 gpadmin Navicat 192.168.27.112   49180 2024-11-15 10:57:08.972485+08   2024-11-15 11:00:00.47403+08 2024-11-15 11:00:00.475431+08 Client ClientRead idle       SELECT c.conkey FROM pg_constraint c WHERE c.contype = 'p' and c.conrelid = 12442 client backend 0 unknown
16384 lhrgpdb 2828502 56   10 gpadmin Navicat 192.168.27.112   49193 2024-11-15 10:57:11.199534+08   2024-11-15 11:03:11.077864+08 2024-11-15 11:03:11.082145+08 Client ClientRead idle       EXPLAIN SELECT
schemaname||'.'||relname as table_name,
-- pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
--and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) desc
LIMIT 50		 client backend 0 unknown

[回到目录]

SQL情况

● 慢查询SQL
pid sess_id client_port datname usename rsqname client_addr application_name state backend_start xact_start state_change query_start xact_stay query_stay query yc_filesize_gb yc_numfiles kill1 kill2
2827483 -1 52882   gpadmin pg_default 192.92.0.38 gp_walreceiver active 2024-11-15 10:47:05.166163+08   2024-11-15 10:47:05.169292+08 2024-11-15 10:47:05.169266+08   994 START_REPLICATION 0/C000000 TIMELINE 1     select pg_terminate_backend(2827483); select pg_cancel_backend(2827483);
2828982 81 -1 postgres gpadmin pg_default   psql active 2024-11-15 11:03:38.757808+08 2024-11-15 11:03:39.466027+08 2024-11-15 11:03:39.466029+08 2024-11-15 11:03:39.466027+08 0 0 SELECT pgsa.pid, pgsa.sess_id, pgsa.client_port, pgsa.datname AS datname, pgsa.usename AS usename, (SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname, pgsa.client_addr client_addr, pgsa.application_name AS application_name, pgsa.state AS state, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, state_change, pgsa.query_start AS query_start, TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay, trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay, REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY, yc_filesize_GB, yc_numfiles, 'select pg_terminate_backend('||pgsa.pid||');' kill1, 'select pg_cancel_backend('||pgsa.pid||');' kill2 FROM pg_stat_activity AS pgsa LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as yc_filesize_GB,sum(numfiles) as yc_numfiles from     select pg_terminate_backend(2828982); select pg_cancel_backend(2828982);

[回到目录]

高可用情况

● standby master情况

● 主库查看wal日志发送状态
pid usesysid usename application_name client_addr client_hostname client_port backend_start backend_xmin state sent_lsn write_lsn flush_lsn replay_lsn write_lag flush_lag replay_lag sync_priority sync_state reply_time spill_txns spill_count spill_bytes
2827483 10 gpadmin gp_walreceiver 192.92.0.38   52882 2024-11-15 10:47:05.166163+08   streaming 0/C148A98 0/C148A98 0/C148A98 0/C148A98       1 sync 2024-11-15 11:03:30.418475+08      

● 从库查看wal日志接收状态

● 主从流复制冲突统计
datid datname confl_tablespace confl_lock confl_snapshot confl_bufferpin confl_deadlock
13289 postgres 0 0 0 0 0
1 template1 0 0 0 0 0
13288 template0 0 0 0 0 0
16384 lhrgpdb 0 0 0 0 0

[回到目录]

数据库性能

● 相关参数

● 重要参数
name setting unit category short_desc extra_desc context vartype source min_val max_val enumvals boot_val reset_val sourcefile sourceline pending_restart
archive_mode off   Write-Ahead Log / Archiving Allows archiving of WAL files using archive_command.   postmaster enum default     {always,on,off} off off     f
client_encoding UTF8   Client Connection Defaults / Locale and Formatting Sets the client's character set encoding.   user string default       SQL_ASCII UTF8     f
gp_resource_manager queue   Resource Usage Sets the type of resource manager. Only support "queue" and "group" for now. postmaster string default       queue queue     f
listen_addresses *   Connections and Authentication / Connection Settings Sets the host name or IP address(es) to listen to.   postmaster string configuration file       localhost * /opt/cloudberrydb/data/coordinator/gpseg-1/postgresql.conf 877 f
logging_collector on   Reporting and Logging / Where to Log Start a subprocess to capture stderr output and/or csvlogs into log files.   postmaster bool default       on on     f
log_min_duration_statement -1 ms Reporting and Logging / When to Log Sets the minimum execution time above which all statements will be logged. Zero prints all queries. -1 turns this feature off. superuser integer default -1 2147483647   -1 -1     f
log_statement all   Reporting and Logging / What to Log Sets the type of statements logged.   superuser enum configuration file     {none,ddl,mod,all} none all /opt/cloudberrydb/data/coordinator/gpseg-1/postgresql.conf 878 f
log_truncate_on_rotation off   Reporting and Logging / Where to Log Truncate existing log files of same name during log rotation.   sighup bool default       off off     f
max_connections 250   Connections and Authentication / Connection Settings Sets the maximum number of concurrent connections.   postmaster integer configuration file 10 262143   200 250 /opt/cloudberrydb/data/coordinator/gpseg-1/postgresql.conf 75 f
port 5432   Connections and Authentication / Connection Settings Sets the TCP port the server listens on.   postmaster integer command line 1 65535   5432 5432     f
stats_queue_level off   Statistics / Query and Index Statistics Collector Collects resource queue-level statistics on database activity.   superuser bool default       off off     f

● GP重要优化参数

● 重要优化参数
paramsegment paramname paramvalue
Master gp_vmem_protect_limit 8192
Segment gp_vmem_protect_limit 8192
Master shared_buffers 125MB
Segment shared_buffers 125MB
Master max_connections 250
Segment max_connections 750
Master max_prepared_transactions 250
Segment max_prepared_transactions 250
Master track_activity_query_size 1kB
Segment track_activity_query_size 1kB
Master gp_resqueue_priority_cpucores_per_segment 4
Segment gp_resqueue_priority_cpucores_per_segment 4
Master gp_enable_global_deadlock_detector off
Segment gp_enable_global_deadlock_detector off
Master gp_segworker_relative_priority 20
Segment gp_segworker_relative_priority 20
Master log_statement all
Segment log_statement none
Master work_mem 32MB
Segment work_mem 32MB
Master temp_buffers 32MB
Segment temp_buffers 32MB
Master maintenance_work_mem 64MB
Segment maintenance_work_mem 64MB
Master effective_cache_size 16GB
Segment effective_cache_size 16GB
Master statement_mem 125MB
Segment statement_mem 125MB
Master max_statement_mem 2000MB
Segment max_statement_mem 2000MB
Master gp_workfile_compression off
Segment gp_workfile_compression off
Master gp_autostats_mode none
Segment gp_autostats_mode none
Master gp_autostats_on_change_threshold 2147483647
Segment gp_autostats_on_change_threshold 2147483647

● 配置不一样的参数
psdname psdvalue psdcount

● 后台写进程统计信息
checkpoints_timed checkpoints_req checkpoint_write_time checkpoint_sync_time buffers_checkpoint buffers_clean maxwritten_clean buffers_backend buffers_backend_fsync buffers_alloc stats_reset
3 1 10233 14 102 0 0 5 0 690 2024-11-15 10:47:02.992018+08

● 没有统计信息且可能需要ANALYZE的表（准确度排名）， 每个库需要单独查询
smischema smitable smisize smicols smirecs anaylze_tb

● 查看当前库的表膨胀信息
table_name table_size index_size all_size bloat_size n_dead_tup n_live_tup dead_tup_ratio vacuums anaylze_tb tb_size

● gp_toolkit.gp_bloat_diag

表oid 模式名 表名 期望页数 实际页数 real_size all_size 表大小 膨胀状态 vacuums anaylze_tb

● 索引膨胀的表

schemaname tablename tb_reloptions index_name index_reloptions cols bloat_per ibloat bloat_size table_size index_size reindex_sql fillfactor ind_avg_len ituples ipages iotta internal leaf wastedipages

● 缓存命中率和事务提交率
datname blks_read blks_hit xact_commit xact_rollback cache_hit commit_hit stats_reset
lhrgpdb 189 7832 50 12 97.64% 80.64% 2024-11-15 10:57:09.29809+08
postgres 373 121584 325 14 99.69% 95.87% 2024-11-15 10:47:03.645844+08
------         99.56% 93.51%  

● 数据库统计详情
gp_segment_id datid datname numbackends xact_commit xact_rollback blks_read blks_hit tup_returned tup_fetched tup_inserted tup_updated tup_deleted conflicts temp_files temp_bytes deadlocks checksum_failures checksum_last_failure blk_read_time blk_write_time session_time active_time idle_in_transaction_time sessions sessions_abandoned sessions_fatal sessions_killed stats_reset
-1 0   0 0 0 65 2510 2526 468 0 0 0 0 0 0 0 0   0 0 0 0 0 0 0 0 0 2024-11-15 10:47:03.038997+08
-1 13289 postgres 3 325 14 373 121584 188353 59688 125 6 76 0 0 0 0 0   0 0 927489.536 5591.688 3.149 27 1 0 0 2024-11-15 10:47:03.645844+08
-1 1 template1 0 35 2 110 4342 30673 3056 0 0 0 0 0 0 0 0   0 0 29.967 7.09 3.633 3 0 0 0 2024-11-15 10:47:03.038959+08
-1 13288 template0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0   0 0 0 0 0 0 0 0 0  
-1 16384 lhrgpdb 2 50 12 189 7832 60755 5666 0 0 0 0 0 0 0 0   0 0 654633.735 5433.349 0 8 0 0 0 2024-11-15 10:57:09.29809+08

● 会话占用内存
datname sess_id usename query segid vmem_mb is_runaway qe_count active_qe_count dirty_qe_count runaway_vmem_mb runaway_command_cnt idle_start
postgres 81 gpadmin SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 -1 44 f 2 1 -1 0 0 2024-11-15 11:03:39.58723+08
postgres 81 gpadmin /*
\qecho <p>● pg_stat_all_tables<p>
SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM gp_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50;
*/



/*
SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb
from pg_database d
WHERE d.datname not in ('template0','template1');
*/

SELECT * from (
SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_table_size(schemaname||'.'||relname)) as table_size,
pg_size_pretty(pg_indexes_size(schemaname||'.'||relname)) as index_size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) as all_size,
pg_size_pretty(pg_table_size(schemaname||'.'||relname) 		-1 44 f 2 1 -1 0 0 2024-11-15 11:03:39.58723+08
postgres 81 gpadmin /*
\qecho <p>● pg_stat_all_tables<p>
SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM gp_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50;
*/



/*
SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb
from pg_database d
WHERE d.datname not in ('template0','template1');
*/

SELECT * from (
SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_table_size(schemaname||'.'||relname)) as table_size,
pg_size_pretty(pg_indexes_size(schemaname||'.'||relname)) as index_size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) as all_size,
pg_size_pretty(pg_table_size(schemaname||'.'||relname) 		0 32 f 2 1 -1 0 0 2024-11-15 11:03:39.587026+08
postgres 81 gpadmin SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 0 32 f 2 1 -1 0 0 2024-11-15 11:03:39.587026+08
postgres 81 gpadmin SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 1 32 f 2 1 -1 0 0 2024-11-15 11:03:39.587028+08
postgres 81 gpadmin /*
\qecho <p>● pg_stat_all_tables<p>
SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM gp_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50;
*/



/*
SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb
from pg_database d
WHERE d.datname not in ('template0','template1');
*/

SELECT * from (
SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_table_size(schemaname||'.'||relname)) as table_size,
pg_size_pretty(pg_indexes_size(schemaname||'.'||relname)) as index_size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) as all_size,
pg_size_pretty(pg_table_size(schemaname||'.'||relname) 		1 32 f 2 1 -1 0 0 2024-11-15 11:03:39.587028+08
lhrgpdb 53 gpadmin SELECT c.conkey FROM pg_constraint c WHERE c.contype = 'p' and c.conrelid = 12442 -1 18 f 1 0 -1 0 0 2024-11-15 11:00:00.475371+08
lhrgpdb 56 gpadmin EXPLAIN SELECT
schemaname||'.'||relname as table_name,
-- pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
--and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) desc
LIMIT 50		 -1 18 f 1 0 -1 0 0 2024-11-15 11:03:11.082141+08
postgres 55 gpadmin set client_encoding to 'UNICODE' -1 14 f 1 0 -1 0 0 2024-11-15 10:57:15.233889+08

● 会话的溢出文件查询
pid yc_filesize_gb yc_numfiles

● 资源队列

● 资源队列的属性

rsqname resname ressetting restypid
pg_default active_statements 20 1
pg_default max_cost -1 2
pg_default min_cost 0 3
pg_default cost_overcommit 0 4
pg_default priority medium 5
pg_default memory_limit -1 6

● gp_toolkit.gp_resqueue_status

queueid rsqname rsqcountlimit rsqcountvalue rsqcostlimit rsqcostvalue rsqmemorylimit rsqmemoryvalue rsqwaiters rsqholders
6055 pg_default 20 0 -1 0 -1 0 0 0

● 用户及其资源队列情况

rolname rolconnlimit queueid rsqname rsqcountlimit rsqcountvalue rsqcostvalue rsqcostlimit rsqmemorylimit_gb rsqmemoryvalue_gb rsqwaiters rsqholders
gpadmin -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_database_owner -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_execute_server_program -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_monitor -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_read_all_data -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_read_all_settings -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_read_all_stats -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_read_server_files -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_signal_backend -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_stat_scan_tables -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_write_all_data -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0
pg_write_server_files -1 6055 pg_default 20 0 0 -1 -1 0 B 0 0

● pg_stat_resqueues（需要参数stats_queue_level为on）

gp_segment_id queueid queuename n_queries_exec n_queries_wait elapsed_exec elapsed_wait
-1 6055 pg_default 0 0 0 0

● gp_toolkit.gp_resq_activity

resqprocpid resqrole resqoid resqname resqstart resqstatus

● gp_toolkit.gp_resq_activity_by_queue

resqoid resqname resqlast resqstatus resqtotal

● gp_toolkit.gp_resq_priority_statement

rqpdatname rqpusename rqpsession rqpcommand rqppriority rqpweight rqpquery
postgres gpadmin 81 141 MAX 1000000 SELECT * from gp_toolkit.gp_resq_priority_statement;

● 新建或重建索引的过程跟踪（GP7）
gp_segment_id pid datid datname relid index_relid command phase lockers_total lockers_done current_locker_pid blocks_total blocks_done tuples_total tuples_done partitions_total partitions_done

● cluster的各个阶段（GP7）
gp_segment_id pid datid datname relid command phase cluster_index_relid heap_tuples_scanned heap_tuples_written heap_blks_total heap_blks_scanned index_rebuild_count

[回到目录]

日志

● master库近7天的错误日志
logtime loguser logdatabase logpid logthread loghost logport logsessiontime logtransaction logsession logcmdcount logsegment logslice logdistxact loglocalxact logsubxact logseverity logstate logmessage logdetail loghint logquery logquerypos logcontext logdebug logcursorpos logfunction logfile logline logstack
2024-11-16 01:02:25.94348+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd27 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
-- pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
--and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) desc
LIMIT 50		 0   cdbdisp.c 313  
2024-11-16 01:02:16.594167+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd25 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
-- pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
--and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50		 0   cdbdisp.c 313  
2024-11-16 01:02:12.104429+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd23 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
-- pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
--and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_total_relation_size(schemaname||'.'||relname) desc
LIMIT 50		 0   cdbdisp.c 313  
2024-11-16 01:02:10.265392+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd21 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
-- pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
--and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_total_relation_size(schemaname||'.'||relname) desc
LIMIT 50		 0   cdbdisp.c 313  
2024-11-16 01:00:11.337076+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd19 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
-- pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
--and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50		 0   cdbdisp.c 313  
2024-11-16 00:59:55.010776+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd15 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
-- pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
--and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50		 0   cdbdisp.c 313  
2024-11-16 00:59:41.826686+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd13 seg-1       sx1 ERROR 42P01 relation "gp_stat_all_tables" does not exist           SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM gp_stat_all_tables a
WHERE a.last_analyze is null		 240   parse_relation.c 1413  
2024-11-16 00:59:09.573216+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd10 seg-1       sx1 ERROR 42P01 relation "gp_stat_all_tables" does not exist           SELECT * from gp_stat_all_tables limit 10 15   parse_relation.c 1413  
2024-11-16 00:58:58.698579+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd9 seg-1       sx1 ERROR 42P01 relation "gp_stat_all_tables" does not exist           SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM gp_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50		 240   parse_relation.c 1413  
2024-11-16 00:58:40.102948+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd8 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50		 0   cdbdisp.c 313  
2024-11-16 00:58:34.027823+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd6 seg-1       sx1 ERROR 42704 unrecognized configuration parameter "gp_execution_context"           SET LOCAL gp_execution_context to 'all segments' 0   guc.c 5772  
2024-11-16 00:58:34.027778+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd6 seg-1       sx1 WARNING 25P01 SET LOCAL can only be used in transaction blocks             0   xact.c 4479  
2024-11-16 00:58:00.716906+08 gpadmin lhrgpdb p2828502 th428542080 192.168.27.112 49193 2024-11-16 00:57:11+08 0 con56 cmd5 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a		 0   cdbdisp.c 313  
2024-11-16 00:57:02.882665+08 gpadmin postgres p2828005 th428542080 192.168.27.112 64085 2024-11-16 00:52:08+08 0 con29 cmd3529 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a		 0   cdbdisp.c 313  
2024-11-16 00:56:37.492714+08 gpadmin postgres p2828005 th428542080 192.168.27.112 64085 2024-11-16 00:52:08+08 0 con29 cmd3527 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50		 0   cdbdisp.c 313  
2024-11-16 00:56:09.606792+08 gpadmin postgres p2828344 th428542080 [local]   2024-11-16 00:56:08+08 0 con45 cmd114 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50;		 0   cdbdisp.c 313  
2024-11-16 00:54:32.349871+08 gpadmin gpperfmon p2828238 th428542080 [local]   2024-11-16 00:54:32+08 0 con40   seg-1       sx1 FATAL 3D000 database "gpperfmon" does not exist             0   postinit.c 1020  
2024-11-16 00:54:31.806357+08 gpadmin postgres p2828156 th428542080 [local]   2024-11-16 00:54:30+08 0 con35 cmd126 seg-1       sx1 ERROR 42P01 relation "temp_db_sizes" does not exist           SELECT db_name,hostname, segment_id, db_size
FROM temp_db_sizes
ORDER BY db_name,hostname, segment_id;		 53   parse_relation.c 1413  
2024-11-16 00:54:31.73437+08 gpadmin postgres p2828156 th428542080 [local]   2024-11-16 00:54:30+08 0 con35 cmd114 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50;		 0   cdbdisp.c 313  
2024-11-16 00:52:18.102566+08 gpadmin postgres p2828005 th428542080 192.168.27.112 64085 2024-11-16 00:52:08+08 0 con29 cmd3523 seg-1       sx1 ERROR 42703 column pgsa.waiting_reason does not exist           SELECT pgsa.pid,
pgsa.sess_id,
pgsa.client_port,
pgsa.datname AS datname,
pgsa.usename AS usename,
(SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname,
pgsa.client_addr client_addr,
pgsa.application_name AS application_name,
pgsa.state AS state,
pgsa.waiting_reason,
pgsa.backend_start AS backend_start,
pgsa.xact_start AS xact_start,
state_change,
pgsa.query_start AS query_start,
TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay,
trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay,
REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY,
yc_filesize_GB,
yc_numfiles,
'select pg_terminate_backend('||pgsa.pid||');' kill1,
'select pg_cancel_backend('||pgsa.pid||');' kill2
FROM pg_stat_activity AS pgsa
-- 溢出文件大小查询
LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d
ON pgsa.pid=d.pid
WHERE pgsa.state not in ( 'idle' ,'idle in transaction (aborted)' )
ORDER BY query_stay DESC,xact_stay DESC
LIMIT 100		 358   parse_relation.c 3913  
2024-11-16 00:50:12.053956+08 gpadmin postgres p2827849 th428542080 [local]   2024-11-16 00:49:52+08 0 con22 cmd3 seg-1       sx1 ERROR 42703 column pgsa.waiting_reason does not exist           SELECT pgsa.pid,
pgsa.sess_id,
pgsa.client_port,
pgsa.datname AS datname,
pgsa.usename AS usename,
(SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname,
pgsa.client_addr client_addr,
pgsa.application_name AS application_name,
pgsa.state AS state,
pgsa.waiting_reason,
pgsa.backend_start AS backend_start,
pgsa.xact_start AS xact_start,
state_change,
pgsa.query_start AS query_start,
TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay,
trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay,
REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY,
yc_filesize_GB,
yc_numfiles,
'select pg_terminate_backend('||pgsa.pid||');' kill1,
'select pg_cancel_backend('||pgsa.pid||');' kill2
FROM pg_stat_activity AS pgsa

LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d
ON pgsa.pid=d.pid
WHERE pgsa.state not in ( 'idle' ,'idle in transaction (aborted)' )
ORDER BY query_stay DESC,xact_stay DESC
LIMIT 100;		 349   parse_relation.c 3913  
2024-11-16 00:48:49.859506+08 gpadmin gpperfmon p2827785 th428542080 [local]   2024-11-16 00:48:49+08 0 con19   seg-1       sx1 FATAL 3D000 database "gpperfmon" does not exist             0   postinit.c 1020  
2024-11-16 00:48:49.785494+08 gpadmin postgres p2827704 th428542080 [local]   2024-11-16 00:48:47+08 0 con14 cmd149 seg-1       sx1 ERROR 42703 column "lorwaiting" does not exist           SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true'; 53   parse_relation.c 3913  
2024-11-16 00:48:49.084064+08 gpadmin postgres p2827704 th428542080 [local]   2024-11-16 00:48:47+08 0 con14 cmd124 seg-1       sx1 ERROR 42P01 relation "temp_db_sizes" does not exist           SELECT db_name,hostname, segment_id, db_size
FROM temp_db_sizes
ORDER BY db_name,hostname, segment_id;		 53   parse_relation.c 1413  
2024-11-16 00:48:48.895567+08 gpadmin postgres p2827704 th428542080 [local]   2024-11-16 00:48:47+08 0 con14 cmd112 seg-1       sx1 ERROR 0A000 query plan with multiple segworker groups is not supported   likely caused by a function that reads or modifies data in a distributed table       SELECT
schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50;		 0   cdbdisp.c 313  
2024-11-16 00:48:48.802159+08 gpadmin postgres p2827704 th428542080 [local]   2024-11-16 00:48:47+08 0 con14 cmd60 seg-1       sx1 ERROR 42703 column pgsa.waiting does not exist           SELECT pgsa.pid,
pgsa.sess_id,
pgsa.client_port,
pgsa.datname AS datname,
pgsa.usename AS usename,
(SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname,
pgsa.client_addr client_addr,
pgsa.application_name AS application_name,
pgsa.state AS state,
pgsa.waiting,
pgsa.waiting_reason,
pgsa.backend_start AS backend_start,
pgsa.xact_start AS xact_start,
state_change,
pgsa.query_start AS query_start,
TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay,
trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay,
REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY,
yc_filesize_GB,
yc_numfiles,
'select pg_terminate_backend('||pgsa.pid||');' kill1,
'select pg_cancel_backend('||pgsa.pid||');' kill2
FROM pg_stat_activity AS pgsa

LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d
ON pgsa.pid=d.pid
WHERE pgsa.state not in ( 'idle' ,'idle in transaction (aborted)' )
ORDER BY query_stay DESC,xact_stay DESC
LIMIT 100;		 358   parse_relation.c 3913  
2024-11-16 00:48:48.800064+08 gpadmin postgres p2827704 th428542080 [local]   2024-11-16 00:48:47+08 0 con14 cmd57 seg-1       sx1 ERROR 42703 column "waiting" does not exist           SELECT pg_locks.pid as pid,
transactionid as transaction_id,
pd.datname db_name,
nspname as schemaname,
relname as object_name,
locktype as lock_type,
mode lock_mode,
CASE
WHEN granted = 'f' THEN
'get_lock'
WHEN granted = 't' THEN
'wait_lock'
END lock_satus,
CASE
WHEN waiting = 'false' THEN
'already get lock,sql executing'
WHEN waiting = 'true' THEN
'waiting get lock,sql waiting execute'
END waitting_satus,
least(query_start, xact_start) AS query_start,
substr(query, 1, 25) AS query_text
FROM pg_locks
left join pg_database pd
on (pg_locks.database=pd.oid)
LEFT OUTER JOIN pg_class
ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace
ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity
WHERE NOT pg_locks.pid = pg_backend_pid()
AND pg_locks.pid = pg_stat_activity.pid
ORDER BY query_start;		 392   parse_relation.c 3913  
2024-11-16 00:47:03.71738+08     p2827191 th-1178040192       0     seg-1         FATAL 57P01 terminating background worker "sweeper process" due to administrator command             0   bgworker.c 778  
2024-11-16 00:47:03.661766+08 gpadmin postgres p2827265 th-1178040192 127.0.0.1 47100 2024-11-16 00:47:03+08 0   cmd5 seg-1       sx1 ERROR 42704 unrecognized configuration parameter "gp_cbdb_deploy"         SQL function "__gp_param_setting_on_master" statement 1
SQL function "gp_param_setting" statement 1		 select * from gp_toolkit.gp_param_setting('gp_cbdb_deploy') 0   guc.c 5772  
2024-11-16 00:47:02.714765+08 gpadmin postgres p2827165 th1987655808 127.0.0.1 47052 2024-11-16 00:47:02+08 0   cmd5 seg-1       sx1 ERROR 42704 unrecognized configuration parameter "gp_cbdb_deploy"         SQL function "__gp_param_setting_on_master" statement 1
SQL function "gp_param_setting" statement 1		 select * from gp_toolkit.gp_param_setting('gp_cbdb_deploy') 0   guc.c 5772  

● segment库近7天的错误日志
logtime loguser logdatabase logpid logthread loghost logport logsessiontime logtransaction logsession logcmdcount logsegment logslice logdistxact loglocalxact logsubxact logseverity logstate logmessage logdetail loghint logquery logquerypos logcontext logdebug logcursorpos logfunction logfile logline logstack
2024-11-16 00:47:04.322006+08 gpadmin   p2827358 th1235978368 192.92.0.38 60830 2024-11-16 00:47:04+08 0     seg1         FATAL 57P03 the database system is starting up last replayed record at 0/C001148           0   postmaster.c 2734  
2024-11-16 00:47:04.316876+08 gpadmin   p2827356 th1235978368 192.92.0.38 60828 2024-11-16 00:47:04+08 0     seg1         FATAL 57P03 the database system is starting up last replayed record at 0/C001148           0   postmaster.c 2734  
2024-11-16 00:47:04.310269+08 gpadmin   p2827352 th-121948032 192.92.0.38 37102 2024-11-16 00:47:04+08 0     seg0         FATAL 57P03 the database system is starting up last replayed record at 0/C001148           0   postmaster.c 2734  
2024-11-16 00:47:04.304348+08 gpadmin   p2827350 th-121948032 192.92.0.38 37100 2024-11-16 00:47:04+08 0     seg0         FATAL 57P03 the database system is starting up last replayed record at 0/C001148           0   postmaster.c 2734  

[回到目录]

其它

● 所有角色(用户)
rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcanlogin rolreplication rolconnlimit rolenableprofile rolprofile rolaccountstatus rolfailedlogins rolpassword rolvaliduntil rollockdate rolpasswordexpire rolbypassrls rolconfig rolresqueue oid rolcreaterextgpfd rolcreaterexthttp rolcreatewextgpfd rolresgroup
pg_database_owner f t f f f f -1 f pg_default 0 0 ********       f   6055 6171 f f f 6438
pg_read_all_data f t f f f f -1 f pg_default 0 0 ********       f   6055 6181 f f f 6438
pg_write_all_data f t f f f f -1 f pg_default 0 0 ********       f   6055 6182 f f f 6438
pg_monitor f t f f f f -1 f pg_default 0 0 ********       f   6055 3373 f f f 6438
pg_read_all_settings f t f f f f -1 f pg_default 0 0 ********       f   6055 3374 f f f 6438
pg_read_all_stats f t f f f f -1 f pg_default 0 0 ********       f   6055 3375 f f f 6438
pg_stat_scan_tables f t f f f f -1 f pg_default 0 0 ********       f   6055 3377 f f f 6438
pg_read_server_files f t f f f f -1 f pg_default 0 0 ********       f   6055 4569 f f f 6438
pg_write_server_files f t f f f f -1 f pg_default 0 0 ********       f   6055 4570 f f f 6438
pg_execute_server_program f t f f f f -1 f pg_default 0 0 ********       f   6055 4571 f f f 6438
pg_signal_backend f t f f f f -1 f pg_default 0 0 ********       f   6055 4200 f f f 6438
gpadmin t t t t t t -1 f pg_default 0 0 ********       t   6055 10 f f f 6438

● pg_user

usename usesysid usecreatedb usesuper userepl usebypassrls passwd valuntil useconfig
gpadmin 10 t t t t ********    

● pg_shadow

usename usesysid usecreatedb usesuper userepl usebypassrls passwd valuntil useconfig create_user
gpadmin 10 t t t t SCRAM-SHA-256$4096:nmNgRIIeXIV1pkwgSMaNoA==$vKKeFYK5roV0SRMGJF2F1M1ZeiAO8ru73VCfkq08kYs=:X2X3/uuDcLaGgeKjXPExJKs5vUWRERLEXAdYXyAB+7A=     CREATE USER gpadmin WITH PASSWORD 'SCRAM-SHA-256$4096:nmNgRIIeXIV1pkwgSMaNoA==$vKKeFYK5roV0SRMGJF2F1M1ZeiAO8ru73VCfkq08kYs=:X2X3/uuDcLaGgeKjXPExJKs5vUWRERLEXAdYXyAB+7A=' CREATEDB;

● 用户和角色

rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcanlogin rolconnlimit rolvaliduntil memberof rolcreaterextgpfd rolcreatewextgpfd rolcreaterexthttp rolreplication
gpadmin t t t t t -1   {} f f f t
pg_database_owner f t f f f -1   {} f f f f
pg_execute_server_program f t f f f -1   {} f f f f
pg_monitor f t f f f -1   {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} f f f f
pg_read_all_data f t f f f -1   {} f f f f
pg_read_all_settings f t f f f -1   {} f f f f
pg_read_all_stats f t f f f -1   {} f f f f
pg_read_server_files f t f f f -1   {} f f f f
pg_signal_backend f t f f f -1   {} f f f f
pg_stat_scan_tables f t f f f -1   {} f f f f
pg_write_all_data f t f f f -1   {} f f f f
pg_write_server_files f t f f f -1   {} f f f f

● 授权信息
raroleid rarolename ramemberid ramembername
3374 pg_read_all_settings 3373 pg_monitor
3375 pg_read_all_stats 3373 pg_monitor
3377 pg_stat_scan_tables 3373 pg_monitor

● 当前库的所有schema
catalog_name schema_name schema_owner default_character_set_catalog default_character_set_schema default_character_set_name sql_path
postgres session_state gpadmin        
postgres gp_toolkit gpadmin        
postgres information_schema gpadmin        
postgres public gpadmin        
postgres pg_catalog gpadmin        
postgres pg_ext_aux gpadmin        
postgres pg_bitmapindex gpadmin        
postgres pg_aoseg gpadmin        
postgres pg_toast gpadmin        

● 所有语言
oid lanname lanowner lanispl lanpltrusted lanplcallfoid laninline lanvalidator lanacl
12 internal 10 f f 0 0 2246  
13 c 10 f f 0 0 2247  
14 sql 10 f t 0 0 2248  
13035 plpgsql 10 t t 13032 13033 13034  

● 所有可用插件

● 所有插件（已编译）
name default_version installed_version comment
btree_gin 1.3   support for indexing common datatypes in GIN
citext 1.6   data type for case-insensitive character strings
dblink 1.2   connect to other PostgreSQL databases from within a database
file_fdw 1.0   foreign-data wrapper for flat file access
fuzzystrmatch 1.1   determine similarities and distance between strings
gp_debug_numsegments 1.0   get / set default numsegments when creating tables
gp_distribution_policy 1.0   check distribution policy in a GPDB cluster
gp_exttable_fdw 1.0 1.0 External Table Foreign Data Wrapper for Cloudberry Database
gp_inject_fault 1.0   simulate various faults for testing purposes
gp_internal_tools 1.0.0 1.0.0 Different internal tools for Cloudberry Database
gp_legacy_string_agg 1.0.0   Legacy one-argument string_agg implementation for Cloudberry
gp_replica_check 0.0.1    
gp_sparse_vector 1.0.1   SParse vector implementation for CBDB
hstore 1.8   data type for storing sets of (key, value) pairs
pageinspect 1.9   inspect the contents of database pages at a low level
pg_trgm 1.6   text similarity measurement and index searching based on trigrams
pgcrypto 1.3   cryptographic functions
plperl 1.0   PL/Perl procedural language
plperlu 1.0   PL/PerlU untrusted procedural language
plpgsql 1.0 1.0 PL/pgSQL procedural language
plpython3u 1.0   PL/Python3U untrusted procedural language
pxf_fdw 1.0   PXF Foreign Data Wrapper for Cloudberry Database
tablefunc 1.0   functions that manipulate whole tables, including crosstab

● 已安装的插件
Name Version Schema Description
gp_exttable_fdw 1.0 pg_catalog External Table Foreign Data Wrapper for Cloudberry Database
gp_internal_tools 1.0.0 session_state Different internal tools for Cloudberry Database
plpgsql 1.0 pg_catalog PL/pgSQL procedural language

[回到目录]