maria-discuss team mailing list archive

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

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
> <https://github.com/MariaDB/server/commit/7dc6066dead562e70a68e6727fe4ee65d0bd0c72>
> Author: Marko Mäkelä <marko.makela@xxxxxxxxxxx>
> Date:   Fri Dec 1 16:51:24 2017 +0200
>
>     MDEV-14511 <https://jira.mariadb.org/browse/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
<https://github.com/mysql/mysql-server/commit/8bae549264f174a926cd3e52561cad4f31e90f07>
(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@xxxxxxxxxxx>
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
<https://jira.mariadb.org/browse/MDEV-11415> and MDEV-515
<https://jira.mariadb.org/browse/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/