Sometimes, there is a need to update the table and index statistics manually using the ANALYZE TABLE command. Without going further into the reasons for such a need, I wanted to refresh this subject in terms of overhead related to running the command on production systems. However, the overhead discussed here is unrelated to the usual cost of diving into table rows to gather statistics, which we can control by setting the number of sample pages.
Five years ago, my colleague Sveta posted a nice blog post about an improvement introduced in Percona Server for MySQL to address unnecessary stalls related to running the command:
ANALYZE TABLE Is No Longer a Blocking Operation
Historically, the problem with running the ANALYZE TABLE command in MySQL was that the query needed an exclusive lock on the table definition cache entry for the table. This makes the query wait for any long-running queries to finish but also can trigger cascading waiting for other incoming requests. In short, ANALYZE could lead to nasty stalls in busy production environments.
A lot has changed since then, but many production systems alive today still run with affected versions. Let’s recap how the situation has evolved over the years.
MySQL Server – Community Edition
The problem applies to all versions of the upstream MySQL Community up to 8.0.23. There were no improvements in the 5.7 series (btw, EOL will be reached this month!), which means even the latest 5.7.43 is affected. Here is an example scenario you may end up here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql > select @@version,@@version_comment; +-----------+------------------------------+ | @@version | @@version_comment | +-----------+------------------------------+ | 5.7.43 | MySQL Community Server (GPL) | +-----------+------------------------------+ 1 row in set (0.00 sec) mysql > show processlist; +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+ | 4 | msandbox | localhost | db1 | Query | 54 | Sending data | select avg(k) from sbtest1 where pad not like '%f%' group by c | | 13 | msandbox | localhost | db1 | Query | 29 | Waiting for table flush | analyze table sbtest1 | | 17 | msandbox | localhost | db1 | Query | 0 | starting | show processlist | | 18 | msandbox | localhost | db1 | Query | 15 | Waiting for table flush | select * from sbtest1 where id=100 | +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+ 4 rows in set (0.00 sec) |
One long query made the ANALYZE wait, but another, normally very fast query, is now waiting, too.
The same situation may happen in MySQL 8.0 series, including 8.0.23. Fortunately, there was a fix in version 8.0.24 addressing this problem. We can only read a bit restrained comment in the release notes about the “wait eliminated”:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-24.html
Indeed, since version 8.0.24, a similar test during a long-running query results in instant query execution:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql > select @@version,@@version_comment; +-----------+------------------------------+ | @@version | @@version_comment | +-----------+------------------------------+ | 8.0.24 | MySQL Community Server - GPL | +-----------+------------------------------+ 1 row in set (0.00 sec) mysql > analyze table sbtest1; +-------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+---------+----------+----------+ | db1.sbtest1 | analyze | status | OK | +-------------+---------+----------+----------+ 1 row in set (0.00 sec) |
However, we can still find a warning in the official documentation, even for the 8.1 version, like this:
ANALYZE TABLE removes the table from the table definition cache, which requires a flush lock. If there are long running statements or transactions still using the table, subsequent statements and transactions must wait for those operations to finish before the flush lock is released. Because ANALYZE TABLE itself typically finishes quickly, it may not be apparent that delayed transactions or statements involving the same table are due to the remaining flush lock.
I requested an update of the related bug report as well as the documentation problem accordingly:
https://bugs.mysql.com/bug.php?id=87065
https://bugs.mysql.com/bug.php?id=112670
Percona Server for MySQL
As mentioned above, Percona introduced a fix and removed unnecessary table definition cache lock as a result of solving this bug report:
https://jira.percona.com/browse/PS-2503
When using the Percona variant, running ANALYZE TABLE was safe already since versions 5.6.38 and 5.7.20, as these were the active development series at the time. You may read the announcement in the release notes here:
https://docs.percona.com/percona-server/5.7/release-notes/Percona-Server-5.7.20-18.html#bugs-fixed
Percona Server for MySQL version 8.0 has been free from the issue since the very first release (I tested back, including the first GA release 8.0.13-3), as the improvement was merged from the Percona Server for MySQL 5.7 series.
MariaDB server
The locking ANALYZE TABLE problem applies to all MariaDB versions up to 10.5.3. In version 10.5.4, the solution from Percona was implemented as described in the following report:
https://jira.mariadb.org/browse/MDEV-15101
Therefore, when you run the query in 10.5.3 or lower, and in any previous series, like even the latest 10.4.31, a similar situation may occur:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | mysql > select @@version,@@version_comment; +----------------+-------------------+ | @@version | @@version_comment | +----------------+-------------------+ | 10.5.3-MariaDB | MariaDB Server | +----------------+-------------------+ 1 row in set (0.000 sec) mysql > show processlist; +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+ | 4 | msandbox | localhost | db1 | Query | 18 | Sending data | select avg(k) from sbtest1 where pad not like '%f%' group by c | 0.000 | | 13 | msandbox | localhost | db1 | Query | 16 | Waiting for table flush | analyze table sbtest1 | 0.000 | | 14 | msandbox | localhost | db1 | Query | 14 | Waiting for table flush | select * from sbtest1 where id=100 | 0.000 | | 15 | msandbox | localhost | NULL | Query | 0 | starting | show processlist | 0.000 | +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+ 4 rows in set (0.000 sec) mysql > select @@version,@@version_comment; +-----------------+-------------------+ | @@version | @@version_comment | +-----------------+-------------------+ | 10.4.31-MariaDB | MariaDB Server | +-----------------+-------------------+ 1 row in set (0.000 sec) mysql > show processlist; +----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 9 | msandbox | localhost | db1 | Query | 18 | Sending data | select avg(k) from sbtest1 where pad not like '%f%' group by c | 0.000 | | 18 | msandbox | localhost | db1 | Query | 16 | Waiting for table flush | analyze table sbtest1 | 0.000 | | 19 | msandbox | localhost | db1 | Query | 12 | Waiting for table flush | select * from sbtest1 where id=100 | 0.000 | | 22 | msandbox | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | +----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+ 9 rows in set (0.000 sec) |
Summary
As long as your database runs on the most recent version of MySQL or MariaDB variant, running ANALYZE TABLE should be absolutely safe and not cause any unexpected stalls.
Users of all three major Percona Server for MySQL series – 5.6.38+, 5.7.20+, and 8.0.x are all safe.
When, for any reason, you are not able to upgrade the community variant to the latest MySQL 8.0.24+ version yet and have to stick with 5.6 or 5.7 series for now, you may just swap MySQL Community binaries to Percona Server for MySQL ones, which are 100% compatible yet free from the problem. At the same time, you may check our post-EOL support for Percona Server for MySQL 5.7.
MariaDB users must upgrade to 10.5.4 or later to avoid the locking problem.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
