maria-discuss team mailing list archive

[Marko Mäkelä <marko.makela@xxxxxxxxxxx>]

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