Performance Analysis and Troubleshooting Methodologies for Databases

Peter Zaitsev在法国蒙彼利埃大数据/数据科学会议上演讲的幻灯片,场地赞助商Elium Tech
About the Author
Peter Zaitsev
Peter Zaitsev co-founded Percona and assumed the role of CEO in 2006. As one of the foremost experts on MySQL strategy and optimization, Peter leveraged both his technical vision and entrepreneurial skills to grow Percona from a two-person shop to one of the most respected open source companies in the business. With over 140 professionals in 30 plus countries, Peter’s venture now serves over 3000 customers – including the “who’s who” of internet giants, large enterprises and many exciting startups. Percona was named to the Inc. 5000 in 2013, 2014, 2015 and 2016. Peter was an early employee at MySQL AB, eventually leading the company’s High Performance Group. A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University where he majored in Computer Science. Peter is a co-author of High Performance MySQL: Optimization, Backups, and Replication, one of the most popular books on MySQL performance. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Database Performance Blog. He has also been tapped as a contributor to Fortune and DZone, and his recent ebook Practical MySQL Performance Optimization Volume 1 is one of’s most popular downloads. Peter lives in North Carolina with his wife and two children. In his spare time, Peter enjoys travel and spending time outdoors.


1. Performance Analysis and Troubleshooting Methodologies for Databases Peter Zaitsev, CEO Percona July 9th, 2019 Montpellier Big Data and Data Science Meetup © 2019 Percona. 1

2.Thank you © 2019 Percona. 2

3.Enjoying South of France © 2019 Percona. 3

4.Lets Get to Kow you! How Many of you are… © 2019 Percona. 4

5.Databases and Performance Databases are frequent Performance Trouble Makers © 2019 Percona. 5

6.Why Databases are Painful ? Generally Non-Linear Scalability Complex Often Poorly understood by developers © 2019 Percona. 6

7.Performance Work with Databases Troubleshooting Capacity Planning Cost and Efficiency Optimization Change Management © 2019 Percona. 7

8.Points of View BlackBox – WhiteBox – “Application “DBA, Ops” Developer” © 2019 Percona. 8

9.Developer Point of View Database as a Blackbox I throw queries at it and it responds DBaaS bring this “promise” to OPS too © 2019 Percona. 9

10.BlackBox Success Criteria for Databases Availability Response Time Correctness Cost © 2019 Percona. 10

11.Ops Point of View Load Resource Utilization System/Hardware Problems Scaling/Capacity Planing © 2019 Percona. 11

12.Methodologies for Performance Troubleshooting and Analyses © 2019 Percona. 12

13.Typical Default Troubleshooting by Random Googling © 2019 Percona. 13

14.Problems with Typical Approach Hard to Assure Outcome Hard to Train People Hard to Automate © 2019 Percona. 14

15.Methodologies Save the Day USE (Utilization, Golden Signals (Latency RED (Rate, Errors(Rate), Saturation, Errors) - Traffic - Errors - Duration) Method Tom Method by Brendan Saturations) Method by Wilkie Gregg Rob Ewaschuk © 2019 Percona. 15

16.USE Method © 2019 Percona. 16

17.USE Method Basics Developed to Troubleshoot Server Performance Issues Resolve 80% of problems with 5% of Effort Operating System Specific Checklists Available © 2019 Percona. 17

18.USE Method in One Sentence “For every resource, check utilization, saturation, and errors.” © 2019 Percona. 18

19.USE Method Terminology Defitinions Resource • all physical server functional components (CPUs, disks, busses, ...) Utilization • the average time that the resource was busy servicing work Saturation • the degree to which the resource has extra work which it can't service, often queued Errors • the count of error events © 2019 Percona. 19

20.USE Method Resources CPUs: sockets, cores, hardware threads (virtual CPUs) Memory: capacity Network interfaces Storage devices: I/O, capacity Controllers: storage, network cards Interconnects: CPUs, memory, I/O © 2019 Percona. 20

21.USE Method with Software Same Basic Resources Apply Additional Software Resources Apply Mutex Locks File Descriptors Connections © 2019 Percona. 21

22.USE Method Benefits Proven Track Record Broad Applicability Detailed Checklists Available © 2019 Percona. 22

23.USE Method Drawbacks Requires Good Understanding of System Architecture Requires Access to Low Level Resources Monitoring Hard to apply in Service “Blackbox” environments © 2019 Percona. 23

24.Cloud Computing Limits in place to isolate Tenants Dynamic Resource Management True “Hardware” properties on Hypervisor level only © 2019 Percona. 24

25.Understanding Queueing © 2019 Percona. 25

26.Many Different Levels Process running on • Actually waiting on the Memory CPU Process Running from • May be put to queue due to CPU User Standpoint saturation Disk request issued to • Can be waiting on network EBS © 2019 Percona. 26

27.USE Method for Linux © 2019 Percona. 27

28.Percona Monitoring and Management 100% Free and Open Source Purpose Build for Open Source Database Monitoring Based on leading Open Source Technologies – Grafana, Prometheus Easy to Set up © 2019 Percona. 28

29.CPU Utilization © 2019 Percona. 29