3 ms·
If we are able to control/modify the protocol at both ends, not just in the middle, would this still be the optimal solution? I've been thinking for a while th
by derefr 7y ago
If we are able to control/modify the protocol at both ends, not just in the middle, would this still be the optimal solution?
I've been thinking for a while that connection-oriented DBMS protocols seem like a good candidate for being carried as flows over a single-socket multiplexed protocol, like HTTP/2-over-TCP is today. (I say "over TCP" because connections between app servers and databases are long-lived and stable, so there isn't really the same advantage in replacing TCP with something like QUIC there.)
Also, such a protocol, by removing the direct "connection = session" association, would remove the ability of DBMSes to key their session state by socket ID, and force them to refactor to more explicit session IDs. This would mean that there'd be an opportunity to introduce another intermediate abstraction layer during the refactoring, keeping "session" separate from "flow", such that the DBMS could have multiple concurrent MVCC transactions/worldstates as individual flows, all referencing the same ancestor MVCC worldstate (with the same config vars set, temporary objects created + populated, etc.) as an explicit stateful session that the flow would be "opened against." Which would be pretty awesome, and would also benefit non-socket-multiplexed flows (e.g. pgbouncer's per-transaction routing mode would no longer have non-SQL-conformant semantics.)
- atombender 7y agoHTTP2 could be great, but how would you implement this with Postgres, which uses one process per connection? You'd have to do all the multiplexing in a single server process, but you'd still have to have one process per underlying session, I think, and so you get the same problem with per-connection overhead that a pool tries to solve.
- derefr 7y agoOne of the advantages of a multiplexed protocol, is that it would reduce the need for each client to keep around a pool of N open+idle connections, just in case the client needs to abruptly make a low-latency query on a new session in parallel to its existing ones. Part of the reason abrupt scheduling of low-latency parallel queries requires clients to keep a pool of open+idle connections, is that the PG backend takes a moment to fork(2) out a process to serve your connection, and nothing about multiplexing eliminates that cost; but another part of the reason, is that it takes the TCP socket a moment to establish, and takes the TCP window even longer than a moment to scale. (And, intriguingly, open+idle separate-TCP-socket flows scale their TCP windows down while idle, so current connection-pooling strategies aren't even getting 100% of the possible benefit.) Decreasing the costs of flow establishment would decrease the number of open+idle flows clients keep around, which means the backend host would be free to do other things with its freed-up resources. In combination with the fact that sessions would be "grouped" to a connection, such that long-term metrics could be captured for the behavior of a client (= one connection) as a whole, you could design an alternative "elastic scheduler" for session backends, without needing to change the forking model. E.g. keep a number of prefork idle backend processes around, associated with each connection, where the size of the prefork pool varies dynamically per connection with the observed velocity of the client's opening of new sessions on that connection. (So, for a client that does everything under one session, you'd have zero observed session-open events per unit time, and thus have zero prefork processes waiting around for that client.)
- manigandham 7y agoYou should look at R2DBC then: https://r2dbc.io/ https://r2dbc.io/ "R2DBC (Reactive Relational Database Connectivity) is an endeavor to bring a reactive programming API to SQL databases." It already has client and driver implementations for several major DBs. Whether the databases themselves pick it up remains to be seen and will probably takes years.
- hinkley 7y agoI think there's a circular argument that connections are expensive, queries are supposed to cheap, so anything you can pay for once at connection creation time should be done then, which then guarantees that creating connections is expensive. How much of that work has to be done for each session sharing one connection? I'm a bit spoiled. The code I work on at work and the hobby project I'm designing share a common quality, in that they are both read-mostly. It is not going to be overly difficult for me to segregate read and write traffic and use separate connection pools - possibly separate servers. Just making that change is going to save me a considerable amount of overhead for resource management, including connections.