Development

It looks like the warning didn't exist in 5.5 at all, so it's not in itself surprising that it only started appearing with 10.1.
I'll re-address other questions to marko.

The message was recently reformatted in MDEV-14776. It was introduced in MySQL 3.23.44 already (October 30, 2001). It used to be this monstrosity:

InnoDB: Warning: difficult to find free blocks from

InnoDB: the buffer pool (%lu search iterations)! Consider

InnoDB: increasing the buffer pool size.

The InnoDB buffer pool is also used for storing information about transactional InnoDB locks.

In the upgrade, could it be possible that some transactions were recovered in XA PREPARE state and never committed or rolled back? In that case, the buffer pool could be filled with record locks for the lock waits of new connections waiting for the recovered transactions to end.

I do not know where you got the 70% figure from. It is only a rule of thumb.

If you are doing a huge UPDATE or DELETE transaction, that would create a large number of record locks. MDEV-16232 should eventually fix those to use implicit locking when there are no conflicts, just like INSERT has always worked.

Hello marko,

In response to your post
In the upgrade, could it be possible that some transactions were recovered in XA PREPARE state and never committed or rolled back? In that case, the buffer pool could be filled with record locks for the lock waits of new connections waiting for the recovered transactions to end.

Yes it is possible. I believe these databases did not use set GLOBAL innodb_fast_shutdown=0 prior to being upgraded. Also, they were not being restricted from client connection prior to shutdown. How can we tell if these databases suffer from what you suggest. Is there a way to flush the buffer pool completely? What do you recommend?

Thanks,

Joe Oreste

joe.oreste@xpressbet.com, sorry, I missed your feedback.

The buffer pool should by default be empty at a startup that follows a clean shutdown. (Crash recovery would read pages into the buffer pool).

InnoDB uses its buffer pool also for some other things than data pages. If I remember correctly, some data structures for the adaptive hash index can be allocated in the buffer pool. And the InnoDB table and record locks are alllocated from the buffer pool.

The innodb_adaptive_hash_index is often causing problems, so it might be helpful to disable it.

If any incomplete transactions were recovered from the undo logs at startup, these transactions would hold table IX locks until they are committed or rolled back. If some new transaction is attempting to acquire a lock that conflicts with one of the recovered transactions, then two sets of record locks would be created for each conflict: one for the lock that the recovered transaction is holding, and a wait lock request for the new transaction.

Normally, recovered incomplete transactions are rolled back by a background thread. If any transactions were recovered in the XA PREPARE state, those would remain in the system until an explicit XA COMMIT or XA ROLLBACK is executed for the transaction.

To analyze the problem, I would need more information. Possibly, the output from the following could help to see what is occupying the buffer pool.

SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

This looks like it may be a duplicate of MDEV-19176.

GeoffMontee, I do not think that this is a duplicate of MDEV-19176, because I see no mention of a hang during startup. It looks like we have an explanation of this in MDEV-16339, related to an XtraDB parameter innodb_empty_free_list_algorithm=BACKOFF. That parameter looks like it could cause buf_LRU_get_free_block() to sleep, relying on page cleaners to free up more blocks. That parameter is not supported by MariaDB 10.2 or later.

Ah, OK. My mistake. Thanks!