Re: Why is my entire database getting locked for a slow select query?

[Rick James]

DBA Lead...

Until _very_ recently table_cache=32768 was a _terrible_ value. This is because the code would do a linear scan of that table cache (table_open_cache). The recently added hashing to make the lookup civilized (that is, much faster).

Queries in the information_schema are notorious for being slower than you would think they should be.

Don't do
SHOW STATUS LIKE 'open%';
Instead, do
SHOW GLOBAL STATUS LIKE 'open%';
For example:

mysql> SHOW STATUS LIKE 'Opened_table_definitions';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Opened_table_definitions | 0     |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Opened_table_definitions';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Opened_table_definitions | 2991  |
+--------------------------+-------+
1 row in set (0.00 sec)

This is because _some_ status values are _SESSION_ as well as _GLOBAL_.