On 2017-06-08 19:55, Ian Kelly wrote:
On Thu, Jun 8, 2017 at 10:47 AM, Israel Brewster
<isr...@ravnalaska.net> wrote:
On Jun 7, 2017, at 10:31 PM, dieter <die...@handshake.de> wrote:
israel <isr...@ravnalaska.net> writes:
On 2017-06-06 22:53, dieter wrote:
...
As such, using psycopg2's pool is essentially
worthless for me (plenty of use for it, i'm sure, just not for me/my
use case).
Could you not simply adjust the value for the "min" parameter?
If you want at least "n" open connections, then set "min" to "n".
Well, sure, if I didn't care about wasting resources (which, I guess
many people don't). I could set "n" to some magic number that would
always give "enough" connections, such that my application never has
to open additional connections, then adjust that number every few
months as usage changes. In fact, now that I know how the logic of the
pool works, that's exactly what I'm doing until I am confident that my
caching replacement is solid.
Of course, in order to avoid having to open/close a bunch of
connections during the times when it is most critical - that is, when
the server is under heavy load - I have to set that number arbitrarily
high. Furthermore, that means that much of the time many, if not most,
of those connections would be idle. Each connection uses a certain
amount of RAM on the server, not to mention using up limited
connection slots, so now I've got to think about if my server is sized
properly to be able to handle that load not just occasionally, but
constantly - when reducing server load by reducing the frequency of
connections being opened/closed was the goal in the first place. So
all I've done is trade dynamic load for static load - increasing
performance at the cost of resources, rather than more intelligently
using the available resources. All-in-all, not the best solution,
though it does work. Maybe if load was fairly constant it would make
more sense though. So like I said *my* use c
ase, whi
ch
is a number of web apps with varying loads, loads that also vary
from day-to-day and hour-to-hour.
On the other hand, a pool that caches connections using the logic I
laid out in my original post would avoid the issue. Under heavy load,
it could open additional connections as needed - a performance penalty
for the first few users over the min threshold, but only the first
few, rather than all the users over a certain threshold ("n"). Those
connections would then remain available for the duration of the load,
so it doesn't need to open/close numerous connections. Then, during
periods of lighter load, the unused connections can drop off, freeing
up server resources for other uses. A well-written pool could even do
something like see that the available connection pool is running low,
and open a few more connections in the background, thus completely
avoiding the connection overhead on requests while never having more
than a few "extra" connections at any given time. Even if you left of
the expiration logic, it would still be an improvement, because while
unused connections
wouldn't
d
rop, the "n" open connections could scale up dynamically until you
have "enough" connections, without having to figure out and hard-code
that "magic number" of open connections.
Why wouldn't I want something like that? It's not like its hard to
code - took me about an hour and a half to get to a working prototype
yesterday. Still need to write tests and add some polish, but it
works. Perhaps, though, the common thought is just "throw more
hardware at it and keep a lot of connections open at all time?" Maybe
I was raised to conservatively, or the company I work for is too
poor.... :-D
Psycopg is first and foremost a database adapter. To quote from the
psycopg2.pool module documentation, "This module offers a few pure
Python classes implementing *simple* connection pooling directly in
the client application" (emphasis added). The advertised list of
features at http://initd.org/psycopg/features/ doesn't even mention
connection pooling. In short, you're getting what you paid for.
It sounds like your needs are beyond what the psycopg2.pool module
provides.
Quite possible. Thus the reason I was looking for clarification on how
the module was intended to work - if it doesn't work in the way that I
want it to, I need to look elsewhere for a solution. My main reason for
posting this thread was that I was expecting it to work one way, but
testing showed it working another way, so I was trying to find out if
that was intentional or user error. Apparently it's intentional, so
there we go - in it's current form at least, my needs are beyond what
the psycopg2 pool provides. Fair enough.
I suggest looking into a dedicated connection pooler like
PgBouncer. You'll find that it's much more feature-rich and
configurable than psycopg2.pool. It's production-ready, unlike your
prototype. And since it's a proxy, it can take connections from
multiple client apps and tune the pool to your overall load rather
than on an app-by-app basis (and thus risk overloading the backend if
multiple apps unexpectedly peak together).
Very true, and I've looked into that (as well as the related, but more
basic, PgPool product), but it seems to me that any external proxy
product like these would defeat *my* purpose for using a pool in the
first place: avoiding the overhead of making/breaking many connections
quickly. That is, all you have really done is gone from connecting to
Postgres to connecting to PgBouncer. You are still making and breaking
just as many connections. Unless connecting to PgBouncer is
significantly cheaper than connecting to Postgres? This may well be the
case, but I haven't yet seen anything to support that. Haven't seen
anything to refute that either, however :)
Of course, there may be many other features provided by such tools that
would make them worthwhile, even for my use case. However, my primary
goal in using a pool was avoiding the connection overhead with each
request, so if a tool doesn't do that, then it isn't the right tool for
me :)
As for why psycopg2.pool is the way it is, maybe most users don't have
your situation of serving multiple apps with loads varying on
different cycles. Most are probably only serving a single app, or if
serving multiple apps then they likely have common user bases with
similar peak times. You can't dynamically adjust the amount of RAM in
your server, so saving resources like RAM at below-peak times only
matters if you're going to do something else with it. In the scenarios
I described there isn't much else to do with it, so I can understand
if saving RAM isn't a priority.
True, but you would still have to deal with the minconn "magic number",
unless you just adjusted it so high from the start that even if your
load/use grows over time you never have to mess with it. Even in the
single app use case, where you don't care about RAM usage (since there
is nothing else trying to use the RAM) in order to get maximum benefit
from a pool you'd have to keep an eye on your usage and make sure it
never (or rarely) exceeds whatever arbitrary value you have set for
minconn. Not a big deal, especially if you tune it high to begin with,
but it is one more thing.
Honestly, some of that is just personal issues. I have problems with
code that is inefficient by design (even if there is nothing to be
gained from efficiency), or that makes assumptions about things when it
could be dynamic. I have often coded in such a way that a given value
can be adjusted dynamically, even when the people giving me the specs
say it will never change. More than once that has enabled me to respond
to a "feature request" or change order by saying "it already does that".
On the other hand, I am probably a poster child for "premature
optimization", and often have to stop myself from optimizing code just
because I can, when in reality it is not worth my time. By the same
token, the idea of wasting resources - even when, as you state, there is
nothing else to do with them - just rubs me the wrong way. As such, I
readily acknowledge that some of my requests/statements stem from my own
personal desires, and not from any actual lack/need in the product.
--
https://mail.python.org/mailman/listinfo/python-list
