MySQL Bugs: #65077: internal temporary tables are contended on THR_LOCK

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

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.

I forgot to mention - one needs BLOB column to have temptable immediately created in MyISAM format

See also bug #43726

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;

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.

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.

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.