A Support Engineer Walkthrough on pt-stalk

作为一名支持工程师,每天我都会从我们的客户那里获取数十个包含iostat、vmstat、top、ps、show engine innodb status、show processlist和许多其他诊断输出的pt-stack样本。
这些是性能和故障排除的工具,我们必须学会以有效和系统的方式消化这些输出。这使我们能够为广大客户提供优质的服务。
在本演示中,我们将分享使用这些数据所获得的知识,以及如何将其应用到您的数据库环境中。
我们将学习设置、捕获数据、编写触发收集和捕获自定义数据的插件、查看我们的系统方法并学习先读取哪些数据以及如何释放pt-stack的纠结线程。
在本演示结束时,您将掌握如何在正确的时间捕获诊断指标的专家知识,并拥有一种通用方法来消化捕获的数据。这允许您诊断和解决MySQL设置常见的许多问题。

展开查看详情

1. A Support Engineer Walkthrough on pt- stalk Marcos Albe Principal Support Engineer - Percona 1 © 2017 Percona

2.Who is Speaking ? • Marcos Albé - Principal Support Engineer @ Percona - 7 years here - Focus on performance and observability 2 © 2017 Percona

3.Agenda 3 © 2017 Percona

4.Agenda 1. What do we USE 2. Who’s stalkin’? 3. How pt-stalk can be USEd 4. Walkthrough 4 © 2017 Percona

5.What do we USE 5 © 2017 Percona

6.What do we USE We use one of these... 6 © 2017 Percona

7.What do we USE Utilization 7 © 2017 Percona

8.What do we USE Utilization Saturation 8 © 2017 Percona

9.What do we USE Utilization Saturation Errors 9 © 2017 Percona

10.What do we USE 10 © 2017 Percona

11.Who’s stalkin’? 11 © 2017 Percona

12.Who’s stalkin’? check out those resources! 12 © 2017 Percona

13.Who’s stalkin’? •Aspersa stalk + Aspersa collect in one! (thanks Daniel & Baron) •# The name 'stalk' is because 'watch' is already taken, and 'stalk' is fun. •Now part of pt-toolkit •Monitors variable or a script’s single-valued output •Triggers capture when set threshold is reached - files from /proc: diskstats, interrupts, meminfo, slabinfo, procstat, procvmstat. - vmstat, iostat, top, netstat, pmap, mpstat, sysctl, df, hostname, mysqladmin ext, mysqladmin debug, lsof). - SHOW ENGINE INNODB STATUS, SHOW ENGINE INNODB MUTEX, SHOW GLOBAL VARIABLES, SHOW FULL PROCESSLIST and SHOW OPEN TABLES. •Production safe! •Custom triggers and custom captures •Companion tools (pt-sift, pt-mext, pt-diskstats, pt-summary); Bash dissection 13 © 2017 Percona

14.Who’s stalkin’: common parameters •Stalking part - --daemonize | --no-stalk - --function=status - --interval=1 - --variable=Threads_running - --threshold=25 - --cycles=5 • Collecting part - --run-time=30 - --sleep=300 - --iterations= - --plugin= - --dest=/var/lib/pt-stalk •Values shown are the defaults for each (where given) 14 © 2017 Percona

15. Who’s stalkin’: work flow 15 © 2017 Percona

16.Who’s stalkin’: working examples I Collect data during 60 seconds for 3 consecutive times, starting immediately. pt-stalk --no-stalk \ --dest=$HOME/percona \ --user=root \ --ask-pass \ --run-time=60 --sleep=60 --iterations=3 16 © 2017 Percona

17.Who’s stalkin’: working examples II Run as a daemon and trigger collection if InnoDB has more than 5 pending fsyncs. pt-stalk --daemonize \ --function=status \ --variable=Innodb_data_pending_fsyncs \ --threshold=5 \ --cycles=2 \ --interval=1 \ --iterations=5 \ --plugin=/usr/local/bin/raid_card_status.sh 17 © 2017 Percona

18.Who’s stalkin’: custom triggers #!/bin/bash # Save this bash snippet as /root/delayed_slave_trg.sh; trg_plugin() { mysql $EXT_ARGV -Ee "SHOW SLAVE STATUS" | \ grep "Seconds_Behind_Master" | \ awk "{ print $2 }"; } # use like pt-stalk --function=/root/delayed_slave_trg.sh - -etc --etc 18 © 2017 Percona

19.Who’s stalkin’: custom capture #!/bin/bash # save as /root/collect_perf.sh before_collect() { # run perf stat; match sleep length to --run- time /usr/bin/perf stat --all-cpus \ --output=${d}/${p}-stat_a \ -- sleep ${OPT_RUN_TIME}; } # use like pt-stalk --plugin=/root/collect_perf.sh --etc --etc 19 © 2017 Percona

20.How pt-stalk can be USE’d 20 © 2017 Percona

21.How pt-stalk can be USE’d Applications System libraries System Call Interface CPU VFS Sockets Interconnect Scheduler File Systems TCP/UDP CPU Linux Kernel Volume Manager IP Virtual Memory Memory Bus Block Device Interface Ethernet Device Drivers DRAM I/O Bus I/O Bridge Expander Interconnect I/O Controller Network Controller Interface Transports Disk Disk Swap Port Port 21 © 2017 Percona

22. SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX oprofile SHOW OPEN TABLES Applications (MySQL!) /proc/interrupts SHOW FULL PROCESSLIST /proc/procstat SHOW ENGINE TOKUDB STATUS System libraries mpstat SHOW GLOBAL VARIABLES System Call Interface CPU VFS Sockets Interconnect SHOW SLAVE STATUS Scheduler mysqladmin ext File Systems TCP/UDP ps CPU Linux Kernel gdb (backtraces) top strace Volume Manager IP Virtual Memory vmstat Memory Bus lsof Block Device Interface Ethernet /proc/meminfo df Device Drivers /proc/slabinfo DRAM iostat /proc/vmstat I/O Bus pmap /proc/diskstats netstat sysctl tcpdump I/O Bridge Expander Interconnect I/O Controller Network Controller Interface Transports Swa Disk Disk Port Port p 22 © 2017 Percona

23.How pt-stalk can be USE’d: Here Be Dragons! gdb (backtraces) oprofile strace tcpdump 23 © 2017 Percona

24.A walkthrough 24 © 2017 Percona

25.A walkthrough Using pt-stalk to solve problems with... ● ...High CPU ● ...High IO ● ...Network 25 © 2017 Percona

26.Demo 26 © 2017 Percona

27.Get Your Tickets for Percona Live Europe! Championing Open Source Databases ▪ MySQL, MongoDB, Open Source Databases ▪ Time Series Databases, PostgreSQL, RocksDB ▪ Developers, Business/Case Studies, Operations ▪ September 25-27th, 2017 ▪ Radisson Blu Royal Hotel, Dublin, Ireland Last Year’s Conference Sold Out! Reserve your spot ASAP. 27 © 2017 Percona

28.Talk to Percona Experts at AWS Re:Invent! Database Performance for Cloud Deployments ▪ Percona Support and Managed Services • Amazon RDS, Aurora, Roll Your Own • MySQL/MariaDB/MongoDB • Reduce costs and optimize performance ▪ Percona Monitoring and Management Demos • Point-in-time visibility and historical trending of database performance • Detailed query analytics ▪ Booth #1138 28 © 2017 Percona