Grouper Users - Open Discussion List

[Shilen Patel <>]

It’s a general recommendation.  Here’s more information:

 

https://spaces.internet2.edu/pages/viewpage.action?pageId=14517958#APIBuilding&Configuration-AnalyzingTablestoImproveQueryPerformance

 

Basically, it’s good to regularly collect stats on your tables to improve performance (e.g. explain plans) especially after you have a lot of changes.  (Note that in MySQL optimize means something different than analyze.)

 

At Duke, we mostly run Oracle and for all of our tables in all of our databases (including Grouper), we automatically do this every night because it’s easy, relatively cheap, and so we don’t have to otherwise think about it.  For other databases, it may not be as cheap so that may influence how frequently you do this.  But especially if you have performance issues and notice that this helps, it would be good to automate it as often as possible.  Or at least keep it in mind to run manually when queries start slowing down.

 

I’m also interested in knowing what other folks with MySQL are doing.  We can update the wiki if appropriate.

 

Thanks!

 

- Shilen

 

On 2/13/18, 4:37 PM, " on behalf of Jeff McCullough" < on behalf of > wrote:

 

    Is that special to MySQL or recommended for any DB?

   

    > On Feb 13, 2018, at 1:35 PM, Wessel, Keith <> wrote:

    >

    > All,

    >

    > Last week at a TIER face-to-face, Shilen told us that we needed to be doing regular runs of table optimizations on our MySQL Grouper tables. This is easy enough to do, but when you try to do an optimization on innodb tables, MySQL informs you that optimize isn't supported and that it's using recreate + analyze.

    >

    > I'm pretty sure that innodb is the default storage engine in MySQL 5.7, and I assume that's why the tables Grouper created use that storage engine.

    >

    > Are others who are using MySQL using different storage engines for the Grouper tables that support a direct optimize? I suspect we could optimize faster if we were using a storage engine that didn't have to fall back on recreate + analyze.

    >

    > Keith

    >