Hi Conor,

The error message for persistent statistics is due to something that I recently changed in MariaDB 10.2.12 and 10.3.3:

commit 7dc6066dead562e70a68e6727fe4ee65d0bd0c72
Author: Marko Mäkelä <marko.makela@mariadb.com>
Date: Fri Dec 1 16:51:24 2017 +0200

MDEV-14511 Use fewer transactions for updating InnoDB persistent statistics

This patch tries to avoid some consistency problems related to InnoDB persistent statistics. It is a long story, but the persistent statistics are being written by an InnoDB internal SQL interpreter that requires the InnoDB data dictionary cache to be locked.

Before the above change, the statistics were written during DDL in separate transactions, which could unnecessarily reduce performance (each commit would require a redo log flush) and break atomicity, because the statistics would be updated separately from the dictionary transaction.

However, because it is unacceptable to hold the data dictionary cache locked while suspending the execution for waiting for a record lock (in the mysql.innodb_index_stats or mysql.innodb_table_stats tables) to be released, any lock conflict will immediately be reported as "lock wait timeout".

As far as I understand, such failures to update statistics did occur earlier as well, but maybe less frequently, because we would really suspend the thread while holding the dictionary lock. It is not the end of the world if the statistics are missing or a bit off. As a matter of fact, I noticed a change in MySQL 5.7.11 (and MariaDB 10.2) that will purposely drop the statistics in a certain case of ALTER TABLE, and rely on a subsequent ha_innobase::open() to update them. It could actually make sense to do this on all ALTER TABLE operations. If we did this, then we would also rename tables in the statistics tables less often (only on RENAME TABLE), and would be less prone to get these errors. I do not think that it makes sense to ever update or rename statistics for the #sql table names.

I think that the likelihood of the problem is increased by executing multiple ALTER TABLE statements in parallel. It also "helps" to use partitioning.

So, in summary, MariaDB 10.2.12 should not be more broken than it was before, and I would like you to submit an issue at https://jira.mariadb.org/ so that this can be fixed in an upcoming release.

On Tue, Jan 9, 2018 at 3:00 PM, Conor Murphy <conor_mark_murphy@hotmail.com> wrote:

Now when we run the "ALTER TABLE .. ENIGNE=InnoDB", it takes hours from the ALTERs to complete and we're getting the following log entries

2018-01-09 12:36:03 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160101` /* Partition `P20160101` */: Lock wait timeout

On a related note, the ALTER TABLE…ALGORITHM=COPY performance for InnoDB should be improved by MDEV-11415 and MDEV-515 in later major MariaDB releases.

One thing that you could try would be to:

ALTER TABLE … DROP INDEX i, DROP INDEX j, …, ENGINE=InnoDB;

ALTER TABLE … ADD INDEX (i), ADD INDEX(i), ALGORITHM=INPLACE;

Until we have MDEV-515, the ENGINE-converting ALTER will be very slow for tables that contain secondary indexes. So, you should copy the table to InnoDB without those indexes, and then create them separately, using a much faster algorithm. MySQL 5.7 (and MariaDB 10.2) got a more efficient algorithm for creating the indexes page by page. In earlier versions, starting with the InnoDB Plugin for MySQL 5.1, the ADD INDEX would only pre-sort the records of each index, and insert them one by one. Even that is much faster than what the engine-conversion does: insert each row one by one, to each index, without any pre-sorting.

With best regards,

Marko

Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

DON’T MISS

M|18

MariaDB User Conference

February 26 - 27, 2018

New York City

https://m18.mariadb.com/