[Building Sakai] Establishing new sessions are killing us during high load
Earle Nietzel
earle.nietzel at gmail.com
Mon Oct 10 08:09:12 PDT 2011
Your indexes look good!
You can try turning off presence (as Sam just mentioned).
In the future with the addition of being able to change properties in a
running system I could see this being one of those options that you switch
off during higher load times but then beable to turn it back on once things
are good again.
Here are some mysql tips:
I would also be checking in mysql the query_cache parameters and make sure
that is working correctly, watch out for excessive pruning of the cache as
that will hurt (If this is the case make the cache larger). Here is what we
run with:
query_cache_type = 1
query_cache_size = 192M
query_cache_wlock_invalidate = 1
Make sure you've upped the table cache the default is way to low for Sakai.
table_cache=2048
You could start at 2048 also make sure you up the number of open files for
mysql process as well (in /etc/security/limits.conf)
mysql - nofile 16384
You can see some of our mysql db stats here for some hints:
http://jira.is.marist.edu/munin/iLearn/db08.ilearn.marist.edu/index.html#mysql2
In Marist's setup the most well endowed server is the database server
running in a VM with 8CPU's 24GB Ram (physical is 12CPU 144GB system).
A major player in Sakai's performance is the database and its queries.
If you have a table causing some problems try running analyze on it that
will sometimes help.
Good luck :)
Earle
On Mon, Oct 10, 2011 at 10:15 AM, Sam Ottenhoff <ottenhoff at longsight.com>wrote:
> 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/bdfd1089/attachment.html
More information about the sakai-dev
mailing list