Systemd offers a powerful alternative to cron for managing and scheduling MySQL administration tasks. This blog post explores how to leverage systemd services to integrate various Percona Toolkit utilities like pt-heartbeat and pt-kill for optimal MySQL performance and health.
In my day to day tasks as a DB Engineer at Mydbops we have been using multiple MySQL tools for multiple use cases to ensure an optimal performance and availability for servers managed by our Remote DBA Team.
A tool like pt-online-schema can be used for any DDL changes ( Overview to DDL algorithm ), if any tool which needs to scheduled for longer period we tend to use screen or cron.
Some of the problems we face when we demonise the process or use screen for running processes.
- The daemon process gets killed when the server reboot happens.
- The screen might accidentally terminate while closing it.
- To flexibility to start or stop the process when required.
These common problem can be overcome by using systemd service.
What is Systemd?
Systemd is a system and service manager for Linux operating systems. When running as the first process on boot (as PID 1), it acts as an init system that brings up and maintains userspace services.
List of few use cases that can be made simple with systemd.service.
- Integrating Pt-heartbeat with Systemd Service
- Integrating Auto kill using pt-kill with Systemd Service.
- Integrating multi query killer with Systemd service
Integrating Pt-heartbeat with Systemd Service
We had the requirement to schedule pt-heartbeat to monitor replication lag for one of our clients under our database managed Services. Here is problem statement pt-heartbeat process was running as a daemon process, the usual problem we were facing was when the system is rebooted for any maintenance , the pt-heartbeat process gets killed and we start receiving the replication lag alerts and then it needs a manual fix.
Script for pt-heartbeat
/usr/bin/pt-heartbeat --daemonize -D mydbops --host=192.168.33.11 --master-server-id 1810 --user=pt-hbt --password=vagrant --table heartbeat --insert-heartbeat-row --update
Now let us integrate it with systemd
$ cd /etc/systemd/system/
$ vi pt-heartbeat.service
##pt-heartbeat systemd service file
[Unit]
Description="pt-heartbeat"
After=network-online.target syslog.target
Wants=network-online.target
[Install]
WantedBy=multi-user.target
[Service]
Type=forking
ExecStart=/usr/bin/pt-heartbeat --daemonize -D mydbops --host=192.168.33.11 --master-server-id 1810 --user=pt-hbt --password=vagrant --table heartbeat --insert-heartbeat-row --update
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=pt-heartbeat
Restart=always
ExecStart = It needs the command which needs to be executed when the service kick start )
Restart = Always option specifies to start the process once the OS is booted up.
Once the new systemd script is pushed, Reload the systemctl daemon and start the service
$ systemctl daemon-reload
$ systemctl start pt-heartbeat
$ systemctl status pt-heartbeat -l
● pt-heartbeat.service - "pt-heartbeat"
Loaded: loaded (/etc/systemd/system/pt-heartbeat.service; disabled; vendor preset: enabled)
Active: active (running) since Mon 2020-06-20 13:20:24 IST; 10 days ago
Main PID: 25840 (perl)
Tasks: 1
Memory: 19.8M
CPU: 1h 1min 47.610s
CGroup: /system.slice/pt-heartbeat.service
└─25840 perl /usr/bin/pt-heartbeat --daemonize -D mydbops --host=192.168.33.11 --master-server-id 1810 --user=pt-hbt --password=vagrant --table heartbeat --insert-heartbeat-row --update
This service can be stopped by just giving ( similar to to any systemctl process )
$ systemctl stop pt-heartbeat
● pt-heartbeat.service - "pt-heartbeat"
Loaded: loaded (/etc/systemd/system/pt-heartbeat.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Jun 20 15:46:07 ip-192-168-33-11 systemd[1]: Stopping "pt-heartbeat"…
Jun 20 15:46:07 ip-192-168-33-11 systemd[1]: Stopped "pt-heartbeat".
Integrating Auto kill using pt-kill with Systemd Service
Usually in production servers long running queries will spike the system resource usage and degrade the MySQL performance drastically or might kill your MySQL process with OOM killer, in order to avoid this hiccups , we can schedule Percona pt-kill process based on the use case defined.
Scheduling the pt-kill service
$ cd etc/systemd/system/
$ vi pt-kill.service
#pt-kill systemd service file
[Unit]
Description="pt-kill"
After=network-online.target syslog.target
Wants=network-online.target
[Install]
WantedBy=multi-user.target
[Service]
Type=forking
ExecStart=/usr/bin/pt-kill --user=mon_ro --host=192.168.33.11 --password=pt@123 --busy-time 200 --kill --match-command Query --match-info (select|SELECT|Select) --match-user cron_ae --interval 10 --print --daemonize
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=pt-kill
Restart=always
$ systemctl daemon-reload
$ systemctl start pt-kill
$ systemctl status pt-kill
pt-kill.service - "pt-kill"
Loaded: loaded (/etc/systemd/system/pt-kill.service; enabled)
Active: active (running) since Wed 2020-06-24 11:00:17 IST; 5 days ago
CGroup: name=dsystemd:/system/pt-kill.service
├─20974 perl /usr/bin/pt-kill --user=mon_ro --host=192.168.33.11 --password=pt@123 --busy-time 200 --kill --match-command Query --match-info (select|SELECT|Select) --match-user cron_ae --interval 10 --print --daemonize
Now we have configured a fail safe pt-kill process.
Integrating multi query killer with Systemd service
Question : Is it possible to integrate multiple Kill Statements for different hosts as single process.
Answer – Yes ! It is possible and quite simple too.
Just add the needed commands as shell script file and make it execute it. In the below example i have chose three different server consider a RDS instance ( more on AWS RDS its Myth ) and a couple of virtual machine.
$ vi pt_kill.sh
/usr/bin/pt-kill --user=pt_kill --host=test.ap-northeast-1.rds.amazonaws.com --password=awkS --busy-time 1000 --rds --kill --match-command Query --match-info "(select|SELECT|Select)" --match-user "(mkt_ro|dash)" --interval 10 --print --daemonize >> /home/vagrant/pt_kill_slave1.log
/usr/bin/pt-kill --user=mon_ro --host=192.168.33.11 --password=pt@123 --busy-time 20 --kill --match-command Query --match-info "(select|SELECT|Select)" --match-user "(user_qa|cron_ae)" --interval 10 --print --daemonize >> /home/vagrant/pt_kill_slave2.log
/usr/bin/pt-kill --user=db_ro --host=192.168.33.12 --password=l9a40E --busy-time 200 --kill --match-command Query --match-info "(select|SELECT|Select)" --match-user sbtest_ro --interval 10 --print --daemonize >> /home/vagrant/pt_kill_slave3.log
Scheduling pt-kill.service for multiple hosts
#pt-kill systemd service file
[Unit]
Description="pt-kill"
After=network-online.target syslog.target
Wants=network-online.target
[Install]
WantedBy=multi-user.target
[Service]
Type=forking
ExecStart=/bin/bash /home/vagrant/pt_kill.sh
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=pt-kill
Restart=always
Reload the daemon and start the service
$ systemctl daemon-reload
$ systemctl start pt-kill
$ systemctl status pt-kill
pt-kill.service - "pt-kill"
Loaded: loaded (/etc/systemd/system/pt-kill.service; enabled)
Active: active (running) since Wed 2020-06-24 11:00:17 IST; 5 days ago
CGroup: name=dsystemd:/system/pt-kill.service
├─20974 Perl /usr/bin/pt-kill --user=pt_kill --host=test.ap-northeast-1.rds.amazonaws.com --password=awkS --busy-time 1000 --rds --kill --match-command Query --match-info "(select...
├─21082 perl /usr/bin/pt-kill --user=mon_ro --host=192.168.33.11 --password=pt@123 --busy-time 20 --kill --match-command Query --match-info "(select...
├─21130 perl /usr/bin/pt-kill --user=db_ro --host=192.168.33.12 --password=l9a40E --busy-time 200 --kill --match-command Query --match-info "(select...
This makes Systemd more useful and easy tool for scheduling mysql tools in database environment. There are many more features in Systemd that be used for scheduling scripts bypassing the use of crontab, hopefully.
Note : All these are sample scripts you ensure you test well before making it in production.
Mydbops offers a comprehensive suite of open-source database management services specifically designed for MySQL.
{{cta}}
.png)
.png)
.svg)
