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\GREDIS 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 10PG 实例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 切换到业务databaseselect 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_insertsfrom (
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";
