Re: [sqlalchemy] Howto identify connection which has not been returned back to the pool?
On Mar 17, 2011, at 10:15 PM, Jaimy Azle wrote: Dear All, Having a logging functionality to trace creation, checkout, and checkin connection from pool is very helpfull. however is there a way to identify connection which has not been returned back to the pool for a period of time? I suspect I had a stale connection object which does not returned back to the pool in my code but i cannot sure, I just get the database server run out of bufferpool stating i have too many uncommitted rowset after running for few days. there's a lot of detail missing there, what is the exact error message, what DB is this (sounds like SQL server). The best way is to use monitoring on the database to identify connections and/or transactions that have been opened for a long time. There are system views and such which provide this information. a minute of google provided this for SQL server: select * from sysprocesses where open_tran 0 Might want to try that. I don't understand, i did commit/rollback consistently for every, mostly probably, data manipulation routine in my code. And at final execution i did recheck any active transaction left and do rollback before returning the session back to the pool. Lastly, the pool has also been configured to recycle connection after 2 minutes to ensure no pending transaction left. However, i might be missed here. sqlalchemy.pool.QueuePool.0x...0x2:Connection PyConnection object at 0xa7d ... exceeded timeout; recycling sqlalchemy.pool.QueuePool.0x...0x2:Closing connection PyConnection object at 0xa7d ... sqlalchemy.pool.QueuePool.0x...0x2:Created new connection PyConnection object at 0xc70 ... sqlalchemy.pool.QueuePool.0x...0x2:Connection PyConnection object at 0xc70 ... checked out from pool sqlalchemy.pool.QueuePool.0x...0x2:Connection PyConnection object at 0xc70 ... being returned to pool -- Salam, -Jaimy Azle -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] understanding connection pooling
Hello, I wanted to make sure, I get this right: every session creates its own connection on demand (only if I actually do query with the session), right? So, using MySQL with max_connections = 100 I quickly run out of connections, because every client is using about 6 connections, one for each dialog window, which has its own session instance. Now I am playing with poolclass = StaticPool instead of QueuePool, which seems to give me the the same speed per query. Or is there any difference to expect, which I just haven't encountered? Also, is there something about StaticPool which prohibits me to do certain things a QueuePool could (except the reconnection note in the docs)? I guess having multiple threads with their own session querying would not work?! Thank you, Sebastian -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] understanding connection pooling
On Mar 18, 2011, at 11:19 AM, Sebastian Elsner wrote: Hello, I wanted to make sure, I get this right: every session creates its own connection on demand (only if I actually do query with the session), right? right So, using MySQL with max_connections = 100 I quickly run out of connections, because every client is using about 6 connections, one for each dialog window, which has its own session instance. seems like an architecture that could use some trimming Now I am playing with poolclass = StaticPool instead of QueuePool, which seems to give me the the same speed per query. Or is there any difference to expect, which I just haven't encountered? Only the enormous difference that MySQL connections aren't threadsafe, as well as the fact that each Session runs its own transaction, a pattern that cannot be shared by multiple sessions on one connection. Though MySQL in MyISAM mode renders the latter point to be moot. Also, is there something about StaticPool which prohibits me to do certain things a QueuePool could (except the reconnection note in the docs)? I guess having multiple threads with their own session querying would not work?! The timeout thing is pretty key with MySQL since their client lib times out a connection unused for 8 hours (such as, overnight). QueuePool has a threadlocal flag that returns the same connection in the same thread (on create_engine() its via use_threadlocal=True, and it appears that flag didn't make it into the docs) that may be able to help here. Thank you, Sebastian -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] understanding connection pooling
So, using MySQL with max_connections = 100 I quickly run out of connections, because every client is using about 6 connections, one for each dialog window, which has its own session instance. seems like an architecture that could use some trimming Yes, you are probably right. But what if I have two dialogs open, change data in both and click apply on the first (so it commits the changes). If both had the same session instance, which was passed from the main window, the second dialogs changes would also be committed, although the user might have wanted to discard them/rollback . Is there an alternate way of doing this? -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: ResourceClosedError with large Text columns
The test case fails. Filed as a MySQLdb bug at
https://sourceforge.net/tracker/?func=detailaid=3223245group_id=22307atid=374932
Cheers,
Martin
On Mar 17, 6:18 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Mar 17, 2011, at 9:11 PM, Martin wrote:
I have a table with a Text column, which i manually set up as a
MEDIUMTEXT type in MySQL (5.0), since MySQL's 2**16 character limit is
not enough for my application. Now when I store large chunks of text
in that column, and try to get it back, SQLAlchemy raises an
ResourceClosedError, saying that my result object does not return any
rows, example:
res=engine.execute('select id from mytable where length(text)100')
res.fetchall()
[('1a9008a84520dc6ec5e4d6607174291d6b10efa3',),
('9781c913a78e90587af24706cb96bdbbc5e71a30',)]
res = engine.execute('select * from mytable where length(text)
100')
res.fetchall()
It means the MySQLdb cursor object does not have a .description attribute,
indicating that its not a row-returning construct. That would appear to be
the wrong answer from MySQLdb.
Construct a MySQLdb test case for this one to see if this is an error on
their end:
import MySQLdb
connection = MySQLdb.connect(user='', passwd='', host='', dbname='')
cursor = connection.cursor()
cursor.execute('select * from table where length(text) 100')
assert cursor.description
print cursor.fetchall()
Traceback (most recent call last):
File stdin, line 1, in module
File /foobar/lib/python2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/
sqlalchemy/engine/base.py, line 2498, in fetchall
l = self.process_rows(self._fetchall_impl())
File /foobar/lib/python2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/
sqlalchemy/engine/base.py, line 2467, in _fetchall_impl
self._non_result()
File /foobar/lib/python2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/
sqlalchemy/engine/base.py, line 2472, in _non_result
This result object does not return rows.
sqlalchemy.exc.ResourceClosedError: This result object does not return
rows. It has been closed automatically.
I googled for the exception type and description, but nothing useful
came up. Does anyone have an idea what's going wrong here?
Cheers,
Martin
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] understanding connection pooling
On Mar 18, 2011, at 12:25 PM, Sebastian Elsner wrote: So, using MySQL with max_connections = 100 I quickly run out of connections, because every client is using about 6 connections, one for each dialog window, which has its own session instance. seems like an architecture that could use some trimming Yes, you are probably right. But what if I have two dialogs open, change data in both and click apply on the first (so it commits the changes). If both had the same session instance, which was passed from the main window, the second dialogs changes would also be committed, although the user might have wanted to discard them/rollback . Is there an alternate way of doing this? Right I was telling folks at Pycon that dialog windows should probably not represent session state directly, there would be detached state associated. The state would then be merged back into a single session using merge(). If you'd like to raise on a conflict, versioning can be used - this was fixed in 0.7 so that when a stale version is passed in via merge(), an exception is thrown. In the GUI app, some local-in-memory versioning scheme can be used between dialog windows, possibly via timestamps. Other things to consider: 1. multiple, mutating, non-modal dialogs is a bit unusual (never seen that before) 2. the MVC paradigm, which applies exactly to a graphical app, implies that a change in one dialog would send an event to all other windows. The conflict between dialog A and B would be immediate via this system, as the submission of A would send the event to B, B sees pending changes and alerts. This state, as well as that a text field was changed, should be maintained by the application through the association of state with each graphical window and each graphical control. Repurposing the ORM's session object to handle this is a very limiting approach, as the session is intended to maintain an interaction with a live database connection only - the intricacies of user interactions aren't really covered by its state management. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: trouble with metaclass
Well,
(I am not a exactly a beginner in Python, just a bit rusty i
guess :-) ) I am working on a way to persist multiple inheritance and
polymorphism in a database, but I am a beginner with SQLalchemy.
Anyway I have a new problem for which i would like some help:
The following code gives an error:
class tablemeta(DeclarativeMeta):
def __new__(cls, name):
temp = dict()
temp[__tablename__] = _ + name
temp[id] = Column(Integer, primary_key = True)
temp[text] = Column(String(120))
return DeclarativeMeta.__new__(cls, name, (Base,), temp)
def __init__(cls, name):
return DeclarativeMeta.__init__(cls, name, (Base,),{})
if __name__ == __main__:
engine = create_engine('sqlite:///tempDB.db', echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
table1 = tablemeta(table1)
row1 = table1(text = detextenzo)
row2 = table1(text = detextenzoennogeenbeetje)
session.add(row1)
session.add(row2)
session.commit()
list = session.query(table1).all() #== ERROR
for l in list:
print str(l)
File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py,
line 299, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such table:
_table1 u'INSERT INTO _table1 (text) VALUES (?)' ('detextenzo',)
Seems an odd error, any ideas?
On Mar 17, 6:20 pm, Chris Withers ch...@simplistix.co.uk wrote:
On 16/03/2011 21:01, farcat wrote:
I have an error i cant figure out (likely a beginners error):
Beginners should not be using metaclasses.
What's your use case here?
Chris
--
Simplistix - Content Management, Batch Processing Python Consulting
-http://www.simplistix.co.uk
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Howto identify connection which has not been returned back to the pool?
On Friday, March 18, 2011, 8:59:04 PM, Michael Bayer wrote: there's a lot of detail missing there, what is the exact error message, what DB is this (sounds like SQL server). The database used was IBM DB2 Express-C, i connect using a modified version of ibm_db_sa adapted for SQLAchemy 0.6.x running on Jython 2.5.2. The application itself is a middleware server serving approximately 200-250 active users every day. I believe there is nothing wrong with the database itself since previous version of the middleware, which was written in delphi, proved running without problem for almost one year deployment. I did sure this comes from my java code base, but it was quite difficult to trace which routine causing this problem since it requires few days to run before those problem arise. The best way is to use monitoring on the database to identify connections and/or transactions that have been opened for a long time. There are system views and such which provide this information. Yes, DB2 does have those feature. However, it does not help much since those number shown is biased because either actively used connection, stale connection object (if any), and iddle connection kept in the pool were also listed there. -- Salam, -Jaimy Azle “+1 for stating fact: Perl is dead. Please don't bring it back” – Matt Joiner “-1 for spreading FUD about perl. It's absolutely not dead.” – Daenyth “+1 + -1 = 0, then, is perl a zombie?” – joaquin -- http://stackoverflow.com/questions/3384385/python-3-2-gil-good-bad -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Session: close(), remove(), expire_all() and expunge_all()
Hi everybody, I'm new with SQLAlchemy and I'm trying to understand better the Session object. First of all, why the expire_all() exists? Shouldn't it be always better to expunge_all() instead? If it is expired, at the use of the instance another query would be issued to reattach it anyway, so why keep useless instances in the identity map (maybe i'm wrong about this fact, i dont know exactly how the identity map works). Second, when we should call the close() method? I dont think if i get it at all. Let's say that I have the following DAO: PersonDAO: def insert(self, person): session = Session() session.add(person) session.commit() // why the commit starts a new transaction? should'n it only start again the next database access? session.close() // is it necessary? Is the a Session instance directly associated to a Connection in the pool? With the default pool size of 5, after 5 call of the insert method, the connection_overflow would be used? What's the time to a Session expire? And just to finish. Why do we use remove() for the scoped_session instead of the close()? Shouldn't it be semantically identical only overrided for the contextual stuff? -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Howto identify connection which has not been returned back to the pool?
On Mar 18, 2011, at 1:59 PM, Jaimy Azle wrote: Yes, DB2 does have those feature. However, it does not help much since those number shown is biased because either actively used connection, stale connection object (if any), and iddle connection kept in the pool were also listed there. DB2 may have a system that lists out individual connections and transactions as well as active statements.That would be a preferable system to just a number. Otherwise use a PoolListener and add every connection checked out to a global set, remove every returned connection from the set. Inspect the set to see what isn't getting returned. http://www.sqlalchemy.org/docs/core/interfaces.html#sqlalchemy.interfaces.PoolListener More aggressive: store the time checked out with each connection in the set. Store the current stack dump via traceback.print_stack() as well. Look for connections older than N seconds, look at the traceback. That is the exact line number where the offending connection was acquired. -- Salam, -Jaimy Azle “+1 for stating fact: Perl is dead. Please don't bring it back” – Matt Joiner “-1 for spreading FUD about perl. It's absolutely not dead.” – Daenyth “+1 + -1 = 0, then, is perl a zombie?” – joaquin -- http://stackoverflow.com/questions/3384385/python-3-2-gil-good-bad -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session: close(), remove(), expire_all() and expunge_all()
On Mar 18, 2011, at 2:55 PM, Israel Ben Guilherme Fonseca wrote: Hi everybody, I'm new with SQLAlchemy and I'm trying to understand better the Session object. First of all, why the expire_all() exists? The purpose is to remove any database-loaded state from all current objects so that the next access of any attribute, or any query execution, will retrieve new state, freshening those objects which are still referenced outside of the session with the most recent available state. It is called automatically upon commit() or rollback() assuming an autocommit=False session, so that when the transaction, and its isolated environment, come to an end, subsequent accesses of those objects will acquire new data from whatever other transactions were committed subsequent to the previous transaction. expire_all() itself is useful when: - the session is used in autocommit=True mode, and new changes from other transactions are desired. - against a database that does not support transactions, or perhaps within a weakly isolated transaction, again to load changes from other transactions or connections. - when SQL statements have been executed against the current transaction using execute() which may have changed significant portions of loaded state on the database. Shouldn't it be always better to expunge_all() instead? If it is expired, at the use of the instance another query would be issued to reattach it anyway, so why keep useless instances in the identity map (maybe i'm wrong about this fact, i dont know exactly how the identity map works). They're not useless at all if you are performing operations upon them which span the scope of multiple transactions, or have any of the above use cases, and don't wish to re-establish a full graph of objects in memory. In-memory objects are essentially proxy objects to an underlying database transaction. The Session mediates this relationship. Second, when we should call the close() method? when you wish to release the transactional and connection pool resources of the Session and remove all objects. I dont think if i get it at all. Let's say that I have the following DAO: PersonDAO: def insert(self, person): session = Session() session.add(person) session.commit() // why the commit starts a new transaction? should'n it only start again the next database access? the commit ends the current transaction, and starts a new transaction from the Session's point of view. However, no SQL or transactional directives are emitted until the first SQL statement is emitted via the Session (either via execute(), query() iteration, or flush). So there is no new database transaction if you commit then cease to use that Session further. session.close() // is it necessary? Not strictly although it removes any state left over in the session, thereby establishing any remaining objects as detached. This is desirable since you might want to ensure that subsequent operations on those objects don't re-emit new SQL. Is the a Session instance directly associated to a Connection in the pool? Yes. This is documented here: http://www.sqlalchemy.org/docs/orm/session.html#what-does-the-session-do With the default pool size of 5, after 5 call of the insert method, the connection_overflow would be used? No. A single session uses one Connection per Engine at a time. By default, it keeps one connection open until rollback(), commit(), or close() is called. http://www.sqlalchemy.org/docs/orm/session.html#managing-transactions illustrates this. What's the time to a Session expire? it expires things when commit or rollback is called.This is also in the above docs. And just to finish. Why do we use remove() for the scoped_session instead of the close()? scoped_session offers all of the methods of the underlying Session via a proxy pattern, so you can call close() on scoped_session, which calls close() on the actual session, or you can call remove(), which emits close() then removes the Session object itself from the registry. The latter has the advantage that any particular state established on the session, such as a Connection-based bind (see the example in http://www.sqlalchemy.org/docs/orm/session.html#joining-a-session-into-an-external-transaction ), or other particular constructor options, are discarded. Shouldn't it be semantically identical only overrided for the contextual stuff? this would be scoped_session.close(). The distinction is discussed to some degree at http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session . -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group
