Hacker News new | ask | show | jobs
by codaphiliac 411 days ago
Wonder how many sqlite databases would be too many. At one point I assume not all databases can be kept opened at all time. what sort of overhead would there be serving a tenant not opened up yet? there has to be caches etc. not warmed up causing lots of disk IO
3 comments
toast0 411 days ago
At some level, it doesn't make a big difference if you've got a file open or not once the file's data falls out of the disk cache, you'll have the same kinds of latency to get it back. Sure, you'll avoid a round or two of latency pulling in the filesystem data to get to the file, but probably not a big deal on SSD.

Chances are, the popular databases stay in cache and are quick to open anyway; and the unpopular ones are rarely accessed so delay is ok. But you'd also be able to monitor for disk activity/latency on a system level and add more disks if you need more throughput; possibly disks attached to other machines, if you also need more cpu/ram to go with it. Should be relatively simple to partition the low use databases, because they're low use.

link
koakuma-chan 411 days ago
In this scenario you would use short-lived connections, and the overhead would probably be approximately the same as reading a file.
link
julik 411 days ago
That would be the FD limit, divided by 3 (the DB itself, the shm file and the WAL).
link
ncruces 411 days ago
But each SQLite connection (even to the same DB) will also consume 2 FDs for the DB and the WAL.

You'll more easily pool connections to the same DB, of course, but the difference might not be so stark.

link
julik 411 days ago
Something like that, yes. A tenant that hasn't been opened yet - well, you create the tenant first, and then proceed "as normal". With ActiveRecord, your `pool:` configuration defines how many database handles you want to keep open at the same time. I set it relatively high but it can be tweaked, I'd say. And there is automatic eviction from the pool, so if you have a few sites which are popular and a lot of sites which are not popular - it should balance out.

There could be merit to "open and close right after" though, for sure.

link