Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-8772

Stall of instance after INSERT (...) SELECT



      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.




            Unassigned Unassigned
            iwo.panowicz Iwo Panowicz
            Satya Bodapati Satya Bodapati
            0 Vote for this issue
            5 Start watching this issue



              Smart Checklist