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...
*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 11:55 AM, Morgan Tocker <morgan.toc...@oracle.com>wrote:
> Hi Brad,
>
> > 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.
>
> It shouldn't matter if they are for the same or different - in 5.5 there
> is one table open cache "instance" - so only one person can be opening or
> closing tables at a time.
>
> In 5.6 this is configurable to reduce contention:
>
> http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances
>
> > https://gist.github.com/bradhe/c9f00eaf93ac588b8339
> >
> > We have the defaults for table_definition_cache and table_open_cache (400
> > each).
>
> I am going to guess and say that you may have a sharded environment with a
> large number of tables?
>
> Another solution that may work, is to increase these caches. In most
> cases it will work fine, but MPB has also blogged about the exception where
> you can get negative scalability (so many cache misses the cache can't work
> effectively):
>
> http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/
>
> I'm not sure how up-to-date the edge case issue is. But hopefully this
> gives you some starting points.
>
> (Others, feel free to chime in!)
>
> - Morgan
