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

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

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

[转到页底]

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


数据库总体概况

● 实例信息
pg_start_time server_ip server_port client_ip client_port server_version primary_or_standby now_date
2021-10-23 11:08:37.983316+00 172.17.0.3 5432 192.168.66.64 4642 (openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit primary 2021-10-23 12:38:42.700099+00

● 数据库基本信息
Name Owner Encoding Collate Ctype Access privileges Size Tablespace Description
omm omm UTF8 en_US.UTF-8 en_US.UTF-8   11 MB pg_default  
postgres omm UTF8 en_US.UTF-8 en_US.UTF-8   13 MB pg_default default administrative connection database
template0 omm UTF8 en_US.UTF-8 en_US.UTF-8 =c/omm
omm=CTc/omm		 11 MB pg_default default template for new databases
template1 omm UTF8 en_US.UTF-8 en_US.UTF-8 =c/omm
omm=CTc/omm		 11 MB pg_default unmodifiable empty database
tpcclhr gaussdb UTF8 en_US.UTF-8 en_US.UTF-8   266 MB pg_default  

● 所有表空间
oid Name Owner Location Access privileges Options Size Description
1663 pg_default omm       313 MB  
1664 pg_global omm       514 MB  

● 当前客户端连接信息
current_user current_database pg_backend_pid
gaussdb tpcclhr 140644085397248

● 前几张大表（当前库）
db schemaname relname rowcount table_size indexes_size total_size
tpcclhr public sbtest7 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest3 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest1 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest6 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest4 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest2 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest5 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest8 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest9 100000 21 MB 4416 kB 25 MB
tpcclhr public sbtest10 100000 21 MB 4416 kB 25 MB
tpcclhr db4ai snapshot 0 8192 bytes 16 kB 24 kB

● 当前库的所有数据库对象
schemaname objecttype cnt
public SEQUENCE 10
db4ai INDEX 2
dbe_perf VIEW 174
public TABLE 10
db4ai composite type 1
db4ai TABLE 1
public INDEX 20

● 当前操作系统运行的状态信息
id name value comments cumulative
0 NUM_CPUS 8 Number of CPUs or processors available f
1 NUM_CPU_CORES 8 Number of CPU cores available (includes subcores of multicore CPUs as well as single-core CPUs) f
2 NUM_CPU_SOCKETS 2 Number of CPU sockets available (represents an absolute count of CPU chips on the system, regardless of multithreading or multi-core architectures) f
3 IDLE_TIME 1843146701 Number of hundredths of a second that a processor has been idle, totalled over all processors t
4 BUSY_TIME 139388355 Number of hundredths of a second that a processor has been busy executing user or kernel code, totalled over all processors t
5 USER_TIME 96157938 Number of hundredths of a second that a processor has been busy executing user code, totalled over all processors t
6 SYS_TIME 43230417 Number of hundredths of a second that a processor has been busy executing kernel code, totalled over all processors t
7 IOWAIT_TIME 22900875 Number of hundredths of a second that a processor has been waiting for I/O to complete, totalled over all processors t
8 NICE_TIME 19620 Number of hundredths of a second that a processor has been busy executing low-priority user code, totalled over all processors t
9 AVG_IDLE_TIME 230393337 Number of hundredths of a second that a processor has been idle, averaged over all processors t
10 AVG_BUSY_TIME 17423544 Number of hundredths of a second that a processor has been busy executing user or kernel code, averaged over all processors t
11 AVG_USER_TIME 12019742 Number of hundredths of a second that a processor has been busy executing user code, averaged over all processors t
12 AVG_SYS_TIME 5403802 Number of hundredths of a second that a processor has been busy executing kernel code, averaged over all processors t
13 AVG_IOWAIT_TIME 2862609 Number of hundredths of a second that a processor has been waiting for I/O to complete, averaged over all processors t
14 AVG_NICE_TIME 2452 Number of hundredths of a second that a processor has been busy executing low-priority user code, averaged over all processors t
15 VM_PAGE_IN_BYTES 6551150592 Total number of bytes of data that have been paged in due to virtual memory paging t
16 VM_PAGE_OUT_BYTES 18619420672 Total number of bytes of data that have been paged out due to virtual memory paging t
17 LOAD 0 Current number of processes that are either running or in the ready state, waiting to be selected by the operating-system scheduler to run. On many platforms, this statistic reflects the average load over the past minute. f
18 PHYSICAL_MEMORY_BYTES 16637542400 Total number of bytes of physical memory f

● 节点的进程信息
node_name pid lwpid thread_name creation_time
gaussdb 140644726077184 67 IncrBgWriter 2021-10-23 11:08:38.399446+00
gaussdb 140644754913024 66 IncrBgWriter 2021-10-23 11:08:38.398781+00
gaussdb 140644782176000 65 IncrBgWriter 2021-10-23 11:08:38.398897+00
gaussdb 140644810487552 64 PageWriter 2021-10-23 11:08:38.39821+00
gaussdb 140644836177664 63 PageWriter 2021-10-23 11:08:38.398143+00
gaussdb 140644130486016 85 undo recycler 2021-10-23 11:08:38.643228+00
gaussdb 140644344395520 81 LWLock Monitor 2021-10-23 11:08:38.634894+00
gaussdb 140644370020096 80 TwoPhase Cleaner 2021-10-23 11:08:38.633274+00
gaussdb 140644947851008 69 Wal Writer Auxiliary 2021-10-23 11:08:38.61514+00
gaussdb 140645057943296 68 Wal Writer 2021-10-23 11:08:38.611709+00
gaussdb 140644875433728 61 CheckPointer 2021-10-23 11:08:38.397482+00
gaussdb 140644858652416 62 InvalidBufferBgWriter 2021-10-23 11:08:38.397675+00
gaussdb 140644085397248 564 psql 2021-10-23 12:38:42.534131+00
gaussdb 140644258412288 84 WLMArbiter  
gaussdb 140644286199552 83 WorkloadMonitor  
gaussdb 140644313986816 82 workload 2021-10-23 11:08:38.635906+00
gaussdb 140644440340224 78 statement flush thread 2021-10-23 11:08:38.812169+00
gaussdb 140644525274880 75 WDRSnapshot 2021-10-23 11:08:38.811736+00
gaussdb 140644469700352 77 Asp 2021-10-23 11:08:38.654603+00
gaussdb 140644499519232 76 PercentileJob 2021-10-23 11:08:38.803184+00
gaussdb 140644630656768 71 JobScheduler 2021-10-23 11:08:38.802671+00

[回到目录]

锁情况

● 所有进程

● 总计
最大连接数 当前连接数 剩余连接数
200 9 191

● 详情
datid datname pid sessionid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change waiting enqueue state resource_pool query_id query connection_info
14435 postgres 140644630656768 140644630656768 10 omm JobScheduler       2021-10-23 11:08:38.802671+00     2021-10-23 12:38:43.01554+00 f   active default_pool 0    
14435 postgres 140644499519232 140644499519232 10 omm PercentileJob       2021-10-23 11:08:38.803184+00     2021-10-23 12:38:34.326241+00 f   active default_pool 0    
14435 postgres 140644469700352 140644469700352 10 omm Asp       2021-10-23 11:08:38.654603+00     2021-10-23 12:38:42.843506+00 f   active default_pool 0    
14435 postgres 140644525274880 140644525274880 10 omm WDRSnapshot       2021-10-23 11:08:38.811736+00     2021-10-23 12:38:43.58068+00 f   idle default_pool 0    
14435 postgres 140644440340224 140644440340224 10 omm statement flush thread       2021-10-23 11:08:38.812169+00     2021-10-23 11:08:38.812216+00 f   idle default_pool 0    
14435 postgres 140644313986816 140644313986816 10 omm workload       2021-10-23 11:08:38.635906+00 2021-10-23 11:08:38.642034+00 2021-10-23 11:08:38.642034+00 2021-10-23 11:08:38.820875+00 f   active default_pool 0 WLM fetch collect info from data nodes  
14435 postgres 140644286199552 140644286199552 10 omm WorkloadMonitor       2021-10-23 11:08:38.640325+00       f     default_pool 0    
14435 postgres 140644258412288 140644258412288 10 omm WLMArbiter       2021-10-23 11:08:38.640702+00       f     default_pool 0    
16825 tpcclhr 140644085397248 140644085397248 16385 gaussdb psql 192.168.66.64   4642 2021-10-23 12:38:42.534131+00 2021-10-23 12:38:43.58464+00 2021-10-23 12:38:43.58464+00 2021-10-23 12:38:43.584645+00 f   active default_pool 844424930133327 select a.* from pg_stat_activity a ;  

● 锁
locktype database relation page tuple bucket virtualxid transactionid classid objid objsubid virtualtransaction pid sessionid mode granted fastpath locktag global_sessionid
relation 16825 11986                 11/1616 140644085397248 140644085397248 AccessShareLock t t 41b9:2ed2:0:0:0:0 0:0#0
virtualxid           11/1616         11/1616 140644085397248 140644085397248 ExclusiveLock t t b:650:0:0:0:7 0:0#0
virtualxid           8/1082         8/1082 140644313986816 140644313986816 ExclusiveLock t t 8:43a:0:0:0:7 0:0#0

● 查询等待锁的线程状态信息
node_name db_name thread_name query_id tid sessionid lwtid psessionid tlevel smpid wait_status wait_event locktag lockmode block_sessionid global_sessionid

● 查询当前使用内存最多的会话信息
sessid threadid contextname level parent totalsize freesize usedsize

● state_change字段长时间没有更新过的连接信息
datid datname pid sessionid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change waiting enqueue state resource_pool query_id query connection_info
14435 postgres 140644440340224 140644440340224 10 omm statement flush thread       2021-10-23 11:08:38.812169+00     2021-10-23 11:08:38.812216+00 f   idle default_pool 0    
14435 postgres 140644525274880 140644525274880 10 omm WDRSnapshot       2021-10-23 11:08:38.811736+00     2021-10-23 12:38:43.842418+00 f   idle default_pool 0    

[回到目录]

SQL情况

● 慢SQL查询

● 最耗IO的SQL

[回到目录]

高可用情况

● 主从流复制情况

● 主库查看wal日志发送状态
pid usesysid usename application_name client_addr client_hostname client_port backend_start state sender_sent_location receiver_write_location receiver_flush_location receiver_replay_location sync_priority sync_state

● 主从流复制冲突统计
datid datname confl_tablespace confl_lock confl_snapshot confl_bufferpin confl_deadlock
1 template1 0 0 0 0 0
16384 omm 0 0 0 0 0
16825 tpcclhr 0 0 0 0 0
14430 template0 0 0 0 0 0
14435 postgres 0 0 0 0 0

[回到目录]

● 逻辑复制

● 查看复制槽
slot_name plugin slot_type datoid database active xmin catalog_xmin restart_lsn dummy_standby

[回到目录]

数据库性能

● 相关参数

● 重要参数
name setting unit category short_desc extra_desc context vartype source min_val max_val enumvals boot_val reset_val sourcefile sourceline
archive_mode off   Write-Ahead Log / Archiving Allows archiving of WAL files using archive_command.   sighup bool default       off off    
client_encoding UTF8   Client Connection Defaults / Locale and Formatting Sets the client's character set encoding.   user string default       SQL_ASCII UTF8    
config_file /var/lib/opengauss/data/postgresql.conf   File Locations Sets the server's main configuration file.   postmaster string override         /var/lib/opengauss/data/postgresql.conf    
connection_info     Reporting and Logging / What to Log Sets the connection info to be reported in statistics and logs.   user string default              
data_directory /var/lib/opengauss/data   File Locations Sets the server's data directory.   postmaster string override         /var/lib/opengauss/data    
hba_file /var/lib/opengauss/data/pg_hba.conf   File Locations Sets the server's "hba" configuration file.   postmaster string override         /var/lib/opengauss/data/pg_hba.conf    
ident_file /var/lib/opengauss/data/pg_ident.conf   File Locations Sets the server's "ident" configuration file.   postmaster string override         /var/lib/opengauss/data/pg_ident.conf    
listen_addresses *   Connections and Authentication / Connection Settings Sets the host name or IP address(es) to listen to.   postmaster string configuration file       localhost * /var/lib/opengauss/data/postgresql.conf 772
log_directory pg_log   Reporting and Logging / Where to Log Sets the destination directory for log files. Can be specified as relative to the data directory or as absolute path. sighup string default       pg_log pg_log    
log_filename postgresql-%Y-%m-%d_%H%M%S.log   Reporting and Logging / Where to Log Sets the file name pattern for log files.   sighup string configuration file       postgresql-%Y-%m-%d_%H%M%S.log postgresql-%Y-%m-%d_%H%M%S.log /var/lib/opengauss/data/postgresql.conf 393
logging_collector on   Reporting and Logging / Where to Log Starts a subprocess to capture stderr output and/or csvlogs into log files.   postmaster bool configuration file       off on /var/lib/opengauss/data/postgresql.conf 385
log_min_duration_statement 1800000 ms Reporting and Logging / When to Log Sets the minimum execution time above which statements will be logged. Zero prints all queries. -1 turns this feature off. superuser integer configuration file -1 2147483647   -1 1800000 /var/lib/opengauss/data/postgresql.conf 448
log_statement none   Reporting and Logging / What to Log Sets the type of statements logged.   superuser enum default     {none,ddl,mod,all} none none    
log_truncate_on_rotation off   Reporting and Logging / Where to Log Truncates existing log files of same name during log rotation.   sighup bool default       off off    
max_connections 200   Connections and Authentication / Connection Settings Sets the maximum number of concurrent connections for clients.   postmaster integer configuration file 10 262143   200 200 /var/lib/opengauss/data/postgresql.conf 74
port 5432   Connections and Authentication / Connection Settings Sets the TCP port the server listens on.   postmaster integer default 1 65535   5432 0    

● 数据库内存
nodename memorytype memorymbytes
gaussdb max_process_memory 12288
gaussdb process_used_memory 458
gaussdb max_dynamic_memory 11069
gaussdb dynamic_used_memory 455
gaussdb dynamic_peak_memory 457
gaussdb dynamic_used_shrctx 155
gaussdb dynamic_peak_shrctx 157
gaussdb max_backend_memory 348
gaussdb backend_used_memory 1
gaussdb max_shared_memory 358
gaussdb shared_used_memory 166
gaussdb max_cstore_memory 512
gaussdb cstore_used_memory 0
gaussdb max_sctpcomm_memory 0
gaussdb sctpcomm_used_memory 0
gaussdb sctpcomm_peak_memory 0
gaussdb other_used_memory 0
gaussdb gpu_max_dynamic_memory 0
gaussdb gpu_dynamic_used_memory 0
gaussdb gpu_dynamic_peak_memory 0
gaussdb pooler_conn_memory 0
gaussdb pooler_freeconn_memory 0
gaussdb storage_compress_memory 0
gaussdb udf_reserved_memory 0

● 后台写进程统计信息
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
506 10 141 1607 0 0 0 84918 0 0 2021-10-23 04:09:59.491128+00

● SQL统计信息
node_name user_name select_count update_count insert_count delete_count mergeinto_count ddl_count dml_count dcl_count total_select_elapse avg_select_elapse max_select_elapse min_select_elapse total_update_elapse avg_update_elapse max_update_elapse min_update_elapse total_insert_elapse avg_insert_elapse max_insert_elapse min_insert_elapse total_delete_elapse avg_delete_elapse max_delete_elapse min_delete_elapse
gaussdb gs_role_copy_files 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
gaussdb gs_role_signal_backend 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
gaussdb gs_role_tablespace 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
gaussdb gs_role_replication 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
gaussdb gs_role_account_lock 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
gaussdb gs_role_pldebugger 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
gaussdb omm 979 0 0 0 0 0 979 0 2479845 2533 8117 580 0 0 0 0 0 0 0 0 0 0 0 0
gaussdb gaussdb 22 0 0 0 0 0 22 7 498435 22656 286719 732 0 0 0 0 0 0 0 0 0 0 0 0
gaussdb lhr 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

● 时间消耗信息统计
stat_id stat_name value
0 DB_TIME 3581001
1 CPU_TIME 3302145
2 EXECUTION_TIME 1709658
3 PARSE_TIME 42729
4 PLAN_TIME 471928
5 REWRITE_TIME 20926
6 PL_EXECUTION_TIME 0
7 PL_COMPILATION_TIME 0
8 NET_SEND_TIME 88606
9 DATA_IO_TIME 105327

● 数据文件IO信息统计（物理读最多的10个文件）
filenum dbid spcid phyrds phywrts phyblkrd phyblkwrt readtim writetim avgiotim lstiotim miniotim maxiowtm
13972 14435 1663 149 0 149 0 29417 0 197 27 9 13944
13990 14435 1663 128 0 128 0 2899 0 22 23 9 1029
13976 16384 1663 122 0 122 0 2982 0 24 18 13 128
13982 16384 1663 42 0 42 0 1181 0 28 20 9 118
13975 14435 1663 32 0 32 0 614 0 19 27 11 64
14029 14435 1663 30 0 30 0 571 0 19 13 11 51
13903 14435 1663 27 0 27 0 484 0 17 17 11 41
14045 14435 1663 26 0 26 0 593 0 22 28 10 132
13993 14435 1663 22 0 22 0 20281 0 921 54 12 19463
13992 14435 1663 17 0 17 0 306 0 18 15 10 38

[回到目录]

其它

● 当前库的所有schema
catalog_name schema_name schema_owner default_character_set_catalog default_character_set_schema default_character_set_name sql_path
tpcclhr pg_toast omm        
tpcclhr cstore omm        
tpcclhr pkg_service omm        
tpcclhr dbe_perf omm        
tpcclhr snapshot omm        
tpcclhr blockchain omm        
tpcclhr pg_catalog omm        
tpcclhr public omm        
tpcclhr sqladvisor omm        
tpcclhr dbe_pldebugger omm        
tpcclhr information_schema omm        
tpcclhr db4ai omm        

● 当前库的所有角色(用户)
rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcatupdate rolcanlogin rolreplication rolauditadmin rolsystemadmin rolconnlimit rolpassword rolvalidbegin rolvaliduntil rolrespool rolparentid roltabspace rolconfig oid roluseft rolkind nodegroup roltempspace rolspillspace rolmonitoradmin roloperatoradmin rolpolicyadmin
gs_role_copy_files f t f f f f f f f -1 ********     default_pool 0     1044 f n       f f f
gs_role_account_lock f t f f f f f f f -1 ********     default_pool 0     1048 f n       f f f
gs_role_replication f t f f f f f f f -1 ********     default_pool 0     1047 f n       f f f
gs_role_tablespace f t f f f f f f f -1 ********     default_pool 0     1046 f n       f f f
omm t t t t t t t t t -1 ********     default_pool 0     10 t n       t t t
gs_role_pldebugger f t f f f f f f f -1 ********     default_pool 0     1055 f n       f f f
gs_role_signal_backend f t f f f f f f f -1 ********     default_pool 0     1045 f n       f f f
lhr f t t t f t f t t -1 ********     default_pool 0     16970 f n       f f f
gaussdb f t f f f t f f t -1 ********     default_pool 0     16385 f n       f f f

usename usesysid usecreatedb usesuper usecatupd userepl passwd valbegin valuntil respool parent spacelimit useconfig nodegroup tempspacelimit spillspacelimit usemonitoradmin useoperatoradmin usepolicyadmin
omm 10 t t t t ********     default_pool 0           t t t
gaussdb 16385 f f f f ********     default_pool 0           f f f
lhr 16970 t f f f ********     default_pool 0           f f f

● 所有语言
lanname lanowner lanispl lanpltrusted lanplcallfoid laninline lanvalidator lanacl
internal 10 f f 0 0 2246  
c 10 f f 0 0 2247  
sql 10 f t 0 0 2248  
java 10 f f 0 0 0  
plpgsql 10 t t 11897 11898 11899  

● 所有可用插件

● 所有插件（已编译）
name default_version installed_version comment
dist_fdw 1.0 1.0 foreign-data wrapper for distfs access
hstore 1.1 1.1 data type for storing sets of (key, value) pairs
plpgsql 1.0 1.0 PL/pgSQL procedural language
postgres_fdw 1.0   foreign-data wrapper for remote PostgreSQL servers
hdfs_fdw 1.0 1.0 foreign-data wrapper for flat file access
mot_fdw 1.0 1.0 foreign-data wrapper for MOT access
file_fdw 1.0 1.0 foreign-data wrapper for flat file access
security_plugin 1.0 1.0 provides security functionality
log_fdw 1.0 1.0 Foreign Data Wrapper for accessing logging data

● 已安装的插件
Name Version Schema Description
dist_fdw 1.0 pg_catalog foreign-data wrapper for distfs access
file_fdw 1.0 pg_catalog foreign-data wrapper for flat file access
hdfs_fdw 1.0 pg_catalog foreign-data wrapper for flat file access
hstore 1.1 pg_catalog data type for storing sets of (key, value) pairs
log_fdw 1.0 pg_catalog Foreign Data Wrapper for accessing logging data
mot_fdw 1.0 pg_catalog foreign-data wrapper for MOT access
plpgsql 1.0 pg_catalog PL/pgSQL procedural language
security_plugin 1.0 pg_catalog provides security functionality

[回到目录]