| 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: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.0-bk, 5.1-bk | OS: | Any |
| Assigned to: | Daniel Price | CPU Architecture: | Any |
[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.
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.