On Oct 8, 2007, at 4:35 PM, Karl Pflästerer wrote:
>
> Hi,
> I try to use SA together with mod_python and its publisher algorithm.
> How can I limit the number of opened connections?
> I use Mysql as db, Apache2.2.6 prefork, mod_python and SA.
> There are only 8 Apache childs running, but a lot more connections
> open.
>
> I have the problem that connections are never closed, so after some
> time
> the mysql sever doesn't handle any more requests.
> A simple script like:
>
> engine = create_engine('mysql://[EMAIL PROTECTED]/pflaesterer?
> use_unicode=True&charset=utf8',
> encoding = 'utf8', convert_unicode=False,
> pool_recycle=1, pool_size=1, echo_pool=True,
> strategy='threadlocal')
> meta = MetaData()
> meta.bind = engine
>
> def index(req):
> conn = engine.connect()
> conn.close()
> return 'hier'
>
>
> called ten times gives me 10 opened connections.
If I run the app from a shell, using a loop and calling index() 10
times, only one connection is ever opened since its immediately
returned to the pool, and then reused for the next iteration. But
that's also because the program runs in under a second; since you
have "pool_recycle" set to only 1 second, its going to open a brand
new connection every second (and close the previous one). If i add
"time.sleep(1)" to my loop then this behavior becomes apparent (but
still, only one connection opened at a time).
Keep in mind theres also a "max_overflow" setting at work here as
well which is defaulted to 10, so the above engine is capable of
pooling 11 simultaneous connections, if you werent calling "close()"
and the connection was left opened. To narrow it down to exactly 1
in all cases, max_overflow would need be set to 0.
However, even if you set pool_size=1 and max_overflow=0 (and
pool_recycle to something sane like 3600), none of this will help
control the total number of connections when using mod_python with
Apache prefork. First off, the "threadlocal" setting is meaningless
since prefork does not use multiple threads per process. Secondly,
each new child apache process will represent a brand new Python
interpreter with its own engine and connection pool. So in this
case, to control the number of concurrent database connections
opened, you'd have to control the number of apache processes; if
apache opened 60 child processes, you'd have 60 connection pools.
Apache's default settings for total number of child processes are
also quite high for a dynamic scripting application; each child
process can take many megs of space and you can quickly run out of
physical memory unless you set this number to be pretty low (or you
have many gigs of physical memory).
There's no way to have a single in-python connection pool shared
amongst child processes since that's not prefork's execution model.
The "threaded" MPM would produce the threaded behavior you're looking
for but I think in most cases in order to have a single, threaded app
server most people use a technology like FastCGI or mod_proxy which
communicate with a separate, multithreaded Python process.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
