Bug #103523 Server hits oom condition when loading two innodb tables with FTS indexes

Submitted: 28 Apr 2021 19:18 Modified: 6 Jul 2021 19:50
Reporter: Herman Lee Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:8.0.24 OS:Any
Assigned to: CPU Architecture:Any

[28 Apr 2021 19:18] Herman Lee 
Description:
We were attempting to restore a number of tables that have a large number of fulltext indexes from a mysqldump backup. When loading the tables in parallel, the server eventually allocates a significant amount of server memory into the fts_optimize_wq. The fts_optimize_thread seems to be unable to keep up with the rate of messages generated. The majority of these messages look to be FTS_MSG_SYNC_TABLE.

The resident memory size of the server process can increase to the point where the process gets killed by memory watchdogs.

How to repeat:
I've generated a test case to simulate the restoring of parallel table using mysqlslap.

# Step 1 - Apply the following patch to 8.0.24 which instruments the size of the fts_optimize_wq so that the fts_optimize_wq queue size can be observed.

diff --git a/storage/innobase/fts/fts0opt.cc b/storage/innobase/fts/fts0opt.cc
index 280e9c6064f..694bece36ab 100644
--- a/storage/innobase/fts/fts0opt.cc
+++ b/storage/innobase/fts/fts0opt.cc
@@ -2416,6 +2416,13 @@ static fts_msg_t *fts_optimize_create_msg(
   return (msg);
 }
 
