Performance

• Mariadb Slow log
• Journal log size Opimization
• Essential logs to watch
• Root log in by both socket and password

Mariadb Slow log

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log';
SET GLOBAL long_query_time = 2; -- Logs queries taking over 2 seconds
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- Optional: logs unindexed queries

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/mariadb-slow.log
sudo chown -R mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

sudo systemctl restart mariadb
# Or on some systems: sudo systemctl restart mysql

head -n 50 /var/log/mysql/mariadb-slow.log.1

SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
SHOW VARIABLES LIKE 'long_query_time';

mysqldumpslow -s c -t 5 /var/log/mysql/mariadb-slow.log.1

sudo truncate -s 0 /var/log/mysql/mariadb-slow.log.1

SET GLOBAL log_queries_not_using_indexes = 'OFF';

log_queries_not_using_indexes = 0

sudo truncate -s 0 /var/log/mysql/mariadb-slow.log.1

sudo -u www-data php occ db:add-missing-indices

Journal log size Opimization

Essential logs to watch

For an Ubuntu server administrator, I recommend monitoring logs in six major areas: kernel/hardware, storage, system services, security, networking, and applications.

1. System and Kernel Logs — highest priority

Kernel messages

journalctl -k
dmesg -T

Watch for:

• I/O error
• EXT4-fs error
• Buffer I/O error
• reset
• disconnect
• USB error
• Out of memory
• OOM killer
• segfault
• hardware error
• thermal
• watchdog

For live monitoring:

journalctl -kf

---

2. Storage and Disk Health ⚠️

Especially important for servers running databases, Docker, Nextcloud, Immich, etc.

Filesystem errors

journalctl -k | grep -iE "error|ext4|xfs|btrfs|i/o"

Critical messages include:

EXT4-fs error
Buffer I/O error
blk_update_request
I/O error
device offline
reset

Disk SMART health

sudo smartctl -a /dev/sda

Monitor:

Run long tests periodically:

sudo smartctl -t long /dev/sda

---

3. Memory and OOM Events

OOM killer

journalctl | grep -iE "out of memory|oom|killed process"

Live:

journalctl -f | grep -iE "oom|memory"

Check memory:

free -h

Swap:

swapon --show

For servers, unexpected OOM events are high priority because they may silently kill:

• MariaDB
• PHP-FPM
• Redis
• Docker containers
• Nextcloud background processes

---

4. Failed System Services

One of the most useful administrator commands:

systemctl --failed

Check system errors:

journalctl -p err..alert

Recent boot errors:

journalctl -b -p warning

Useful services to watch:

systemctl status nginx
systemctl status php8.3-fpm
systemctl status mariadb
systemctl status redis-server
systemctl status docker

Depending on your PHP version:

systemctl list-units --type=service

---

5. Nginx Web Server

For web servers:

tail -f /var/log/nginx/error.log

Access log:

tail -f /var/log/nginx/access.log

Important errors:

502 Bad Gateway
504 Gateway Timeout
connect() failed
upstream timed out
permission denied
too many open files

For error frequency:

grep -i "error" /var/log/nginx/error.log

---

6. PHP-FPM

Check:

journalctl -u php8.3-fpm

Live:

journalctl -fu php8.3-fpm

Watch for:

• PHP worker crashes
• server reached pm.max_children
• Out of memory
• Slow requests
• Permission errors
• Session errors

A particularly important message:

server reached pm.max_children

This usually means PHP-FPM is saturated.

---

7. MariaDB / MySQL Database

Check logs:

journalctl -u mariadb

Live:

journalctl -fu mariadb

Watch for:

• InnoDB corruption
• Disk full
• Connection exhaustion
• Crashes
• Table corruption

Useful checks:

mysqladmin status

Connections:

mysqladmin processlist

---

8. Docker

For your type of server setup, Docker should be monitored continuously.

Docker daemon

journalctl -u docker

Live:

journalctl -fu docker

Container status

docker ps

Stopped containers:

docker ps -a

Container logs:

docker logs CONTAINER_NAME

Live:

docker logs -f CONTAINER_NAME

Check Docker disk usage:

docker system df

This is important because Docker logs and images can unexpectedly fill the filesystem.

---

9. Disk Space 🚨

Run:

df -h

Also check inode exhaustion:

df -ih

Administrators should generally investigate:

Find large directories:

sudo du -xh / --max-depth=1 2>/dev/null | sort -h

For logs:

journalctl --disk-usage

Reduce old journals if necessary:

sudo journalctl --vacuum-time=14d

---

10. Network Problems

Check networking services:

journalctl -u NetworkManager

For Ubuntu Server using systemd-networkd:

journalctl -u systemd-networkd

