Performance Review with PMM

1 .Performance Review With PMM Daniel Guzmán Burgos April 23rd 2019 

2 . Agenda ● What is Performance ● Visibility or Die ● PMM refresh ● Statistic Analysis ● Environment Overview ● PMM’s Customization 

3 .What is Performance? 

4 . What is Performance? People often use terms such as: ❏ “Performance” ❏ “Scalability” ❏ and “High Availability” as synonyms in casual conversation 

5 .What is Performance? THEY ARE NOT ! they’re completely different. 

6 . What is Performance? Performance is independent of Capacity and Scalability. There’s a high level analogy that helps to clarify the differences 

7 . What is Performance? To make an analogy with cars on a highway: ★ Capacity is the number of lanes times the maximum safe speed. ★ Scalability is the degree to which you can add more cars and more lanes without slowing traffic ★ Performance is how fast the car is. 

8 .What is Performance? 

9 .What is Performance? 

10 .What is Performance? 

11 .Visibility 

12 . Visibility This is visibility mysql> show processlist; +----+-------------+-----------+------+---------+------+---------------------+------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-------------+-----------+------+---------+------+---------------------+------------------+-----------+---------------+ | 1 | system user | | NULL | Sleep | 2750 | wsrep: aborter idle | NULL | 0 | 0 | | 2 | system user | | NULL | Sleep | 2750 | wsrep: applier idle | NULL | 0 | 0 | | 4 | system user | | NULL | Sleep | 2750 | wsrep: applier idle | NULL | 0 | 0 | | 5 | system user | | NULL | Sleep | 2750 | wsrep: applier idle | NULL | 0 | 0 | | 6 | system user | | NULL | Sleep | 2750 | wsrep: applier idle | NULL | 0 | 0 | | 7 | system user | | NULL | Sleep | 2750 | wsrep: applier idle | NULL | 0 | 0 | | 8 | system user | | NULL | Sleep | 2750 | wsrep: applier idle | NULL | 0 | 0 | | 9 | system user | | NULL | Sleep | 2750 | wsrep: applier idle | NULL | 0 | 0 | | 10 | system user | | NULL | Sleep | 2750 | wsrep: applier idle | NULL | 0 | 0 | | 12 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 | | 71 | root | localhost | NULL | Sleep | 1 | | NULL | 487 | 974 | | 73 | root | localhost | NULL | Sleep | 1 | | NULL | 66 | 238 | | 74 | root | localhost | NULL | Sleep | 1 | | NULL | 1 | 0 | +----+-------------+-----------+------+---------+------+---------------------+------------------+-----------+---------------+ 13 rows in set (0.05 sec) 

13 . Visibility This is also visibility --vmstat-- r b swpd free buff cache si so bi bo in cs us sy id wa st 8 4 171616 15904 0 223804 0 5 135 81 142 112 3 1 96 0 0 1 0 171476 16028 0 219068 48 0 248 95 653 612 7 5 87 1 0 wa 0% 15% 0% . . . . . . . . . . . . . . . . . . . . . . . . . . . --innodb-- txns: 4xnot (0s) 0 queries inside InnoDB, 0 queries in queue Main thread: sleeping, pending reads 0, writes 0, flush 0 Log: lsn = 2531047, chkp = 2531038, chkp age = 9 Threads are waiting at: Threads are waiting on: --processlist-- State 9 wsrep 5 2 starting 1 Receiving from client Command 14 Sleep 2 Query 1 Connect --stack traces-- No stack trace file exists --oprofile-- No opreport file exists 

14 . Visibility And this is Visibility with colors 

15 .PMM 

16 .PMM 

17 .Statistic Analysis Getting serious 

18 . Statistic Analysis ➔ It all comes down to “Asking the right questions” ➔ Focus on Behavior, not configuration (yet) ➔ What is good for you is not necessary good for the other. ➔ Finding 1 performance problem doesn’t mean is the ONLY problem. Keep looking and find correlation. 

19 . Statistic Analysis Finding the the questions to asks can be hard. 1. What makes you think there is a problem? 2. Has this system ever performed well? 3. What changed? Software? HW? Load? 4. Can the perform degradation be expressed in terms of latency or run time? 5. Does the problem affect other people or applications? 6. What is the environment? What sw and hw is used? Versions? Configuration? 

20 . Statistic Analysis We usually operates in the darkness. No more info than the one available in PMM. 

21 . Statistic Analysis Best performance wins are from eliminating unnecessary work 

22 .Statistic Analysis Less guesswork! 

23 . Statistic Analysis Subject matters ➔ General MySQL ➔ Storage Engine specific (InnoDB, RocksDB, etc) ➔ Server (Disk/CPU/Memory/Network) 

24 . Statistic Analysis Subject matters ➔ General MySQL ➔ Storage Engine specific (InnoDB, RocksDB, etc) ➔ Server (Disk/CPU/Memory/Network) Small detour to the USE method 

25 . Statistic Analysis The USE method ➔ Not a tool ➔ Not a product ➔ Is a documented procedure ➔ If you don’t know where to start, start here 

26 . Statistic Analysis The USE method For every resource, check utilization, saturation, and errors. http://www.brendangregg.com/usemethod.html 

27 .Statistic Analysis The USE method 

28 . Statistic Analysis The USE method has opened the door to new methods: The RED Method 

29 . Statistic Analysis The RED method: For every resource monitor ● Rate (the number of requests per second) ● Errors (the number of those requests that are failing) ● Duration (the amount of time those requests take) https://grafana.com/files/grafanacon_eu_2018/Tom_Wilkie_GrafanaCon_EU_2018.pdf