+int fts_optimize_get_queue_count(void) {
+  if (!fts_optimize_wq) {
+    return -1;
+  }
+  return ib_wqueue_get_count(fts_optimize_wq);
+}
+
 /** Add the table to add to the OPTIMIZER's list. */
 void fts_optimize_add_table(dict_table_t *table) /*!< in: table to add */
 {
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 08c46bbed4e..c4426f44ac5 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -1054,6 +1054,9 @@ static SHOW_VAR innodb_status_variables[] = {
      SHOW_LONG, SHOW_SCOPE_GLOBAL},
     {"dblwr_writes", (char *)&export_vars.innodb_dblwr_writes, SHOW_LONG,
      SHOW_SCOPE_GLOBAL},
+    {"fts_optimize_queue_count",
+     (char *)&export_vars.innodb_fts_optimize_queue_count, SHOW_LONG,
+     SHOW_SCOPE_GLOBAL},
     {"log_waits", (char *)&export_vars.innodb_log_waits, SHOW_LONG,
      SHOW_SCOPE_GLOBAL},
     {"log_write_requests", (char *)&export_vars.innodb_log_write_requests,
diff --git a/storage/innobase/include/fts0fts.h b/storage/innobase/include/fts0fts.h
index 7937374b935..ee0f04f9405 100644
--- a/storage/innobase/include/fts0fts.h
+++ b/storage/innobase/include/fts0fts.h
@@ -681,6 +681,8 @@ void fts_free(dict_table_t *table); /*!< in/out: table with
  @return DB_SUCCESS if all OK */
 dberr_t fts_optimize_table(dict_table_t *table); /*!< in: table to optimiza */
 
+int fts_optimize_get_queue_count(void);
+
 /** Startup the optimize thread and create the work queue. */
 void fts_optimize_init(void);
 
diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
index c357033df0a..933a67748ea 100644
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -1132,6 +1132,7 @@ struct export_var_t {
   ulint innodb_buffer_pool_read_ahead_evicted; /*!< srv_read_ahead evicted*/
   ulint innodb_dblwr_pages_written;            /*!< srv_dblwr_pages_written */
   ulint innodb_dblwr_writes;                   /*!< srv_dblwr_writes */
+  ulint innodb_fts_optimize_queue_count;       /*!< fts_optimize_queue_count */
   ulint innodb_log_waits;                      /*!< srv_log_waits */
   ulint innodb_log_write_requests;             /*!< srv_log_write_requests */
   ulint innodb_log_writes;                     /*!< srv_log_writes */
diff --git a/storage/innobase/include/ut0wqueue.h b/storage/innobase/include/ut0wqueue.h
index 70786e4aba7..63ba8ce345c 100644
--- a/storage/innobase/include/ut0wqueue.h
+++ b/storage/innobase/include/ut0wqueue.h
@@ -59,6 +59,8 @@ void ib_wqueue_free(ib_wqueue_t *wq); /*!< in: work queue */
 @param[in] heap Memory heap to use for allocating the list node */
 void ib_wqueue_add(ib_wqueue_t *wq, void *item, mem_heap_t *heap);
 
+int ib_wqueue_get_count(ib_wqueue_t *wq);
+
 /********************************************************************
 Check if queue is empty. */
 ibool ib_wqueue_is_empty(
diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc
index 40cea6d8414..01f6a187c82 100644
--- a/storage/innobase/srv/srv0srv.cc
+++ b/storage/innobase/srv/srv0srv.cc
@@ -64,6 +64,7 @@ this program; if not, write to the Free Software Foundation, Inc.,
 #endif /* !UNIV_HOTBACKUP */
 #include "ibuf0ibuf.h"
 #ifndef UNIV_HOTBACKUP
+#include "fts0fts.h"
 #include "lock0lock.h"
 #include "log0recv.h"
 #include "mem0mem.h"
@@ -1597,6 +1598,8 @@ void srv_export_innodb_status(void) {
 
   export_vars.innodb_dblwr_writes = srv_stats.dblwr_writes;
 
+  export_vars.innodb_fts_optimize_queue_count = fts_optimize_get_queue_count();
+
   export_vars.innodb_pages_created = stat.n_pages_created;
 
   export_vars.innodb_pages_read = stat.n_pages_read;
diff --git a/storage/innobase/ut/ut0wqueue.cc b/storage/innobase/ut/ut0wqueue.cc
index 6adba8916bc..dc073693b98 100644
--- a/storage/innobase/ut/ut0wqueue.cc
+++ b/storage/innobase/ut/ut0wqueue.cc
@@ -42,6 +42,7 @@ this program; if not, write to the Free Software Foundation, Inc.,
 struct ib_wqueue_t {
   ib_mutex_t mutex; /*!< mutex protecting everything */
   ib_list_t *items; /*!< work item list */
+  int count;
   os_event_t event; /*!< event we use to signal additions to list */
 };
 
@@ -57,6 +58,7 @@ ib_wqueue_t *ib_wqueue_create(void) {
 
   wq->items = ib_list_create();
   wq->event = os_event_create();
+  wq->count = 0;
 
   return (wq);
 }
@@ -79,11 +81,20 @@ void ib_wqueue_add(ib_wqueue_t *wq, void *item, mem_heap_t *heap) {
   mutex_enter(&wq->mutex);
 
   ib_list_add_last(wq->items, item, heap);
+  wq->count++;
   os_event_set(wq->event);
 
   mutex_exit(&wq->mutex);
 }
 
+int ib_wqueue_get_count(ib_wqueue_t *wq) {
+  int count;
+  mutex_enter(&wq->mutex);
+  count = wq->count;
+  mutex_exit(&wq->mutex);
+  return count;
+}
+
 /********************************************************************
 Wait for a work item to appear in the queue for specified time. */
 void *ib_wqueue_timedwait(
@@ -103,6 +114,7 @@ void *ib_wqueue_timedwait(
 
     if (node) {
       ib_list_remove(wq->items, node);
+      wq->count--;
 
       mutex_exit(&wq->mutex);
       break;

# Step 2 - Create the following four tables in the test database of a mysql instance:

create table t1 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, `col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, `col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, `col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY (`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY (`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY (`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY (`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY (`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY (`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY (`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY (`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY (`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY (`col29`), FULLTEXT KEY (`col30`)) engine=innodb;

create table t2 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, `col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, `col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, `col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY (`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY (`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY (`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY (`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY (`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY (`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY (`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY (`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY (`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY (`col29`), FULLTEXT KEY (`col30`)) engine=innodb;

create table t3 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, `col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, `col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, `col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY (`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY (`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY (`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY (`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY (`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY (`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY (`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY (`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY (`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY (`col29`), FULLTEXT KEY (`col30`)) engine=innodb;

create table t4 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, `col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, `col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, `col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY (`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY (`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY (`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY (`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY (`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY (`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY (`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY (`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY (`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY (`col29`), FULLTEXT KEY (`col30`)) engine=innodb;

# Step 3 - Run mysqlslap to generate traffic to the tables:

mysqlslap -P 13000 -h 127.0.0.1 -u root --create-schema='test' --query='insert into t1 ( `col01`, `col02`, `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`) VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()))' --concurrency=2 --iterations=1000000

mysqlslap -P 13000 -h 127.0.0.1 -u root --create-schema='test' --query='insert into t2 ( `col01`, `col02`, `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`) VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()))' --concurrency=2 --iterations=1000000

mysqlslap -P 13000 -h 127.0.0.1 -u root --create-schema='test' --query='insert into t3 ( `col01`, `col02`, `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`) VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()))' --concurrency=2 --iterations=1000000

mysqlslap -P 13000 -h 127.0.0.1 -u root --create-schema='test' --query='insert into t4 ( `col01`, `col02`, `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`) VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()))' --concurrency=2 --iterations=1000000

# Step 4 - observe the size of the optimize queue keeps increasing

show global status like 'innodb_fts%';
[29 Apr 2021 9:43] MySQL Verification Team 
Hello Herman Lee,

Thank you for the report and test case.
Verified as described with 8.0.24 build.

regards,
Umesh
[29 Apr 2021 11:55] MySQL Verification Team 
MySQL Server 8.0.24 test results. Didn't wait for OOM but resident memory was increasing steadily and OOM was inevitable

Attachment: 103523_8.0.24.results.txt (text/plain), 19.26 KiB.
[6 Jul 2021 19:50] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.7.36, 8.0.27 release, and here's the proposed changelog entry from the documentation team:

Concurrent insert operations on multiple tables with full-text indexes
caused a large number of full-text index synchronization requests,
resulting in an out of memory condition.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
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.