| Bug #33278 | ANALYZE TABLE in InnoDB holds exclusive locks on subsequent execution | ||
|---|---|---|---|
| Submitted: | 16 Dec 2007 22:51 | Modified: | 11 Dec 2009 21:44 |
| Reporter: | Morgan Tocker | ||
| Status: | Patch pending | ||
| Category: | Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.0-bk, 5.1-bk | OS: | Any |
| Assigned to: | Kristofer Pettersson | Target Version: | 5.1+ |
| Triage: | Triaged: D2 (Serious) / R3 (Medium) / E5 (Major) | ||
[17 Dec 2007 11: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 14: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 15:34]
Konstantin Osipov
This should actually be fixed in 5.1 by a patch for Bug#12713. Needs re-verification.
[27 Feb 2008 15:34]
Konstantin Osipov
The patch for 12713 will make it into the main tree only later this week.
[6 Mar 2008 17: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 18: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 17: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 19: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 1: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
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.