Optimize Database, improve SNO API performance

Node Operators
1

Hi there. I’m leveraging the GitHub - anclrii/Storj-Exporter: Prometheus exporter for monitoring Storj storage nodes but I’m seeing that some of my nodes are experiencing slow SNO API responses; so slow, that they don’t return responses after a few minutes.

Examples of some endpoints timing out are:
/api/sno/satellite/12L9ZFwhzVpuEKMUNUqkaTLGzwY9G24tbiigLiXpmZWKwmcNDDs
/api/sno/estimated-payout

I ended up moving all of my databases to NVMEs, separate from the data location of my nodes. It’s interesting because I have multiple nodes on the same machine, and one returns fine, and the other doesn’t. It’s sporadic. For example, I have one node that’s been timing out for the past few days, but the other node is perfectly fine.

Comparing the two, I see the following filesizes:

File Node 1 Node 2
bandwidth.db 1,912,053,760 22,265,856
bandwidth.db-shm 1,245,184 32,768
bandwidth.db-wal 638,212,752 8,441,912
heldamount.db 53,248 53,248
info.db 16,384 16,384
notifications.db 24,576 24,576
orders.db 32,768 32,768
orders.db-shm 32,768
orders.db-wal 0
piece_expiration.db 284,540,928 282,505,216
piece_expiration.db-shm 32,768 32,768
piece_expiration.db-wal 4,120,032 4,128,272
piece_spaced_used.db 24,576 24,576
piece_spaced_used.db-shm 32,768 24,576
piece_spaced_used.db-wal 20,632 32,768
pieceinfo.db 24,576 0
pricing.db 24,576 24,576
pricing.db-shm 32,768 32,768
pricing.db-wal 0 0
reputation.db 36,864 36,864
reputation.db-shm 32,768 32,768
reputation.db-wal 0 0
satellites.db 32,768 32,768
satellites.db-shm 32,768 32,768
satellites.db-wal 0 0
secret.db 24,576 24,576
storage_usage.db 299,008 303,104
storage_usage.db-shm 32,768 32,768
storage_usage.db-wal 0 0
used_serial.db 20,480 20,480

Bandwidth.db is huge compared to the other, yet they both have roughly 3.8TB of data, and have the same amount in/out bandwidth.

Are there ways to reduce the size of this database without deleting it completely?

The historical data that’s maintained in here is just for the dashboard… how long is that historical data for? Is it only a months worth?

Thanks!

2

check the logs of node one if bandwith.db is locked.

try to stop the node and vakuum the db.

if nothing helps, recreate database.

3

1,9 GiB it’s a huge size for bandwidth. First check if it’s ok, than do the vaccuum.
Search for “how to repair corrupted databases” or something; it’s in docs.

4

I believe it’s forever, so no retention period.

5

Mine is 76MiB on the oldest node… But I lost DBs once when I have tried to migrate to Linux:

6

The bandwidth.db file contains two tables of information. One is the bandwidth_usage_rollups table that stores historical rollups of bandwidth usage with a granularity of one hour (per satellite and per data transfer type, so you should expect around 20-30 rows per hour of node’s operation, not much). The other one is bandwidth_usage , which stores bandwidth usage for each single data transfer (potentially thousands per hour). You can look at this file using any SQLite client (though, preferably do this on a copy of the file made while the node was turned off, just to be sure; this paste is reformatted and trimmed for clarity):

% sqlite3 bandwidth.db
sqlite> .schema
CREATE TABLE bandwidth_usage (
	satellite_id  BLOB    NOT NULL,
	action        INTEGER NOT NULL,
	amount        BIGINT  NOT NULL,
	created_at    TIMESTAMP NOT NULL
);
CREATE TABLE bandwidth_usage_rollups (
	interval_start	TIMESTAMP NOT NULL,
	satellite_id  	BLOB    NOT NULL,
	action        	INTEGER NOT NULL,
	amount        	BIGINT  NOT NULL,
	PRIMARY KEY ( interval_start, satellite_id, action )
);

