List:General Discussion« Previous MessageNext Message »
From:Morgan Tocker Date:March 17 2014 6:55pm
Subject:Re: SHOW CREATE TABLE suddenly slow on InnoDB?
View as plain text  
Hi Brad,

> That sounds right. Here's the process list (scrubbed) and the show engine
> innodb status. Notice that all of the SHOW CREATE TABLE aren't for hte same
> table, just got cleaned up that way.

It shouldn't matter if they are for the same or different - in 5.5 there is one table open
cache “instance” - so only one person can be opening or closing tables at a
time.

In 5.6 this is configurable to reduce contention:
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances

> https://gist.github.com/bradhe/c9f00eaf93ac588b8339
> 
> We have the defaults for table_definition_cache and table_open_cache (400
> each).

I am going to guess and say that you may have a sharded environment with a large number of
tables?

Another solution that may work, is to increase these caches.  In most cases it will work
fine, but MPB has also blogged about the exception where you can get negative scalability
(so many cache misses the cache can’t work effectively):
http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/

I’m not sure how up-to-date the edge case issue is.  But hopefully this gives you
some starting points.

(Others, feel free to chime in!)

- Morgan
Thread
SHOW CREATE TABLE suddenly slow on InnoDB?Brad Heller17 Mar 2014
  • Re: SHOW CREATE TABLE suddenly slow on InnoDB?Andrew Moore17 Mar 2014
    • Re: SHOW CREATE TABLE suddenly slow on InnoDB?Brad Heller17 Mar 2014
  • Re: SHOW CREATE TABLE suddenly slow on InnoDB?Morgan Tocker17 Mar 2014
    • Re: SHOW CREATE TABLE suddenly slow on InnoDB?Brad Heller17 Mar 2014
      • Re: SHOW CREATE TABLE suddenly slow on InnoDB?Morgan Tocker17 Mar 2014
        • Re: SHOW CREATE TABLE suddenly slow on InnoDB?Brad Heller17 Mar 2014
          • Re: SHOW CREATE TABLE suddenly slow on InnoDB?shawn l.green17 Mar 2014
          • Re: SHOW CREATE TABLE suddenly slow on InnoDB?Morgan Tocker17 Mar 2014