Pool size question

[Mike Orr]

I have a few Pylons applications that share a SQL access log routine.
Yesterday I migrated it from MySQL to PostgreSQL, and I'm getting a
bunch of errors like this:

>> connection = self.contextual_connect(close_with_result=True)
Module sqlalchemy.engine.base:1229 in contextual_connect
>> return self.Connection(self, self.pool.connect(), close_with_result=close_with_result, **kwargs)
Module sqlalchemy.pool:142 in connect
>> return _ConnectionFairy(self).checkout()
Module sqlalchemy.pool:304 in __init__
>> rec = self._connection_record = pool.get()
Module sqlalchemy.pool:161 in get
>> return self.do_get()
Module sqlalchemy.pool:631 in do_get
>> raise exc.TimeoutError("QueuePool limit of size %d overflow %d reached, connection timed out, timeout %d" % (self.size(), self.overflow(), self._timeout))
TimeoutError: QueuePool limit of size 5 overflow 10 reached,
connection timed out, timeout 30

I assume this means I should set ``create_engine(pool_size=,
max_overflow=)`` to some large number. But what would be a good
starting point if it's currently at the default? 25? 50? The sites
are like this:

Site #1: Highest traffic. Failing at 6 requests/minute according to
the error reports in my inbox. Using Postgres only for the stats, but
soon will be using it for site stuff too (which is working fine with
SQLite).

Site #2: Second-highest traffic. Using Postgres both for the stats and
the site. No problems seen yet.

Site #3: The most critical site, but moderate traffic. Using
PostgreSQL for both the stats and the site. One user complaint.

Site #4: Small site, little used. Using PostgreSQL only for stats.

Each site has a separate engine for its site stuff and for the stats
(so one or two engines per site, going to different databases).

For now I'm going to set 'max_overflow=15' and see if that fixes it.

--
Mike Orr <slugg...@gmail.com>

[Mike Orr]

I checked the requests per hour since yesterday afternoon.

Site #1 stopped working at 6pm and has no requests logged until I
restarted it. Still, I got six of these exceptions every minute this
morning. And I could connect to the db via psql fine. On what occasion
does SQLA raise this error? Is it opening a new connection? Does it
try once, or try multiple times before it times out? Should I set
'pool_recycle'?

Site #2 has between 590 - 1255 requests per hour in that time.

Site #3 has max 17 requests per hour. And I clarified that the user
may not have gotten an error on this site, I may have misunderstood
his report.

Site #4 has 0 requests per hour, which is typical.

--
Mike Orr <slugg...@gmail.com>

[Michael Bayer]

On Oct 28, 2011, at 12:53 PM, Mike Orr wrote:

> I have a few Pylons applications that share a SQL access log routine.
> Yesterday I migrated it from MySQL to PostgreSQL, and I'm getting a
> bunch of errors like this:
>
>>> connection = self.contextual_connect(close_with_result=True)
> Module sqlalchemy.engine.base:1229 in contextual_connect
>>> return self.Connection(self, self.pool.connect(), close_with_result=close_with_result, **kwargs)
> Module sqlalchemy.pool:142 in connect
>>> return _ConnectionFairy(self).checkout()
> Module sqlalchemy.pool:304 in __init__
>>> rec = self._connection_record = pool.get()
> Module sqlalchemy.pool:161 in get
>>> return self.do_get()
> Module sqlalchemy.pool:631 in do_get
>>> raise exc.TimeoutError("QueuePool limit of size %d overflow %d reached, connection timed out, timeout %d" % (self.size(), self.overflow(), self._timeout))
> TimeoutError: QueuePool limit of size 5 overflow 10 reached,
> connection timed out, timeout 30

As the problem appeared when moving from MySQL to PG, this almost certainly has nothing to do with pool size and everything to do with Postgres' much more aggressive locking behavior. My advice would be to do a "ps -ef | grep post" on your database server, and look for any lines that say "Idle in transaction". Those would be PG connections that are blocking on a lock. A system would normally have none of those present long enough to actually show up in a ps listing. Assuming that's the case you'd then need to fix your applications so that they release connections immediately when they've completed their work within a given transaction.