[Building Sakai] Establishing new sessions are killing us during high load
Steve Swinsburg
steve.swinsburg at gmail.com
Tue Oct 11 03:23:20 PDT 2011
At the risk of hijacking this thread, https://jira.sakaiproject.org/browse/STAT-299
cheers,
s
On 11/10/2011, at 7:28 PM, Jean-Francois Leveque wrote:
> 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