5 ms·

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

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

koakuma-chan 1y ago
In this scenario you would use short-lived connections, and the overhead would probably be approximately the same as reading a file.
julik 1y ago
That would be the FD limit, divided by 3 (the DB itself, the shm file and the WAL).
- ncruces 1y 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.
  - julik 1y 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.
toast0 1y 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.

koakuma-chan 1y ago

In this scenario you would use short-lived connections, and the overhead would probably be approximately the same as reading a file.

julik 1y ago

That would be the FD limit, divided by 3 (the DB itself, the shm file and the WAL).

ncruces 1y 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.
- julik 1y 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.

toast0 1y 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.