MySQL性能诊断与实践 洪斌 PHPCON2018

介绍性能诊断方法论,以及观测工具在MySQL性能分析过程中的运用,并通过实际案例展示面对未知环境的性能问题,该如何诊断。

展开查看详情

1.MySQL

2.• • •

3.• Little’s Law (queueing theory) • Amdahl’s Law (1967) • Universal Scalability Law (1993)

4.• USE(Utilization Saturation and Errors) • on-cpu & off-cpu • •

5.MySQL

6.• top • dmesg | tail oom-killer tcp drop • vmstat 1 r free si so us, sy, id, wa, st • mpstat -P ALL 1 CPU • pidstat 1 CPU • iostat -xz 1 r/s, w/s, rkB/s, wkB/s, await, avgqu-sz, %util • free -m • sar -n DEV 1 • sar -n TCP,ETCP 1 tcp active/s, passive/s, retrans/s

7. MySQL • error log & slow log & general log • MySQL SHOW [SESSION|GLOBAL] STATUS • SHOW PROCESSLIST • InnoDB SHOW ENGINE INNODB STATUS • Explain • performance schema

8.1. 2. MySQL 3. MySQL 4. InnoDB 5. MySQL

9. InnoDB • InnoDB SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON (t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.constraint_type IN ('PRIMARY KEY','UNIQUE')) WHERE t.table_schema NOT IN ('mysql','information_schema', ‘performance_schema') AND t.engine = ‘InnoDB' AND c.table_name IS NULL; • • ( ) SELECT a.requesting_trx_id ' ID' ,b.trx_mysql_thread_id ' ID', TIMESTAMPDIFF(SECOND,b.trx_wait_started,NOW()) ‘ ', b.trx_query ' ' , a.blocking_trx_id ' ID' ,c.trx_mysql_thread_id ' ID',d.INFO ' ' FROM information_schema.INNODB_LOCK_WAITS a INNER JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id=b.trx_id INNER JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id=c.trx_id INNER JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id=d.ID ;

10.• max_connection • innodb_buffer_pool_size • Innodb_flush_neighbors • Innodb_io_capacity • Innodb_log_file_size • innodb_thread_concurrency

11.SQL

12. Note • “ ” • •

13. BPF • BPF = Berkeley Packet Filter • The Berkeley Packet Filter (BPF) provides a raw interface to data link layers, permitting raw link-layer packets to be sent and received. • Since version 3.18, the Linux kernel includes an extended BPF virtual machine, termed extended BPF (eBPF). It can be used for non-networking purposes http://www.tcpdump.org/papers/bpf-usenix93.pdf

14.• Linux kernel 4.4+ ( 4.9+ ) • Bcc https://github.com/iovisor/bcc/blob/master/ INSTALL.md • MySQL -DENABLE_DTRACE=1 & systemtap- sdt-devel

15. Bcc 1. execsnoop 2. opensnoop 3. ext4slower 4. biolatency 5. biosnoop 6. cachestat 7. tcpconnect 8. tcpaccept 9. tcpretrans 10. gethostlatency 11. runlat 12. profile

16.Query

17.Query

18.VFS

19.Ext4

20.

21.MySQL IO

22.

23.

24. 1 MySQL • • USE • MySQL

25. 2 xtrabackup thread_list=$(gdb -p $1 -q -batch -ex 'info threads'| awk '/mysqld/{print $1}'|grep -v '*'|sort -nk1) for i in $thread_list; do echo ">>>>> thread $i <<<<<" grl=`gdb -p $1 -q -batch -ex "thread $i" -ex 'p do_command::thd->thread_id' -ex 'p do_command::thd- >global_read_lock'|grep -B3 GRL_ACQUIRED_AND_BLOCKS_COMMIT` if [[ $grl =~ 'GRL_ACQUIRED_AND_BLOCKS_COMMIT' ]]; then echo "$grl" ; break fi done

26.PHPCON www.phpconchina.com PPT https://github.com/ThinkDevelopers/PHPConChina QQ 34449228 135615537 ThinkInLAMP