Introduce a generic RDBMS connection pool layer

[jamadden]

Currently, each ZODB Connection requires one or two RDBMS connections. The load connection is opened when the Connection is opened, and the store connection is opened the first time the ZODB Connection is used to write. Both of these remain open for the lifetime of the Connection.

For good performance (object caching), ZODB encourages ZODB connection pooling, but doesn't limit the number of total connections it will open (excess connections are closed entirely when returned to the pool). This means there's no limit on the number of RDBMS connections RelStorage could open when the system is under load. Large pools can be used to handle periods of high load, but that results in abnormally large numbers of RDBMS connections being required. Unless the pool is configured to time-out old connections (not the default), these connections never go away.

The ZODB Connection pool recycles connections based on how many non-ghost objects they have cached. If a site is mostly read-only (say 90%), but has some percentage of write activity (say 10%), it can happen that connections that write make their way to the front of the pool but concurrent read activity may push them back until another write happens. Over time, instead of the connection pool consisting of 90% with one RDBMS connection and 10% with 2, you wind up with 100% having two connections open.

Another observation is that much of the time for many transactions a ZODB Connection doesn't actually need a store connection; only the commit process at the very end does. (Ignoring when we need to allocate OIDs, which happens when someone uses Connection.add() explicitly and none are preallocated.)

I think all of this means that RelStorage could benefit from internally pooling its RDBMS connections. By divorcing the number of RDBMS connections from the number of ZODB connections, having larger, longer lived ZODB connection pools becomes more practical, and/or more worker processes.

Note that this doesn't change the total number of required connections in the worst case scenario (unless RelStorage's pool refuses to open new connections and blocks), but based on observations in a system under moderately heavy load (around 1,000 concurrent humans writing to multiple objects across multiple requests within a 5 minute period) in such steady-state scenarios it could substantially reduce the practical number required.

With the changes in RS3 that introduced IManagedDBConnection as a higher level object, this shouldn't be too difficult.

[PythonLinks]

This is a brilliantly written document. Please add it to the source code documentation.
People are pushing me to move the US Green Party presidential site Maps.Howie2020.tech to PostgreSQL, so I am trying to better understand RelStorage. WIth all due respect the architecture is quite inaccessible. Over time it is all just getting more and more complicated. For newbies, it is too much. And I have been using FileStorage for 20 years, read a lot of the source code, so I do get the basic concepts, but the Rel Storage architecture is beyond me.

So documents like this one are brilliant, they very clearly lay out how things work. I want to return to this document in the future, and read it a few more times. I want to refer to it every time I need to better understand the connection pool.

I encourage everyone to read the two ZODB articles Jason recently wrote.
https://dev.nextthought.com/blog/index.html

But I now understand they are very different from this document. Like many of the RelStorage talks I have seen, the articles focus on the performance metrics. Exactly what most people care about. But I care about the architecture. What is this growing beast I am dealing with? And articles like this one address that concern. So congratulations Jason. Thank you for all of the brilliant work you are doing. And please add this document to the source code.

Maybe I will even edit it there later!