PostgreSQL 数据库巡检
背景
PostgreSQL数据库的巡检脚本在这里，自动生成txt格式巡检报告(包括采集项以及建议项目)。
https://github.com/digoal/pgsql_admin_script/blob/master/generate_report.sh
巡检项目前包括了如下：
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 操作系统信息 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 主机名:
----->>>---->>> 以太链路信息:
----->>>---->>> IP地址信息:
----->>>---->>> 路由信息:
----->>>---->>> 操作系统内核:
----->>>---->>> 内存(MB):
----->>>---->>> CPU:
----->>>---->>> 块设备:
----->>>---->>> 拓扑:
----->>>---->>> 进程树:
----->>>---->>> 操作系统配置文件 静态配置信息:
----->>>---->>> /etc/sysctl.conf
----->>>---->>> /etc/security/limits.conf
----->>>---->>> /etc/security/limits.d/*.conf
----->>>---->>> /etc/sysconfig/iptables
----->>>---->>> /etc/fstab
----->>>---->>> /etc/rc.local
----->>>---->>> /etc/selinux/config
----->>>---->>> /boot/grub/grub.conf
----->>>---->>> /var/spool/cron 用户cron配置
----->>>---->>> chkconfig --list
----->>>---->>> iptables -L -v -n -t filter 动态配置信息:
----->>>---->>> iptables -L -v -n -t nat 动态配置信息:
----->>>---->>> iptables -L -v -n -t mangle 动态配置信息:
----->>>---->>> iptables -L -v -n -t raw 动态配置信息:
----->>>---->>> sysctl -a 动态配置信息:
----->>>---->>> mount 动态配置信息:
----->>>---->>> selinux 动态配置信息:
----->>>---->>> 建议禁用Transparent Huge Pages (THP):
----->>>---->>> 硬盘SMART信息(需要root):
----->>>---->>> /var/log/boot.log
----->>>---->>> /var/log/cron(需要root)
----->>>---->>> /var/log/dmesg
----->>>---->>> /var/log/messages(需要root)
----->>>---->>> /var/log/secure(需要root)
----->>>---->>> /var/log/wtmp
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库信息 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 数据库版本:
----->>>---->>> 用户已安装的插件版本:
----->>>---->>> 用户使用了多少种数据类型:
----->>>---->>> 用户创建了多少对象:
----->>>---->>> 用户对象占用空间的柱状图:
----->>>---->>> 当前用户的操作系统定时任务:
----->>>---->>> 获取pg_hba.conf md5值:
----->>>---->>> 获取pg_hba.conf配置:
----->>>---->>> 获取postgresql.conf md5值:
----->>>---->>> 获取postgresql.conf配置:
----->>>---->>> 用户或数据库级别定制参数:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库错误日志分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 获取错误日志信息:
----->>>---->>> 获取连接请求情况:
----->>>---->>> 获取认证失败情况:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库慢SQL日志分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 慢查询统计:
----->>>---->>> 慢查询分布头10条的执行时间, ms:
----->>>---->>> 慢查询分布尾10条的执行时间, ms:
----->>>---->>> auto_explain 分析统计:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库空间使用分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 输出文件系统剩余空间:
----->>>---->>> 输出表空间对应目录:
----->>>---->>> 输出表空间使用情况:
----->>>---->>> 输出数据库使用情况:
----->>>---->>> TOP 10 size对象:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库连接分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 当前活跃度:
----->>>---->>> 总剩余连接数:
----->>>---->>> 用户连接数限制:
----->>>---->>> 数据库连接限制:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库性能分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> TOP 5 SQL : total_cpu_time
----->>>---->>> 索引数超过4并且SIZE大于10MB的表:
----->>>---->>> 上次巡检以来未使用或使用较少的索引:
----->>>---->>> 数据库统计信息, 回滚比例, 命中比例, 数据块读写时间, 死锁, 复制冲突:
----->>>---->>> 检查点, bgwriter 统计信息:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库垃圾分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 表引膨胀检查:
----->>>---->>> 索引膨胀检查:
----->>>---->>> 垃圾数据:
----->>>---->>> 未引用的大对象:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库年龄分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 数据库年龄:
----->>>---->>> 表年龄:
----->>>---->>> 长事务, 2PC:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库XLOG, 流复制状态分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 是否开启归档, 自动垃圾回收:
----->>>---->>> 归档统计信息:
----->>>---->>> 流复制统计信息:
----->>>---->>> 流复制插槽:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 数据库安全或潜在风险分析 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 密码泄露检查:
----->>>---->>> 简单密码检查:
----->>>---->>> 用户密码到期时间:
----->>>---->>> SQL注入风险分析:
----->>>---->>> 普通用户对象上的规则安全检查:
----->>>---->>> 普通用户自定义函数安全检查:
----->>>---->>> unlogged table 和 哈希索引:
----->>>---->>> 剩余可使用次数不足1000万次的序列检查:
----->>>---->>> 触发器, 事件触发器:
----->>>---->>> 检查是否使用了a-z 0-9 _ 以外的字母作为对象名:
----->>>---->>> 锁等待:
----->>>---->>> 继承关系检查:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 重置统计信息 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 重置统计信息:
----->>>---->>> 重置pg_stat_statements统计信息:
----->>>---->>> 获取recovery.done md5值:
----->>>---->>> 获取recovery.done配置:
----->>>---->>> 获取recovery.conf md5值:
----->>>---->>> 获取recovery.conf配置:
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
| 附加信息 |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
----->>>---->>> 操作系统sysstat收集的统计信息
----->>>---->>> 其他建议:
后期改进
1. 考虑增加自动推荐索引模块
2. 区分不同版本，新增巡检项目。
3. 新增一个提醒点，分区是否过多的问题。可能引入CPU飙高的问题。
《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》