Hello @infolloyd Happy you are here
First enable mariadb errors by commenting out the skip_log_error and syslog directives in /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf this way
# NOTE: This file is read only by the traditional SysV init script, not systemd.
# MariaDB systemd does _not_ utilize mysqld_safe nor read this file.
#
# For similar behaviour, systemd users should create the following file:
# /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
#
# To achieve the same result as the default 50-mysqld_safe.cnf, please create
# /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
# with the following contents:
#
# [Service]
# User=mysql
# StandardOutput=syslog
# StandardError=syslog
# SyslogFacility=daemon
# SyslogLevel=err
# SyslogIdentifier=mysqld
#
# For more information, please read https://mariadb.com/kb/en/mariadb/systemd/
#
[mysqld_safe]
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# especially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
socket = /var/run/mysqld/mysqld.sock
nice = 0
# comment out here
# skip_log_error
# syslog
What this does is isolate all you mariadb errors to /var/log/mysql/error.log for better support and attention.
For your performance issue first use htop to check the process consuming alot of resources (CPU or memory etc) Follow this guide to install on your OS
If you believe its mariadb check /var/lib/mysql/mysqld-slow.log for slow queries but this log is not enabled by default so you have to:
Run nano /etc/mysql/mariadb.conf.d/50-server.cnf and look up log_error and below it uncomment
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_slow_rate_limit = 1000
#log_slow_verbosity = query_plan
log-queries-not-using-indexes
Then restart the service and tail it for slow queries:
$ systemctl restart mysqld
$ tail -f /var/log/mysql/slow-query.log
You can also log query by query for slow queries by following this tutorial
To analyze mariadb logs I use Percona Toolkit: apt-get install percona-toolkit to analyze binary and slow logs, log MySQL foreign key errors, log MySQL deadlocks, read queries from a log and analyze how they use indexes.
For example to analyze slow query logs run
$ pt-query-digest /var/log/mysql/slow-query.log
Check this out MariaDB / MySQL Tweaks As Default & Option
Basically run nano /etc/my.cnf use the keyboard shortcut CTRL+W to search for skip-name-resolve and comment it out. Restart mariadb systemctl restart mysqld