[Building Sakai] deadlock when storing events
Stephen Marquard
stephen.marquard at uct.ac.za
Fri Nov 5 06:52:30 PDT 2010
There is some evidence to show that if you have a large enough session
table to cause performance problems, the startup check can also create a
performance problem, in the form of a possible 5-10 minute delay on app
server startup.
I have argued before that the approach followed for this issue is
incorrect, so far to no avail.
But as to the deadlock issue, it's not clear why this is happening. We
haven't ever seen this locally with mysql, so it may be
Oracle-specific.
Regards
Stephen
--
Stephen Marquard, Learning Technologies Co-ordinator
Centre for Educational Technology, University of Cape Town
http://www.cet.uct.ac.za
Email / IM (Jabber/XMPP): stephen.marquard at uct.ac.za
Phone: +27-21-650-5037 Cell: +27-83-500-5290
>>> "Berg, Alan" <A.M.Berg at uva.nl> 11/5/2010 1:54 PM >>>
Hi,
Because the issue has occurred before there is a Sakai startup check
on the session table that should warn in the logs. I believe it was
added to trunk about 6 months ago. Not sure if it is in 2.7.1 or 2.8,
should be. Aaron Z wrote the code, Aaron is this in 2.8 alpha 3?
Interestingly we had the same issue for another LMS about 6 years ago
at UvA, so it is a common issue across systems.
Alan
Alan Berg
QA Director - The Sakai Foundation
Senior Developer / Quality Assurance
Group Education and Research Services
Central Computer Services
University of Amsterdam
http://home.uva.nl/a.m.berg
________________________________________
From: sakai-dev-bounces at collab.sakaiproject.org
[sakai-dev-bounces at collab.sakaiproject.org] on behalf of Steve
Swinsburg [steve.swinsburg at gmail.com]
Sent: 05 November 2010 12:34
To: David Horwitz
Cc: sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] deadlock when storing events
Interestingly, we've never cleaned out the event or session table and
don't have performance issues. And it's been running since January 2007.
I'd have to get some stats to tell you how many rows we have. MySQL db
too.
Cheers
Steve
Sent from my iPhone
On 05/11/2010, at 18:06, David Horwitz <david.horwitz at uct.ac.za>
wrote:
> Hi Joshua,
>
> Yes if you just let the event table grow you will hit issues. We
move
> old events and sessions to another db on another server so we have
the
> historical data but keep good performance in production.
>
> The perl script we use is here:
>
> http://source.cet.uct.ac.za/svn/sakai/scripts/trunk/archive_stats.pl
>
> Basically its safe to remove events and sessions where the session
is
> closed. You however do need the events service for Sakai to run
properly
> a number of low level services rely on it to run properly in
clusters.
>
> David
>
> On 11/05/2010 12:54 AM, Joshua Swink wrote:
>> We had a deadlock when writing to the event table today.
Fortunately
>> the system recovered, but many users had slow service and/or HTTP
500
>> errors for a few minutes.
>>
>> Any advice on preventing this would be appreciated. This table has
>> been troublesome for a while now. We're looking into dropping all
but
>> the most recent data, such as the last two years, or possibly not
>> using the event service at all. Is this an important service that
we'd
>> miss, or is it just there for forensics?
>>
>> Thanks for any and all help with this.
>>
>> --
>> Joshua Swink
>>
>> 2010-11-04 13:21:51,647 WARN
>>
org.sakaiproject.event.impl.ClusterEventTracking$$EnhancerByCGLIB$$207e2f09
>> org.sakaiproject.db.impl.BasicSqlService - Sql.dbWrite(): error
code:
>> 60 sql: insert into SAKAI_EVENT
>> (EVENT_ID,EVENT_DATE,EVENT,REF,SESSION_ID,EVENT_CODE,CONTEXT)
values
>> (SAKAI_EVENT_SEQ.NEXTVAL,?, ?, ?, ?, ?, ?) binds:
>> 20101104202143788 user.login 879ccbca-8d5d-4037-be7c-8aad0bda8f34
m
>> null
>> java.sql.SQLException: ORA-00060: deadlock detected while waiting
for resource
>>
>> at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
>> at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
>> at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
>> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
>> at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
>> at
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
>> at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
>> at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
>> at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
>> at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
>> at
org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1205)
>> at
org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1060)
>> at
org.sakaiproject.event.impl.ClusterEventTracking.writeBatchEvents(ClusterEventTracking.java:335)
>> at
org.sakaiproject.event.impl.ClusterEventTracking.run(ClusterEventTracking.java:490)
>> at java.lang.Thread.run(Thread.java:595)
>> 2010-11-04 13:21:51,657 WARN
>>
org.sakaiproject.event.impl.ClusterEventTracking$$EnhancerByCGLIB$$207e2f09
>> org.sakaiproject.event.impl.ClusterEventTracking -
>> org.sakaiproject.event.impl.ClusterEventTracking
>> $$EnhancerByCGLIB$$207e2f09 at 17d91382.writeBatchEvents:
>> java.lang.RuntimeException: SqlService.dbWrite failure
>> _______________________________________________
>> 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"
>>
> _______________________________________________
> 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"
_______________________________________________
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"
_______________________________________________
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"
###
UNIVERSITY OF CAPE TOWN
This e-mail is subject to the UCT ICT policies and e-mail disclaimer
published on our website at
http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from
+27 21 650 9111. This e-mail is intended only for the person(s) to whom
it is addressed. If the e-mail has reached you in error, please notify
the author. If you are not the intended recipient of the e-mail you may
not use, disclose, copy, redirect or print the content. If this e-mail
is not related to the business of UCT it is sent by the sender in the
sender's individual capacity.
###
More information about the sakai-dev
mailing list