MySQL Forums
Forum List  »  InnoDB

The total number of locks exceeds the lock table sizeSource
Posted by: Avi Vainshtein
Date: October 07, 2019 02:16AM

Greetings,

We use 5.7.23 community edition.
Recently MySQL instance crashed - seems that because of "Over 95 percent of the buffer pool is occupied by lock heaps or the adaptive hash index" behavior.

Before the instance crashed - a few (three) Active sessions were involved into Locks during about 1 hour.

Two of those sessions were locked upon "Insert .. Into ... Values" statements, each on a different table.
Generally, there are a lot of ForeignKey relations between application tables.

The innodb_buffer_pool_size is 24GB size.

The total locks "heap size" of mentioned two sessions was rising constantly during that hour, and just before the crash - their sum became 22.6GB (comparing to 24GB pool size).
A message appeared :
"[ERROR] [FATAL] InnoDB: Over 95 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. Your buffer pool size is 3071 MB. Maybe you should make the buffer pool bigger? We intentionally generate a seg fault to print a stack trace on Linux!"

The default isolation level is Repeatable Read.


Questions :

Q1. Under which conditions the locks "heap size" could grow constantly for a session locked at "Insert .. Into ... Values" statement/transaction ?

Q2. The size of those two tables involved into high "heap size" is 0.5GB and 3GB. How could it be that the total "heap size" became 22.6GB ?

Q3. What is the proper way to avoid such cases ? I understood that the increase innodb_buffer_pool_size could be a solution, but i am afraid that after increasing pool size (current 24 GB, RAM is 48GB) - the behavior can return...

Any assistance is welcome,
Avi Vainshtein
DBA

Options: ReplyQuote


Subject
Views
Written By
Posted
The total number of locks exceeds the lock table sizeSource
92
October 07, 2019 02:16AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.