MySQL Bugs: #27062: Creation of temp tables (memory/heap) sets a global mutex

Bug #27062	Creation of temp tables (memory/heap) sets a global mutex
Submitted:	13 Mar 2007 3:19	Modified:	7 Aug 2007 19:51
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.1, 5.0	OS:	Any (any)
Assigned to:	Michael Widenius	CPU Architecture:	Any
Tags:	bfsm_2007_07_19, heap, Memory, mutex, temp, tmp

Description:
Creation of a temp memory (heap) table requires a global mutex.
This would affect scalability with many connections, and should not be necessary since tmp tables are local to each thread/connection anyway.

How to repeat:
From http://ozlabs.org/~anton/linux/sysbench/

gdb was used to attach to mysqld while the benchmark was running. A backtrace of all processes was then taken.

# gdb /usr/sbin/mysqld 12345
(gdb) thread apply all backtrace
...
Many of the threads were blocking on a mutex in heap_open and heap_close:

#0  0x0ff573c8 in __lll_lock_wait () from /lib/tls/libpthread.so.0
#1  0x0ff51cdc in pthread_mutex_lock () from /lib/tls/libpthread.so.0
#2  0x1041c44c in heap_close ()
#3  0x10252d44 in ha_heap::close ()
#4  0x101c23a0 in free_tmp_table ()
#5  0x101cf5ac in JOIN::destroy ()
#6  0x102a0fc0 in st_select_lex::cleanup ()
#7  0x101e1d44 in mysql_select ()
#8  0x101e26c8 in handle_select ()
#9  0x1018bb6c in mysql_execute_command ()
#10 0x101ec414 in Prepared_statement::execute ()
#11 0x101ec8c4 in mysql_stmt_execute ()
#12 0x10192d5c in dispatch_command ()
#13 0x10193f94 in do_command ()
#14 0x101949c0 in handle_one_connection ()
#15 0x0ff50618 in start_thread () from /lib/tls/libpthread.so.0

It turns out MySQL has a global mutex (THR_LOCK_heap) protecting the code in heap/*.

Suggested fix:
Remove unnecessary global mutexes from the mysqld codebase.

As a first step, inventorise them on a forge.mysql.com/wiki/ page (public) that users can review and update as new cases are found. This also allows non-MySQL devs to potentially have a bash at fixing one or more of these cases.

See also http://bugs.mysql.com/27063 (glibc malloc threading/scaling problems).

Thank you for a problem report. Verified with code review also on 5.1-BK:

int heap_create(const char *name, uint keys, HP_KEYDEF *keydef,
                uint reclength, ulong max_records, ulong min_records,
                HP_CREATE_INFO *create_info)
{
  uint i, j, key_segs, max_length, length;
  HP_SHARE *share;
  HA_KEYSEG *keyseg;

  DBUG_ENTER("heap_create");
  pthread_mutex_lock(&THR_LOCK_heap);
...

HP_INFO *heap_open(const char *name, int mode)
{
  HP_INFO *info;
  HP_SHARE *share;

  DBUG_ENTER("heap_open");
  pthread_mutex_lock(&THR_LOCK_heap);
...

int heap_close(HP_INFO *info)
{
  int tmp;
  DBUG_ENTER("heap_close");
  pthread_mutex_lock(&THR_LOCK_heap);
  tmp= hp_close(info);
  pthread_mutex_unlock(&THR_LOCK_heap);
  DBUG_RETURN(tmp);
}

So, same global mutext is used.

From Serg:
> According to the following bench the problem was found in the glic
> > malloc function http://ozlabs.org/~anton/linux/sysbench/

1. "It turns out MySQL has a global mutex (THR_LOCK_heap) protecting the
   code in heap/*."

- Arjen has already reported it as a bug:
  http://bugs.mysql.com/bug.php?id=27062

2. "The glibc malloc library is known to have issues with threading. ...
   Google have written an alternate malloc library that is known to perform
   better with threads."

- it's WL#1289 (and WL#3661, BUG#25244, BUG#27063)

Jeffrey- unless the replacement malloc library can be included in the near future, I think another solution is needed. This is not just for people explicitly using MEMORY tables, it's the server *internally* using temporary MEMORY tables during execution of a query. This affects your major scale-out customers.

If more than one connection at a time has to create a temporary table (not unlikely on a busy site), you don't want one blocking all the others. That's just not good for performance.
I'm sure adjustments can be made in the code that would at least minimize the mutex duration.

Fixed in 5.source tree. Will be in 5.1.21

Noted in 5.1.21 changelog.

The server acquired a global mutex for temporary tables, although
such tables are thread-specific. This affected performance by
blocking other threads.