Monitoring MySQL from the command line

Slide 1

Slide 1 text

Frédéric Descamps Community Manager Oracle MySQL MySQL Belgian Days - February 2024 Monitoring MySQL from the command line

Slide 2

Slide 2 text

Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. 2

Slide 3

Slide 3 text

@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

Slide 4

Slide 4 text

Monitoring MySQL Why in command line ? Copyright @ 2024 Oracle and/or its affiliates. 4

Slide 5

Slide 5 text

Plenty of GUI Monitoring Solutions Copyright @ 2024 Oracle and/or its affiliates. 5

Slide 6

Slide 6 text

Because it's cool Ž Copyright @ 2024 Oracle and/or its affiliates. 6

Slide 7

Slide 7 text

Any other reasons ? Faster: to deploy to nd the info Lighter Only available option Copyright @ 2024 Oracle and/or its affiliates. 7

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

The Top of the Top Our best friends for a long time... Copyright @ 2024 Oracle and/or its affiliates. 8

Slide 10

Slide 10 text

Maintained now by Tsubasa Tanaka Oracle ACE Pro InnoTop Copyright @ 2024 Oracle and/or its affiliates. 9

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

InnoTop Copyright @ 2024 Oracle and/or its affiliates. 11

Slide 13

Slide 13 text

InnoTop Copyright @ 2024 Oracle and/or its affiliates. 12

Slide 14

Slide 14 text

InnoTop Copyright @ 2024 Oracle and/or its affiliates. 13

Slide 15

Slide 15 text

Alternatives and other tools Copyright @ 2024 Oracle and/or its affiliates. 14

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

The Challenger My new best friend ! Copyright @ 2024 Oracle and/or its affiliates. 18

Slide 22

Slide 22 text

Created and Maintained by Charles Thompson Dolphie Copyright @ 2024 Oracle and/or its affiliates. 19

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

Dolphie - Processlist And it can switch between using Information_Schema or Performance_Schema ! Copyright @ 2024 Oracle and/or its affiliates. 21

Slide 25

Slide 25 text

Dolphie - Error Log Copyright @ 2024 Oracle and/or its affiliates. 22

Slide 26

Slide 26 text

Dolphie - Memory Allocation Copyright @ 2024 Oracle and/or its affiliates. 23

Slide 27

Slide 27 text

Dolphie - Active Connected Users Statistics Copyright @ 2024 Oracle and/or its affiliates. 24

Slide 28

Slide 28 text

Dolphie - Thread Details Copyright @ 2024 Oracle and/or its affiliates. 25

Slide 29

Slide 29 text

Dolphie - Graphs: DML Copyright @ 2024 Oracle and/or its affiliates. 26

Slide 30

Slide 30 text

Dolphie - Graphs: Bu er Pool Requests Copyright @ 2024 Oracle and/or its affiliates. 27

Slide 31

Slide 31 text

Dolphie - Graphs: Redo Log Copyright @ 2024 Oracle and/or its affiliates. 28

Slide 32

Slide 32 text

Dolphie - Graphs: Disk I/O Copyright @ 2024 Oracle and/or its affiliates. 29

Slide 33

Slide 33 text

Dolphie - Replication: InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 30

Slide 34

Slide 34 text

Dolphie - Replication: InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 30

Slide 35

Slide 35 text

Dolphie - Replication: even further ! Copyright @ 2024 Oracle and/or its affiliates. 31

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Dolphie - 3.4 preview ! No blocking calls and a new UI style: Copyright @ 2024 Oracle and/or its affiliates. 33

Slide 38

Slide 38 text

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Dolphie h ps://github.com/charles-001/dolphie $ pip install dolphie Copyright @ 2024 Oracle and/or its affiliates. 36

Slide 41

Slide 41 text

If I need more ? ... but easy and in command line ! Copyright @ 2024 Oracle and/or its affiliates. 37

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

MySQL Shell - Graphs: generation Copyright @ 2024 Oracle and/or its affiliates. 40

Slide 45

Slide 45 text

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 43