Bug #65077 internal temporary tables are contended on THR_LOCK_myisam
Submitted: 23 Apr 2012 14:32 Modified: 29 Aug 2012 15:11
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[23 Apr 2012 14:32] Domas Mituzas
Description:
If workload on a server is heavy temporary tables user (e.g. UNION ALL, ha ha), it will become all melting, thrashing, falling off a cliff and otherwise unhappy in mi_create() and mi_close(), while fighting for THR_LOCK_myisam.

Example stack counts:

    864 __lll_mutex_lock_wait,_L_mutex_lock_107,pthread_mutex_lock,mi_close,handler::drop_table,free_tmp_table,st_select_lex_unit::cleanup,mysql_union,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_one_connection,start_thread,clone,??
     80 __lll_mutex_lock_wait,_L_mutex_lock_107,pthread_mutex_lock,mi_create,create_myisam_tmp_table,create_tmp_table,select_union::create_result_table,st_select_lex_unit::prepare,mysql_union,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_one_connection,start_thread,clone,??
    782 __lll_mutex_lock_wait,_L_mutex_lock_107,pthread_mutex_lock,mi_open,ha_myisam::open,handler::ha_open,open_tmp_table,create_tmp_table,select_union::create_result_table,st_select_lex_unit::prepare,mysql_union,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_one_connection,start_thread,clone,??

How to repeat:
Run any intensive workload, that creates temptables.

Suggested fix:
Don't grab global mutexes for temptable initialization.
[23 Apr 2012 15:44] Domas Mituzas
I forgot to mention - one needs BLOB column to have temptable immediately created in MyISAM format
[25 Apr 2012 9:32] Sveta Smirnova
See also bug #43726
[25 Apr 2012 9:34] Sveta Smirnova
It is easy to confirm lock is acquired. Just run following test case with mtr started as --manual-gdb, then set breakpoint in mi_open (mi_create or mi_close) and on row where THR_LOCK_myisam is set.

Test case:

--source include/have_innodb.inc

create table t1(f1 blob) engine=innodb;
create table t2(f1 blob) engine=innodb;

insert into t1 values('jhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhkjhjklhjkhjkhkjhk');
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t2 select * from t1;

--disable_result_log
select * from t1;
--enable_result_log
select now();
--disable_result_log
--send select * from t1 union all select * from t2 union all select sleep(10) as f1
--enable_result_log

sleep 1;
connect(addconroot,localhost,root,,test,,);
connection addconroot;
select now();
--disable_result_log
select * from t1 union all select * from t2;
--enable_result_log
select now();
--disable_result_log
select * from t1 union all select * from t2;
--enable_result_log
select now();

select 1;
[25 Apr 2012 9:39] Sveta Smirnova
Problem with test case above it gives no new information: it is described in bug # already.

But if run another test case it is possible to re-create problem.

1. Load dump attached
2. Take test case from bug #43726
3. Prepare Poor man's profiler script from http://poormansprofiler.org/
4. Result depends from number of threads. If you can not create thousand of threads on test machine you need to change Poor man's profiler so nsamples=10.
5. Prepare mysqlslap with query attached.
6. Run FLUSH TABLE t1;
7. Run mysqlslap script in parallel
8. Run Poor man's profiler in parallel.

So my conclusion this is mostly mix of two bugs mentioned, but which can create problems for queries which use InnoDB tables only.
[29 Aug 2012 15:11] Paul Dubois
Noted in 5.6.7, 5.7.0 changelogs.

Internal temporary MyISAM tables were unnecessarily registered in an
open-table list protected by a global mutex, causing excessive mutex
contention.
[3 May 2013 6:52] Laurynas Biveinis
5.6$ bzr log -r 4195.3.1

------------------------------------------------------------
revno: 4195.3.1
committer: Sergey Vojtovich <sergey.vojtovich@oracle.com>
branch nick: mysql-5.6-bug14000697
timestamp: Wed 2012-08-29 16:49:37 +0400
message:
  BUG#14000697 - 65077: INTERNAL TEMPORARY TABLES ARE CONTENDED ON
                 THR_LOCK_MYISAM
  
  MyISAM registers all open tables on myisam_open_list. This list is
  protected by global mutex (THR_LOCK_myisam). Under concurrent
  load it causes excessive THR_LOCK_myisam mutex contention.
  
  Fixed by not registering internal temporary tables on
  myisam_open_list, similarly to MEMORY engine.

3902.56.1 in 5.7.