SystemD related stuff for MariaDB and MySQL

Table of Contents

• SystemD Notizen
• InnoDB NUMA interleave with MariaDB
• Checking Galera Cluster SST timeout with SystemD
• Kill MariaDB Galera node under SystemD
• MariaDB Memory locking (--memlock) with SystemD
• Who is restarting my mariadbd?

SystemD Notizen

SystemD paged per default nach less. Das ist ungewohnt und doof, weil es gegen allgemeines Linux-Verhalten verstösst. Abhilfe schafft:

shell> systemctl --no-pager status
shell> alias systemctl='systemctl --no-pager'

Das selbe können wir auch verwenden, wenn wir bei MariaDB start/stop Problemen im SystemD Journal nachschauen müssen:

shell> journalctl --no-pager -xe --unit=mariadb

Eine weitere Herausforderung könnte die Anpassung des MariaDB Unit Files sein:

shell> update-alternatives --config editor
shell> export SYSTEMD_EDITOR=vi
shell> export EDITOR=vi
shell> export VISUAL=vi
shell> export SELECTED_EDITOR="/usr/bin/vim.basic"
# C-x C-e
# /etc/systemd/system/mariadb.service.d/override.conf
shell> systemctl show mariadb | grep NOFILE
shell> systemctl edit mariadb

[Service]

LimitNOFILE=12288

shell> systemctl edit --full mariadb
shell> systemctl daemon-reload
shell> systemctl restart mariadb
shell> systemctl cat mariadb
shell> systemctl revert mariadb

Leider funktioniert auf meinem Kubuntu 16.04 das setzten der $EDITOR Variable nicht wie erwartet... Bei älteren SystemD Versionen ist die Funktion edit noch nicht implementiert.

InnoDB NUMA interleave with MariaDB

The MariaDB server system variable innodb_numa_interleave is currently not supported (any more). See: MariaDB MDEV-21399.

As an alternative the mysqld_safe option --numa-interleave (MariaDB SystemD Options) or the SystemD property NUMAPolicy can be used instead.

NUMAPolicy=interleave works from SystemD v243 onwards. You can find your current SystemD version as follows:

shell> systemctl --version
systemd 245 (245.4-4ubuntu3.6)
+PAM +AUDIT +SELINUX +IMA +APPARMOR +SMACK +SYSVINIT +UTMP +LIBCRYPTSETUP +GCRYPT +GNUTLS +ACL +XZ +LZ4 +SECCOMP +BLKID +ELFUTILS +KMOD +IDN2 -IDN +PCRE2 default-hierarchy=hybrid

The NUMA Policy of the the MariaDB Process can be shown as follows (this has to be verified yet!):

shell> systemctl show mariadb | grep NUMAPolicy
NUMAPolicy=n/a

To check the NUMA distribution of your MariaDB memory continue reading: What if your system is still swapping? - NUMA!

Configure NUMA policy as follows with systemctl edit mariadb:

[Services]

NUMAPolicy=interleave
NUMAMask=all

Checking Galera Cluster SST timeout with SystemD

See also SSTs and Systemd.

shell> systemctl show mariadb | grep -i timeout
TimeoutStartUSec=1min 30s
TimeoutStopUSec=1min 30s
JobTimeoutUSec=infinity
JobRunningTimeoutUSec=infinity
JobTimeoutAction=none

Kill MariaDB Galera node under SystemD

The newest releases of MariaDB make it really hard to deliberately kill a MariaDB Galera Cluster node and keep it down. But we need this for our Galera Cluster training exercises! So how to do it today? Change the MariaDB unit file:

[Service]

Restart=no

We somehow did not manage to change it with systemctl edit as one should do it (Bug?)! Then proceed as follows:

shell> systemctl daemon-reload

shell> systemctl show  mariadb | grep Restart=
Restart=no

shell> systemctl kill --signal=SIGKILL mariadb

MariaDB Memory locking (--memlock) with SystemD

If your MariaDB server is swapping a bit, which is seen quite often, possibly memlocking the process can help to prevent swapping. This can be done as follows:

[Service]

LimitMEMLOCK=infinity

To check if it works or worked you can run the following query:

SQL> SHOW GLOBAL VARIABLES LIKE 'locked%';
+------------------+-------+                                                                            
| Variable_name    | Value |                                                                             
+------------------+-------+                                                                              
| locked_in_memory | OFF   |
+------------------+-------+

Or from the O/S perspective:

shell> grep 'locked memory' /proc/$(pidof -s mariadbd)/limits
Max locked memory         67108864             67108864             bytes    

Who is restarting my mariadbd?

If the MariaDB DB daemon is restarting unexpectedly it is typically because of SystemD. But not always. We had a case where the application required a database server and was always restarting our mariadbd process. The command systemctl stop mariadb and systemctl disable mariadb did NOT help. With the following commands you can find out who it is:

shell> systemctl list-dependencies --reverse mariadb
shell> systemctl list-dependencies systemd-sysusers.service --all --reverse