List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:March 17 2014 10:42pm
Subject:Re: SHOW CREATE TABLE suddenly slow on InnoDB?
View as plain text  
Hello Brad,

On 3/17/2014 5:50 PM, Brad Heller wrote:
> 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...
>

If it's localized to the one machine, have you looked to ensure that 
your physical disks are all working properly?

In the past, sudden decreases in disk response (such as loading and 
reading the .frm file) have happened to others due to a RAID element 
being dead but appearing alive because your RAID controller is 
simulating the content of the dead disk.

Other potential sources of interference are:
* File lock contention if you are using a networked share (or 
potentially even the network itself).
* File lock contention from an anti-virus (or other file scanning) program
* Automated backup software (shadow copy).

Yours,
-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
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