[Building Sakai] Establishing new sessions are killing us during high load
Earle Nietzel
earle.nietzel at gmail.com
Mon Oct 10 10:49:35 PDT 2011
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.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20111010/18819da1/attachment.html
More information about the sakai-dev
mailing list