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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
