[Building Sakai] Establishing new sessions are killing us during high load
Jean-Francois Leveque
jean-francois.leveque at upmc.fr
Tue Oct 11 01:28:02 PDT 2011
Could you tell us which JIRA has the Site Stats server wide stats
improvement, Steve ?
J-F
On 10/10/2011 23:54, Steve Swinsburg wrote:
> 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
>> <mailto: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 <tel:304-885-5333> | M 703-967-5212
>> <tel:703-967-5212> |jkusnetz at apus.edu <mailto:jkusnetz at apus.edu>|
>> www.apus.edu <http://www.apus.edu/>____
>>
>> __ __
>>
>> *From:*Earle Nietzel [mailto:earle.nietzel at gmail.com
>> <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 <mailto: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 <mailto: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
>> <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
>> <mailto: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.____
More information about the sakai-dev
mailing list