Bug #33278 ANALYZE TABLE in InnoDB holds exclusive locks on subsequent execution
Submitted: 16 Dec 2007 21:51 Modified: 11 Apr 2014 18:52
Reporter: Morgan Tocker Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to: Daniel Price CPU Architecture:Any

[16 Dec 2007 21:51] Morgan Tocker
Description:
Analyze table seems to (for no good reason) hold an exclusive lock on the second execution (see how to repeat).

How to repeat:
-- Step 1. Create a sample table, load a lot of same rows.  3263442 should do on my system.

DROP TABLE IF EXISTS my_innodb_test;
CREATE TABLE my_innodb_test ( id INT NOT NULL auto_increment PRIMARY KEY, a char(255) NOT NULL) ENGINE=InnoDB;

INSERT INTO my_innodb_test (id, a) VALUES (NULL, REPEAT('a', 255));
INSERT INTO my_innodb_test (id, a) SELECT NULL, a.a FROM my_innodb_test a, my_innodb_test b;
INSERT INTO my_innodb_test (id, a) SELECT NULL, a.a FROM my_innodb_test a, my_innodb_test b;
INSERT INTO my_innodb_test (id, a) SELECT NULL, a.a FROM my_innodb_test a, my_innodb_test b;
INSERT INTO my_innodb_test (id, a) SELECT NULL, a.a FROM my_innodb_test a, my_innodb_test b;
INSERT INTO my_innodb_test (id, a) SELECT NULL, a.a FROM my_innodb_test a, my_innodb_test b;

-- Step 2. Issue an expensive SELECT query.

SELECT count(*) FROM my_innodb_test;

-- Step 3.  While step 2 is still running, in another session run this:

ANALYZE TABLE my_innodb_test;
ANALYZE TABLE my_innodb_test;

-- Results: The first ANALYZE is almost instant, the second waits for step 2 to complete!
-- Now run an insert in a third session to prove that the analyze is an exclusive lock:

 insert into my_innodb_test (id, a) values (NULL, 'test!');
 
 -- Note: Any statement blocks, not just inserts (not related to the autoincrement lock):
 
update my_innodb_test set a = 'test!' where id = 10;
 
-- Results in show processlist should be something like this:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State             | Info                                                      |
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
|  1 | root | localhost | test | Query   |  235 | Sending data      | select count(*) from my_innodb_test                       |
|  3 | root | localhost | test | Query   |   81 | Waiting for table | analyze table my_innodb_test                              |
|  4 | root | localhost | test | Query   |   23 | Waiting for table | insert into my_innodb_test (id, a) values (NULL, 'test!') |
|  5 | root | localhost | NULL | Query   |    0 | NULL              | show processlist                                          |
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
4 rows in set (0.02 sec)

Suggested fix:
It seems like the query is held up in the SQL Layer.  It's technically possible to figure out cardinality from MVCC, so InnoDB should be allowed to do that.
[17 Dec 2007 10:49] Mark Leith
The backtrace for the ANALYZE TABLE thread that is waiting is:

Thread 11 (process 201 thread 0x3303):
#0  0x900247e7 in semaphore_wait_signal_trap ()
No symbol table info available.
#1  0x900287d4 in pthread_cond_wait ()
#2  0x00094864 in wait_for_refresh ()
#3  0x0009a87d in open_table ()
#4  0x0009b027 in open_tables ()
#5  0x0009b29b in open_and_lock_tables ()
#6  0x00118249 in mysql_admin_table ()
#7  0x001192f6 in mysql_analyze_table ()
#8  0x00079bcb in mysql_execute_command ()
#9  0x0007a655 in mysql_parse ()
#10 0x0007b091 in dispatch_command ()
#11 0x0007bf01 in do_command ()
#12 0x0007ca4b in handle_one_connection ()
#13 0x90024147 in _pthread_body ()

If you look in open_table(), the only way that we can get to wait_for_refresh() is:

