[Building Sakai] autocommit

[Steve Swinsburg]

Hi Jeremy,

Do you have any idea what transactions are running for so long? I agree, anything over a few seconds is too long.

cheers,
Steve


On 30/10/2012, at 6:00 AM, "Kusnetz, Jeremy" <JKusnetz at APUS.EDU> wrote:

> We are having Oracle look at our MySQL performance.  We had a pretty big database performance hit for a short time last night.   They noted the following about long running transactions that haven’t been commited.  I’ve read this thread: http://collab.sakaiproject.org/pipermail/sakai-dev/2009-July/002772.html  But has there been any change?  If things still stand as of 2.8 to where they were in this thread it looks like it’s possibly not cycling through our connection pools resetting that transaction.  We were thinking about trying to turn on autocommit on a node or two to see what happens, but now that I read this there looks like there may be issues.
>  
> On the MySQL end I thought about changing wait_timeout to something reasonable, right now ours is set to pretty much never timeout (1 year!).  But what kind of problem would it cause if it closes one of those transactions that was open for a long time but not commited.  Would it just get recycled?  What other things could it timeout and close that shouldn’t be?  I’m thinking this might be the right path to go.  What value would make sense?  I was thinking of starting conservatively, maybe 8-24 hours and go down from there.  As you will see, Oracle recommends 1 minute which I think is way too short.
>  
> Here is what I got from Oracle:
>  
> Having an old TRANSACTION can become a very serious problem. What he noted was that some of your transactions have been active for more than 64 hours without either issuing a COMMIT command or a ROLLBACK command. Except in extremely rare situations, I cannot imagine a need for any transaction to remain active for more than about half an hour. In fact, I prefer to design my code so that transactions start and complete in less than a few seconds. Having several transactions lasting multiple DAYS seems like horribly incorrect application design and can lead to horrible database performance.
>  
> I would make sense to me to have a wait_timeout set to 60 seconds or less. However, I did not write your applications so I cannot say what may be an appropriate value for wait_timeout. There are four things you need to check: 
> 
> 1) Did your application or your connection parameters incorrectly disable autocommit?
> 2) Does your connection pool have a timer for 'most time a connection can remain idle in the pool before being killed'
> 3) Are there execution paths in your application that result in a valid START TRANSACTION that would not be followed by a COMMIT or ROLLBACK before the connection is returned to the pool?
> 4) Does your application return connections to the pool by abandoning the connection object or does it do the appropriate .Close() method for your connection library?
> 
> What you see is almost always the result of either bad configuration choices or sloppy programming. Setting the wait_timeout timer to a very low value can force those connections to close but if the application is not prepared to repeat the contents of a valid transaction that may be force-terminated when the connection is killed by the timer, it may start giving you new problems. 
> 
> The simple thing to do is to first double-check that your application and connection parameters are not accidentally disabling the default autocommit behavior.
> This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
> 
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> 
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20121030/bfe3b30d/attachment.html