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

[Kusnetz, Jeremy]

We have a job that regularly prunes these, it actually runs multiple
times a day.  It moves out non active sessions and move the associated
events out to archive tables.  We are not doing this during peak load
times so that process wouldn't be causing contention during high load.

 

We are not regularly optimizing the tables, I guess we should.  But, we
just finished doing full mysqldumps and restores into all of our
databases last week, so they should be fairly optimized.  Well on second
thought maybe they aren't since none of the data in them would still be
there from when we did the dumps and restores.  That is a good thought.

 

From: Sam Ottenhoff [mailto:ottenhoff at longsight.com] 
Sent: Monday, October 10, 2011 9:48 AM
To: Kusnetz, Jeremy
Cc: sakai-dev
Subject: Re: [Building Sakai] Establishing new sessions are killing us
during high load

 

Jeremy, In addition to Earle's request for index checks, can you confirm
your tables are InnoDB? 

 

Are you pruning your SAKAI_SESSION and SAKAI_EVENT tables on a regular
basis?  Are you regularly optimizing these tables after large pruning
runs?

 

--Sam

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.SESSI
ON_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"

 

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/20111010/e50d727b/attachment.html