[Building Sakai] critical performance problem: realm operations hanging

[Hedrick Charles]

We're seeing long pauses in production, in 2.8.0 (slightly pre-release). It turns out the queries involving realms are hanging while updates occur, and they're taking like 30 sec. The transactions that seem to be causing the trouble are

                131393 Query    INSERT INTO SAKAI_REALM_RL_GR (REALM_KEY, USER_ID, ROLE_KEY, ACTIVE, PROVIDED) VALUES ( (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = x'2F736974652F39393435383763652D636466322D343361362D393635622D313462393264643564623163'), x'65303562303238322D393832342D346566362D393166632D366639663930313034366336',  (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = x'53747564656E74'), x'31', x'30')
                131393 Query    update SAKAI_REALM set PROVIDER_ID = x'323031313A393A30313A3037303A3233303A30313A',MAINTAIN_ROLE = (select MAX(ROLE_KEY) from SAKAI_REALM_ROLE where ROLE_NAME = x'496E7374727563746F72'),CREATEDBY = x'66646231303237612D386438332D343734642D383032372D663131633432633234626339',MODIFIEDBY = x'66646231303237612D386438332D343734642D383032372D663131633432633234626339',CREATEDON = '2011-08-06 00:50:58',MODIFIEDON = '2011-10-10 11:08:38' where ( REALM_ID = x'2F736974652F39393435383763652D636466322D343361362D393635622D313462393264643564623163' )
                131389 Query    update SAKAI_REALM set PROVIDER_ID = x'323031313A393A30313A3037303A3233303A30313A',MAINTAIN_ROLE = (select MAX(ROLE_KEY) from SAKAI_REALM_ROLE where ROLE_NAME = x'496E7374727563746F72'),CREATEDBY = x'66646231303237612D386438332D343734642D383032372D663131633432633234626339',MODIFIEDBY = x'66646231303237612D386438332D343734642D383032372D663131633432633234626339',CREATEDON = '2011-08-06 00:50:58',MODIFIEDON = '2011-10-10 11:08:45' where ( REALM_ID = x'2F736974652F39393435383763652D636466322D343361362D393635622D313462393264643564623163' )


This is with Mysql 5.1. I don't see any obvious problem.