mysql性能优化 洪斌_MySQL性能诊断与实践 洪斌 PHPCON2018

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 <<<<:thd->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