Current network connections:

ss -tulpn

Network errors:

ip -s link

Watch for:

• RX errors
• TX errors
• Dropped packets
• Interface resets

---

11. SSH and Security 🔐

Ubuntu authentication log:

sudo tail -f /var/log/auth.log

Failed SSH attempts:

grep "Failed password" /var/log/auth.log

Successful logins:

grep "Accepted" /var/log/auth.log

Current users:

who

Recent logins:

last

Failed login database:

lastb

---

12. System Reboots and Unexpected Shutdowns

Check reboot history:

last reboot

Previous boot:

journalctl -b -1

Check for unexpected shutdown:

journalctl | grep -iE "shutdown|reboot|panic|crash"

Kernel panic:

journalctl -k | grep -i panic

---

13. Cron and Scheduled Jobs

Depending on Ubuntu configuration:

journalctl -u cron

Or:

grep CRON /var/log/syslog

Important for:

• Nextcloud cron
• Backups
• Database dumps
• SSL certificate renewal
• Monitoring scripts

---

14. Nextcloud-specific Monitoring

For your Nextcloud servers, I would specifically watch:

sudo -u www-data php /var/www/nextcloud/occ status

Cron:

sudo -u www-data php /var/www/nextcloud/occ background:cron

Logs:

tail -f /path/to/nextcloud/data/nextcloud.log

Watch for:

• Database errors
• Redis connection failures
• File locking problems
• PHP memory exhaustion
• Background job failures
• WebDAV errors
• WOPI/Collabora errors

---

⭐ My Recommended Administrator Dashboard

For a self-hosted Ubuntu server, these are the most important things to continuously monitor:

┌───────────────────────────────────────┐
│ Ubuntu Server Monitoring              │
├───────────────────────────────────────┤
│ 🔴 Disk I/O / Filesystem errors       │
│ 🔴 SMART disk health                  │
│ 🔴 Disk space / inode usage           │
│ 🔴 OOM / memory exhaustion            │
│ 🔴 Failed systemd services            │
│ 🔴 Docker container failures          │
│ 🟠 Nginx 502/504 errors               │
│ 🟠 PHP-FPM saturation                 │
│ 🟠 MariaDB errors                     │
│ 🟠 Network interface errors           │
│ 🟠 SSH/security attacks               │
│ 🟡 Backup failures                    │
│ 🟡 SSL certificate expiration         │
│ 🟡 Temperature / hardware issues      │
└───────────────────────────────────────┘

🚀 My recommended daily commands

# Critical system errors
journalctl -p err..alert --since today

# Failed services
systemctl --failed

# Disk usage
df -h

# Memory
free -h

# Docker containers
docker ps -a

# Kernel/hardware errors
journalctl -k -p err..alert

# Recent authentication failures
grep "Failed password" /var/log/auth.log | tail

For your Ubuntu servers specifically

Given that you operate multiple Ubuntu servers with Nextcloud, Nginx, PHP-FPM, MariaDB, Redis, Docker, Immich, and other self-hosted services, I would prioritize:

1. Disk I/O errors → 2. SMART health → 3. OOM events → 4. disk capacity → 5. failed services → 6. Docker container health → 7. Nginx/PHP/MariaDB errors.

Root log in by both socket and password

The fix: let root log in by socket or password

First, look at how root is currently set up. Don’t paste the output here, since it contains the password hash:

sudo mariadb -u root -p -e "SHOW CREATE USER 'root'@'localhost'\G"

You’ll see something like IDENTIFIED BY PASSWORD '*ABC...' (the mysql_native_password plugin) or IDENTIFIED VIA .... If it’s the common native-password form, add the socket as an alternative while keeping the same hash, so the password stays unchanged:

sudo mariadb -u root -p -e "ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING '<hash from SHOW CREATE USER>' OR unix_socket;"

Replace <hash from SHOW CREATE USER> with the *... string from the previous command. If it shows a different plugin (for example ed25519), tell me the plugin name only and I’ll adjust the statement.

Keep a second terminal logged in as mariadb -u root -p while you do this, so a typo can’t lock you out.

Verify

sudo mariadb -e "SELECT CURRENT_USER();"        # works without a password now
sudo mariadb -u root -p -e "SELECT 1;"           # password still works
sudo mariadb-admin --local flush-slow-log && echo "flush ok"

Catch up on skipped maintenance

Once the socket login works, run the start-up script once by hand. It runs mariadb-upgrade and the root-account check, both safe to repeat:

sudo /etc/mysql/debian-start

You should see “Upgrading MariaDB tables if necessary” without the FATAL ERROR line. You have a dump from earlier if you ran the backup step, but for the medical instance I’d make a fresh one first.