How to reduce x-ui.db-wal size on Ubuntu

To reduce the size of the x-ui.db-wal file, you need to perform a forced checkpoint of the SQLite database. The problem occurs when the WAL (Write-Ahead Logging) file doesn’t automatically shrink after processing transactions. Here are effective solutions for Ubuntu 22.04.

Table of Contents

• Forced Database Checkpoint
• SQLite Parameter Configuration
• Complete WAL File Cleanup
• Preventing Regrowth
• Alternative Methods
• Result Verification

Forced Database Checkpoint

The most effective way to reduce the WAL file size is to perform a forced database checkpoint. To do this, connect to the SQLite database using the command:

sudo sqlite3 /etc/x-ui/x-ui.db

Inside the SQLite interactive shell, execute the command:

PRAGMA wal_checkpoint(SQLITE_CHECKPOINT_TRUNCATE);

This command forces a full checkpoint and truncates the WAL file to zero size. If this doesn’t work, try a passive checkpoint:

PRAGMA wal_checkpoint(PASSIVE);

Important: After executing the command, exit SQLite using .quit

SQLite Parameter Configuration

To prevent the WAL file from growing again, configure SQLite parameters. Connect to the database:

sudo sqlite3 /etc/x-ui/x-ui.db

Set a journal size limit:

PRAGMA journal_size_limit = 1048576; -- 1MB

You can also configure automatic checkpointing:

PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -10000; -- 10MB cache

These parameters will help control the WAL file size and prevent excessive growth.

Complete WAL File Cleanup

If forced checkpointing doesn’t help, you can completely clear the WAL file. To do this:

1. Stop the x-ui service:

sudo systemctl stop x-ui

2. Delete the WAL file:

sudo rm /etc/x-ui/x-ui.db-wal

3. Start the service:

sudo systemctl start x-ui

SQLite will automatically create a new WAL file on the next write to the database. This method is the most radical but effective when other methods don’t work.

Preventing Regrowth

To prevent the WAL file from growing again, add automatic checkpointing to cron:

sudo crontab -e

Add the following line for hourly checkpointing:

0 * * * * sqlite3 /etc/x-ui/x-ui.db "PRAGMA wal_checkpoint(PASSIVE);"

You can also create a script to monitor the WAL file size:

#!/bin/bash
WAL_SIZE=$(du -b /etc/x-ui/x-ui.db-wal | cut -f1)
if [ $WAL_SIZE -gt 1073741824 ]; then # 1GB
    sqlite3 /etc/x-ui/x-ui.db "PRAGMA wal_checkpoint(TRUNCATE);"
fi

Save this script and add it to cron for periodic execution.

Alternative Methods

Using vacuum

Perform a full database cleanup:

sudo sqlite3 /etc/x-ui/x-ui.db "VACUUM;"

This command will recreate the database without free space, which may also reduce the WAL file size.

Changing journal mode

If WAL mode is not critical for your application, you can temporarily disable it:

sudo sqlite3 /etc/x-ui/x-ui.db "PRAGMA journal_mode = DELETE;"

This will force SQLite to use the old journaling mode, which doesn’t create separate WAL files.

Result Verification

After performing the procedures, check the WAL file size:

ls -lh /etc/x-ui/x-ui.db-wal

Ensure the x-ui service is running correctly:

sudo systemctl status x-ui

Check that the x-ui control panel is working properly, ensuring all data is being saved correctly.

If the problem occurs regularly, consider:

• Increasing disk space
• Setting up automatic database backup
• Using a database on a faster drive (SSD)

Sources

1. SQLite User Forum: WAL journal file-size keeps on growing
2. Database Administrators Stack Exchange - SQLite and Write Ahead Logging
3. Stack Overflow - SQLITE database WAL file size keeps growing
4. The Unterminated String - SQLite: Vacuuming the WALs
5. Ask Ubuntu - The tracker file meta.db-wal filled my drive

Conclusion

• The size of the x-ui.db-wal file can be effectively reduced using forced checkpointing with PRAGMA wal_checkpoint(TRUNCATE)
• Configuring SQLite parameters (journal_size_limit, synchronous) will help prevent WAL file regrowth
• If necessary, you can completely delete the WAL file after stopping the service
• Automating checkpointing via cron will ensure continuous control of the WAL file size
• Regular database monitoring will help promptly detect and prevent WAL file size issues

These methods will help solve the problem of excessive WAL file size and ensure stable operation of your x-ui server on Ubuntu 22.04.