MySQL数据库性能监控与诊断.docx
- 文档编号:15927816
- 上传时间:2023-07-09
- 格式:DOCX
- 页数:21
- 大小:361.77KB
MySQL数据库性能监控与诊断.docx
《MySQL数据库性能监控与诊断.docx》由会员分享,可在线阅读,更多相关《MySQL数据库性能监控与诊断.docx(21页珍藏版)》请在冰点文库上搜索。
MySQL数据库性能监控与诊断
MySQL性能监控与诊断
苏普@TaobaoDBA
•监控系统天机:
–群组、Dashboard...
•监控架构
•其他工具
MySQL监控:
告警架构
DBWithAgent
北斗展现
数据Server
数据分析
DBWithAgent
DBWithAgent
DBWithAgent
DBWithAgent
DBWithAgent
数据报表
MySQL监控
•找到你关心的主机:
MySQL监控:
Dashboard
天机==>Dashboard
MySQL监控:
性能指标
天机==>Dashboard==>趋势图
MySQL监控:
SQL运行
天机==>Dashboard==>TopSQL
MySQL监控:
群组监控
天机==>群组==>KPI趋势
MySQL监控:
实时监控
天机==>群组==>实时趋势
MySQL监控:
告警
天机==>告警==>已发送
MySQL监控:
告警阀值
天机==>告警==>主机阀值
合并告警
多次确认发送
常见故障排查
•慢日志
mk-query-digest\
--type=slowlog\
--since="2011-11-0215:
14:
00"\
--until="2011-11-0215:
16:
25"\
--filter'($event->{host}||$event->{ip}||"")=~m/192.168.0.1/'\slow.log
|more
常见故障排查
(2)
•Orzdba--lazy
InternalTroubleshooting
•GDB
•tcpdump
•oprofiled
GDB
•QuickAndSimple
sudosh-c'gdb-ex"setpagination0"–ex\
"threadapplyallbt"--batch\
-p$(pidofmysqld)>bt.log'
sudosh-c'gdb-ex"setpagination0"-ex\
"threadapplyallbtfull"--batch\
-p$(pidofmysqld)>bt.log.full'
GDB-1
•Sample:
#00x00002aaab3020481inbuf_LRU_search_and_free_block
#10x00002aaab302094einbuf_LRU_get_free_block
#20x00002aaab301643einbuf_page_init_for_read
#30x00002aaab30214d5inbuf_read_page_low
#40x00002aaab3021ec1inbuf_read_ahead_linear
#50x00002aaab3018d0einbuf_page_get_gen
#60x00002aaab300c612inbtr_pcur_move_to_next_page
#70x00002aaab30b8d6ainrow_search_for_mysql
#80x00002aaab305133cinha_innodb:
:
general_fetch
#90xa7d97inrr_sequential
#100x1b756insub_select
#110x2e0adindo_select
#120x3b0d4inJOIN:
:
exec
GDB-2
•PMP:
poorman'sprofiler
forxin$(seq1$nsamples)
do
gdb-ex"setpagination0"-ex"threadapplyallbt"
-batch-p$pid
sleep$sleeptime
done|\
awk'
BEGIN{s="";}
/Thread/{prints;s="";}
/^\#/{if(s!
=""){s=s","$4}else{s=$4}}
END{prints}'|\
sort|uniq-c|sort-r-n-k1,1
tcpdump
•tcpdump:
nohuptcpdump-n-nn-tttt-ibond0\
-s65535'port3306'-wtcpdump.ret-C100&
tcpdump-1
•tcpdump:
Client端瓶颈
tcpdump-2
•tcpdump:
Server端瓶颈
tcpdump-3
•tcpdump:
ResponseTimeMonitor
oprofile
•oprofile:
sudoyuminstallbinutils-devel
./configure--with-kernel-support&&make
makeinstall
sudoopcontrol--deinit
sudomodprobeoprofiletimer=1
$dmesg|grepoprofile|tail-n1
sudoopcontrol--reset
sudoopcontrol--separate=lib--no-vmlinux\
--start--image=/opt/mysql/bin/mysqld
sudoopcontrol--dump
sudoopcontrol--shutdown
opreport-l/opt/mysql/bin/mysqld
Oprofile-1
•oprofile:
CPU:
CPUwithtimerinterrupt,speed0MHz(estimated)
Profilingthroughtimerinterrupt
samples%imagenamesymbolname
3404372.3826ha_innodb_plugin.so.0.0.0buf_LRU_search_and_free_block
483710.2845ha_innodb_plugin.so.0.0.0ut_delay
29596.2915ha_innodb_plugin.so.0.0.0buf_LRU_free_block
12832.7279libpthread-2.5.sopthread_mutex_lock
8511.8094ha_innodb_plugin.so.0.0.0buf_page_is_corrupted
5461.1609ha_innodb_plugin.so.0.0.0mutex_spin_wait
3150.6698libc-2.5.somemcpy
2390.5082ha_innodb_plugin.so.0.0.0buf_calc_page_new_checksum
1790.3806ha_innodb_plugin.so.0.0.0rec_get_offsets_func
1620.3444libpthread-2.5.sopthread_mutex_unlock
1070.2275ha_innodb_plugin.so.0.0.0row_search_for_mysql
980.2084ha_innodb_plugin.so.0.0.0row_sel_store_mysql_rec
数据查询:
WebSQLPlus
WebSQLPlus==>分库分表
数据查询:
云梯
链接:
hive>
SELECTcount
(1)
FROMs_xxx
WHEREuser_id=452751208andpt=200;
Q&A
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 数据库 性能 监控 诊断