jplindst, with this change, the tables could be created in any storage engine (such as Aria or MyISAM). I suppose that moving to other storage engines (which might use table-level locking) might increase the possibility of lock waits. If I remember correctly, in the existing implementation there are some tweaks for disabling Galera replication. The final code can surely be reviewed by the Galera team.

Use cases to handle

Let's list the usage scenarios of stat tables:

• Use1: a select or DML statement starts. It needs to read statistics for the tables it is using.
• Use2: manual ANALYZE TABLE is performed. It needs to update the statistics data.
• Use3: a background statistics update is triggered. It collects the new statistics and so needs to update it.

• Use4: SQL-level usage of stat tables. A user runs a statement that does a read, DML, or even a DDL operation on the statistic table.

• Use5: a DDL operation on a table causes the statistics to be either updated or removed.

How EITS does it

• There is code to handle Use1 and Use2.
• There is no Use3.
• Use4 has some limitations, e.g.
  • "ERROR 1428 (HY000): You can't combine write-locking of system tables with other tables or lock types", MDEV-7363

Use1: How EITS reads the statistics

TODO.
(As far as I understand, opening stat tables is "hooked" into the process of opening regular table. There is code at SQL layer that injects EITS tables into the list of tables that the statement is opening... This way, opening and locking is done in one shot. It might be hard to repeat this inside innodb?)
TODO^ verify this.

Use2: How EITS updates statistics

Use2 is done as follows:

1. Statistics is collected.
2. sql_statistics.cc: update_statistics_for_table() saves the data.

  start_new_trans new_trans(thd);

  open_stat_tables()

  ...

  stat_file->ha_update_row(record[1], record[0]);

  ...

  thd->commit_whole_transaction_and_close_tables();

  new_trans.restore_old_transaction();

note that the above is run while holding a lock on the table being analyzed.
(Does this cause a conflict for MySQL table locking mechanism?)

Updating statistics inside InnoDB.

Currently InnoDB is using its own internal SQL interpreter to perform statistics updates.
The interpreter calls InnoDB internal APIs to perform operations on statistics data.

The new implementation is expected to use SQL-level objects for doing this. Like get an MDL lock, call open_table (or similar) to get TABLE object and a ha_innobase object. Is this right?

Related to MDEV-25919, I refactored the InnoDB code that accesses the persistent statistics tables. We still invoke the InnoDB internal parser, but I believe that the hangs have now been fixed. We will acquire MDL and InnoDB table locks on the statistics tables, as well as MDL on the user table, before locking the dict_sys cache.

Side note: I see that MySQL Bug #83079 was incorrectly closed as fixed in MySQL 5.7.23, most likely mistaking it to be a duplicate of a regression that was introduced in MySQL 5.7 (the fix of Oracle Bug #29390736 was the basis of the MariaDB fix of MDEV-14637).

The root cause of these hangs is older (present since the introduction of InnoDB persistent statistics in MySQL 5.6 and MariaDB 10.0). They were fixed in MariaDB 10.6 by MDEV-25919.

Some hangs related to all types of InnoDB statistics (including the ‘transient’ ones that STATS_PERSISTENT replaced) were possible due to MDEV-29883.