[sqlalchemy] Possible problem with pool?
I'm getting an odd issue with connection recycling. Here's whats happening: 20080616 10:50:01,654 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf4ddc exceeded timeout; recycling 20080616 10:50:01,654 INFO [sqlalchemy.pool.QueuePool.0x..2c] Closing connection _mysql.connection open to 'localhost' at 8bf4ddc 20080616 10:50:01,655 INFO [sqlalchemy.pool.QueuePool.0x..2c] Created new connection _mysql.connection open to 'localhost' at 8bf9f2c 20080616 10:50:01,656 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c checked out from pool 20080616 10:50:02,249 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c being returned to pool 20080616 10:50:02,251 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c checked out from pool 20080616 10:50:02,907 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c being returned to pool 20080616 10:50:02,909 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c checked out from pool 20080616 10:50:02,913 INFO [sqlalchemy.pool.QueuePool.0x..ac] Connection _mysql.connection open to 'localhost' at 91261e4 exceeded timeout; recycling 20080616 10:50:02,913 INFO [sqlalchemy.pool.QueuePool.0x..ac] Closing connection _mysql.connection open to 'localhost' at 91261e4 20080616 10:50:02,914 INFO [sqlalchemy.pool.QueuePool.0x..ac] Created new connection _mysql.connection open to 'localhost' at a3841bc 20080616 10:50:02,915 INFO [sqlalchemy.pool.QueuePool.0x..ac] Connection _mysql.connection open to 'localhost' at a3841bc checked out from pool 20080616 10:50:03,100 INFO [sqlalchemy.pool.QueuePool.0x..2c] Invalidate connection _mysql.connection open to 'localhost' at 9c06b0c (reason: OperationalError:(2006, 'MyS QL server has gone away')) 20080616 10:50:03,101 INFO [sqlalchemy.pool.QueuePool.0x..ac] Invalidate connection _mysql.connection open to 'localhost' at 9c137a4 (reason: OperationalError:(2006, 'MyS QL server has gone away')) 20080616 10:50:03,101 INFO [sqlalchemy.pool.QueuePool.0x..2c] Closing connection _mysql.connection open to 'localhost' at 9c06b0c 20080616 10:50:03,101 INFO [sqlalchemy.pool.QueuePool.0x..ac] Closing connection _mysql.connection open to 'localhost' at 9c137a4 20080616 10:50:03,102 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection None being returned to pool 20080616 10:50:03,102 INFO [sqlalchemy.pool.QueuePool.0x..ac] Connection None being returned to pool 20080616 10:50:03,103 INFO [sqlalchemy.pool.QueuePool.0x..2c] Pool disposed. Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0 20080616 10:50:03,104 INFO [sqlalchemy.pool.QueuePool.0x..ac] Pool disposed. Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0 20080616 10:50:03,104 INFO [sqlalchemy.pool.QueuePool.0x..2c] Pool recreating 20080616 10:50:03,105 INFO [sqlalchemy.pool.QueuePool.0x..ac] Pool recreating This is after the app has been left for several hours (the recycle time is 3600) and happens every time. It looks like it realises that connection 8bf4ddc is old and recycles it and checks out the new connection OK. However, the second time it does this (with 91261e4) it recycles it and checks it out. But then it uses some random other connection that doesn't seem to have been checked out 9c06b0c. Even more strangely it uses another connection 9c137a4, again not checked out. Both of these are old and fail with mysql server has gone away. After this I keep getting errors like: Can't reconnect until invalid transaction is rolled back I'm about to delve into the pool code to try and figure it out, anyone have any ideas what might be causing this problem? --~--~-~--~~~---~--~~ 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: Possible problem with pool?
My bad, turns out to be a threading issue :) On Jun 16, 11:04 am, Geoff [EMAIL PROTECTED] wrote: I'm getting an odd issue with connection recycling. Here's whats happening: 20080616 10:50:01,654 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf4ddc exceeded timeout; recycling 20080616 10:50:01,654 INFO [sqlalchemy.pool.QueuePool.0x..2c] Closing connection _mysql.connection open to 'localhost' at 8bf4ddc 20080616 10:50:01,655 INFO [sqlalchemy.pool.QueuePool.0x..2c] Created new connection _mysql.connection open to 'localhost' at 8bf9f2c 20080616 10:50:01,656 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c checked out from pool 20080616 10:50:02,249 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c being returned to pool 20080616 10:50:02,251 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c checked out from pool 20080616 10:50:02,907 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c being returned to pool 20080616 10:50:02,909 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection _mysql.connection open to 'localhost' at 8bf9f2c checked out from pool 20080616 10:50:02,913 INFO [sqlalchemy.pool.QueuePool.0x..ac] Connection _mysql.connection open to 'localhost' at 91261e4 exceeded timeout; recycling 20080616 10:50:02,913 INFO [sqlalchemy.pool.QueuePool.0x..ac] Closing connection _mysql.connection open to 'localhost' at 91261e4 20080616 10:50:02,914 INFO [sqlalchemy.pool.QueuePool.0x..ac] Created new connection _mysql.connection open to 'localhost' at a3841bc 20080616 10:50:02,915 INFO [sqlalchemy.pool.QueuePool.0x..ac] Connection _mysql.connection open to 'localhost' at a3841bc checked out from pool 20080616 10:50:03,100 INFO [sqlalchemy.pool.QueuePool.0x..2c] Invalidate connection _mysql.connection open to 'localhost' at 9c06b0c (reason: OperationalError:(2006, 'MyS QL server has gone away')) 20080616 10:50:03,101 INFO [sqlalchemy.pool.QueuePool.0x..ac] Invalidate connection _mysql.connection open to 'localhost' at 9c137a4 (reason: OperationalError:(2006, 'MyS QL server has gone away')) 20080616 10:50:03,101 INFO [sqlalchemy.pool.QueuePool.0x..2c] Closing connection _mysql.connection open to 'localhost' at 9c06b0c 20080616 10:50:03,101 INFO [sqlalchemy.pool.QueuePool.0x..ac] Closing connection _mysql.connection open to 'localhost' at 9c137a4 20080616 10:50:03,102 INFO [sqlalchemy.pool.QueuePool.0x..2c] Connection None being returned to pool 20080616 10:50:03,102 INFO [sqlalchemy.pool.QueuePool.0x..ac] Connection None being returned to pool 20080616 10:50:03,103 INFO [sqlalchemy.pool.QueuePool.0x..2c] Pool disposed. Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0 20080616 10:50:03,104 INFO [sqlalchemy.pool.QueuePool.0x..ac] Pool disposed. Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0 20080616 10:50:03,104 INFO [sqlalchemy.pool.QueuePool.0x..2c] Pool recreating 20080616 10:50:03,105 INFO [sqlalchemy.pool.QueuePool.0x..ac] Pool recreating This is after the app has been left for several hours (the recycle time is 3600) and happens every time. It looks like it realises that connection 8bf4ddc is old and recycles it and checks out the new connection OK. However, the second time it does this (with 91261e4) it recycles it and checks it out. But then it uses some random other connection that doesn't seem to have been checked out 9c06b0c. Even more strangely it uses another connection 9c137a4, again not checked out. Both of these are old and fail with mysql server has gone away. After this I keep getting errors like: Can't reconnect until invalid transaction is rolled back I'm about to delve into the pool code to try and figure it out, anyone have any ideas what might be causing this problem? --~--~-~--~~~---~--~~ 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] inverted in_
Hi!
How can I invert the in_ operator? I need a select like:
... name NOT IN ('alice', 'bob')...
...filter(not User.name.in_('alice', 'bob'))... is not working.
Thanks for advices.
David
--~--~-~--~~~---~--~~
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: inverted in_
filter(sqlalchemy.not_(User.name.in_('alice', 'bob')))
How can I invert the in_ operator? I need a select like:
... name NOT IN ('alice', 'bob')...
...filter(not User.name.in_('alice', 'bob'))... is not working.
Hope that helps,
E
signature.asc
Description: OpenPGP digital signature
[sqlalchemy] no server side cursors in SA 0.4.6?
Using Postgres 8.3.1
Consider the following script. In SA 0.4.3 it works as intended. In SA
0.4.6, it does not. In particular, the time to get the resultset in 0.4.3
is sub-second. The time in 0.4.6 is about 20 seconds. Also, when running
on 0.4.3 the memory consumption of the script is constant under 10MB. When
running on 0.4.6, it grows to hundreds of MB and is dependent on the size of
the result set. Seems to me that 0.4.3 used a cursor like it was configured
to and 0.4.6 ignored the server_side_cursors=True parameter to the
create_engine call. How do I make 0.4.6 use server side cursors?
#!/usr/bin/python
from time import time
from sqlalchemy import text
from sqlalchemy import create_engine
stime = time()
engine = create_engine('postgres://[EMAIL PROTECTED]/postgres',
server_side_cursors=True, encoding='utf-8')
conn = engine.connect()
trans = conn.begin()
print have engine, connection, transaction after about %.4f seconds %
(time() - stime)
stime = time()
rs = conn.execute(text(select * from generate_series(1,1000) s0,
generate_series(1,1) s1))
print have resultset after about %.4f seconds % (time() - stime)
count = 0
stime = time()
for r in rs:
count += 1
print counted %s rows after about %.4f seconds % (count, time() - stime)
stime = time()
rs.close()
print closed resultset after about %.4f seconds % (time() - stime)
stime = time()
trans.commit()
print commited after about %.4f seconds % (time() - stime)
stime = time()
conn.close()
print closed connection after about %.4f seconds % (time() - stime)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
