[Building Sakai] Establishing new sessions are killing us during high load
Kusnetz, Jeremy
JKusnetz at APUS.EDU
Mon Oct 10 08:52:28 PDT 2011
Currently according to innotob our query cache hit rate is around 9%.
We can certain look to update to those values.
We actually got Oracle/MySQL support last night during our load issues.
The major things we changed was setting table_cache=3000. We also
changed our innodb_log_file_size from 3 files at 256M to 2 files at
2047M. They also had us change the innodb_max_dirty_page_pct = 2 down
from the default 90. I'm not quite understanding that one yet.
They are still evaluating, I'll pass the query cache settings to them to
see what they think.
Our limits.conf has:
mysql soft nofile 4096
mysql hard nofile 65535
Jeremy Kusnetz | Sr. Systems Engineer
American Public University System
American Military University | American Public University
661 S George Street, Charles Town, WV 25414
T 304-885-5333 | M 703-967-5212 | jkusnetz at apus.edu| www.apus.edu
<http://www.apus.edu>
From: Earle Nietzel [mailto:earle.nietzel at gmail.com]
Sent: Monday, October 10, 2011 11:09 AM
To: sakai-dev
Cc: Kusnetz, Jeremy
Subject: Re: [Building Sakai] Establishing new sessions are killing us
during high load
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.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.
_______________________________________________
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/cea9247b/attachment.html
More information about the sakai-dev
mailing list