[Deploying Sakai] database load in 2.9

[Matthew Jones]

Yea,it's a tough call because for a demo or very small instance of under
~200 users the cache settings are fine. You can probably run it wit a 1G
heap fine. This is how much we're giving to the QA and nightly servers.

However when you run in production for real, you'll probably have a
4G,6G,8G heap and be expecting to support ~2000 users per node. In that
case the cache settings are way too low because they're really tuned for
the 200 users. I *believe* ehcache pre-allocates the memory so it would
cause a problem if we made the settings increased by default. We'd either
have to figure out a way to have some type of setting that can use multiple
different defaults, or just document it much better?

On Fri, Sep 14, 2012 at 1:23 AM, Steve Swinsburg
<steve.swinsburg at gmail.com>wrote:

> 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/d17d73e1/attachment.html