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

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

Hmm..  Shawn’s guesses are probably better than mine then.

> 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…


InnoDB data dictionary is always stored in ibdata1 + there is MySQL data dictionary stored
in .frm files.  I can’t think of a specific reason why accessing it could be slower
until after a dump and restore.

I believe that Performance Schema could be helpful here.  This is a view that will work
with 5.5:
https://github.com/MarkLeith/dbahelper/blob/master/views/p_s/io_global_by_wait_by_bytes.sql

(PS is not enabled by default in 5.5, but file IO is instrumented.. you just need to turn
it on.)

- 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