Stall of instance after INSERT (...) SELECT

General

Escalation

General

Escalation

Description

It's a copy of a bug report: https://bugs.mysql.com/bug.php?id=96236.

MySQL can get easily stalled with:

2023-05-24T11:49:16.184610Z 13 [Warning] [MY-011959] [InnoDB] Difficult to find free blocks in the buffer pool (44707 search iterations)! 44707 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostico info below. Pending flushes (fsync) log: 0; buffer pool: 0. 39194 OS file reads, 75324 OS file writes, 8471 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

Another way to reproduce:

CREATE TABLE source (id int primary key auto_increment, a varchar(100), b varchar(100), c varchar(100), d varchar(100), e varchar(100), hdl_source_id int, hdl_created_date datetime);
CREATE TABLE dest LIKE source;
INSERT INTO dest SELECT id,a,b,c,d,e,hdl_source_id,hdl_created_date FROM (select *, row_number() over(partition by id order by hdl_source_id desc, hdl_created_date desc) rn FROM source) a WHERE rn=1;

source table needs a lot of rows, around 500k.

Environment

None

AFFECTED CS IDs

CS0036594

Activity

Show:

Satya Bodapati May 25, 2023 at 10:24 AM

From the upstream bug report, the reporter says, all pages are pinned. this essentially means cache eviction is disabled.

THe described code that tries to optimize insert/select for intrinsic temp tables still exists. Indeed, this is a big design and a WTF moment!

I also believe this mtr holding latching pages is acting as a cover to hide other problems. may be around buffer page relocations etc. IMHO, simply disabling the optimization isn't going to be straighforward.

Needs some serious effort to understand the side effects. And upstream doesn't think it to be serious bug