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

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.

https://gist.github.com/bradhe/c9f00eaf93ac588b8339

We have the defaults for table_definition_cache and table_open_cache (400
each).

*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 5:55 AM, Morgan Tocker <morgan.tocker@stripped>wrote:

> Hi Brad,
>
> > I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query
> so I
> > can figure out what could possibly have made them suddenly slow down?
> >
> > mysql> SHOW CREATE TABLE `my_table`;
> > ...
> > 1 row in set (37.48 sec)
> >
> > We tend to execute many of these statements concurrently, but it's never
> > been a problem until recently. I upgraded the IO subsystem, and our
> > statistics indicate that it's not maxing out IO (at least IOPS).
> >
> > This is problematic because the ORM we're using uses that to figure out
> the
> > structure of our DB...
>
> I am going to guess that it could be contention trying to open the table.
>  i.e. when you run into this issue and run SHOW PROCESSLIST, does it show
> any threads waiting in state "Opening tables"?
>
> (If you can paste a sanitized SHOW PROCESSLIST this helps a lot.)
>
> - 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