MySQL性能监控(sql命令语句MONyog⼯具)全掌握,这⼀篇⾜以!MySQL性能监控⽅式
Mysql监控,有两种⽅式:
①是通过命令或SQL来完成监控任务
②通过性能监控⼯具,例如:MONyog。
下⾯,就跟着⼩鱼⼀起,看看如何监控MySQL的!!
1、性能监控之sql语句命令
1. 监控事例的等待
select event,sum(decode(wait_Time,0,0,1))"Prev",
sum(decode(wait_Time,0,1,0))"Curr",count(*)"Tot"
from v$session_Wait
group by event order by4;
2. 回滚段的争⽤情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O ⽐例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控⽂件系统的 I/O ⽐例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes,
b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个⽤户下所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value+ b.value"logical_reads", c.value"phys_reads",
round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100"miss ratio",
(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率,应该⼩于1%
select sum(pins)"Total Pins",sum(reloads)"Total Reloads",
sum(reloads)/sum(pins)*100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins)"hit radio",sum(reloads)/sum(pins)"reload percent" from v$librarycache;
9. 显⽰所有数据库对象的类别和⼤⼩
select count(name) num_instances ,type,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size) size_required
from dba_object_size
group by type order by2;
10. 监控 SGA 中重做⽇志缓存区的命中率,应该⼩于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN('redo allocation','redo copy');⽚
11.监控内存和硬盘的排序⽐率,最好使它⼩于 .10,增加 sort_area_size
SELECT name,value FROM v$sysstat WHERE name IN('sorts (memory)','sorts (disk)');
12. 监控当前数据库谁在运⾏什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 监控字典缓冲区
SELECT(SUM(PINS - RELOADS))/SUM(PINS)"LIB CACHE"FROM V$LIBRARYCACHE;
SELECT(SUM(GETS - GETMISSES -USAGE-FIXED))/SUM(GETS)"ROW CACHE"FROM V$ROWCACHE;
SELECT SUM(PINS)"EXECUTIONS",SUM(RELOADS)"CACHE MISSES WHILE EXECUTING"FROM V$LIBRARYCACHE;后者除以前者,此⽐率⼩于1%,接近0%为好。
SELECT SUM(GETS)"DICTIONARY GETS",SUM(GETMISSES)"DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
14. ORACLE字符集
select*from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
select busy/(busy+idle)"shared servers busy"from v$dispatcher;
此值⼤于0.5时,参数需加⼤
mysql下载到了c盘 select sum(wait)/sum(totalq)"dispatcher waits"from v$queue where type='dispatcher';
select count(*)from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时,参数需加⼤
⼩屌丝这时候不乐意了,
⼩屌丝:鱼哥,我作为⼀个屌丝,我可不想记这些命令,有没有可视化⼯具,让我来搞搞~
⼩鱼:那必须的有!也不瞅瞅我是谁。(不要脸的劲⼜来了 ~ ~)
2、性能监控之监控⼯具MONyog
2.下载好以后,就安装即可,最后看到这个界⾯就⾏:
注意⼏点:
①这个⽹址要记住,最后你可登录的,就是这个⽹址:127.0.0.1
②安装的时候,建议不要安装到系统盘(/C盘),有的时候,会启不来(这不是晚上睡得太晚,太累… 别想多了) 。
3.配置sqlserver,刚刚进⼊MONyog页⾯,会要你add a server, 点击追加信息
4.慢查询配置
切换到"ADVANCED"tab页,进⾏慢查询配置
如果不好⽤的话,需要到数据库服务器上到/etc⽬录的myf⽂件,在⽂件的最后加上以下内容,保存⽂件log-slow-queries=/var/lib/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexe
5.死锁设置
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论