> In your design, every time a user hits your service, a new connection, specific to that user, will have to be made. This will incur network traffic and the overhead of logging in to the DBMS.
If connecting to your DB is significantly increasing your page times, you've got seriously fast pages. Even back when I was working with a MySQL database regularly in 2010, connect + login was 5 ms at maximum (and I think it was much less, I just don't remember that far).
> If you're thinking about using something like SQLite, you will hit a hard wall when the OS isn't able to open any more file descriptors, as well.
You could always just only keep open the most recently used 1 Million databases. It's pretty easy to tune the FD limit, FreeBSD lets you set it to one FD per 16k of ram without modifying the kernel, but it's a challenge to be so memory and cpu efficient that that's a limit.
All that said, it really depends on the application. If this is consumer facing, and each account is an end user, one database per user is probably excessive overhead; one database (or sharded, if you've got the users) or one (flatish) file per user makes a lot more sense.
If it's business facing, than one database per account could make sense. You would have isolation between customers and could put big customers on dedicated boxes and let the customer drive upgrades and migrations etc. Just please please please consider that corporations merge and divide all the time, don't be like G Suite and not offer a way to merge and divide accounts to reflect their corporate ownership.
Ok, you and I can be friends. A lot of people are using 'lightweight' frameworks where hello world is 30 ms, and then they call slow services and run slow queries, etc.
If your target is 10 ms, then you probably should worry about db connection time.
>connect + login was 5 ms at maximum (and I think it was much less, I just don't remember that far)
is that with creating a new connection in the pool?
we work with microservices so we have a "db gateway" so any request to the DB goes through that and it routes to the correct db server for that tenent. our latency for an already "hot" connection is about 40-50 on average but i belive the lowest number i got (for query by primary key in a kinda small table) was no less than 20-30
and opening a new connection added a couple of 10's atleast to that number
Yeah, that's a totally new connection (no pool), time from memory (could be off).
On my home system with a database I happen to be running anyway, I see:
$ time mysql -u mythtv -h 192.168.0.12 mythconverg \
-e 'select * from credits where person = 147628 limit 1' > /dev/null
real 0m0.023s
Server is running a Celeron(R) CPU 1007U @ 1.50GHz, client is Celeron(R) 2955U @ 1.40GHz, networking is 1GBps. I don't have a super easy way to measure just the connect + login time, so this is connect + login + indexed query. The server is lightly loaded, and I warmed up the table, but it's also a laptop chip on a desktop oriented board with a lowend NIC.
> If connecting to your DB is significantly increasing your page times, you've got seriously fast pages. Even back when I was working with a MySQL database regularly in 2010, connect + login was 5 ms at maximum (and I think it was much less, I just don't remember that far).
One of the major ways I helped Mastodon was improving their connection pooling situation. If you haven't encountered connection size issues with your database count yourself lucky.
My experience was with Apache + mod_php, so there was no option to pool connections between workers, and you would set the Apache connection limits such that they summed up to less than the MySQL connection limits (unless you had a lot of traffic that didn't hit the database... then sizing would be tricky)
If connecting to your DB is significantly increasing your page times, you've got seriously fast pages. Even back when I was working with a MySQL database regularly in 2010, connect + login was 5 ms at maximum (and I think it was much less, I just don't remember that far).
> If you're thinking about using something like SQLite, you will hit a hard wall when the OS isn't able to open any more file descriptors, as well.
You could always just only keep open the most recently used 1 Million databases. It's pretty easy to tune the FD limit, FreeBSD lets you set it to one FD per 16k of ram without modifying the kernel, but it's a challenge to be so memory and cpu efficient that that's a limit.
All that said, it really depends on the application. If this is consumer facing, and each account is an end user, one database per user is probably excessive overhead; one database (or sharded, if you've got the users) or one (flatish) file per user makes a lot more sense.
If it's business facing, than one database per account could make sense. You would have isolation between customers and could put big customers on dedicated boxes and let the customer drive upgrades and migrations etc. Just please please please consider that corporations merge and divide all the time, don't be like G Suite and not offer a way to merge and divide accounts to reflect their corporate ownership.