[bug]: listinvoices hangs with a large sqlite DB

[ZZiigguurraatt]

I have a large sqlite DB:

39bfbbb23c5a:/$ ll -h ~/.lnd/data/graph/regtest/channel.sqlite
-rw-r--r--    1 litd     litd       13.1G Apr 14 08:03 /home/litd/.lnd/data/graph/regtest/channel.sqlite
39bfbbb23c5a:/$ 

Here's my LND version:

39bfbbb23c5a:/$ lncli -v
lncli version 0.19.0-beta.rc1 commit=lightning-terminal-
39bfbbb23c5a:/$ 

I try to run time lncli listinvoices --max_invoices=300000 > /dev/null and it uses about 100% CPU but it uses minimal disk activity and RAM. I've let it run over over an hour and I get nothing back.

On the contrary, listpayments is much faster for the same query limit, responding in under 3 minutes:

39bfbbb23c5a:/$ time lncli listpayments --max_payments=300000 > /dev/null

real	2m42.307s
user	0m9.795s
sys	0m1.955s
39bfbbb23c5a:/$ 

Contrary to what is reported in #9709 for listpaymets, Control+C does stop the CPU usage on the node for listinvoices.

I have not tested this issue yet with postges.

[ZZiigguurraatt]

Testing with a much smaller query, you can see things are really slow:

39bfbbb23c5a:/$ time lncli listinvoices --max_invoices=3000 > /dev/null

real	14m38.046s
user	0m0.140s
sys	0m0.034s
39bfbbb23c5a:/$ 

[ziggie1984]

are you testing this with native invoices active ?

[ZZiigguurraatt]

Sorry, yes, forgot to include that I am running with --lnd.db.backend=sqlite --lnd.db.use-native-sql.

[bhandras]

Note that normal use case for listing invoices is to use pagination.

[ZZiigguurraatt]

Note that normal use case for listing invoices is to use pagination.

Right, but still very slow even when I limit.

Also, in #9709 I suggest that we have a default for listpayments to encourage it rather than assume the user is going to. I think the same should apply for listinvoices, but I think this is a separate issue.

[ZZiigguurraatt]

Testing with a much smaller query, you can see things are really slow:

39bfbbb23c5a:/$ time lncli listinvoices --max_invoices=3000 > /dev/null

real	14m38.046s
user	0m0.140s
sys	0m0.034s
39bfbbb23c5a:/$ 

I re-tested the above with postgres with what should be the same DB (it was created from the same original bbolt DB) and I'm getting a lot better performance:

86747ab2d56c:/$ time lncli listinvoices --max_invoices=3000 > /dev/null

real	0m1.391s
user	0m0.183s
sys	0m0.033s
86747ab2d56c:/$ 

I then try to run the original command that I was struggling with and I get

86747ab2d56c:/$ time lncli listinvoices --max_invoices=300000 > /dev/null

real	4m23.243s
user	0m14.413s
sys	0m2.896s
86747ab2d56c:/$ 

.

I also compare to the listpayments query and I get:

86747ab2d56c:/$ time lncli listpayments --max_payments=300000 > /dev/null

real	7m45.421s
user	0m11.311s
sys	0m2.235s
86747ab2d56c:/$ 

which leads me to believe that postgres is much slower than SQLite for KV schema but much faster for SQL schema (payments are currently still using the old KV schema whereas invoices have been converted to use the SQL schema).

Here's some info about my postgres DB size:

# du -hs pgdata/
33G	pgdata/
#

This is interesting because in lightninglabs/lndinit#21 (comment) I reported that the postgres DB size was 20G, so the conversion from KV to SQL schema (which was done for this test) must use more disk space.

[ZZiigguurraatt]

which leads me to believe that postgres is much slower than SQLite for KV schema

This is also demonstrated here too: #9729 (comment)