The latter is only supposed to be stored for a brief period of time until a chore will aggregate per-transfer data. This chore is run once an hour, so technically you should not be seeing rows older than ~2 hours in the bandwidth_usage table. You can look at the number of rows in both tables (this is for a node operating for close to three years, full and hence and not accepting more uploads):

sqlite> SELECT COUNT(1) FROM bandwidth_usage;
19
sqlite> SELECT COUNT(1) FROM bandwidth_usage_rollups;
280927

The bandwidth.db on this node is currently 36MB.

I see at least two reasons why the bandwidth.db file might grow to gigabytes.

Sometimes the rollup procedure is not performed correctly. SQLite is a transactional database. Transactions can fail if they are not done quickly enough. If a database is stored on a slow storage (e.g. a HDD with parity-based block storage with a slow copy-on-write fragmented file system on a memory-starved setup), rollups may start to fail, accumulating more and more data in the bandwidth_usage table, making future rollups even bigger. You can verify this hypothesis by looking at the row count of the bandwidth_usage table.

A possible one-time workaround is to reduce the I/O on the storage the database is placed for about two hours and make sure a rollup is performed correctly. If the node itself is the source of most I/O on that storage, you can do that e.g. by restarting the node with a disk allocation below the current stored amount (the node will stop accepting uploads then), or disabling network access (the node will become offline, so no I/O). You can check whether the rollup was performed by looking at the number of rows in the database, or at storage node logs (a Performing bandwidth usage rollups entry at the INFO level signifies the start of a rollup procedure—it would be nice to have the end logged as well…).

Another possible reason comes from the fact that SQLite does not free up disk space after deleting rows to optimize for the case where that disk space would be necessary for new rows. This is usually the case for storage nodes, but after big peaks of traffic (or after a large consecutive number of missing rollup executions!) you may actually end up with large amount of disk space allocated, but not used by SQLite. You can verify this hypothesis by looking at database statistics:

sqlite> SELECT SUM(pgsize) FROM dbstat;
36712448

(which matches the file size of 36MB here, so no problem).

To fix this problem, the best way is to vacuum the database file.

5 Likes
7

Tried vacuum.

File Node 1 Node 1 After Change
bandwidth.db 1,912,053,760 1,821,536,256 -4.73%
bandwidth.db-shm 1,245,184 -100.00%
bandwidth.db-wal 638,212,752 -100.00%
heldamount.db 53,248 53,248 0.00%
info.db 16,384 16,384 0.00%
notifications.db 24,576 24,576 0.00%
orders.db 32,768 24,576 -25.00%
orders.db-shm 32,768 -100.00%
orders.db-wal 0
piece_expiration.db 284,540,928 264,638,464 -6.99%
piece_expiration.db-shm 32,768 -100.00%
piece_expiration.db-wal 4,120,032 -100.00%
piece_spaced_used.db 24,576 24,576 0.00%
piece_spaced_used.db-shm 32,768 -100.00%
piece_spaced_used.db-wal 20,632 -100.00%
pieceinfo.db 24,576 24,576 0.00%
pricing.db 24,576 16,384 -33.33%
pricing.db-shm 32,768 -100.00%
pricing.db-wal 0
reputation.db 36,864 24,576 -33.33%
reputation.db-shm 32,768 -100.00%
reputation.db-wal 0
satellites.db 32,768 20,480 -37.50%
satellites.db-shm 32,768 -100.00%
satellites.db-wal 0
secret.db 24,576 24,576 0.00%
storage_usage.db 299,008 253,952 -15.07%
storage_usage.db-shm 32,768 -100.00%
storage_usage.db-wal 0
used_serial.db 20,480 20,480 0.00%

Logs look fine with no errors too.
I’m guessing at this point it’s probably worth just deleting the database and having a new one get re-created. - Followed the instructions here: FATAL Unrecoverable error {"error": "Error during preflight check for storagenode databases "bandwidth" - #3 by BrightSilence
and now the storj node is back online and API is much quicker to resolve. I’m curious to see if this has any impact on node performance (eg. node1 vs node2). I may report back on any differences I see.

Thanks y’all.