[sqlalchemy] Re: want to suppress automatic refresh
Michael,
On Mar 25, 3:00 pm, Michael Bayer mike...@zzzcomputing.com wrote:
SQLAlchemy also doesn't issue BEGIN. You might want to look at setting
autocommit to false on your MySQLdb connection, since that's the layer that
would be sending out BEGIN.
I looked into this. BTW, I use SQLAlchemy v 0.5 in a corporate
environment, and upgrading is expensive (time/effort).
My belief that 0.5 issued BEGIN statements was based on inspection of
the echo log i.e. create_engine(..., echo=True). I thought that the
echo log was a transparent trace of the exact statements sent to the
server. Upon further investigation, that seems not the case. The
BEGIN statements appear in the echo log, but they don't show up in the
mysqld server log. We can see why at line 984 of engine/base.py
[ http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py#L984
]
982 def _begin_impl(self):
983 if self._echo:
984 self.engine.logger.info(BEGIN)
985 try:
986 self.engine.dialect.do_begin(self.connection)
987 except Exception, e:
988 self._handle_dbapi_exception(e, None, None, None,
None)
989 raise
Therefore, I am no longer worried about the emission of BEGIN.
However, inspecting the mysqld general query log [
http://dev.mysql.com/doc/refman/5.0/en/query-log.html ], I noticed a
bunch of ROLLBACK statements.
8 Query INSERT INTO xxx (yy, zzz) VALUES (NULL, 'foobar')
8 Query commit
8 Query rollback
8 Query INSERT INTO jjj (yy, zzz, a, , ccc, dd,
ee, fff...
8 Query commit
8 Query rollback
8 Query UPDATE jjj SET a=1, =2, ccc=1, dd=now() WHERE
jjj.zzz = 193
8 Query INSERT INTO lll (dd, z, , ss, , w,
uuu) VALUES ('2010-03-15...
8 Query INSERT INTO (yy, nn) VALUES (NULL, 'barfoo')
8 Query commit
8 Query rollback
Every COMMIT is followed by a ROLLBACK, which appears wasteful. Which
software do I blame for that, SQLAlchemy or the MySQLdb DBAPI
connector?
Thanks,
Keith
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: want to suppress automatic refresh
keith cascio wrote: Every COMMIT is followed by a ROLLBACK, which appears wasteful. Which software do I blame for that, SQLAlchemy or the MySQLdb DBAPI connector? It's not wasteful at all in the usual case unless one wants to have leftover row/table locks and transactional state sitting idle in their connection pool, preventing other operations from proceeding. In the case of no statements executed since the previous COMMIT, its simple and 100% reliable to issue a simple ROLLBACK instead of attempting to gauge if any further statements *might* have been executed on the connection since the last known COMMIT. This is all within the usual realm of a ROLLBACK costing almost nothing. But since you're on MyISAM and have no transactional state, and somehow a ROLLBACK is incurring unacceptable overhead (curious, do you have any profiling data which illustrates how much time this takes ?), as I mentioned earlier this is configurable, you want to set reset_on_return to False in your Pool. you'll need to create the QueuePool manually and pass it to create_engine() using pool=QueuePool(...). Thanks, Keith -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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: want to suppress automatic refresh
Michael, I apologize if I came off at all rude. I noticed how helpful you are and I value your advice. Thank you for your patience. On Mar 26, 1:21 pm, Michael Bayer mike...@zzzcomputing.com wrote: It's not wasteful at all in the usual case unless one wants to have leftover row/table locks and transactional state sitting idle in their connection pool, preventing other operations from proceeding. In the case of no statements executed since the previous COMMIT, its simple and 100% reliable to issue a simple ROLLBACK instead of attempting to gauge if any further statements *might* have been executed on the connection since the last known COMMIT. This is all within the usual realm of a ROLLBACK costing almost nothing. Now I understand the reason for it. Good explanation. But since you're on MyISAM and have no transactional state, and somehow a ROLLBACK is incurring unacceptable overhead (curious, do you have any profiling data which illustrates how much time this takes ?) Here is a ping from the real production client against the real production server: $ /usr/sbin/ping -s a.bbb..d.com . . a.bbb..d.com PING Statistics 17 packets transmitted, 17 packets received, 0% packet loss round-trip (ms) min/avg/max/stddev = 112./120.2/127./5.34 120 milliseconds is 3/25ths of a second ( 1/9 of a second). I can't test on the production servers, but I tested my program between two servers with 56ms ping: 56ms avg ping, 14MiB input - reset_on_return=True: 1031.53 seconds (17.2 minutes) reset_on_return=False: 932.27 seconds (15.5 minutes) So, with 56ms ping, the ROLLBACK statements incur my program a 10% slowdown. I also tried it between two servers with 158ms ping (much smaller input). 158ms avg ping, 664KiB input - reset_on_return=True: 145.20 seconds (2.42 minutes) reset_on_return=False: 127.43 seconds (2.12 minutes) So, with 158ms ping, the ROLLBACK statements incur my program a 12% slowdown. We consider 10-12% *SIGNIFICANT*! And that slowdown is for a bunch of useless ROLLBACKs against a database with no transaction support whatsoever. They are not free. They cost. As I said before, touching the database is expensive. It doesn't matter if the traffic accomplishes nothing useful. as I mentioned earlier this is configurable, you want to set reset_on_return to False in your Pool. you'll need to create the QueuePool manually and pass it to create_engine() using pool=QueuePool(...). This worked. Now I don't see the ROLLBACK statements any more. This is extremely helpful. It's saving us about 10-12% elapsed time. Now if we could get rid of the COMMIT statements, we would likely save another 10%. Thanks, Keith -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: want to suppress automatic refresh
On Mar 26, 2010, at 7:36 PM, keith cascio wrote: Here is a ping from the real production client against the real production server: $ /usr/sbin/ping -s a.bbb..d.com . . a.bbb..d.com PING Statistics 17 packets transmitted, 17 packets received, 0% packet loss round-trip (ms) min/avg/max/stddev = 112./120.2/127./5.34 120 milliseconds is 3/25ths of a second ( 1/9 of a second). I can't test on the production servers, but I tested my program between two servers with 56ms ping: 56ms avg ping, 14MiB input - reset_on_return=True: 1031.53 seconds (17.2 minutes) reset_on_return=False: 932.27 seconds (15.5 minutes) So, with 56ms ping, the ROLLBACK statements incur my program a 10% slowdown. I also tried it between two servers with 158ms ping (much smaller input). how many sessions are you opening and closing ? theres exactly one connection pool return per session.commit(). two whole minutes of rollback() would imply you're checking in/out of the pool hundreds of thousands of times. My own tests show that MySQLdb can do 2 rollback calls per second on a local network connection, or 2.4 million in a couple of minutes. Using OurSQL, the time cuts in half. if you're on ORM, keep a single connection checked out and use flush() to send changes over. then you'll have no commit or rollback whatsoever sent over. 158ms avg ping, 664KiB input - reset_on_return=True: 145.20 seconds (2.42 minutes) reset_on_return=False: 127.43 seconds (2.12 minutes) So, with 158ms ping, the ROLLBACK statements incur my program a 12% slowdown. We consider 10-12% *SIGNIFICANT*! And that slowdown is for a bunch of useless ROLLBACKs against a database with no transaction support whatsoever. They are not free. They cost. As I said before, touching the database is expensive. It doesn't matter if the traffic accomplishes nothing useful. as I mentioned earlier this is configurable, you want to set reset_on_return to False in your Pool. you'll need to create the QueuePool manually and pass it to create_engine() using pool=QueuePool(...). This worked. Now I don't see the ROLLBACK statements any more. This is extremely helpful. It's saving us about 10-12% elapsed time. Now if we could get rid of the COMMIT statements, we would likely save another 10%. Thanks, Keith -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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: want to suppress automatic refresh
Michael On Mar 25, 12:33 pm, Michael Bayer mike...@zzzcomputing.com wrote: nothing ever refreshes automatically. only things that have been expired, or were never loaded in the first place, are loaded when requested. Good to know. to reduce expirations, readhttp://www.sqlalchemy.org/docs/session.html#committing. This is exactly what I was looking for, specifically To disable this behavior, configure sessionmaker() with expire_on_commit=False. However, now that I did, things are more complicated, and SQLAlchemy 0.5 complains. 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 BEGIN 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 UPDATE xxx SET yyy=now() WHERE xxx.z = %s AND xxx.a = %s 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 [193302, None] 2010-03-25 13:19:02,050 INFO sqlalchemy.engine.base.Engine.0x...f054 ROLLBACK Traceback (most recent call last): . . . File sqlalchemy/orm/mapper.py, line 1401, in _save_obj sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 I will begin to investigate how to avoid that error. I'd appreciate any advice or hints. Also, I would like to know how I prevent SQLAlchemy from issuing BEGIN and COMMIT statements to the database. This is a MySQL database with MyISAM tables that have no transaction support. This is an overloaded, remote database, and touching it is expensive. Also, if applicable, consider using fewer database-level defaults to populate columns on flush - these values are necessarily read in when you request to see them. Good to know, but this was not my specific problem. To increase what's loaded in a single query is a more elaborate topic which involves the specifics of the mappings you are using. Usually eager loading is the first place to start. If you're using joined table inheritance, read the mapper docs on configuring with polymorphic. Also not my specific problem. Thanks, Keith -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: want to suppress automatic refresh
2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 BEGIN 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 UPDATE xxx SET yyy=now() WHERE xxx.z = %s AND xxx.a = %s 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 [193302, None] 2010-03-25 13:19:02,050 INFO sqlalchemy.engine.base.Engine.0x...f054 ROLLBACK Traceback (most recent call last): . . . File sqlalchemy/orm/mapper.py, line 1401, in _save_obj sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: want to suppress automatic refresh
Michael On Mar 25, 1:27 pm, keith cascio keithautoma...@gmail.com wrote: However, now that I did, things are more complicated, and SQLAlchemy 0.5 complains. 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 BEGIN 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 UPDATE xxx SET yyy=now() WHERE xxx.z = %s AND xxx.a = %s 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 [193302, None] 2010-03-25 13:19:02,050 INFO sqlalchemy.engine.base.Engine.0x...f054 ROLLBACK Traceback (most recent call last): . . . File sqlalchemy/orm/mapper.py, line 1401, in _save_obj sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 I will begin to investigate how to avoid that error. I'd appreciate any advice or hints. Perhaps I found the right solution for my case. Whenever I construct a new mapped object o, and session.add(o), and session.commit(o), I also call session.expire(o). Now my program runs to completion without fatal errors AND produces correct output AND issues the minimal number of SELECT statements to the poor database. That is my goal. -- Keith -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: want to suppress automatic refresh
Michael On Mar 25, 1:50 pm, Michael Bayer mike...@zzzcomputing.com wrote: SQLA uses the DBAPI in its default mode of autocommit=False and is always going to issue flushes followed by a COMMIT or ROLLBACK. There's also a ROLLBACK which occurs automatically via the connection pool and you can turn that one off with a connection pool flag. But these operations are practically free with MyISAM so there's no overhead consideration. Thank you for the informative explanation. However, BEGIN/COMMIT/ ROLLBACK are absolutely not free in my case. I'm dealing with significant network latency because the database server is on another continent and also the server itself is beleaguered. It is very expensive to touch the database at all, even for what amounts to a no- op. I want to turn off the issuing of transaction statements. I am able to do so with other database abstraction layer software. Thank you for all your help, Keith -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: want to suppress automatic refresh
keith cascio wrote: Michael On Mar 25, 1:50 pm, Michael Bayer mike...@zzzcomputing.com wrote: SQLA uses the DBAPI in its default mode of autocommit=False and is always going to issue flushes followed by a COMMIT or ROLLBACK. There's also a ROLLBACK which occurs automatically via the connection pool and you can turn that one off with a connection pool flag. But these operations are practically free with MyISAM so there's no overhead consideration. Thank you for the informative explanation. However, BEGIN/COMMIT/ ROLLBACK are absolutely not free in my case. I'm dealing with significant network latency because the database server is on another continent and also the server itself is beleaguered. It is very expensive to touch the database at all, even for what amounts to a no- op. I want to turn off the issuing of transaction statements. I am able to do so with other database abstraction layer software. its not an option in SQLAlchemy unless you want to subclass/monkeypatch the MySQL dialect. Thank you for all your help, Keith -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] Re: want to suppress automatic refresh
keith cascio wrote: Michael On Mar 25, 1:50 pm, Michael Bayer mike...@zzzcomputing.com wrote: SQLA uses the DBAPI in its default mode of autocommit=False and is always going to issue flushes followed by a COMMIT or ROLLBACK. There's also a ROLLBACK which occurs automatically via the connection pool and you can turn that one off with a connection pool flag. But these operations are practically free with MyISAM so there's no overhead consideration. Thank you for the informative explanation. However, BEGIN/COMMIT/ ROLLBACK are absolutely not free in my case. I'm dealing with significant network latency because the database server is on another continent and also the server itself is beleaguered. It is very expensive to touch the database at all, even for what amounts to a no- op. I want to turn off the issuing of transaction statements. I am able to do so with other database abstraction layer software. SQLAlchemy also doesn't issue BEGIN.You might want to look at setting autocommit to false on your MySQLdb connection, since that's the layer that would be sending out BEGIN. Thank you for all your help, Keith -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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.
