[Building Sakai] deadlock when storing events

[David Horwitz]

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"
>