[Deploying Sakai] database load in 2.9

[Steve Swinsburg]

Should those cache settings be increased by default? I bet most people run the defaults of those. 

Cheers
Steve 

Sent from my iPhone

On 14/09/2012, at 10:59, Matthew Jones <matthew at longsight.com> wrote:

> 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"
> 
> _______________________________________________
> 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/20120914/7ec09ae4/attachment.html