MySQL数据库性能诊断方案

1、技术创新，变革未来MySQL数据库性能诊断方案第一印象慢再试一次优化 SQL调大 buffer pool换 SSDMySQL慢怎么办第一印象慢再试一次 (不可复现的外界因素影响)优化 SQL (执行复杂度 需求复杂度)调大 buffer pool (MySQL资源限制)换 SSD (服务器资源限制)“MySQL 慢怎么办目录-MySQL 慢的诊断思路系统观测工具介绍-bcc (eBPF脚本集) 使用举例eBPF 使用方法/限制MySQL 慢的诊断思路MySQL内部 观测外部资源 观测外部需求 改造MySQL 慢的诊断思路MySQL 内部观测show processlistexplainprof

2、ilingperformance_schema / sysinnodb_metrics- ?MySQL 慢的诊断思路- 外部资源观测- 60s 快速巡检MySQL 慢的诊断思路uptimedmesg -T | tailvmstat 1mpstat -P ALLpidstat 1MySQL 慢的诊断思路iostat -xz 1free -msar -n DEV 1sar -n TCP,ETCP 1topMySQL 慢的诊断思路- 外部需求 改造- Examples of Common QueriesMySQL 慢的诊断思路SELECT article, dealer, priceFROMshop

3、 s1 WHERE price=(SELECT MAX(s2.price)FROM shop s2WHERE s1.article = s2.article);MySQL 慢的诊断思路SELECT s1.article, dealer, s1.priceFROM shop s1 JOIN (SELECT article, MAX(price) AS priceFROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price =s2.price;MySQL 慢的诊断思路SELECT s1.article, s1.d

4、ealer, s1.priceFROM shop s1LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price 1: 02 - 3: 04 - 7: 08 - 15: 016 - 31: 032 - 63: 064 - 127: 400308128 - 255: 148021|*256 - 511: 261|512 - 1023: 3|1024 - 2047: 0|2048 - 4095: 1|selectbcc 使用举例 - 1query latency (us) 0 - 1: countdistribution: 0|*|*|32

5、- 6364 - 127128 - 255256 - 511512 - 10231024 - 20472048 - 40954096 - 81918192 - 16383: 0: 9198: 25826: 8283: 12568: 14533: 9840: 4031: 463|*|*|*|*|*|select + insertbcc 使用举例 - 2MySQL 慢查询./dbslower -p pidof mysqld -m 5 - mysqlbcc 使用举例 - 2Tracing database queries for pids 4754 slower than 5 ms.select +

6、 updateTIME(s)PIDMSQUERY0.95604447545.358UPDATEsbtest1 SETk=k+1 WHEREid=5140.95619947545.837UPDATEsbtest1 SETk=k+1 WHEREid=5050.95687647545.257UPDATEsbtest1 SETk=k+1 WHEREid=5030.95597747546.656UPDATEsbtest1 SETk=k+1 WHEREid=5030.95628747546.801UPDATEsbtest1 SETk=k+1 WHEREid=5030.95587047547.554UPDA

7、TEsbtest1 SETk=k+1 WHEREid=4980.95632947547.121UPDATEsbtest1 SETk=k+1 WHEREid=497bcc 使用举例 - 2与MySQL的慢日志相比, 可以低成本地完成:获取少量慢查询获取某种模式的慢查询获取某个用户的慢查询Linux IO stackMySQLVFS (read/write/fdatasync/)Filesystem (ext4/)Block I/O (device mapper/)I/O scheduler (cfq/deadline/)SCSIPhysical Devicebcc 使用举例 - 3VFS 延迟分

8、析./ext4dist 2 1bcc 使用举例 - 3distribution|*|: count: 0: 2: 507: 22123: 10444: 2073: 3598: 11234: 2409|*|*|*|*|*|operation = writeusecs0 - 12 - 34 - 78 - 1516 - 3132 - 6364 - 127128 - 255256 - 511bcc 使用举例 - 4Ext4 文件IO延迟分析./ext4slower 1bcc 使用举例 - 4Tracingext4 operationsslowerthan 1 msTIMECOMMPIDT BYTESO

9、FF_KBLAT(ms)FILENAME21:59:40mysqld4754S 003.56ib_logfile121:59:40mysqld4754S 008.42sbtest1.ibd21:59:41mysqld4754S 003.83ib_logfile121:59:41mysqld4754S 008.35sbtest1.ibd21:59:42mysqld4754S 008.50sbtest1.ibd21:59:42mysqld4754S 003.53ib_logfile121:59:42mysqld4754S 008.34sbtest1.ibd21:59:43mysqld4754S 0

10、02.69ib_logfile121:59:43mysqld4754S 008.41sbtest1.ibdbcc 使用举例 - 4排查其他程序对IO的影响,抓住证据Tracingext4 operationsslowerthan 10 msTIMECOMMPIDT BYTESOFF_KBLAT(ms)FILENAME22:03:14dd42639W 10737418240873.20test1.img22:03:15mysqld4754W 1048576102416.48ibdata122:03:15mysqld4754W 507904204813.98ibdata122:03:15mysql

11、d4754W 1048576130252815.10sbtest1.ibd22:03:15mysqld4754S 00110.94ibdata122:03:16mysqld4754W 1048576130662422.35sbtest1.ibdbcc 使用举例 - 5块设备延迟分析./biolatency -D 2bcc 使用举例 - 5disk = sdbdistributionusecs0 - 1: count: 0|*|16 - 3132 - 6364 - 127128 - 255256 - 511512 - 10231024 - 20472048 - 4095: 0: 4694: 33

12、99: 2211: 2250: 642: 0: 0|*|*|*|*|select & updatebcc 使用举例 - 5distribution: count: 0|*|disk = sdbusecs0 - 164 - 127128 - 255256 - 511512 - 10231024 - 20472048 - 4095: 0: 0: 2: 0: 0: 3|*|selectbcc 使用举例 - 6MySQL线程对文件的IO压力汇总./filetop -p pidof mysqld -C 5bcc 使用举例 - 622:26:30 loadavg: 7.50 5.28 4.87 18/19

13、25 44235TIDCOMMREADSWRITESR_KbW_KbTFILE39956mysqld01150462Rib_logfile140075mysqld01070424Rib_logfile139900mysqld012200137RR820-08.log38046mysqld012630142RR820-08.log39085mysqld01010332Rib_logfile138957mysqld01140425Rib_logfile139959mysqld0102RibmPAQIO4780mysqld04028Rib_logfile140266mysqld01070361Rib

14、_logfile139984mysqld01110414Rib_logfile1bcc 使用举例 - 7短生命周期的临时文件检测./filetop -p pidof mysqld -C 5bcc 使用举例 - 7rootR820-08:/usr/share/bcc/tools# ./filelifeTIMEPIDCOMMAGE(s)FILE22:17:0143687cron0.00tmpfgHF5vY22:22:2139170mysqld5.30#sql1292_59a1f_0.frmbcc 使用举例 - 8短连接分析./tcplifebcc 使用举例 - 8PIDCOMMLADDRLPORT

15、RADDRRPORTTX_KBRX_KBMS44245sysbench35038330616699312.0544245sysbench35036330617736312.2044245sysbench35034330615662312.4144245sysbench35032330614638312.4544245sysbench35026330614626313.1744245sysbench35028330612552313.18bcc 使用举例 - 9长连接分析./tcptop -C 5bcc 使用举例 - 922:33:41 loadavg: 17.28 6.81 5.01 126/

16、1933 44788PIDCOMMLADDRRADDRRX_KBTX_KB44668sysbench:35654:33061611636944669sysbench:35650:33061595736544702sysbench:35728:33061587136344758sysbench:35838:33061583436244698sysbench:35718:330615797362bcc 使用举例 - 10CPU offcpu 消耗分析offcputime -df -p pgrep -nx mysqld 30bcc 使用举例 - 10ABCBAADAABCDECCEdo_syscall_64fil_io buf_read_pagebtr_cur_search_to_nth_level row_search_mvccha_innobase:index_readvfs_readbcc 使用举例目录-MySQL 慢的诊断思路系统观测工具介绍-bcc (eBPF脚本集) 使用举例eBPF 使用方法/限制eBPF 使用方法/限