数据库进程 | ps -ef | grep kingbase | grep '\-D' | 进程正常 |
查看服务端口 | netastat -nltp|grep 54321 | 服务端口54321处于监听状态 |
事务ID | select datname, age(datfrozenxid) from sys_database; | 小于3亿 |
长事务 | select pid, backend_start, xact_start, query_start, state, backend_xid,query from sys_stat_activity where current_timestamp - xact_start > interval '2 hour'; | 无返回结果,事务启动时间小于2小时 |
数据库锁信息 | select database,relation,sc.relname,pid,mode,granted FROM sys_locks sl left join sys_database sd on sl.database=sd.oid left join sys_class sc on sl.relation=sc.oid WHERE sys_backend_pid()<>sl.pid and granted='f'; | 无返回结果,数据库无锁等待 |
各数据库大小 | select datname,sys_size_pretty(sys_database_size(datname)) from sys_database; | 记录大小 |
表膨胀 表大小 | 1.N_DEAD_TUP SELECT schemaname||'.'||relname, n_dead_tup, n_live_tup, round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio FROM sys_stat_all_tables WHERE n_dead_tup >= 10000 ORDER BY dead_tup_ratio DESC LIMIT 10; 查询单表大小:select sys_size_pretty(sys_relation_size('tablename')); 2.最大的五张表及大小 SELECT table_schema || '.' || table_name AS table_full_name, sys_size_pretty(sys_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY sys_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 5; | 列出N_DEAD_TUP大于1w的表名及表大小(N_DEAD_TUP越大,代表该表update 、delete操作较多,即表的碎片垃圾越多) 列出占用空间最大的五张表及大小 |
最大连接数/连接数/活跃连接 | select (select setting as "最大连接数" from sys_settings where name='max_connections'), (select count(*)::text as "当前连接数" from sys_stat_activity), (select count(*)::text as "活跃连接数" from sys_stat_activity where state <> 'idle'); | 记录真实数值 连接数/最大连接数 小于80% |
操作系统错误日志 | cat /var/log/messages|grep -i error | 无返回结果 |
数据库错误日志 | 检查日志是否存在异常ERROR、FATAL 日志位置:数据目录/sys_log | 无异常 |
xlog日志文件数量 | show data_directory ; cd xxx/sys_xlog ls -lR | grep "^-" | wc -l | 记录数量 注意增长情况(与数据量对比、与上个月数值对比) |