How to design a mysql performance monitoring software

Firstly, introduce the tool pt-stall in Percona-Toolkit. Speaking of PT toolkit, everyone is familiar with it. Commonly used tools such as pt-query-digest and pt-online-schema-change all come from this toolkit, so I won't introduce them here.

The main function of pt-stall is to collect diagnostic information when OS and MySQL have problems, including:

1.CPU, IO, memory, disk, network and other information. At the operating system level;

2.MySQL level row lock waiting, session connection, master-slave copy, status parameters and other information.

Moreover, pt-stall is a Shell script, which is friendly to people who don't understand perl. The monitoring logic and monitoring commands in the script can also be used for reference to build your own monitoring system.

Third, use

Then let's see how to use this tool.

Pt-stall usually monitors MySQL as a background service, waits for trigger conditions, and collects relevant diagnostic data when trigger conditions occur.

Parameters related to trigger conditions are as follows:

Function:

The default is status, which means monitoring the output of SHOW GLOBAL STATUS;

It can also be set as processlist, that is, monitoring the output of show processlist;

Variable:

The default value is Threads_running, which represents the monitoring parameters, and the specific monitoring items are specified according to the monitoring output above;

Threshold:

The default value is 25, which indicates the monitoring threshold. If the monitored parameters exceed the threshold, the trigger condition will be met.

When the value of the monitoring parameter is not a number, it needs to be used together with the matching parameter, such as the state column of the processlist;

Period:

The default value is 5, which means that the collection will only be triggered if the trigger condition is met five times in a row.

Connection parameters: host, password, port, socket.

Other important parameters:

Iterations: This parameter specifies that pt-stalk exits after triggering collection for many times, and will always run by default.

Runtime: This parameter specifies how long data will be collected after collection is triggered. The default value is 30 seconds.

Sleep: This parameter specifies how long sleep will continue to monitor after the collection is triggered. The default value is 300 seconds.

Interval: Specify the inspection frequency of status parameters, and decide whether to trigger acquisition. The default value is 1 sec.

Dest: the storage path of monitoring data, which is /var/lib/pt-stall by default.

Retention time: monitoring data retention time, the default is 30 days.

Daemonize: It runs in the background service and is not turned on by default.

Log: background running log, which is /var/log/pt-stall.log by default.

Collection: collects diagnostic data when the trigger occurs, and is turned on by default.

Collecting GDB: collecting gdb stack traces requires gdb tools.

Tracking tools are needed to collect tracking data.

Collection -tcpdump: collecting tcpdump data requires tcpdump tools.