数据库巡检项

ORACLE
db_cache_size 无steal情况；
shared_pool_size 无steal情况；
free memory大于1024M；

pga_aggregate_target    无overallco情况；    
log_buffer    大于128M    
session_cached_cursors    使用率低于60%    
OPEN CURSOR    使用率低于60%    
db_files     使用率低于60%    
DATA  tablespace Usage    使用率低于80%    
UNDOTBS  Usage    active & unexpired使用率低于80%    
Temporary tablespace Usage    使用率低于60%    
redo log size & group        
FRA Usage    使用率低于80%    
processes    历史峰值低于80%    
block_corruption    无坏块    
DATA 卷空间检查    适用于filesystem、核心DATA卷空间使用率低于80%    
TOP SQL

MONGODB
CG配置
检查cg配置：
/opt/cgtools/cginfo -t perf -s cpu
/opt/cgtools/cginfo -t perf -s mem

路由、配置、数据节点CPU使用率        "检查zabbix监控，有超过70%的cg配置，则需升配

http://hidba.source.com.cn/hidba/minitor/mongodb
调高cpu使用率同时，需联动调高memory配置，同时评估应用连接数是否需同步调整。

数据节点内存使用率
检查zabbix监控，有超过70%的cg配置，则需升配

连接数配置        
非分片架构：

主、从、仲裁连接数需设置相同

连接数有超70%，则需评估升配，调高连接数的同时，需联动观察节点cpu、memory同步调高。

分片架构：
Mongos端连接数调高，需联动调整配置层、shard层节点的连接数。关联关系：
Mongod连接数=单个mongos配置连接数 * mongos数量+200
Mongod连接数调整，需联动调整memory、cpu的配置；
Mongod memory配置调整，需联动调整db cacheSizeGB参数

replication 状态        
1、oplogsize检查

rs.printReplicationInfo()
若当前oplogsize小于5—7天变化量，则需扩容oplogsize

2、lag检查
rs.printSlaveReplicationInfo()
如果oplog lag超过2小时，则需检查从库状态，是否有慢操作，是否hang。可重启从库。

cluster 整体状态        
非分片架构：

rs.status()
有primary存在，且仅有1个primary存在
replicaSet中所有节点statemsg为PRIMARY、SECONDARY、ARBITER三态，若有其他状态异常；health=1，否则异常

分片架构：
Sh.status()
配置层、分片层副本状态正常：rs.status()
分片层未有丢失，分片均衡满足当前设置，未有jumbo chunk存在。

rebalance状态        
在mongos端执行：

sh.getBalancerState() 确认均衡状态为当前您需要控制的状态。

停止均衡方法：
sh.stopBalancer()
检查停止均衡结果：
sh.getBalancerState()
use config
while( sh.isBalancerRunning() ) {

      print(""waiting..."");
      sleep(1000);

}

启动均衡方法：
sh.startBalancer()
sh.getBalancerState() 确认结果为true

MYSQL CG配置 "检查cg配置：
/opt/cgtools/cginfo -t perf -s cpu
/opt/cgtools/cginfo -t perf -s mem (内存使用率尽量低于80%)

CPU使用率        ZABBIX监控、GRAFANA监控
检查keepalived环境        查看/var/log/messages
查看错误日志        查看error.log日志是否有错误信息
连接数配置        ZABBIX监控、GRAFANA监控
查看慢sql        检查slow.log里面是否有活动的慢sql，如有提前进行优化
检查连接数情况    SLEEP状态 + QUERY状态 会话数量< 用户最大连接数*60%    
 MYSQL > select  db,user,command,count(*) from information_schema.processlist group by db,user,command order by 4 desc;

MYSQL > show variables like '%connections%';

InnoDB Buffer命中率情况    不低于90%    "mysql> show status like'innodb_buffer_pool_read%';

命中率 = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%"

查看同步情况        show slave status\G

REDIS CG配置 "检查cg配置：
/opt/cgtools/cginfo -t perf -s cpu
/opt/cgtools/cginfo -t perf -s mem

CPU使用率        ZABBIX监控、GRAFANA监控
内存使用率        ZABBIX监控、GRAFANA监控
连接数配置        ZABBIX监控、GRAFANA监控
BIG KYES提前检查        [PRD-DMZREC:cnsz081257:6455:S ~]$r --bigkeys
数据分布情况检查    cluster各节点数据分布均衡    
查看GRAFANA监控各节点内存使用量；

