Hi Michael,
Hope it is okay to resurrect this thread. Restart times where great with 10.2.14, until I recently enabled FTS on thousands of tables. Shutdown and startup both take over an hour. This, coupled with MariaDB eating up far greater sums of allocated memory in my.cnf, I need to restart every few few days or risk hitting the OOM Killer.
Any ideas?
I am sorry, but there have been several problems with InnoDB fulltext indexes. My personal opinion is that the fulltext search in InnoDB was badly designed and is lacking essential features. These observations still apply to it: https://mariadb.com/resources/blog/initial-impressions-innodb-fulltext I hope that some day, MariaDB will gain a redesigned fulltext search that works with any storage engine. Some synchronization problems or hangs have been fixed since 10.2.14, but I am not convinced that the code is free of race conditions or hangs. Like Daniel Black pointed out, stack traces of all threads would be very helpful. MDEV-16557 (10.2.17) might help on shutdown, because it would allow some fulltext search operations to be interrupted inside InnoDB. (All currently running queries are supposed to be aborted on shutdown.)
2019-03-07 20:20:35 140185550460672 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table. This basically reports a bug in InnoDB fulltext indexing. We could find this one by running a server (in a testing environment) inside gdb, and setting a breakpoint to where this message is reported.
Because of this bug, I suppose that your fulltext searches can miss some results that should have matched the search pattern. (Please file this bug separately with some more details, preferably with a repeatable test case.) While searching our bug database for the above message, I noticed that you had filed MDEV-18867 for these slow start/shutdown issues. I would suggest to continue the diagnosis there.
Startup (close to 1 hour): [snip] 2019-03-07 20:38:38 140573848283328 [Note] InnoDB: Highest supported file format is Barracuda. 2019-03-07 21:31:01 140573848283328 [Note] InnoDB: 128 out of 128 rollback segments are active.
According to the gdb-startup.txt that you attached to MDEV-18867 the problem is fts_check_and_drop_orphaned_tables(), which we can remove once MDEV-18518 implements atomic multi-table transactions in InnoDB, possibly in MariaDB 10.5. When it comes to the slow shutdown, I would recommend to try with the newest MariaDB 10.2 release, and report details if the problem remains with that. One more source of "slow start" remains in the case when some redo log was applied in recovery (which is not the case in the log that you posted). In this case, InnoDB is reading every data file, to validate that all the .ibd files have correct names and tablespace IDs. I believe that this check is redundant and can be removed, now that MariaDB 10.2.19+ (and 10.3 or 10.4) have crash-safe RENAME operations inside InnoDB. I have filed that as https://jira.mariadb.org/browse/MDEV-18733 MariaDB slow start after crash recovery With best regards, Marko