Today, LND supports using several database backends with the default being bbolt.
Postgres represents a more battle-tested deployment of a DB and comes with some features of interest that benefit performance and data durability:
- Async / sync replication
- Vacuum for dead tuples cleanup
- (with SQL schema) optimizations around index use
- Write transaction parallelism (bbolt is single writer)
This doc will go through some common configuration recommendations as well as some queries to monitor the database activity.
It's best to use zstd for WAL compression if you can spare the CPU for it.
wal_compression = zstd
Raise the checkpoint timeout from it's default of 5m. This allows Postgres to coalesce changes (which results in smaller WAL deltas being shipped for replication). The tradeoff is that if you go too high here startup recovery performance takes a hit.
checkpoint_timeout = 10min
random_page_cost = 1.1
The default is 4 and causes the query planner to avoid index scans.
jit_above_cost = -1
jit = off
Disabling JIT is helpful as it's most useful for long-running queries while postgres_kvdb does not do those.
autovacuum_vacuum_cost_limit = 2000
Autovacuums are important for sustained and consistent performance.
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
Auto explain extension prints out an EXPLAIN ANALYZE on queries that exceed a runtime threshold (500ms here).
max_locks_per_transaction = 128
max_pred_locks_per_transaction = 1024
LND needs a lot of locks per transaction to function. You may see a shared memory error if you leave these at the default values.
work_mem = 16MB
shared_buffers = 6000MB
synchronous_standby_names = 'walreceiver'
These options are meant to be customized based on the instance's available resources. Provided values are for my personal machine which has 6C 12T and 32GB of memory. During the case studies, these values were tailored for each node runner's machine.
List connection count
lnd=# SELECT datname, COUNT(datid) FROM pg_stat_activity GROUP BY datname;
datname | count
---------+-------
lnd | 3
View connections with breakdown by active/idle
lnd=# SELECT
count(*) AS total_connections,
count(*) FILTER (WHERE state = 'active') AS active_connections,
count(*) FILTER (WHERE state = 'idle') AS idle_connections
FROM pg_stat_activity WHERE datname='lnd';
total_connections | active_connections | idle_connections
-------------------+--------------------+------------------
3 | 1 | 1
View currently executing queries and their state
lnd=# SELECT state, query
FROM pg_stat_activity WHERE datname='lnd';
state | query
---------------------+------------------------------------------------------------------
idle | commit
idle in transaction | SELECT value FROM channeldb_kv WHERE parent_id=857413 AND key=$1
(2 rows)
In testing postgres configurations, I reached out to two node runners with large amounts of traffic on their routing nodes (> 200k forwards lifetime over ~3y).
They tried the configurations above but initially were unsure that their node was any faster. However, this was due to an LNDg query being inefficient. Once my PR was merged, LNDg performance was much quicker.
Context: cryptosharks131/lndg#404
On a before/after basis, we observed an approximate 40% increase in TPS compared to default postgres configurations.
When we tested individual configuration options, random_page_cost had a very large impact. I theorize this is because it makes the postgres query planner favor index scans over sequential scans. The default value of 4 models 90% of reads being serviced from cache (true) and that a random access is 40x more expensive than a sequential one. While this was true for rotational storage media, the assumption didn't really hold for modern SSDs.
The runner-up for most improvement was more subtle: autovacuum thresholds. Without routine autovacuums, postgres performance steadily declined over time. By ensuring that autovacuums ran on a roughly daily basis we were able to ensure consistent performance over time.
- ListPayments
- ForwardingHistory
Both of these RPCs are slow now. However, with some smart querying (incrementing both start_date and index_offset in the case of ForwardingHistory for example) the performance is tolerable.
Once more SQL schemas are available, performance should increase drastically as postgres will be able to better "understand" how to serialize transactions.
Note: as of lnd v0.18.5 the Postgres write lock is removed. This should translate to better write performance compared to prior versions.
synchronous_standby_names = 'walreceiver'is mentioned twice.