Kupo Database

[conraddit]

Hello,

The only options to store Kupo's database that I've found are: RAM (--in-memory) and disk (--workdir). Does (or will) Kupo support Postgres and/or MySQL to store the data?

The reason for the question is that I believe that queries could be a lot quicker if done against a proper database, rather than the SQLite file, because Postgres servers can be set up to auto scale, can be set up with redundancies, etc.

So far the solution we've come up with is to have many Kupo instances running in parallel, tied to a load balancer, which allows us to distribute the loads for queries but this is expensive in terms of resources and relatively hard to scale.

All insights are welcome.

Thank you all for what you do!

[KtorZ]

Does (or will) Kupo support Postgres and/or MySQL to store the data?

Likely not because it goes against the original design goals of Kupo of being a lightweight and easy-to-use tool. At the moment, it works as a single binary with no system dependencies whatsoever and I intend to keep it that way.

The reason for the question is that I believe that queries could be a lot quicker if done against a proper database

Ouch. I will not repeat that to SQLite I promise. But more seriously, I think the keyword in your sentence is "I believe". Do you have any data points? As far as I've seen from different users, Kupo is actually (an order of magnitude) faster than popular Postgres alternatives for a fraction of the resources. Kupo is quite heavily optimized, supports concurrent reads and write, and has decent performances even for large queries.
Hence I am pondering whether this is a gut feeling that you have, or whether you have actual benchmarks / data-points to back this. In which case I would be interested to dig in those since so far, I've seen Kupo be able to handle quite an important load by itself.

So far the solution we've come up with is to have many Kupo instances running in parallel, tied to a load balancer, which allows us to distribute the loads for queries but this is expensive in terms of resources and relatively hard to scale.

How is that more expensive than running an auto-scaling Postgres cluster? (again, genuine question, if you have actual numbers I would definitely be interested). From my experience, Kupo can run on quite low hardware and thus is relatively cheap to scale. Having said that, you're right that this is currently the "proper way" of scaling it.

Note that I have plan to include sharding backed-in in the future, but this is more of a way to deal with chain growth than to improve the current performances.

[conraddit]

It's definitely a gut feeling and apologies if I didn't express this properly. It's the first time I'm running Kupo so I don't have a lot of things to compare it to.

Maybe we're syncing too much data that we don't need to and thus the delays but, for example, if we sync from a specific data point, certain requests can take 2+ seconds.

I've also tried to DOS Kupo and a large instance (64 cores) can be DOS'd with under 100 concurrent requests mainly because, I believe, the requests are expensive from a CPU perspective. Maybe it's unrelated to SQLite.

I've tried running it on RAM and on disk. Both return roughly the same response times. I was expecting RAM to be considerably faster but both could be DOS'd with relative ease.

This is how we start it on disk and RAM:

kupo --node-socket /home/ubuntu/node/node.socket --node-config /home/ubuntu/node/mainnet-config.json --workdir /home/ubuntu/kupo01/ --host 0.0.0.0 --port 1442 --since 72316796.c58a24ba8203e7629422a24d9dc68ce2ed495420bf40d9dab124373655161a20 --match "*/*" --prune-utxo

kupo --node-socket /home/ubuntu/node/node.socket --node-config /home/ubuntu/node/mainnet-config.json --in-memory --host 0.0.0.0 --port 1451 --since 72316796.c58a24ba8203e7629422a24d9dc68ce2ed495420bf40d9dab124373655161a20 --match "*/*" --prune-utxo

--prune-utxo improved things significantly, lowering response times for certain requests from 15 seconds to 2.

This is an example query that can take 2 seconds for a response:

curl http://localhost:1442/matches/0be55d262b29f564998ff81efe21bdc0022621c12f15af08d0f2ddb1.6aa2153e1ae896a95539c9d62f76cedcdabdcdf144e564b8955f609d660cf6a2?unspent&created_after=82000000

I'm sure I'm doing something wrong.

Thank you for all you do Mr. KtorZ.

[KtorZ]

Hey! Thanks for the details. So there are a few things to keep in mind:

1. Kupo primary use-case is for "small" setups and it is therefore configured by default towards these use-cases. In particular, I am not too surprises that it does poorly when approaching 100 concurrent clients. Kupo maintains an internal pools of read-only connection to the database which is configured to 50 by default which plays well for up to around 50 concurrent clients. If you are running on beefier machine with more juice, you may want to raise that up a little (--max-concurrency). It is not unbounded by default and it is actually by design that the server will start replying 503 errors when reaching its capacity. It allows to apply back pressure to clients instead of just piling up incoming connections. Note however that the actions are heavily CPU-bounded as you've noticed.

2. Do not use --in-memory in production. Especially with large permissive patterns like */*. It actually usually performs worse than the file mode because SQLite is heavily optimized to leverage the OS kernel routines for paging and file access. The in-memory is more of a useful tool for testing and prototyping but it rapidly gets out of hand in real-life scenario -- with even worse performances.

3. You need to be careful and make sure that you have indexes installed before making substantial queries. Seeing the options you run Kupo with, it seems to be the case (you can double check with the logs which in principle informs you about that on startup). So don't expect good query performances when --defer-db-indexes is on.

4. I did also forget an index in the previous release 😬 ... This is fixed in v2.4.0 but prior to that, there was no index on the policies table which affected any query by asset id or policy id. Queries ended up doing a full scan of the table instead of leveraging the binary-search index. So if you're not running 2.4.0 already, please do and your performance concerns will probably go away!

[conraddit]

You rock, KtorZ. I'll follow your instructions, to check on improvements and will follow up. Thank you sir!

[conraddit]

You rock. I believe 90% of all our issues are sorted.

FYI the solution that we came up with a few weeks back, to scale Kupo, is to have multiple instances running, tied by a load balancer. With proper NVMe bandwidth, we can scale Kupo by adding more instances as needed. The max-concurrency setting is also helpful.

I do believe that there'll be demand for a scalable version of Kupo, as more and more projects decide to use it. But that's just my gut feeling, based on how we had to scale our infra using load balancers and multiple Kupo instances.

[KtorZ]

I am glad to hear that ! And also curious about what kind of traffic do you currently need to serve?

I am always on the fence regarding making Kupo more complicated for only a hypothetical gain in performance. One thing that is sure is that I wouldn't abandon SQLite but perhaps consider supporting multiple database backend. It should be fairly easy to achieve with the current architecture. But given the observed performance on dbsync for example, I am not yet convince that there will be much more benefits in using something like Postgres, or MySQL. It's really hard to tell whether that's worth the effort.