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.