Bug #43726 THR_LOCK_myisam is locked while flushing unwritten key blocks to disk
Submitted: 18 Mar 2009 12:07 Modified: 18 Mar 2009 14:43
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S5 (Performance)
Version:5.1.32/6.0 OS:Any
Assigned to:
Triage: Triaged: D2 (Serious) / R5 (Severe)

[18 Mar 2009 12:07] Shane Bester
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?
[18 Mar 2009 14:43] Miguel Solorzano
Thank you for the bug report. Verified as described.
[20 Jul 2010 20:59] J. Allen Dove
Is there any update on this bug? It makes delay_key_write useless in high-volume situations where it could make a big performance difference.