How to reduce x-ui.db-wal size in Ubuntu 22

Managing Large x-ui.db-wal Files in Ubuntu 22

The db-wal file in x-ui can grow significantly due to SQLite’s Write-Ahead Logging (WAL) mode, which accumulates changes before applying them. To reduce the size of the x-ui.db-wal file in Ubuntu 22, you need to force a database checkpoint and optimize SQLite settings. These actions will help clear accumulated changes and return the file size to normal values.

Contents

• What is the db-wal file and why does it grow?
• Methods to reduce the db-wal file size
• Optimizing the x-ui database
• Preventing excessive db-wal growth in the future
• Verifying results

What is the db-wal file and why does it grow?

The x-ui.db-wal file is part of the SQLite database operating in Write-Ahead Logging (WAL) mode. This mode is designed to improve performance with concurrent database access, but it leads to accumulation of unprocessed changes in a separate file.

Main reasons for db-wal file growth:

1. Lack of automatic cleanup - SQLite doesn’t always perform checkpointing in a timely manner, especially with high write intensity
2. Incorrect configuration - lack of WAL file size limitations
3. Process blocking - active database connections prevent checkpoint execution
4. Application errors - incorrect transaction handling

As noted in forum discussions, db-wal files can reach extreme sizes - from tens to hundreds of gigabytes, which is a common problem for many applications using SQLite in WAL mode.

Methods to reduce the db-wal file size

1. Forcing CHECKPOINT execution

The most effective way to reduce the db-wal file size is to force a SQLite database checkpoint:

# Stop the x-ui service
sudo systemctl stop x-ui

# Perform forced checkpoint
sqlite3 /etc/x-ui/x-ui.db "PRAGMA wal_checkpoint(FULL)"

# Check the result
ls -lh /etc/x-ui/x-ui.db*

This command forces SQLite to immediately apply all accumulated changes and reset the WAL file to zero size. As explained in the SQLite User Forum, a forced checkpoint can be executed from a separate process.

2. Deleting the db-wal file (temporary solution)

If the checkpoint command didn’t help, you can manually delete the db-wal file:

# Stop the x-ui service
sudo systemctl stop x-ui

# Create a backup of the database
sudo cp /etc/x-ui/x-ui.db /etc/x-ui/x-ui.db.backup

# Delete the WAL file
sudo rm /etc/x-ui/x-ui.db-wal

# Start the service
sudo systemctl start x-ui

Important: When deleting the db-wal file, SQLite will automatically create a new WAL file upon the next database access. This is a temporary solution that doesn’t address the root problem.

3. Database optimization

After performing a checkpoint, it’s recommended to optimize the database itself:

# Stop the x-ui service
sudo systemctl stop x-ui

# Perform VACUUM to repack the database
sqlite3 /etc/x-ui/x-ui.db "VACUUM"

# Check the result
ls -lh /etc/x-ui/x-ui.db

The VACUUM command repacks the database, eliminating fragmentation and reducing its physical size.

Optimizing the x-ui database

SQLite settings for limiting WAL file size

To prevent excessive WAL file growth in the future, you can set limits directly in the database:

# Stop the x-ui service
sudo systemctl stop x-ui

# Set WAL size limits
sqlite3 /etc/x-ui/x-ui.db "
PRAGMA journal_size_limit = 10485760;  -- Limit WAL to 10MB
PRAGMA synchronous = NORMAL;           -- Reduce synchronization frequency
PRAGMA cache_size = -10000;           -- Increase cache size
PRAGMA temp_store = MEMORY;           -- Use memory for temporary tables
"

# Start the service
sudo systemctl start x-ui

These settings will help control the WAL file size and improve database performance.

Automatic cleanup via cron

For automatic WAL file cleanup, you can set up a cron job:

# Create a cleanup script
sudo nano /usr/local/bin/cleanup-x-ui-wal.sh

# Add the following code:
#!/bin/bash
if [ "$(systemctl is-active x-ui)" = "active" ]; then
    systemctl stop x-ui
    sqlite3 /etc/x-ui/x-ui.db "PRAGMA wal_checkpoint(FULL)"
    systemctl start x-ui
fi

# Make the script executable
sudo chmod +x /usr/local/bin/cleanup-x-ui-wal.sh

# Add to cron (daily cleanup at 3 AM)
sudo crontab -e
# Add the line:
0 3 * * * /usr/local/bin/cleanup-x-ui-wal.sh

Preventing excessive db-wal growth in the future

Monitoring file size

Set up monitoring of the db-wal file size for early problem detection:

# Create a monitoring script
sudo nano /usr/local/bin/monitor-x-ui-wal.sh

# Add code:
#!/bin/bash
WAL_SIZE=$(du -b /etc/x-ui/x-ui.db-wal | cut -f1)
MAX_SIZE=$((1024 * 1024 * 100))  # 100MB

if [ $WAL_SIZE -gt $MAX_SIZE ]; then
    echo "WARNING: x-ui.db-wal is too large: $WAL_SIZE bytes" | logger -t x-ui-monitor
    # You can add automatic cleanup here
fi

# Make executable and add to cron
sudo chmod +x /usr/local/bin/monitor-x-ui-wal.sh
sudo crontab -e
# Add check every 15 minutes
*/15 * * * * /usr/local/bin/monitor-x-ui-wal.sh

Regular database maintenance

Set up regular maintenance for the x-ui database:

# Create a maintenance script
sudo nano /usr/local/bin/maintenance-x-ui.sh

# Add code:
#!/bin/bash
systemctl stop x-ui
sqlite3 /etc/x-ui/x-ui.db "
PRAGMA wal_checkpoint(FULL);
VACUUM;
ANALYZE;
"
systemctl start x-ui

# Make executable
sudo chmod +x /usr/local/bin/maintenance-x-ui.sh

# Add to cron (weekly maintenance on Sunday at 4 AM)
sudo crontab -e
# Add the line:
0 4 * * 0 /usr/local/bin/maintenance-x-ui.sh

Verifying results

After applying optimization methods, check the results:

# Check file sizes
ls -lh /etc/x-ui/x-ui*

# Check database size
sqlite3 /etc/x-ui/x-ui.db "SELECT COUNT(*) FROM sqlite_master;"

# Check current SQLite settings
sqlite3 /etc/x-ui/x-ui.db "PRAGMA journal_mode; PRAGMA journal_size_limit;"

# Monitor disk usage
df -h /etc/x-ui/

If the db-wal file size remains large, you may need to:

1. Check for errors in x-ui logs
2. Analyze database activity
3. Consider switching to a different DBMS for high-load scenarios

Sources

1. SQLite User Forum: WAL journal file-size keeps on growing…
2. Stack Overflow: SQLITE database WAL file size keeps growing
3. GitHub: SQLite performance tuning
4. Ask Ubuntu: The tracker file meta.db-wal filled my drive
5. Linux Mint Forums: ~/.cache/tracker/meta.db-wal 32GB
6. Reddit: database-wal file taking up all space

Conclusion

Reducing the x-ui.db-wal file size in Ubuntu 22 requires a systematic approach:

1. Immediately perform a forced checkpoint using the PRAGMA wal_checkpoint(FULL) command after stopping the service
2. Set WAL limits via PRAGMA commands to prevent excessive growth
3. Implement regular maintenance of the database using cron scripts
4. Set up monitoring for early detection of WAL file size problems

For 40 clients, an 11GB db-wal file is indeed excessive. After optimization, the normal file size should be a few megabytes, maximum tens of megabytes with active work. If the problem persists, you may want to check for data leaks or errors in the x-ui application itself.