Description:
In function mi_close() the mutex THR_LOCK_myisam is locked, during which time flush_key_blocks() is called.
If you use DELAY_KEY_WRITE and load alot of data, this means that opening any other myisam table will have to wait until this flushing completes.
Here is a symptom:
---------+------+----------------+------------------+
Command | Time | State | Info |
---------+------+----------------+------------------+
Query | 136 | | flush table t1 |
Query | 134 | Opening tables | select * from t2 |
Sleep | 168 | | |
Query | 0 | | show processlist |
---------+------+----------------+------------------+
table t1 just had 500M of data loaded and then flushed. Another thread
tried a select from t2, which cannot be opened until the flush completes.
How to repeat:
thread1: (populate the table, increase Key_blocks_not_flushed)
--------------------------------------------------------------
flush tables;
set global flush_time=0;
set global key_buffer_size=500*1024*1024;
set global delay_key_write=all;
\r
drop table if exists t1;
drop table if exists t2;
create table t1(a varchar(255),b varchar(255),c varchar(255),d varchar(255),e varchar(255),key(a),key(b),key(c),key(d),key(e),key(a),key(b),key(c),key(d),key(e),key(a),key(b),key(c),key(d),key(e))delay_key_write=1 engine=myisam;
create table t2(a varchar(255))engine=myisam;
flush tables;
delimiter $
drop procedure if exists p1$
create procedure p1(num int)
begin
declare i int default '0';
repeat
insert into t1 values (
repeat(rand(),100),
repeat(rand(),100),
repeat(rand(),100),
repeat(rand(),100),
repeat(rand(),100));
set i=i+1;
until i>num end repeat;
end $
call p1(100000) $
delimiter ;
thread 2:
flush table t1;
thread 1:
select * from t2;
After flushing the table, switch to thread 1 ASAP and watch how long the select takes while those unflushed key blocks are being written.
Suggested fix:
evaluate if its needed to lock THR_LOCK_myisam in mi_close() for so long?
