[Building Sakai] autocommit

[Kusnetz, Jeremy]

I'm not sure how to tell.  I asked oracle and they didn't really answer
directly other than saying 1000's of queries could have been run in the
transaction.  Any idea how to tell what they were?

 

From: Steve Swinsburg [mailto:steve.swinsburg at gmail.com] 
Sent: Monday, October 29, 2012 7:21 PM
To: Kusnetz, Jeremy
Cc: sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] autocommit

 

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
<http://collab.sakaiproject.org/pipermail/sakai-dev/2009-July/002772.htm
l>   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
<mailto:sakai-dev at collab.sakaiproject.org> 
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
<http://collab.sakaiproject.org/mailman/listinfo/sakai-dev> 

TO UNSUBSCRIBE: send email to
sakai-dev-unsubscribe at collab.sakaiproject.org
<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org>  with a subject
of "unsubscribe"

 

This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20121029/c6985f7d/attachment.html