Understanding and Resolving Temporary Sort Files in Vertica
In high-load Vertica environments, it is common for temporary sort files to accumulate as part of normal query processing. This occurs frequently with large data sets, complex joins, memory-intensive operations, and statements such as INSERT, MERGE INTO, and queries using WITH clauses, which can generate a significant number of sort temp files.
These files typically appear in the format: sort_temp_part_0_*.dat
Under normal conditions, Vertica automatically removes these files when the related query finishes.
However, when many heavy queries run concurrently or a session becomes stalled or blocked, Vertica may be unable to release the associated file handles.
As a result, temporary sort files start to accumulate and may consume disk space rapidly.
When disk space becomes insufficient, errors such as the following can occur:
/data/v_DBname_node0001_data/Sort_Temp_part_0_1234-1.dat: No space left on device
Additional Factors That Can Trigger “No Space Left on Device” Errors
The “No space left on device” error can still appear even when there seems to be free disk space. Here are a few things that can cause this and are worth checking:
1. Inode Exhaustion
Even if the disk shows free space, the filesystem may have run out of inodes—the metadata entries used to track files. When all inodes are used, no new files can be created, which triggers the same “No space left on device” message.
How to check:
Run df -i to see inode usage on each filesystem.
2. Disk Quotas
Some systems enforce quotas for users or groups, limiting either total disk space or total number of files/inodes. If the user hits their quota, they will receive the error even though the overall filesystem still has available space.
How to check:
Use quota -u <username> or quota -g <groupname>.
3. RAID or LVM Storage Layers
If the system uses LVM or RAID, the underlying physical storage may be full or misconfigured, even though the logical volume reports free space to the OS.
How to check:
Inspect physical volume usage (pvdisplay, lvdisplay) or RAID health (mdadm --detail).
Cleaning Communal Storage in EON Mode Using the Reaper Queue
In EON mode, temporary files related to communal storage can be cleaned using the reaper queue, and there is no need to restart the node or the cluster.
To add files to the reaper queue do:SELECT CLEAN_COMMUNAL_STORAGE('true'); -- This will add files to the reaper queue and return immediately.
-- The queued files are removed automatically by the reaper service,
-- or can be removed manually by calling FLUSH_REAPER_QUEUE
SELECT FLUSH_REAPER_QUEUE();
-- This will sync catalog and delete all files in the reaper queue.
SELECT CLEAN_COMMUNAL_STORAGE('false');
-- Report information about extra files but do not queue them for deletion.
Safe Cleanup Procedure for Temporary Files in Vertica Enterprise Edition
In Vertica Enterprise Edition (EE) we have to ensure that Vertica itself completes the cleanup cycle by allowing sessions to terminate cleanly or by performing a controlled restart when required.
Safe remediation focuses on identifying and clearing any stuck or long-running operations and then enabling Vertica to close its own file handles so it can purge the temporary files properly.
Required EE steps to ensure a safe and complete cleanup:
- Determine the original value for the MaxClientSessions parameter by querying the CONFIGURATION_PARAMETERS system table:
SELECT CURRENT_VALUE
FROM CONFIGURATION_PARAMETERS
WHERE parameter_name='MaxClientSessions';
- Set the MaxClientSessions parameter to 0 to prevent new non-dbadmin connections:
ALTER DATABASE mydb SET MaxClientSessions = 0;-- In addition to the MaxClientSessions setting, Vertica also allows up to 5 dbadmin sessions per node.
- Issue the CLOSE_ALL_SESSIONS() command to remove existing sessions:
SELECT CLOSE_ALL_SESSIONS();
- Query the SESSIONS table:
SELECT * FROM SESSIONS;
When the session no longer appears in the SESSIONS table continue to the next step below.
- Please check the system status, as the issue may already be resolved if Vertica has stopped the session that was generating the temporary files. If the problem persists, verify that the file system is not full, and then restart the node with the highest disk usage - typically the initiator node.
For example: admintools -t stop_node -s 10.10.10.58
Sending signal 'TERM' to ['10.10.10.58']
Successfully sent signal 'TERM' to hosts ['10.10.10.58'].
Details:
Host: 10.10.10.58 - Success - PID: 3551043 Signal TERM
Checking for processes to be down
All processes are down.
Details:
Host 10.10.10.58 Success process 3551043 is down
admintools -t restart_node -s 10.10.10.58 -d EEVDB
*** Restarting nodes for database EEVDB ***
Restarting host [10.10.10.58] with catalog [v_eevdb_node0002_catalog]
Issuing multi-node restart
Starting nodes:
v_eevdb_node0002 (10.10.10.58)
Starting Vertica on all nodes.
Please wait, databases with a large catalog may take a while to initialize.
Node Status: v_eevdb_node0002: (DOWN)
Node Status: v_eevdb_node0002: (INITIALIZING)
Node Status: v_eevdb_node0002: (RECOVERING)
Node Status: v_eevdb_node0002: (UP)
- Restore the MaxClientSessions parameter to its original value:
ALTER DATABASE db_name SET MaxClientSessions = 50;
Another option is to restart the database as shown below.
As dbadmin issue:SELECT SHUTDOWN('true');
-- This forces the database to shut down, disallowing further connections.
How to Detect Runaway Queries and Spill Events Before Cleanup
How to identify the runaway query next time you can use:SELECT session_id, user_name, transaction_id, run_time, statement
FROM sessions
ORDER BY run_time DESC;
And here’s a query that will determine all spills in the last 24hrs:SELECT
time,
transaction_id,
statement_id,
request_id,
event_type,
event_description
FROM dc_execution_engine_events
WHERE (NOW() - time) < '24 hours'
AND event_type ILIKE '%SPILL%'
ORDER BY time DESC;
These queries should be run before cleanup to identify sessions blocking file handle release.