$REDIS_HOME/bin/redis-trib.rb info --password $pw IP:PORT ---查看keys分布情况

Slowlog检查    开发确认业务增长量    127.0.0.1:6472> slowlog get 10

PG 实例CG分配CPU容量及使用率
$ cg
==================== Cgroup Performance: cpu ====================
DB_TYPE INSTANCE_NAME CPU_USER CPU_SYS CPU_USED CPU_ALLO ALLO_RATE CPU_GLOB GLOB_RATE
------- ------------- -------- ------- -------- -------- --------- -------- ---------
postgres eits 4.5 0.9 5.6 20 28.0% 20 28.0%

==================== Cgroup Performance: memory ====================
DB_TYPE INSTANCE_NAME MEM_OOM MEM_FILE_GB MEM_MAP_GB MEM_USED_GB MEM_ALLO_GB ALLO_RATE MEM_GLOB_GB GLOB_RATE
------- ------------- ------- ----------- ---------- ----------- ----------- --------- ----------- ---------
postgres eits 0 0.0 0.0 0.0 0.0 0.0% 251 0.0%

==================== Cgroup Performance: proc ====================
DB_TYPE INSTANCE_NAME DATA_VOLUME VOLUME_FUSER PROC_COUNT
------- ------------- ----------- ------------ ----------
postgres eits /source/pg5521/data postgres:143 142"

最大连接数        select setting from pg_settings  where name ~'max_connections';
查看shared_buffers        
# show shared_buffers ;

shared_buffers

40GB
(1 row)

Time: 1.495 ms"

缓存命中率(90%以上为OK)        
\c 切换到业务database

select t.snap_time,round(CAST ( (100*(t.blks_hit-t.pre_hit)/((t.blks_read-t.pre_read)+(t.blks_hit-t.pre_hit+1))) AS numeric) ,2)::varchar as hitrate
from
(select snap_time,blks_hit,blks_read,lag(b.blks_hit) over (partition by 1 order by s.snap_time) pre_hit,
lag(b.blks_read) over (partition by 1 order by s.snap_time) pre_read
from pgawr_database_v b,pgawr_snap s where b.snapid = s.snapid and datname=current_database() order by s.snap_time desc) t limit 20;"

data卷容量        
$ df -TH|grep $PORT
                 vxfs    22G  373M   21G   2% /source/pg5521/app
                 vxfs    37T   23T   14T  64% /source/pg5521/data
备份卷容量        
$ df -TH|grep pgbackup
                 nfs     23T   17T  5.5T  76% /source/pgbackup
                 
事物ID检查        
查询database事物年龄及百分比

Select datname,age(datfrozenxid),2^31 - age(datfrozenxid) left_age,round(age(datfrozenxid)/2^31::numeric,4)*100||'%' age_used_pct from pg_database;
查询表事物年龄及百分比
SELECT relname, pg_size_pretty(pg_table_size(oid)) as table_size,age(relfrozenxid),2^31-age(relfrozenxid) as left_age,round(age(relfrozenxid)/2^31::numeric,4)*100||'%' age_used_pct FROM pg_class where Relkind in ('r','t') order by 2 desc;"

超过10小时长事物        select 'pg.long_transaction:'||count(*) from pg_stat_activity where state <> 'idle' and now() - xact_start > '36000 sec'::interval;
是否有阻塞        "with recursive t_wait as (select a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid ,a.transactionid from pg_locks a where not a.granted),

t_run as (select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start
,b.usename,b.datname from pg_locks a,pg_stat_activity b
where a.pid=b.pid and a.granted),
w as(select r.pid r_pid, w.pid w_pid
from t_wait w,t_run r where
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.transactionid is not distinct from w.transactionid and
r.virtualxid is not distinct from w.virtualxid)
,c(waiter, holder, root_holder, path, deep) as(
select w_pid, r_pid, r_pid, w_pid||'->'||r_pid, 1 from w
union
select w_pid, r_pid, c.holder, w_pid||'->'||c.path, c.deep+1 from w t, c where t.r_pid = c.waiter
)
select t1.waiter, t1.holder, t1.root_holder, path, t1.deep from c t1 where
not exists(select 1 from c t2 where t2.path ~ t1.path and t1.path<>t2.path )
Order by root_holder;

