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

We actually only have about 60 tables in that database. I've tried
increasing the cache and open tables limits and get the same behavior.

mysql> select @@table_definition_cache, @@table_open_cache,
@@innodb_file_per_table, @@innodb_open_files;
+--------------------------+--------------------+-------------------------+---------------------+
| @@table_definition_cache | @@table_open_cache | @@innodb_file_per_table |
@@innodb_open_files |
+--------------------------+--------------------+-------------------------+---------------------+
|                     4096 |               3000 |                       1 |
                300 |
+--------------------------+--------------------+-------------------------+---------------------+
1 row in set (0.10 sec)

A few other tests I've tried:

1. Stand up a new machine, dump just the schema in to it, and run the test.
Performs flawlessly, so it's probably just this machine/snapshot.
2. Stand up a snapshot of my existing machine, truncate the tables,
optimize the truncated tables, and run the test. I get the bad behavior!

Correct me if I'm wrong but it'd appear that there's just something
fundamentally broken this machines' InnoDB ibdata file/data dictionary? All
the contention comes out of the dictionary, but I'd expect the optimize to
re-write the dictionary entries...



*Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514 |
Skype: brad.heller | @bradhe <http://www.twitter.com/bradhe> |
@cloudability<http://www.twitter.com/cloudability>

We're hiring! https://cloudability.com/jobs<http://www.cloudability.com/jobs>


On Mon, Mar 17, 2014 at 11:55 AM, Morgan Tocker <morgan.tocker@stripped>wrote:

> 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