[Deploying Sakai] database load in 2.9

[Mike Jennings]

Where can I find more information about tuning the ehCache info.  I know 
that UNC is running the defaults and I would love to see how this could 
improve our performance.

In MySQL we were finding a lot of queries in the slow query log that 
were getting there because MySQL was choosing a very bad execution path. 
  Actually what was happening is that the execution path would be good 
one minute, then flop to bad, then to good, but would usually stick on a 
bad one.

To fix this issue we implemented these settings (actually yesterday) 
that seems to fix this from happening.

We set the  innodb_stats_on_metadata  to be Off so that Statistics would 
not be overly run on the database and also changed the 
innodb_stats_sample_pages from the default of 8 to 32.  The 
innodb_stats_sample_pages controls the number of random dives that MySQL 
will when trying to figure out the correct execution path.  When you 
have really large tables it is better to have more dives to help find 
the best execution path possible.

After we did this and did a Analyze on all tables in the database, the 
execution path issue has been resolved and is sticking on the correct one.

Mike



==============================================================================
Mike Jennings
Teaching and Learning Developer
University of North Carolina at Chapel Hill

Office: (919) 843-5013
Cell: (919) 698-3746
E-mail: mike_jennings at unc.edu

On 9/14/2012 10:08 AM, Hedrick Charles wrote:
> There are no changes in any of this from 2.8.1 to 2.9. I did increase
> some cache sizes, but it sounds like not nearly enough. I'll do that and
> look at KNL-934.
>
> On Sep 13, 2012, at 8:59:11 PM, Matthew Jones <matthew at longsight.com
> <mailto: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 <mailto: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 <mailto: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
>>         <mailto:production at collab.sakaiproject.org>
>>         http://collab.sakaiproject.org/mailman/listinfo/production
>>
>>         TO UNSUBSCRIBE: send email to
>>         production-unsubscribe at collab.sakaiproject.org
>>         <mailto:production-unsubscribe at collab.sakaiproject.org> with a
>>         subject of "unsubscribe"
>>
>>
>>
>>     _______________________________________________
>>     production mailing list
>>     production at collab.sakaiproject.org
>>     <mailto:production at collab.sakaiproject.org>
>>     http://collab.sakaiproject.org/mailman/listinfo/production
>>
>>     TO UNSUBSCRIBE: send email to
>>     production-unsubscribe at collab.sakaiproject.org
>>     <mailto: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"
>