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 aaaaa.bbb.cccc.ddddd.com > . > . > ----aaaaa.bbb.cccc.ddddd.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.
