Monitoring MySQL from the command line

MySQL Belgian Days 2024 - Overview of command line tools to monitor MySQL

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL MySQL Belgian Days - February 2024 Monitoring MySQL from the command line
2. Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. 2
3. @lefred MySQL Evangelist using MySQL since version 3.20 devops believer likes living in h ps://lefred.be Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 3
4. Monitoring MySQL Why in command line ? Copyright @ 2024 Oracle and/or its affiliates. 4
5. Plenty of GUI Monitoring Solutions Copyright @ 2024 Oracle and/or its affiliates. 5
6. Because it's cool Ž Copyright @ 2024 Oracle and/or its affiliates. 6
7. Any other reasons ? Faster: to deploy to nd the info Lighter Only available option Copyright @ 2024 Oracle and/or its affiliates. 7
8. Any other reasons ? Faster: to deploy to nd the info Lighter Only available option but often not suitable for forensics analysis Copyright @ 2024 Oracle and/or its affiliates. 7
9. The Top of the Top Our best friends for a long time... Copyright @ 2024 Oracle and/or its affiliates. 8
10. Maintained now by Tsubasa Tanaka Oracle ACE Pro InnoTop Copyright @ 2024 Oracle and/or its affiliates. 9
11. Wri en in Perl Compatible with old versions & forks Can be complicated, no easy visualization Best processlist handling (refresh automatic or manual) First public release: July 2006 InnoTop Copyright @ 2024 Oracle and/or its affiliates. 10
12. InnoTop Copyright @ 2024 Oracle and/or its affiliates. 11
13. InnoTop Copyright @ 2024 Oracle and/or its affiliates. 12
14. InnoTop Copyright @ 2024 Oracle and/or its affiliates. 13
15. Alternatives and other tools Copyright @ 2024 Oracle and/or its affiliates. 14
16. Percona Tooklit summary utilities pt-summary: system summary report $ wget percona.com/get/pt-summary $ sudo sh pt-summary pt-mysql-summary: MySQL summary report $ wget percona.com/get/pt-summary $ sh pt-mysql-summary Copyright @ 2024 Oracle and/or its affiliates. 15
17. mytop certainly the oldest one 0.1 was released in July 2000 no changes since 12 years (1.7) wri en in Perl mytop is still part of many Linux distributions but the tool is obsolete, it was designed for MySQL 3.22.x, 3.23.x and 4.x Copyright @ 2024 Oracle and/or its affiliates. 16
18. mytop certainly the oldest one 0.1 was released in July 2000 no changes since 12 years (1.7) wri en in Perl mytop is still part of many Linux distributions but the tool is obsolete, it was designed for MySQL 3.22.x, 3.23.x and 4.x Copyright @ 2024 Oracle and/or its affiliates. 16
19. MySQL 8.x only wri en in Go nice widgets but complicated h ps://github.com/lefred/innotopgo innotopGo Copyright @ 2024 Oracle and/or its affiliates. 17
20. MySQL 8.x only wri en in Go nice widgets but complicated h ps://github.com/lefred/innotopgo innotopGo Copyright @ 2024 Oracle and/or its affiliates. 17
21. The Challenger My new best friend ! Copyright @ 2024 Oracle and/or its affiliates. 18
22. Created and Maintained by Charles Thompson Dolphie Copyright @ 2024 Oracle and/or its affiliates. 19
23. Wri en in Python Compatible with old versions and cloud Provides nice overview and graphs Easy to extend Supports a lot of MySQL 8 features Relication Dashboard is amazing First release: September 2022 Dolphie Copyright @ 2024 Oracle and/or its affiliates. 20
24. Dolphie - Processlist And it can switch between using Information_Schema or Performance_Schema ! Copyright @ 2024 Oracle and/or its affiliates. 21
25. Dolphie - Error Log Copyright @ 2024 Oracle and/or its affiliates. 22
26. Dolphie - Memory Allocation Copyright @ 2024 Oracle and/or its affiliates. 23
27. Dolphie - Active Connected Users Statistics Copyright @ 2024 Oracle and/or its affiliates. 24
28. Dolphie - Thread Details Copyright @ 2024 Oracle and/or its affiliates. 25
29. Dolphie - Graphs: DML Copyright @ 2024 Oracle and/or its affiliates. 26
30. Dolphie - Graphs: Bu er Pool Requests Copyright @ 2024 Oracle and/or its affiliates. 27
31. Dolphie - Graphs: Redo Log Copyright @ 2024 Oracle and/or its affiliates. 28
32. Dolphie - Graphs: Disk I/O Copyright @ 2024 Oracle and/or its affiliates. 29
33. Dolphie - Replication: InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 30
34. Dolphie - Replication: InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 30
35. Dolphie - Replication: even further ! Copyright @ 2024 Oracle and/or its affiliates. 31
36. Dolphie - and more ! Copyright @ 2024 Oracle and/or its affiliates. 32
37. Dolphie - 3.4 preview ! No blocking calls and a new UI style: Copyright @ 2024 Oracle and/or its affiliates. 33
38. Dolphie - 3.4 preview ! Copyright @ 2024 Oracle and/or its affiliates. 34
39. Dolphie - 3.4 preview ! Copyright @ 2024 Oracle and/or its affiliates. 35
40. Dolphie h ps://github.com/charles-001/dolphie $ pip install dolphie Copyright @ 2024 Oracle and/or its affiliates. 36
41. If I need more ? ... but easy and in command line ! Copyright @ 2024 Oracle and/or its affiliates. 37
42. MySQL Shell MySQL Shell also provides an easy way to capture information regarding the system: collectDiagnostics() Collects MySQL diagnostics information for standalone and managed topologies collectHighLoadDiagnostics() Collects MySQL high load diagnostics information collectSlowQueryDiagnostics() Collects MySQL diagnostics and pro ling information for a slow query Copyright @ 2024 Oracle and/or its affiliates. 38
43. MySQL Shell - Graphs: collection It's also possible to collect data to be plo ed (locally or remotely later) using a plugin for MySQL Shell: h ps://github.com/lefred/mysqlshell-plugins/ Copyright @ 2024 Oracle and/or its affiliates. 39
44. MySQL Shell - Graphs: generation Copyright @ 2024 Oracle and/or its affiliates. 40
45. MySQL Shell - Graphs: results Copyright @ 2024 Oracle and/or its affiliates. 41
46. MySQL Shell - Plugins To graphs generation requires the following Python modules: Ready to extend those graphs with your pre ered plots ? Copyright @ 2024 Oracle and/or its affiliates. 42
47. MySQL Shell - Plugins To graphs generation requires the following Python modules: Ready to extend those graphs with your pre ered plots ? Copyright @ 2024 Oracle and/or its affiliates. 42
48. Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 43
49. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 44