表锁        select a.locktype,a.pid,a.relation,a.mode,a.granted,b.relname from pg_locks a,pg_class b where a.relation=b.oid and a.mode='AccessExclusiveLock';
备份        $ pg_rman show

或者$pg_probackup show

查看主从同步状态

1、 pg lag 需要在主库执行，salve_addr为主机物理ip，如果没有对应的行说明主从延迟过大或者无从库
2、查看从库延迟日志大小
select client_addr,pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024 from pg_stat_replication;

AUTOVACUUM情况    1、是否存在有长时间不被AUTOVACUUM的对象；

2、是否存在DEAD TUPPLE占比接近20%的情况；
3、是否存在DEAD TUPPLE占比高的对象；

频繁更新的表情况    大于0就需要检查原因    "select tablename,table_updates,table_deletes,table_inserts

from (

    SELECT distinct a.table_name as tablename, 
    ((max(a.n_tup_upd) over (partition by a.table_name))-(min(a.n_tup_upd) over (partition by a.table_name))) as table_updates ,
    ((max(a.n_tup_del) over (partition by a.table_name))-(min(a.n_tup_del) over (partition by a.table_name))) as table_deletes,
    ((max(a.n_tup_ins) over (partition by a.table_name))-(min(a.n_tup_ins) over (partition by a.table_name))) as table_inserts
    FROM pgawr_tables_v a 
    join  pgawr_snap c  on a.snapid=c.snapid 
    WHERE  c.snap_time>=current_date - interval '1 D'
    AND c.snap_time<=current_date
   and a.table_name !~'pg_toast|dbmgr|information_schema|pg_catalog|pgagent' 
   group by a.table_name,a.n_tup_upd,a.n_tup_del,a.n_tup_ins
   ) tb where round(table_updates::numeric/(table_inserts+table_deletes)::numeric,2)>1 and (table_inserts+table_deletes)>0 and table_updates>100*1000 order by table_updates desc limit 20;

.达到vacuum触发条件，但是7天内未完成vacuum    有记录返回就需要检查原因    "select current_database(),t1.schemaname,t1.relname,
        case when last_autovacuum is null and last_vacuum is null then current_date - interval '5 D' 
             when last_autovacuum is null and last_vacuum is not null then last_vacuum 
             when last_autovacuum is not null and last_vacuum is null then last_autovacuum 
             else (case when last_autovacuum>=last_vacuum then last_autovacuum else last_vacuum end) end as last_time,
                        t1.n_live_tup,n_dead_tup,pg_size_pretty(pg_table_size(t2.oid)) as table_size,age(t2.relfrozenxid)
        from pg_stat_all_tables t1
   left join pg_class t2
             on t1.relname=t2.relname
  inner join pg_namespace t4
          on t1.schemaname=t4.nspname and t2.relnamespace=t4.oid  
       where  t1.n_live_tup>0
         and ( age(t2.relfrozenxid)>1500000000
         or t1.n_dead_tup/ ( t1.n_live_tup + t1.n_dead_tup ) *100>=10)
         and case when last_autovacuum is null and last_vacuum is null then current_date - interval '5 D' 
             when last_autovacuum is null and last_vacuum is not null then last_vacuum 
             when last_autovacuum is not null and last_vacuum is null then last_autovacuum 
             else (case when last_autovacuum>=last_vacuum then last_autovacuum else last_vacuum end) end + interval '7 D'< current_timestamp 
                         order by age(t2.relfrozenxid) desc limit 20;

BIG TABLES    大于100G的表需要安排分区改造    select current_database(),relname,pg_table_size(oid) tab_size from pg_class where relkind in ('r','t') and pg_table_size(oid)/1024/1024/1024>=100;
pg_class对象数，pathman分区表数    "PG_CLASS大于10W；

PATHMAN分区表数大于1W；
需要安排检查； select current_database(),(select count(1) from pg_class ) as "pg_class",(select count(1) from pg_tables) as "tables",(select count(1) from pg_indexes) as "indexes",(select count(1) from pathman_partition_list) as "partitions";

shared_buffers

相关文章：