> 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
(PS is not enabled by default in 5.5, but file IO is instrumented.. you just need to turn