[Deploying Sakai] database load in 2.9

[Matthew Jones]

I'm guessing this is Mysql, otherwise on Oracle you could have an AWR
report before and after that would more directly tell what was causing the
problem as you'd have your top query log before and after.

Generally the only thing I've usually done is run mysqlreport and look at
the slow query log if it shows any slow queries, both of these are
discussed here [1].

Also another great thing to do run mysqltuner [2] which will tell you if
any parameters on the server can be optimized (more memory/etc) or any
tables optimized. We were recently able to significantly reduce the
temporary tables created on disk and optimize a few tables (a few Lessons
tables seemed to be fragmented) by using some of these programs. It seems
odd that baseline would be up so much higher though and I also can't think
of anything obvious that would be causing it.

This query showed the fragmented tables.

*SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN
('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';*

I'd also check your cache statistics. Some of the caches are way too low
for production, specially the authz and the Site Cache. Improving those
even a few percent could save millions of database queries. If the cache is
full and they have a high count of NotFound you should increase it, if it's
Expired you might want to increase the TTL (since they should be getting
invalidated correctly across the cluster anyway). Even going from 10K-50K
(or 100K) on the authz is overall a small increase in the heap (which was
only like 20MB at 10K elements) but could be a huge performance increase.

[1]
http://www.askdbexperts.com/2011/08/simple-method-to-isolate-slow-mysql.html
[2] https://github.com/rackerhacker/MySQLTuner-perl

On Thu, Sep 13, 2012 at 7:35 PM, Sam Ottenhoff <ottenhoff at longsight.com>wrote:

> Are any other schools on 2.9.x like Rice willing to share info?  Have you
> enabled new features that are disabled by default like portal chat?  My
> assumption is that 2.9 like 2.8 (with optional features disabled) would
> represent continued performance improvement.
>
> The only major performance improvement that has been merged since Rutgers
> migrated to 2.9.x is KNL-934 (authz query improvements from VT as discussed
> at the conference TCC meeting).
>
> --Sam
>
>
> On Wed, Sep 12, 2012 at 8:02 AM, Hedrick Charles <hedrick at rutgers.edu>wrote:
>
>> We had a database performance problem the first week in Sept. Last year
>> our database server ran 10 - 20% load, now and then up to 30%. This year it
>> was doubt that. 60% average indicates we don't have enough to handle peaks,
>> and in fact user performance was bad.
>>
>> This wasn't such a big deal, because I had a new server ready. I just
>> accelerated moving it into production. It's possible that something about
>> our load has changed, but it could also indicate a greater database load
>> for 2.9.
>>
>> _______________________________________________
>> production mailing list
>> production at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/production
>>
>> TO UNSUBSCRIBE: send email to
>> production-unsubscribe at collab.sakaiproject.org with a subject of
>> "unsubscribe"
>>
>
>
> _______________________________________________
> production mailing list
> production at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/production
>
> TO UNSUBSCRIBE: send email to
> production-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20120913/6683e1b6/attachment.html