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%';