[Building Sakai] Establishing new sessions are killing us during high load
Steve Swinsburg
steve.swinsburg at gmail.com
Mon Oct 10 14:54:58 PDT 2011
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
>
>
>
> 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.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"
>
>
>
>
>
> 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/20111011/b9386129/attachment.html
More information about the sakai-dev
mailing list