Investigation: I/O performance of Kupo

[Quantumplation]

I was talking with josef from @cexplorer, explaining how to spin up the required resources for auditing the Sundae governance votes.

He raised a very interesting point: short write cycles on a lower-end SSD or older NVME can be really damaging to the lifespan of the disk. Modern NVME suffers less from this, but that can be expensive.

So we got curious about how Kupo's IO patterns look, and whether there would be benefit in a small batching write cache.

sqlite3 likely has pretty sophisticated write batching internally, but since it also needs to meet high durability criteria, it likely doesn't wait very long before flushing this buffer.

I'm not an expert on this kind of thing, so I figured I'd open the discussion and see if there's anyone who would be willing to help measure and quantify this! :)

[KtorZ]

It could be interesting to increase the page_size to 16KB or even 64KB and see. The default is at 4KB, and a higher page size should reduce the number of I/O; Since most write queries are rather big (especially with permissive patterns like * or */*) this may even provide some performance benefits.

Do any of you have any concrete numbers regarding this?

[KtorZ]

@cexplorer would you be keen to provide some more insights? And perhaps test a experimental branch for me and see whether it improves the I/O operations? In particular, I am interested whether this is specific to the initial synchronization or rather to the database queries.

Note also that SQLite being an on-disk file-based database, there will always be a quite number of I/O -- SQLite being here an intelligent wrapper on top of the OS file primitives.

[cexplorer]

Hi, thanks in advance for the great work.

I'll say upfront, I don't have any performance issues (as we are using enterprise nvmes), but on the other hand I imagine we're unnecessarily i/o troubling the storage during the initial sync, especially if someone is using slower storage.

It occurred to me not to do inserts / commits immediately during the initial sync every block/tx, but to batch the inserts into the db in much larger chunks for example.

But I'm not an expert on this db at all, so more just an idea.

[KtorZ]

It occurred to me not to do inserts / commits immediately during the initial sync every block/tx, but to batch the inserts into the db in much larger chunks for example.

This is already what's happening though 😅 ... Kupo does quite massive batch inserts; sometimes inserting 25k rows in a single transaction. Internally it's using a producer/consumer pattern connected with a bounded queue so it takes a bit of time (few seconds) to "warm up". The producer (which is pulling data from the node) is usually faster than the consumer (the one writing into the database) so the queue becomes full rapidly and while the consumer writes stuff to the database, the queue is getting filled up concurrently by the producer. Thus during a long-range synchronization, Kupo is typically inserting many blocks at once -- though never more than 200 blocks at once and there are millions of blocks on the chain already. So that's still quite a few I/O.

I've done various experiments on that and it turns out that larger batches aren't necessarily faster to insert. There's a sweet spot, before which increasing the size of each write transactions starts harming performances.