[sqlalchemy] Re: How are connections managed?
Hello Again Michael, > This makes perfect sense to me, I've been looking at my application > log data this morning and can see the connections being created, > pooled and checked out exactly as I would expect which is a very good > sign. > > I can however see that my application can potentialy see a large > number of connections in use though, perhaps a maximum of around 30 at > any one time due to concurrent 'threads' in the FSM, I'll look into > changing the configuration options and creating a bigger pool, this > will no doubt help the situation. > > Cheers for your advice, > > Heston So, I've made some changes to my code which as I understand it should bump my connection pool right up, I've done this like so: # Create the engine to the database. # Add a custom overflow and larger pool size, this should help matters. engine = create_engine(connection_string, echo=False, pool_size=42, max_overflow=10) Now, how can I ensure these settings are applied properly? I've enabled logging on the sqlalchemy.pool namespace and get log output like this: 2008-10-20 10:35:46,173 pool.py 212 INFO Created new connection <_mysql.connection open to 'localhost' at 8713904> 2008-10-20 10:35:46,174 pool.py 212 INFO Connection <_mysql.connection open to 'localhost' at 8713904> checked out from pool 2008-10-20 10:35:46,225 pool.py 212 INFO Created new connection <_mysql.connection open to 'localhost' at 87825fc> 2008-10-20 10:35:46,226 pool.py 212 INFO Connection <_mysql.connection open to 'localhost' at 87825fc> checked out from pool 2008-10-20 10:35:46,294 pool.py 212 INFO Connection <_mysql.connection open to 'localhost' at 87825fc> being returned to pool 2008-10-20 10:35:46,327 pool.py 212 INFO Connection <_mysql.connection open to 'localhost' at 8713904> being returned to pool 2008-10-20 10:35:55,938 pool.py 212 INFO Connection <_mysql.connection open to 'localhost' at 87825fc> checked out from pool 2008-10-20 10:35:55,964 pool.py 212 INFO Connection <_mysql.connection open to 'localhost' at 8713904> checked out from pool 2008-10-20 10:35:56,002 pool.py 212 INFO Connection <_mysql.connection open to 'localhost' at 8713904> being returned to pool 2008-10-20 10:35:56,033 pool.py 212 INFO Connection <_mysql.connection open to 'localhost' at 87825fc> being returned to pool This is fine, however, I was hoping it would give me details on my pool size, remaining connections, if it was using the overflow connections etc. Have I gone about this in the correct manor? Many thanks, Heston --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How are connections managed?
Hi Michael, Thanks for your response on this. > the engine is using a connection pool which by default will keep five > connections opened persistently. It also has an "overflow" of 10 > additional connections which are opened on an as-needed basis and > closed after usage. At the point of 15 connections in use, the pool > throttles additional requests until a connection is available. This > is of course all entirely configurable and the docs explain how to do > this in detail. > > Each of your Session instances will procure a connection from the pool > when they are first used (such as, issuing a query). They then hold > on to that connection persistently, which is considered to be the > active transaction, until you rollback(), commit(), or close() the > session, or the session is garbage collected. The "persistent > transaction" behavior of session is known in the 0.4 series as > "transactional" and in the 0.5 series as "autocommit=False". If you > flip this flag, then the Session only pulls connections from the pool > for each individual statement execution and/or flush(), and returns it > immediately afterwards. This makes perfect sense to me, I've been looking at my application log data this morning and can see the connections being created, pooled and checked out exactly as I would expect which is a very good sign. I can however see that my application can potentialy see a large number of connections in use though, perhaps a maximum of around 30 at any one time due to concurrent 'threads' in the FSM, I'll look into changing the configuration options and creating a bigger pool, this will no doubt help the situation. Cheers for your advice, Heston --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How are connections managed?
On Oct 16, 9:39 am, "Heston James - Cold Beans" <[EMAIL PROTECTED]> wrote: > Afternoon Guys, > > I have a suspicion that I'm leaving MySQL database connections open when I > shouldn't be and I'm trying to understand how they are managed by > SQLAlchemy. > > I currently create an engine instance and bind my session maker too it like > this: > > # Create the engine to the database. > > engine = create_engine(connection_string, echo=False) > > # Connect the session. > > Session = sessionmaker(bind=engine) > > I then create sessions around my application by using: > > # Create a new session. > > session = Session() > > and once finished with it closing the session like this: > > # Close the session. > > session.close() > > When are new connections established to the database when using this method? > And when are they closed again? The only reason I ask is that I've seen a > few errors recently on high load instance of my application which struggle > to connect to the database, I'm also seeing a few table corruptions and I > think they're all related issues from me perhaps creating too many > connections. the engine is using a connection pool which by default will keep five connections opened persistently. It also has an "overflow" of 10 additional connections which are opened on an as-needed basis and closed after usage. At the point of 15 connections in use, the pool throttles additional requests until a connection is available. This is of course all entirely configurable and the docs explain how to do this in detail. Each of your Session instances will procure a connection from the pool when they are first used (such as, issuing a query). They then hold on to that connection persistently, which is considered to be the active transaction, until you rollback(), commit(), or close() the session, or the session is garbage collected. The "persistent transaction" behavior of session is known in the 0.4 series as "transactional" and in the 0.5 series as "autocommit=False". If you flip this flag, then the Session only pulls connections from the pool for each individual statement execution and/or flush(), and returns it immediately afterwards. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
