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