[sqlalchemy] Re: expensive .rollback() in pool implementation?
Ordered by: internal time
List reduced from 178 to 20 due to restriction 20
ncalls tottime percall cumtime percall filename:lineno(function)
# 2150.3710.0020.3710.002 {method 'query' of
'_mysql.connection' objects}
* 2150.3060.0010.3060.001 {method 'rollback' of
'_mysql.connection' objects}
2150.0290.0000.0290.000 {method 'store_result' of
'_mysql.connection' objects}
2150.0280.0000.0480.000 sqlalchemy/engine/base.py:
1425(_init_metadata)
2150.0220.0000.0220.000 {method 'next_result' of
'_mysql.connection' objects}
2320.0200.0000.2460.001 utils.py:278(new_fun)
2320.0170.0001.3050.006 RecentActions.py:
197(GetCounter)
2150.0130.0000.4980.002 MySQLdb/cursors.py:
129(execute)
2150.0120.0000.0500.000 MySQLdb/cursors.py:
107(_do_get_result)
2110.0110.0000.0370.000 decimal.py:516(__new__)
2150.0090.0000.0360.000 sqlalchemy/engine/
default.py:136(__init__)
2320.0090.0001.3150.006 utils.py:178(protected)
2150.0080.0000.0090.000 MySQLdb/cursors.py:
40(__init__)
4300.0080.0000.0140.000 threading.py:93(acquire)
6410.0080.0000.0080.000 {built-in method match}
2110.0060.0000.0140.000 decimal.py:
3092(_string2exact)
* 2150.0060.0000.3460.002 sqlalchemy/pool.py:
276(_finalize_fairy)
2150.0060.0000.6130.003 sqlalchemy/engine/base.py:
853(_execute_text)
6620.0060.0000.0060.000 logging/__init__.py:
1158(getEffectiveLevel)
This is cumulative log for 215 requests. '#' - query to DB, '*' -
returning connection to the pool.
Times for query() and rollback() execution are comparable.
Query is SELECT person_id, type, sum(count) FROM CommentCounter WHERE
person = %d AND type = '%s' and answered in (0, 1) group by person_id
On May 4, 7:52 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On May 4, 2008, at 9:41 AM, Andrew Stromnov wrote:
Recently I'd switched to SA MySQL connection pool implementation.
Every time, when app returns connection to pool (through .close()
method), SA triggers .rollback() on this connection (http://
www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/pool)
.
In my case ROLLBACK is rather expensive operation and waste too much
MySQL time. I'm using MySQL 5.1 and set autocommit=1 on
initialization.
How to disable this .rollback() triggering?
its necessary so that any transactional state existing on the
connection is discarded. autocommit=1 is not part of DBAPI so SQLA
is not built around that modelbut even if it is switched on, it
says nothing about table or row locks which may exist on the
connection which also would need to be released via ROLLBACK.
do you have any profiling data that illustrate ROLLBACK being
expensive ? its generally an extremely cheap operation particularly
if little or no state has been built up on the connection.
--~--~-~--~~~---~--~~
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: expensive .rollback() in pool implementation?
thanks for the effort. Please use r4640 and specify
rollback_returned=False to the Pool constructor.
On May 5, 2008, at 5:53 AM, Andrew Stromnov wrote:
Ordered by: internal time
List reduced from 178 to 20 due to restriction 20
ncalls tottime percall cumtime percall filename:lineno(function)
# 2150.3710.0020.3710.002 {method 'query' of
'_mysql.connection' objects}
* 2150.3060.0010.3060.001 {method 'rollback' of
'_mysql.connection' objects}
2150.0290.0000.0290.000 {method 'store_result' of
'_mysql.connection' objects}
2150.0280.0000.0480.000 sqlalchemy/engine/base.py:
1425(_init_metadata)
2150.0220.0000.0220.000 {method 'next_result' of
'_mysql.connection' objects}
2320.0200.0000.2460.001 utils.py:278(new_fun)
2320.0170.0001.3050.006 RecentActions.py:
197(GetCounter)
2150.0130.0000.4980.002 MySQLdb/cursors.py:
129(execute)
2150.0120.0000.0500.000 MySQLdb/cursors.py:
107(_do_get_result)
2110.0110.0000.0370.000 decimal.py:516(__new__)
2150.0090.0000.0360.000 sqlalchemy/engine/
default.py:136(__init__)
2320.0090.0001.3150.006 utils.py:178(protected)
2150.0080.0000.0090.000 MySQLdb/cursors.py:
40(__init__)
4300.0080.0000.0140.000 threading.py:93(acquire)
6410.0080.0000.0080.000 {built-in method match}
2110.0060.0000.0140.000 decimal.py:
3092(_string2exact)
* 2150.0060.0000.3460.002 sqlalchemy/pool.py:
276(_finalize_fairy)
2150.0060.0000.6130.003 sqlalchemy/engine/base.py:
853(_execute_text)
6620.0060.0000.0060.000 logging/__init__.py:
1158(getEffectiveLevel)
This is cumulative log for 215 requests. '#' - query to DB, '*' -
returning connection to the pool.
Times for query() and rollback() execution are comparable.
Query is SELECT person_id, type, sum(count) FROM CommentCounter WHERE
person = %d AND type = '%s' and answered in (0, 1) group by person_id
On May 4, 7:52 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On May 4, 2008, at 9:41 AM, Andrew Stromnov wrote:
Recently I'd switched to SA MySQL connection pool implementation.
Every time, when app returns connection to pool (through .close()
method), SA triggers .rollback() on this connection (http://
www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/pool)
.
In my case ROLLBACK is rather expensive operation and waste too much
MySQL time. I'm using MySQL 5.1 and set autocommit=1 on
initialization.
How to disable this .rollback() triggering?
its necessary so that any transactional state existing on the
connection is discarded. autocommit=1 is not part of DBAPI so
SQLA
is not built around that modelbut even if it is switched on, it
says nothing about table or row locks which may exist on the
connection which also would need to be released via ROLLBACK.
do you have any profiling data that illustrate ROLLBACK being
expensive ? its generally an extremely cheap operation particularly
if little or no state has been built up on the connection.
--~--~-~--~~~---~--~~
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: expensive .rollback() in pool implementation?
Michael Bayer wrote: thanks for the effort. Please use r4640 and specify rollback_returned=False to the Pool constructor. I changed that name in r4643 to 'reset_on_return'. I'm hoping to be able to support database-specific methods for state reset in 0.5, and the new option name is a little more future proof in that regard. --~--~-~--~~~---~--~~ 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: expensive .rollback() in pool implementation?
On May 4, 2008, at 9:41 AM, Andrew Stromnov wrote: Recently I'd switched to SA MySQL connection pool implementation. Every time, when app returns connection to pool (through .close() method), SA triggers .rollback() on this connection (http:// www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/pool.py#L291) . In my case ROLLBACK is rather expensive operation and waste too much MySQL time. I'm using MySQL 5.1 and set autocommit=1 on initialization. How to disable this .rollback() triggering? its necessary so that any transactional state existing on the connection is discarded. autocommit=1 is not part of DBAPI so SQLA is not built around that modelbut even if it is switched on, it says nothing about table or row locks which may exist on the connection which also would need to be released via ROLLBACK. do you have any profiling data that illustrate ROLLBACK being expensive ? its generally an extremely cheap operation particularly if little or no state has been built up on the connection. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
