When MariaDB encounters a full disk due to temporary .MAD files in /var/tmp/, it creates a perfect storm:

[Warning] mysqld: Disk is full writing '/var/tmp/#sql_b6f_0.MAD' (Errcode: 28)

First stop MariaDB service:

sudo systemctl stop mariadb
# Or for older systems:
sudo service mysqld stop

Locate and remove the offending file:

sudo find /var/tmp/ -name "*.MAD" -exec ls -lh {} \;
sudo rm -f /var/tmp/#sql_b6f_0.MAD

Verify space is freed before restarting:

df -h

Set temporary directory limits in /etc/my.cnf:

[mysqld]
tmpdir = /mnt/larger-volume/tmp
tmp_table_size = 256M
max_heap_table_size = 256M

For systemd services, add limits:

[Service]
LimitFSIZE=1073741824  # 1GB file size limit
PrivateTmp=true

Create a monitoring script (/usr/local/bin/check_mariadb_tmp.sh):

#!/bin/bash
THRESHOLD=90
TMP_USAGE=$(df /var/tmp --output=pcent | tail -1 | tr -d '% ')

if [ $TMP_USAGE -gt $THRESHOLD ]; then
    logger "MariaDB tmpdir usage critical: ${TMP_USAGE}%"
    # Add cleanup logic or alerting here
fi

Add cron job:

*/15 * * * * /usr/local/bin/check_mariadb_tmp.sh

Consider these filesystem-level protections:

# Create dedicated partition with limits
sudo mkdir /mnt/mariadb_tmp
sudo mount -o size=10G,nr_inodes=1m -t tmpfs none /mnt/mariadb_tmp

Or implement LVM thin provisioning:

lvcreate -V50G -T vg00/lv_thin -n mariadb_tmp
mkfs.ext4 /dev/vg00/mariadb_tmp

---

When MariaDB encounters disk space issues with temporary files, it typically generates warning messages like:

[Warning] mysqld: Disk is full writing '/var/tmp/#sql_b6f_0.MAD' (Errcode: 28)

The .MAD files are temporary files created during certain operations like:

• Large ALTER TABLE operations
• Complex sorting operations
• Bulk inserts with temporary tables

Yes, it's generally safe to remove these files manually when MariaDB is stopped:

# Stop MariaDB first
sudo systemctl stop mariadb

# Remove the problematic file
sudo rm -f /var/tmp/#sql_*.MAD

# Verify space is freed
df -h

# Restart MariaDB
sudo systemctl start mariadb

Configure MariaDB to better manage temporary files:

1. Set Temporary Directory Limits

Edit /etc/my.cnf or /etc/mysql/my.cnf:

[mysqld]
tmpdir = /mnt/larger_disk/tmp  # Point to a dedicated partition
tmp_table_size = 64M
max_heap_table_size = 64M

2. Implement Disk Space Monitoring

Create a monitoring script (check_space.sh):

#!/bin/bash
THRESHOLD=90
CURRENT=$(df /var | grep /var | awk '{ print $5}' | sed 's/%//g')

if [ "$CURRENT" -gt "$THRESHOLD" ]; then
    echo "Disk space alert: $CURRENT% used on /var" | mail -s "Disk Space Alert" admin@example.com
    # Optionally kill long-running queries
    mysql -e "SELECT id,time,info FROM information_schema.processlist WHERE time > 300 ORDER BY time DESC"
fi

3. Optimize Problematic Queries

Find queries that generate large temporary files:

# In MySQL/MariaDB shell:
SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.processlist WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;

For frequent offenders, consider:

• Adding proper indexes
• Breaking large operations into batches
• Using pt-online-schema-change for ALTER TABLE operations

For systems with frequent large operations:

[mysqld]
internal_tmp_disk_storage_engine = InnoDB
tmp_table_size = 128M
max_heap_table_size = 128M
slave_load_tmpdir = /mnt/tmp