[Building Sakai] Establishing new sessions are killing us during high load

[Earle Nietzel]

Hi Jeremy could you check what indexes you have?

I see the following

SAKAI_SESISON:
  UNIQUE KEY `SAKAI_SESSION_INDEX` (`SESSION_ID`),
  KEY `SAKAI_SESSION_SERVER_INDEX` (`SESSION_SERVER`),
  KEY `SAKAI_SESSION_START_END_IE`
(`SESSION_START`,`SESSION_END`,`SESSION_ID`),
  KEY `SESSION_ACTIVE_IE` (`SESSION_ACTIVE`)

SAKAI_PRESENCE:
  KEY `SAKAI_PRESENCE_SESSION_INDEX` (`SESSION_ID`),
  KEY `SAKAI_PRESENCE_LOCATION_INDEX` (`LOCATION_ID`)

Earle

On Sun, Oct 9, 2011 at 5:40 PM, Kusnetz, Jeremy <JKusnetz at apus.edu> wrote:

> It seems that establishing a new Sakai session is doing some ugly things to
> the database.  I’m seeing the SAKAI_PRESSENCE and SAKAI_SESSION tables
> getting locked.****
>
> ** **
>
> During peak times when we are getting hundreds of new sessions per minute
> this is bringing the database down to it’s knees.  I have to physically
> break users from being able to login, and then it takes a good 5 minutes for
> the database to catch up, we just see hundreds of running mysql processes.
> ****
>
> ** **
>
> Once logins are broken, users with established sessions are running just
> fine.   So we can handle users inside of Sakai just fine.****
>
> ** **
>
> We tried switching the SAKAI_PRESENCE table to a memory table, that didn’t
> really seem to help much.****
>
> ** **
>
> Just the real basics, we are using CLE 2.6.3 on top of MySQL 5.0.  This is
> a clustered environment.****
>
> ** **
>
> I can post more details, but just wanted to see if there are any ideas on a
> quick fix here.****
>
> ** **
>
> Here is an example of a locked query:****
>
> ** **
>
> select
> AX.SESSION_ID,AX.SESSION_SERVER,AX.SESSION_USER,AX.SESSION_IP,AX.SESSION_HOSTNAME,AX.SESSION_USER_AGENT,AX.SESSION_START,AX.SESSION_END,AX.SESSION_ACTIVE
> from SAKAI_SESSION AX inner join SAKAI_PRESENCE A ON AX.SESSION_ID =
> A.SESSION_ID where AX.SESSION_ACTIVE=1 and A.LOCATION_ID =
> x'3137343731392D70726573656E6365'****
> 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/20111010/2f56f4b6/attachment.html