[ERROR] /usr/sbin/mysqld: The table '/tmp/ramdisk/#sql*' is full

SUMMARY:

When the MySQL server encounters the critical error indicating that temporary storage (/tmp/ramdisk/#sql*) is full, database administrators must quickly address the issue by either increasing resource limits, optimizing complex queries, or changing the temporary directory location to prevent potential server crashes.

Check Disk Space: Administrators must first check the available space in /tmp and, if necessary, move MySQL’s tmpdir location within the my.cnf configuration file to a partition with adequate storage.
Increase Memory Allocation: To prevent MySQL from resorting to writing large temporary tables to disk, increase the tmp_table_size and max_heap_table_size variables in the [mysqld] section of the configuration file and restart the service.
Optimize Query Logic: Complex queries involving large datasets that rely on GROUP BY or ORDER BY should be reviewed using EXPLAIN . Consider adding indexes or breaking the process into smaller batches to reduce the size of the required temporary tables.
Kill Offending Processes: As a last resort to save a production server, use the show processlist command to identify queries in the “Creating tmp table” state and terminate them immediately using the kill command and the associated Process ID (PID).

Proactive optimization of configuration settings and complex queries is essential to minimize the creation of large temporary tables and maintain robust database stability.

SUMMARY:
1. Check Available Space on /tmp
2. Increase MySQL Temporary Table Size
3. Review Your Queries
4. Kill the Offending Query

The MySQL tmpdir full error indicates that there isn’t enough space in the temporary storage to hold the temporary table data. Temporary tables in MySQL are created when the server needs to process complex queries, such as those using GROUP BY, ORDER BY, or derived tables.

Error: [ERROR] /usr/sbin/mysqld: The table '/tmp/ramdisk/#sql*’

The size of the temporary tables can exceed storage for several reasons.

Possible causes:

Limited Disk Size: The directory /tmp/ramdisk is a ramdisk and might have a fixed size that is too small for the temporary table MySQL needs.
Large or Complex Queries: The query that ran might involve large datasets or complex operations, leading MySQL to create large temporary tables.

Troubleshooting and Solutions:

1. Check Available Space on /tmp

To check the available space in /tmp, run:

df -h /tmp

If the disk is full:

Clear out unused files or increase the disk space.
Or move MySQL’s tmpdir to a different partition with more space (e.g., an SSD or a larger disk).

To change the tmpdir, edit your my.cnf (or my.ini) file:

[mysqld]
tmpdir = /path/to/large/directory

Then restart MySQL:

sudo systemctl restart mysqld

Make sure the new location is writable by the MySQL user.

2. Increase MySQL Temporary Table Size

If the issue is that MySQL’s temporary tables are too large, you can increase the memory available for temporary tables to avoid swapping to disk.

Check the current values:

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

If they are too small, you can increase them in your MySQL configuration file, under the [mysqld] section.

For example:

[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M

Then, restart MySQL:

sudo systemctl restart mysqld

This will allow MySQL to store larger temporary tables in memory before resorting to disk.

3. Review Your Queries

If your queries are generating large temporary tables:

Optimize queries by adding indexes or reducing the amount of data being processed.
Use EXPLAIN to check the execution plan and optimize the query logic.
Consider breaking up large queries into smaller batches.

4. Kill the Offending Query

In my case, I had to act quickly. The datadir was running out of disk space since temporary tables were writing to disk. First, I had to identify the offending query or queries.

View current queries running and look for “Creating tmp table” and “Copying to tp table” in the state column: