[Deploying Sakai] database load in 2.9
Hedrick Charles
hedrick at rutgers.edu
Fri Sep 14 08:08:11 PDT 2012
Thanks. We have one slow query. So that's no an issue for us. mysqltuner shows some possibilities though I'm not sure how serious they are.
I'm definitely going to increase some cache sizes and look at the kernel patch. That probably has the best likelihood of helping. On the otter hand, with our new database server, we don't actually have a performance issue. Others may not be in a position to move to a much faster system though.
On Sep 14, 2012, at 10:32:09 AM, Mike Jennings <mike_jennings at unc.edu> wrote:
> 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"
>>
More information about the production
mailing list