Integrating MySQL tools with Systemd Service

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.

One thought on “Integrating MySQL tools with Systemd Service

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s