Handling big Sqlite DBs

[DerEwige]

My nodes machine has started to feel slow and unresponsive at times.
After some investigation I found the problem in the eclair.sqlite.
The eclair.sqlite has grown to over 1 GB within 2 months of my node running.
And the default setting creates a backup file every 10 seconds.

For now I have increased the interval for the backup and it helped.
But this is only a temporary fix.

What are my options should I migrate to PostgreSQL?
Can I trim down my DB? (I don’t really need all the payment info from one month ago)

[t-bast]

The eclair.sqlite has grown to over 1 GB within 2 months of my node running.

That is very surprising...maybe it's linked to your non-standard use of the ignore_nodes parameters, we may store that in the DB somewhere...can you diagnose what tables are taking all this space?

Can I trim down my DB?

Yes of course, if you don't need some rows in the DB, you should definitely have a script to delete them.

[DerEwige]

*** Page counts for all tables with their indices *****************************

SENT_PAYMENTS..................................... 159201      66.5% 
RECEIVED_PAYMENTS................................. 57932       24.2% 
HTLC_INFOS........................................ 22273        9.3% 
LOCAL_CHANNELS.................................... 91           0.038% 
PEERS............................................. 2            0.0% 
PENDING_SETTLEMENT_COMMANDS....................... 2            0.0% 
RELAY_FEES........................................ 2            0.0% 
VERSIONS.......................................... 2            0.0% 
DUMMY_TABLE_FOR_LOCKING........................... 1            0.0% 
SQLITE_SCHEMA..................................... 1            0.0% 

*** Page counts for all tables and indices separately *************************

SENT_PAYMENTS..................................... 139060      58.1% 
RECEIVED_PAYMENTS................................. 50226       21.0% 
HTLC_INFOS........................................ 15202        6.3% 
HTLC_INFOS_IDX.................................... 7071         3.0% 
SQLITE_AUTOINDEX_SENT_PAYMENTS_1.................. 6211         2.6% 
SENT_PARENT_ID_IDX................................ 6176         2.6% 
SENT_PAYMENT_HASH_IDX............................. 5648         2.4% 
SQLITE_AUTOINDEX_RECEIVED_PAYMENTS_1.............. 5607         2.3% 
SENT_CREATED_IDX.................................. 2106         0.88% 
RECEIVED_CREATED_IDX.............................. 2099         0.88% 
LOCAL_CHANNELS.................................... 90           0.038% 
DUMMY_TABLE_FOR_LOCKING........................... 1            0.0% 
PEERS............................................. 1            0.0% 
PENDING_SETTLEMENT_COMMANDS....................... 1            0.0% 
RELAY_FEES........................................ 1            0.0% 
SQLITE_AUTOINDEX_LOCAL_CHANNELS_1................. 1            0.0% 
SQLITE_AUTOINDEX_PEERS_1.......................... 1            0.0% 
SQLITE_AUTOINDEX_PENDING_SETTLEMENT_COMMANDS_1.... 1            0.0% 
SQLITE_AUTOINDEX_RELAY_FEES_1..................... 1            0.0% 
SQLITE_AUTOINDEX_VERSIONS_1....................... 1            0.0% 
SQLITE_SCHEMA..................................... 1            0.0% 
VERSIONS.......................................... 1            0.0% 

I guess it is just the 1 million payments I did in those 2 months.
I assume it is save to delete data from SENT_PAYMENTS and RECEIVED_PAYMENTS?

[t-bast]

Yes, the payment sent/received tables contain the invoice and some details about the route and failures, so it can grow if you're making/receiving a lot of payments.

You can indeed delete data from those tables as soon as you don't need them, eclair itself doesn't need them once a payment is complete, it's only for the node operator's accounting that we keep them.

[DerEwige]

I believe I will export them into another sqlite DB before removing them. So I still have access to the information, but does not make issues with my backup

[DerEwige]

Ok I sucessfully shrunk down my DB to 71 MB again

[DerEwige]

@t-bast
besides SENT_PAYMENTS and RECEIVED_PAYMENTS.
Is it possbible to delete something from HTCL_INFOS?

Or is stuff rom closed channels automatically removed?

I have 1.7 million entries in HTCL_INFOS

[DerEwige]

@t-bast
So far purging payments.sent and payments.received works great

But I rapidly run into a potential problem with LOCAL.htlc_infos.
I already have 12 million entries in that table and the table is taking up 4.5 GB of space

Is my only option to close and reopen old channels?

[t-bast]

I already have 12 million entries in that table and the table is taking up 4.5 GB of space

You really are sending a ton of payments, even our node has less than 1GB in this table 😅

Is my only option to close and reopen old channels?

Currently yes, that's the only way to guarantee that a revoked commitment cannot be published anymore and we can delete that old state. Once we add splicing, that will be a more convenient way of cleaning up that state: every splice will allow us to remove all the previous htlc_infos.

[DerEwige]

yeah ^^
I'm still working on my rebalancing algorithm, to increase the success chance.
So, I can get enough rebalancing payments through, with less payment attempts.
But if you try to do rebalancing at a competitive rate the failure rate is just so very high.

So fare I have optimized the algorithm to use less resources.
This allows me to do up to 10 rebalancing attempts per seconds in burst and sustainable 4 attempts per second over the period of a day.

Currently I do about 300’000 rebalancing tries each day for a few hundred successful payments.

Right now I try to hunt down a racing condition on banning/unbanning messages that lead to some channels being unbanned even though I want them to be on the permanent ban list.

Once I’ve solved that issue, the number of payments attempts per day should go down.

I hope you don't mind all my questions and that the insights from my rather extreme use case might help to optimize eclair even further

[t-bast]

To be completely honest, I believe that such rebalancing is useless, and even potentially harmful for overall payment reliability. Purely off-chain local rebalancing creates an unbalance somewhere else, which in turn will likely trigger another rebalancing that undoes the first one. It creates the illusion of traffic, but most likely (almost) none of that traffic is real payments.

A much saner way of rebalancing is to use PeerSwap, which doesn't have this "global equilibrium" issue.

[DerEwige]

Once PeerSwap arrives on eclair I will surely give it a try.

Regarding my rebalancing strategy.

The strategy is actually rather gently. One might not think of it that way, when you see the insane amount of tries.

The algorithm gets a maximum fee rate in PPM. But this maximum is only available to the algorithm when it tries to balance a 0.0 channel with a 1.0 channel. Plus, the algorithm does not try to rebalance all the way in one go. Rather it just tries to move it by say 0.01.

So the algo just slowly and gently tries to bush the channel into balance, and the closer a channel comes to balance the less the pressure from the algo.

This means that most channels don’t settle into balance. But find their equilibrium at something like 0.8 or 0.3. Depending if they lead to a sink or source.

My fees get changed every 5 minutes if needed (since last CLN version this does not lead to disabled channels anymore)
Fees don’t go down directly but in a Dutch auction style.

This way if it creates an unbalance on the other node, the more I try to balance the more the pressure from the other side.
This way each channel should settle around a point that is in both sides best interest.
Or at least that is the idea

But smaller payments with decently low fees tend to fail a lot.
like 99.99% of the time a lot. This then just leads to such high number of payment tries.