[Building Sakai] Establishing new sessions are killing us during high load
Kusnetz, Jeremy
JKusnetz at APUS.EDU
Mon Oct 10 15:25:22 PDT 2011
I think we can reduce how often we clean these tables out. We have
pretty high volume here, just for example our September EVENT archive
table is about 21 million rows, October's is already at 9 million rows.
SESSION archive is at 1.5 million for September and 725K for October.
Maybe I can clear out inactive sessions and archives once a night and do
an optimize table once pruned out instead of what we are doing now,
multiple times during the day (not during peaks) and no optimization.
From: Steve Swinsburg [mailto:steve.swinsburg at gmail.com]
Sent: Monday, October 10, 2011 5:55 PM
To: Kusnetz, Jeremy
Cc: sakai-dev
Subject: Re: [Building Sakai] Establishing new sessions are killing us
during high load
I think the pruning you are doing is excessive and may be causing the
issue. To compare, we have data in the SAKAI_SESSION and SAKAI_EVENT
tables going back to 2007, and we are running 2.6 without an issue in
this area[1].
Once we move to 2.8 we'll be cleaning it out, but not that regularly
(once a week perhaps).
[1] This historical data was required for the previous impl of server
wide stats in Site Stats, but isn't anymore.
On 11/10/2011, at 4:49 AM, Earle Nietzel wrote:
Your query cache is at 64M which is the maxim size I would recommend.
When query cache gets larger you may start to observe queries waiting
while data in the query cache is invalidated.
Problem is if you doing excessive pruning this also has a negative
impact as the query_cache is not efficient which can be seen with a 9%
hit rate. I would argue that this is a very inefficient cache. Cache'ing
can be very tricky and its about hitting the sweet spot anything to
either side of the sweet spot can have a negative impact.
We started at 32M and incremented it by 32M increments until I got to
192M and I still didn't see any negative cpu but our lowmem_prunes kept
getting lower while the cache hit rate went up.
Query Cache from our production system:
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 22471 |
| Qcache_free_memory | 59252856 |
| Qcache_hits | 339546433 |
| Qcache_inserts | 238156810 |
| Qcache_lowmem_prunes | 103635745 |
| Qcache_not_cached | 813706737 |
| Qcache_queries_in_cache | 59505 |
| Qcache_total_blocks | 142539 |
+-------------------------+-----------+
hit % = Qcache_hits / (Qcache_hits + Qcache_inserts)
59% = 0.58775234 = 339546433 / (339546433 + 238156810)
I would say 59% is not to shabby, but I can still see room for
improvement as the lowmem_prunes could be reduced though lowmem_prunes
can be tricky to reduce because some queries will be evicted due to
fragmentation.
Earle
On Mon, Oct 10, 2011 at 12:55 PM, Kusnetz, Jeremy <JKusnetz at apus.edu>
wrote:
Here is Oracle/MySQL comments on the query cache setting that you have
Earl.
Hi Jeremy,
My comments are inline.
On Oct 10, 2011, at 10:54 AM, Kusnetz, Jeremy wrote:
> Another institution running our application also suggested making the
following changes:
>
> query_cache_type = 1
This is active already (from global variables):
| query_cache_type | ON
|
> query_cache_size = 192M
Your query cache is at 64M which is the maxim size I would recommend.
When query cache gets larger you may start to observe queries waiting
while data in the query cache is invalidated.
> query_cache_wlock_invalidate = 1
This causes queries that create a write lock to a MyiSAM table to
invalidate any data in the query cache from that table and force clients
to wait for the write lock to be released. If the data has to be the
latest up to date information then yes, but it can hinder performance.
Best Regards,
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.
_______________________________________________
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/09194850/attachment.html
More information about the sakai-dev
mailing list