MySQL Bugs: #84424: InnoDB main thread blocks all writes with a state: enforcing dict cache limit

Bug #84424	InnoDB main thread blocks all writes with a state: enforcing dict cache limit
Submitted:	5 Jan 2017 21:01	Modified:	9 Jan 2017 18:29
Reporter:	Vladimir Dmitriev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.19a	OS:	Other (AWS RDS)
Assigned to:		CPU Architecture:	Any
Tags:	innodb

Description:
With a seemingly random interval (10mins — 4hours), for a period up to 10 minutes, MySQL server locks transactions with writes, updates and deletes. Reads come through. During the condition, InnoDB status shows a main thread in a state: enforcing dict cache limit.

Server started to expose following behavior without any apparent reason.

Initially, we speculated that problem may be related with low value of table_open_cache variable (Open_tables was capped at 2000), after increasing the variable problem persisted. Table_open_cache_overflows variable does not increase.

InnoDB status also shows that main thread 47009390147328 is locking itself(?):

--Thread 47009390147328 has waited at srv0srv.cc line 1998 for 94.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x1357420 created in file dict0dict.cc line 972
a writer (thread id 47009390147328) has reserved it in mode  wait exclusive
number of readers 14, waiters flag 1, lock_word: fffffffffffffff2
Last time read locked in file row0upd.cc line 149
Last time write locked in file /local/mysqlbuild/mysql-5.6.19a.R1/storage/innobase/srv/srv0srv.cc line 1998

InnoDB and global statuses are attached. 

How to repeat:
Not known.

SHOW ENGINE INNODB STATUS; SHOW GLOBAL STATUS;

Attachment: innodb_global_statuses.txt (text/plain), 160.64 KiB.

have you tried to increase table_definition_cache. 

```
For InnoDB, table_definition_cache acts as a soft limit for the number of open table instances in the InnoDB data dictionary cache. If the number of open table instances exceeds the table_definition_cache setting, the LRU mechanism begins to mark table instances for eviction and eventually removes them from the data dictionary cache
```

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_definitio...

Yes, we have also tried to increase table_definition_cache parameter. There is about 120 tables in the single database used on a server. 

Global status shows Open_table_definitions=194 Opened_table_definitions=794 

Increasing table_definition_cache to 4000, then to 10000 didn't help.

SHOW GLOBAL VARIABLES;

Attachment: global_variables.txt (text/plain), 47.33 KiB.

Then a backtrace wil be helpful  to investigate the problem while the scenario repeats  (use pstack or pt-pmp )  :)

Hi!

Thank you for your bug report. This is, however, not a bug, but a very well known issue.

You have to do several things in order to alleviate the problem:

* increase the additional memory pool
* increase total number of file handles available to MySQL
* increase number of file handles for InnoDB
* improve performance of the I/O on your operating system

Issue was resolved by restarting the database. I was not able neither to google this problem, or find it on bugs.mysql.com. I do confirm I/O subsystem was stressed (purge lag reached 400M) prior the problem, we had this lag before without a freeze. Is there any information to help better understand the issue and quantify the required beef up?

Also, during the "enforcing dict cache limit" main thread state we observed zero I/O and CPU workload on a server.