Performance Review with PMM

关于作者
Daniel Guzmán Burgos
Daniel studied Electronic Engineering, but quickly becomes interested on all data things. Worked as a DBA since 2007 for several companies including one of the major social gaming company at that time in Latin America. Daniel lives in Bogotá, Colombia and in his free time love to spend time with his family and to eat on any kind restaurants. Working for Percona since 2014, he is part of the Consulting team.

展开查看详情

1.Performance Review With PMM Daniel Guzmán Burgos April 25th 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