MySQL性能诊断与实践 洪斌 PHPCON2018

2020-02-27 484浏览

  • 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 purposeshttp://www.tcpdump.org/papers/bpf-usenix93.pdf
  • 14.‫ڹ‬൉๵կ • Linux kernel 4.4+ (വគ 4.9+ ) • ਞᤰBcchttps://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-'>command::thd-