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

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> 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