Fix SQLite Transaction Database Page Size Issue

rippled servers with full ledger history (or a very large amount of transaction history) and a database that was initially created with a rippled version earlier than 0.40.0 (released January 2017) may encounter a problem with their SQLite database page size that stops the server from operating properly. Servers that store only recent transaction history (the default configuration) and servers whose database files were created with rippled version 0.40.0 and later are not likely to have this problem.

This document describes steps to detect and correct this problem if it occurs.

Background

rippled servers store a copy of their transaction history in a SQLite database. Before version 0.40.0, rippled configured this database to have a capacity of roughly 2 TB. For most uses, this is plenty. However, full transaction history back to ledger 32570 (the oldest ledger version available in the production XRP Ledger history) is likely to exceed this exceed the SQLite database capacity. rippled servers version 0.40.0 and later create their SQLite database files with a larger capacity, so they are unlikely to encounter this problem.

The capacity of the SQLite database is a result of the database's page size parameter, which cannot be easily changed after the database is created. (For more information on SQLite's internals, see the official SQLite documentation.) The database can reach its capacity even if there is still free space on the disk and filesystem where it is stored. As described in the Fix below, reconfiguring the page size to avoid this problem requires a somewhat time-consuming migration process.

Tip

Full history is not necessary for most use cases. Servers with full transaction history may be useful for long-term analysis and archive purposes or as a precaution against disasters. For a less resource-intense way to contribute to the storage of transaction history, see History Sharding.

Detection

If your server is vulnerable to this problem, you can detect it two ways:

You can detect the problem proactively (before it causes problems) if your rippled server is version 1.1.0 or later.
You can identify the problem reactively (when your server is crashing) on any rippled version.

In both cases, detection of the problem requires access to rippled's server logs.

Tip

The location of the debug log depends on your rippled server's config file. The default configuration writes the server's debug log to the file /var/log/rippled/debug.log.

Proactive Detection

To detect the SQLite page size problem proactively, you must be running rippled 1.1.0 or later. The rippled server writes a message such as the following in its debug log periodically, at least once every 2 minutes. (The exact numeric values from the log entry and the path to your transaction database depend on your environment.)

Transaction DB pathname: /opt/rippled/transaction.db; SQLite page size: 1024
  bytes; Free pages: 247483646; Free space: 253423253504 bytes; Note that this
  does not take into account available disk space.

The value SQLite page size: 1024 bytes indicates that your transaction database is configured with a smaller page size and does not have capacity for full transaction history. If the value is already 4096 bytes or higher, then your SQLite database should already have adequate capacity to store full transaction history and you do not need to perform the migration described in this document.

The rippled server halts if the Free space described in this log message becomes less than 524288000 bytes (500 MB). If your free space is approaching that threshold, fix the problem to avoid an unexpected outage.

Reactive Detection

If your server's SQLite database capacity has already been exceeded, the rippled service writes a log message indicating the problem and halts.

rippled 1.1.0 and Later

On rippled versions 1.1.0 and later, the server shuts down gracefully with a message such as the following in the server's debug log:

Free SQLite space for transaction db is less than 512MB. To fix this, rippled
  must be executed with the vacuum <sqlitetmpdir> parameter before restarting.
  Note that this activity can take multiple days, depending on database size.

Earlier than rippled 1.1.0

On rippled versions before 1.1.0, the server crashes repeatedly with messages such as the following in the server's debug log:

Terminating thread doJob: AcquisitionDone: unhandled
  N4soci18sqlite3_soci_errorE 'sqlite3_statement_backend::loadOne: database or
  disk is full while executing "INSERT INTO [...]

Fix

You can fix this issue using rippled on supported Linux systems according to the steps described in this document. In the case of a full-history server with system specs approximately matching the recommended hardware configuration, the process may take more than two full days.

Prerequisites

You must be running rippled version 1.1.0 or later.
- Upgrade rippled to the latest stable version before starting this process.
- You can check what version of rippled you have installed locally by running the following command:
```
rippled --version
```
You must have enough free space to temporarily store a second copy of the transaction database, in a directory that is writable by the rippled user. This free space does not need to be in the same filesystem as the existing transaction database.
The transaction database is stored in the transaction.db file in the folder specified by your configuration's [database_path] setting. You can check the size of this file to see how much free space you need. For example:
```
ls -l /var/lib/rippled/db/transaction.db
```

Migration Process

To migrate your transaction database to a larger page size, perform the following steps:

Check that you meet all the prerequisites.
Create a folder to store temporary files during the migration process.
```
mkdir /tmp/rippled_txdb_migration
```
Grant the rippled user ownership of the temporary folder so it can write files there. (This is not necessary if your temporary folder is somewhere the rippled user already has write access to.)
```
chown rippled /tmp/rippled_txdb_migration
```
Confirm that your temporary folder has enough free space to store a copy of the transaction database.
For example, compare the Avail output from the df command to the size of your transaction.db file.
```
df -h /tmp/rippled_txdb_migration

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2       5.4T  2.6T  2.6T  50% /tmp
```
If rippled is still running, stop it:
```
sudo systemctl stop rippled
```
Open a screen session (or other similar tool) so that the process does not stop when you log out:
```
screen
```
Become the rippled user:
```
sudo su - rippled
```
Run rippled executable directly, providing the --vacuum command with the path to the temporary directory:
```
/opt/ripple/bin/rippled -q --vacuum /tmp/rippled_txdb_migration
```
The rippled executable immediately displays the following message:
```
VACUUM beginning. page_size: 1024
```
Wait for the process to complete. This can take more than two full days.
When the process is complete, the rippled executable displays the following message, then exits:
```
VACUUM finished. page_size: 4096
```
While you wait, you can detach your screen session by pressing CTRL-A, then D. Later, you can reattach your screen session with a command such as the following:
```
screen -x -r
```
When the process is over, exit the screen session:
```
exit
```
For more information on the screen command, see the official Screen User's Manual or any of the other many resources available online.
Restart the rippled service.
```
sudo systemctl start rippled
```
Confirm that the rippled service started successfully.
You can use the commandline interface to check the server status (unless you have configured your server not to accept JSON-RPC requests). For example:
```
/opt/ripple/bin/rippled server_info
```
For a description of the expected response from this command, see the server_info method documentation.
Watch the server's debug log to confirm that the SQLite page size is now 4096:
```
tail -F /var/log/rippled/debug.log
```
The periodic log message should also show significantly more free pages and free pages than it did before the migration.
Optionally, you may now remove the temporary folder you created for the migration process.
```
rm -r /tmp/rippled_txdb_migration
```
If you mounted additional storage to hold the temporary copy of the transaction database, you can unmount and remove it now.