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

[Kusnetz, Jeremy]

We had SAKAI_PRESENCE as an in memory table, I just switched it back to
InnoDB.  I think in memory tables to full table locking, while innodb
will do row level locking.

 

But I've had SAKAI_PRESENCE as InnoDB before, and we have found that
during load testing (as during high load) that establishing new sessions
during high load really kill things.  If we stop adding users during
high load during load testing the load testing errors clear up with the
existing users, as soon as we start adding more users to the load errors
start piling up.

 

Does anyone else have experience with the memory storage engine vs
innodb for SAKAI_PRESENCE?  It mentions doing this in the admin guide.

 

I was just reading that by default the memory engine uses hash indexing
instead of btree, not sure if that makes a difference here.

 

Here are our indexes:

 

mysql> show index in SAKAI_PRESENCE;

+----------------+------------+-------------------------------+---------
-----+-------------+-----------+-------------+----------+--------+------
+------------+---------+

| Table          | Non_unique | Key_name                      |
Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed
| Null | Index_type | Comment |

+----------------+------------+-------------------------------+---------
-----+-------------+-----------+-------------+----------+--------+------
+------------+---------+

| SAKAI_PRESENCE |          1 | SAKAI_PRESENCE_SESSION_INDEX  |
1 | SESSION_ID  | A         |        6234 |     NULL | NULL   | YES  |
BTREE      |         |

| SAKAI_PRESENCE |          1 | SAKAI_PRESENCE_LOCATION_INDEX |
1 | LOCATION_ID | A         |        6234 |     NULL | NULL   | YES  |
BTREE      |         |

+----------------+------------+-------------------------------+---------
-----+-------------+-----------+-------------+----------+--------+------
+------------+---------+

2 rows in set (0.00 sec)

 

mysql> show index in SAKAI_SESSION;

+---------------+------------+----------------------------+-------------
-+----------------+-----------+-------------+----------+--------+------+
------------+---------+

| Table         | Non_unique | Key_name                   | Seq_in_index
| Column_name    | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |

+---------------+------------+----------------------------+-------------
-+----------------+-----------+-------------+----------+--------+------+
------------+---------+

| SAKAI_SESSION |          0 | SAKAI_SESSION_INDEX        |            1
| SESSION_ID     | A         |       49384 |     NULL | NULL   | YES  |
BTREE      |         |

| SAKAI_SESSION |          1 | SAKAI_SESSION_SERVER_INDEX |            1
| SESSION_SERVER | A         |          35 |     NULL | NULL   | YES  |
BTREE      |         |

| SAKAI_SESSION |          1 | SAKAI_SESSION_START_END_IE |            1
| SESSION_START  | A         |       16461 |     NULL | NULL   | YES  |
BTREE      |         |

| SAKAI_SESSION |          1 | SAKAI_SESSION_START_END_IE |            2
| SESSION_END    | A         |       49384 |     NULL | NULL   | YES  |
BTREE      |         |

| SAKAI_SESSION |          1 | SAKAI_SESSION_START_END_IE |            3
| SESSION_ID     | A         |       49384 |     NULL | NULL   | YES  |
BTREE      |         |

| SAKAI_SESSION |          1 | SESSION_ACTIVE_IE          |            1
| SESSION_ACTIVE | A         |           2 |     NULL | NULL   | YES  |
BTREE      |         |

+---------------+------------+----------------------------+-------------
-+----------------+-----------+-------------+----------+--------+------+
------------+---------+

6 rows in set (0.23 sec)

 

From: Earle Nietzel [mailto:earle.nietzel at gmail.com] 
Sent: Monday, October 10, 2011 9:41 AM
To: Kusnetz, Jeremy
Cc: sakai-dev
Subject: Re: [Building Sakai] Establishing new sessions are killing us
during high load

 

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