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 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).
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 20000 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.