if (table->s->version != refresh_version)
{
   /*
   ** There is a refresh in progress for this table
   ** Wait until the table is freed or the thread is killed.
   */
   close_old_data_files(thd,thd->open_tables,0,0);
   if (table->in_use != thd)
     wait_for_refresh(thd);

Session 2 within the *first* ANALYZE TABLE is changing the table's internal version (table->s->version), meaning that it needs to be reloaded in to the table cache. This can not be done until the first session (the long running SELECT COUNT(*)) has completed. 

So all subsequent ANALYZE TABLE commands (and indeed most probably other DML such as INSERT, UPDATE etc.) will have to wait for the first session to complete, so that the table can be reloaded in to the table cache. 

I believe the question now is - does ANALYZE TABLE *really* need to update the table's internal version number?
[17 Dec 2007 13:53] Heikki Tuuri
This is probably a bug in MySQL's table locking code.

ANALYZE TABLE in InnoDB just refreshes the table statistics. I do not know if the table version number should be changed every time we refresh the stats!
[27 Feb 2008 14:34] Konstantin Osipov
This should actually be fixed in 5.1 by a patch for Bug#12713. Needs re-verification.
[27 Feb 2008 14:34] Konstantin Osipov
The patch for 12713 will make it into the main tree only later this week.
[6 Mar 2008 16:52] Susanne Ebrecht
It's still at bk tree of version 5.0 from 2008-03-05 23:30 CET 
CET == UTC+1

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.0.60-debug

I needed some more rows because otherwise my "select count()" was too fast.
I can repeat exact the same behaviour then Morgan by using 13*10^6 rows.

Making a "select count()" and two times Analyze at a second connection and making insert/update statements at a third connection and "show processlist" at a fourth connection:

Using insert statement from above:

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 26
  State: Sending data
   Info: SELECT count(*) FROM my_innodb_test
*************************** 2. row ***************************
     Id: 2
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 22
  State: Waiting for table
   Info: ANALYZE TABLE my_innodb_test
*************************** 3. row ***************************
     Id: 3
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 14
  State: Waiting for table
   Info: insert into my_innodb_test (id, a) values (NULL, 'test!')
*************************** 4. row ***************************
     Id: 4
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 0
  State: NULL
   Info: show processlist

Using update statement from above:

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.0.60-debug
1 row in set (0.00 sec)

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 12
  State: Sending data
   Info: SELECT count(*) FROM my_innodb_test
*************************** 2. row ***************************
     Id: 2
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 8
  State: Waiting for table
   Info: ANALYZE TABLE my_innodb_test
*************************** 3. row ***************************
     Id: 3
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 4
  State: Waiting for table
   Info: update my_innodb_test set a = 'test!' where id = 10
*************************** 4. row ***************************
     Id: 4
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 0
  State: NULL
   Info: show processlist

Also ít's still at bk tree of version 5.1 from 2008-03-06 02:30 CET 

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.1.24-rc-debug

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 10
  State: Sending data
   Info: SELECT count(*) FROM my_innodb_test
*************************** 2. row ***************************
     Id: 2
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 6
  State: Waiting for table
   Info: ANALYZE TABLE my_innodb_test
*************************** 3. row ***************************
     Id: 3
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 3
  State: Waiting for table
   Info: insert into my_innodb_test (id, a) values (NULL, 'test!')
*************************** 4. row ***************************
     Id: 4
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
4 rows in set (0.16 sec)

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 12
  State: Sending data
   Info: SELECT count(*) FROM my_innodb_test
*************************** 2. row ***************************
     Id: 2
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 4
  State: Waiting for table
   Info: ANALYZE TABLE my_innodb_test
*************************** 3. row ***************************
     Id: 3
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 2
  State: Waiting for table
   Info: update my_innodb_test set a = 'test!' where id = 10
*************************** 4. row ***************************
     Id: 4
   User: miracee
   Host: localhost
     db: miracee
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
[7 Mar 2008 17:13] Konstantin Osipov
ANALYZE TABLE updates table statistics.
After ANALYZE table completed, we must purge all old open tables from the table cache, to ensure that the new statistics is available to SQL statements.
Currently we do not do this "online" - that is, the way a purge must happen
is that we can't have old and new versions of the same table in the cache.
Until this is implemented, the bug can not be fixed.
[15 Dec 2009 16:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94197

3262 Kristofer Pettersson	2009-12-15
      Bug#33278 ANALYZE TABLE in InnoDB holds exclusive locks on subsequent execution
      
      ANALYZE TABLE gathers table statistics for the optimizer. In
      order to aggregate the results the statement has to wait for
      the table to be flushed. This causes multiple calls to be
      queued and the effect is the same as if an exclusive lock was
      held by the ANALYZE TABLE operation.
      
      This patch adds an exception for the InnoDB storage engine
      since this storage engine only provide a rough estimate
      through ANALYZE TABLE anyway and doesn't benefit from
      the synchronization mechanism the same way as MyISAM does.
     @ mysql-test/r/analyze_innodb.result
        * Added test for bug 33278
     @ mysql-test/t/analyze_innodb.test
        * Added test for bug 33278
     @ sql/sql_class.cc
        * Add synchronization point
     @ sql/sql_parse.cc
        * Add synchronization point
     @ sql/sql_table.cc
        * Don't remove the table from the cache during
          a ANALYZE TABLE operation if the db_type is
          DB_TYPE_INNODB.
[15 Dec 2009 18:37] Kristofer Pettersson
Feedback from Kostja: "I want a flag"

The flag would reside on the SE side and indicate if the table cache entry needs to be flushed.
[17 Dec 2009 0:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94635

3262 Kristofer Pettersson	2009-12-17
      Bug#33278 ANALYZE TABLE in InnoDB holds exclusive locks on subsequent execution
      
      ANALYZE TABLE gathers table statistics for the optimizer. In
      order to aggregate the results the statement has to wait for
      the table to be flushed. This causes multiple calls to be
      queued and the effect is the same as if an exclusive lock was
      held by the ANALYZE TABLE operation.
      
      This patch adds a handlerton flag which can indicate if a 
      storage engine (InnoDB or MyISAM) needs to invalidate the
      table cache after an ANALYZE TABLE operation. Since InnoDB
      has its own way of keeping relevant statistics it wont carry
      this flag and thus avoid the blocking issue.
     @ mysql-test/r/analyze_innodb.result
        * Added test for bug 33278
     @ mysql-test/t/analyze_innodb.test
        * Added test for bug 33278
     @ sql/handler.h
        * Introduced new handlerton flag for a cache
          invalidation option after an ANALYZE TABLE
          operation.
     @ sql/sql_class.cc
        * Add synchronization point
     @ sql/sql_parse.cc
        * Add synchronization point
     @ sql/sql_table.cc
        * Don't remove the table from the cache during
          a ANALYZE TABLE operation if the db_type is
          DB_TYPE_INNODB.
     @ storage/heap/ha_heap.cc
        * Added handleton flag HTON_FLUSH_AFTER_ANALYZE
     @ storage/myisam/ha_myisam.cc
        * Added handleton flag HTON_FLUSH_AFTER_ANALYZE
[28 Apr 2010 22:52] Caio James
This is affecting us in MySQL 5.1.45. Is there an ETA when this patch will be applied?

Thanks! Caio
[11 May 2010 13:59] Vasil Dimov
This patch changes the code to stop removing the table from MySQL cache during ANALYZE if the table is an InnoDB table.

Inside ::analyze() InnoDB copies its own stats onto MySQL stats.

The change looks ok to me.
[11 May 2010 20:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108041

3379 Konstantin Osipov	2010-05-12
      Committing on behalf of Kristofer Pettersson:
      A fix and a test case for Bug#33278 ANALYZE TABLE in InnoDB 
      holds exclusive locks on subsequent execution,
      with review fixes.
      
      ANALYZE TABLE gathers table statistics for the optimizer. In
      order to aggregate the results the statement has to wait for
      the table to be flushed. This causes multiple calls to be
      queued and the effect is the same as if an exclusive lock was
      held by the ANALYZE TABLE operation.
          
      This patch adds a handlerton flag which can indicate if a 
      storage engine (InnoDB or MyISAM) needs to invalidate the
      table cache after an ANALYZE TABLE operation. Since InnoDB
      has its own way of keeping relevant statistics it wont carry
      this flag and thus avoid the blocking issue.
     @ mysql-test/r/analyze_innodb.result
        * Added test for bug 33278
     @ mysql-test/t/analyze_innodb.test
        * Added test for bug 33278
     @ sql/handler.h
        * Introduced new handlerton flag for a cache
          invalidation option after an ANALYZE TABLE
          operation.
     @ sql/sql_table.cc
        * Don't remove the table from the cache during
          a ANALYZE TABLE operation if the db_type is
          DB_TYPE_INNODB.
     @ storage/heap/ha_heap.cc
        * Added handleton flag HTON_FLUSH_AFTER_ANALYZE
     @ storage/myisam/ha_myisam.cc
        * Added handleton flag HTON_FLUSH_AFTER_ANALYZE
[11 May 2010 20:57] Konstantin Osipov
Further investigation has shown that the patch can't work.
InnoDB, in addition to storing the analyze statistics in dict_table_t stores it in every individual handler, in ha_innobase::stats structure.
Unless all TABLE objects are expelled from the table cache after analyze,
some ha_innobase::stats instances will remain incorrect.

A test case for gdb confirming the issue:

create table t1 (a int, key(a)) engine=innodb;
insert into t1 (a) values (1), (2), (3);
analyze table t1;
insert into t1 (a) values (4), (5), (6);

# switch to another connection.
lock table t1; 

# switch to the original connection
# the below statement only succeeds if the tree contains the patch
analyze table t1; # updates table->stats.records to 6.

# switch to lock table read connection:
select * from t1; # set a breakpoint in open_table and observe table->file->stats.records=3

In addition to the patch attached to the bug report, InnoDB needs to
be changed to not store analyze results in ha_innobase object.
Handling over to InnoDB team for completion.
Requesting a re-triage as this is essentially a feature request,
and not a bug, and one that is too risky to push in 5.1.
(Love it when some technical foundation is dragged in to make us
do a feature).
[9 Jun 2010 18:56] Konstantin Osipov
What can be done in 5.5, is that we can make sure that the lock is taken during the first execution, not the second. The lock will be taken for a very brief period after ANALYZE has executed, but before sending OK to the client. If that would solve the customer problem, it's not hard to do.
[9 Jun 2013 14:36] MySQL Verification Team
problem still seems to affect 5.7.2...
[4 Apr 2014 11:31] Jon Olav Hauglid
The current behavior is expected and and the result of a limitation
of the current design which is sadly not easy to lift. We are aware
of the issue.

The reason for the behavior is that ANALYZE TABLE upon completion
marks currently cached table definitions as obsolete. This means that
any new statements wishing to use the table will have to reload the
table definition into the table cache.

However since we don't support having multiple versions of a table
definition versions loaded, new statements (including a second ANALYZE
TABLE) have to wait until old table definitions are gone from the cache.
I.e. wait until any other statements/transactions using the same
table, have completed. 

This limitation should be documented. I'm therefore reclassifying this bug
as a documentation issue.
[11 Apr 2014 18:52] Daniel Price
This behaviour has been documented as an InnoDB table restriction, which is linked to from the ANALYZE TABLE page.

http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Thank you for the bug report.