[Building Sakai] Establishing new sessions are killing us during high load
Sam Ottenhoff
ottenhoff at longsight.com
Mon Oct 10 07:15:05 PDT 2011
I don't have any experience using an in-memory table for SAKAI_PRESENCE, but
I think you can expect to see about a 50% improvement in DB performance by
disabling Sakai's presence:
display.users.present=false
Lots of pruning in a MySQL table usually requires optimization for the space
to be reclaimed. I would run it after every prune of the table.
--Sam
On Mon, Oct 10, 2011 at 10:08 AM, Kusnetz, Jeremy <JKusnetz at apus.edu> wrote:
> 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.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"****
>
> ** **
> 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/908d95c9/attachment.html
More information about the sakai-dev
mailing list