Merge lp:~laurynas-biveinis/percona-server/bug1179978-5.5 into lp:percona-server/5.5

[Laurynas Biveinis]

Fix bug 1179978 / MySQL bug 65077 (internal temporary tables are
contended on THR_LOCK_myisam) by backporting revision 4195.3.1 from
lp:mysql-server/5.6.

5.6$ bzr log -r 4195.3.1

------------------------------------------------------------
revno: 4195.3.1
committer: Sergey Vojtovich <email address hidden>
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.

http://jenkins.percona.com/job/percona-server-5.5-param/737/

[Alexey Kopytov (akopytov)]

review: Approve

=== modified file 'Percona-Server/include/my_base.h'
--- Percona-Server/include/my_base.h	2012-03-01 08:27:43 +0000
+++ Percona-Server/include/my_base.h	2013-05-16 14:29:32 +0000
@@ -319,6 +319,7 @@
 #define HA_CREATE_PAGE_CHECKSUM	32
 #define HA_CREATE_DELAY_KEY_WRITE 64
 #define HA_CREATE_RELIES_ON_SQL_LAYER 128
+#define HA_CREATE_INTERNAL_TABLE 256
 
 /*
   The following flags (OR-ed) are passed to handler::info() method.
 
=== modified file 'Percona-Server/sql/sql_select.cc'
--- Percona-Server/sql/sql_select.cc	2013-03-22 03:29:56 +0000
+++ Percona-Server/sql/sql_select.cc	2013-05-16 14:29:32 +0000
@@ -11222,7 +11222,7 @@
 		       param->start_recinfo,
 		       share->uniques, &uniquedef,
 		       &create_info,
-		       HA_CREATE_TMP_TABLE)))
+		       HA_CREATE_TMP_TABLE | HA_CREATE_INTERNAL_TABLE)))
   {
     table->file->print_error(error,MYF(0));	/* purecov: inspected */
     table->db_stat=0;
 
=== modified file 'Percona-Server/storage/myisam/mi_close.c'
--- Percona-Server/storage/myisam/mi_close.c	2012-02-16 09:48:16 +0000
+++ Percona-Server/storage/myisam/mi_close.c	2013-05-16 14:29:32 +0000
@@ -31,7 +31,8 @@
 		      (long) info, (uint) share->reopen,
                       (uint) share->tot_locks));
 
-  mysql_mutex_lock(&THR_LOCK_myisam);
+  if (info->open_list.data)
+    mysql_mutex_lock(&THR_LOCK_myisam);
   if (info->lock_type == F_EXTRA_LCK)
     info->lock_type=F_UNLCK;			/* HA_EXTRA_NO_USER_CHANGE */
 
@@ -54,7 +55,8 @@
     info->opt_flag&= ~(READ_CACHE_USED | WRITE_CACHE_USED);
   }
   flag= !--share->reopen;
-  myisam_open_list=list_delete(myisam_open_list,&info->open_list);
+  if (info->open_list.data)
+    myisam_open_list= list_delete(myisam_open_list, &info->open_list);
   mysql_mutex_unlock(&share->intern_lock);
 
   my_free(mi_get_rec_buff_ptr(info, info->rec_buff));
@@ -108,7 +110,8 @@
     }
     my_free(info->s);
   }
-  mysql_mutex_unlock(&THR_LOCK_myisam);
+  if (info->open_list.data)
+    mysql_mutex_unlock(&THR_LOCK_myisam);
   if (info->ftparser_param)
   {
     my_free(info->ftparser_param);
 
=== modified file 'Percona-Server/storage/myisam/mi_create.c'
--- Percona-Server/storage/myisam/mi_create.c	2011-07-03 23:48:19 +0000
+++ Percona-Server/storage/myisam/mi_create.c	2013-05-16 14:29:32 +0000
@@ -43,6 +43,7 @@
        base_pos,long_varchar_count,varchar_length,
        max_key_block_length,unique_key_parts,fulltext_keys,offset;
   uint aligned_key_start, block_length;
+  uint internal_table= flags & HA_CREATE_INTERNAL_TABLE;
   ulong reclength, real_reclength,min_pack_length;
   char filename[FN_REFLEN],linkname[FN_REFLEN], *linkname_ptr;
   ulong pack_reclength;
@@ -563,7 +564,8 @@
   if (! (flags & HA_DONT_TOUCH_DATA))
     share.state.create_time= (long) time((time_t*) 0);
 
-  mysql_mutex_lock(&THR_LOCK_myisam);
+  if (!internal_table)
+    mysql_mutex_lock(&THR_LOCK_myisam);
 
   /*
     NOTE: For test_if_reopen() we need a real path name. Hence we need
@@ -620,7 +622,7 @@
     NOTE: The filename is compared against unique_file_name of every
     open table. Hence we need a real path here.
   */
-  if (test_if_reopen(filename))
+  if (!internal_table && test_if_reopen(filename))
   {
     my_printf_error(0, "MyISAM table '%s' is in use "
                     "(most likely by a MERGE table). Try FLUSH TABLES.",
@@ -809,14 +811,16 @@
       goto err;
   }
   errpos=0;
-  mysql_mutex_unlock(&THR_LOCK_myisam);
+  if (!internal_table)
+    mysql_mutex_unlock(&THR_LOCK_myisam);
   if (mysql_file_close(file, MYF(0)))
     goto err_no_lock;
   my_free(rec_per_key_part);
   DBUG_RETURN(0);
 
 err:
-  mysql_mutex_unlock(&THR_LOCK_myisam);
+  if (!internal_table)
+    mysql_mutex_unlock(&THR_LOCK_myisam);
 
 err_no_lock:
   save_errno=my_errno;
 
=== modified file 'Percona-Server/storage/myisam/mi_open.c'
--- Percona-Server/storage/myisam/mi_open.c	2012-11-14 11:32:36 +0000
+++ Percona-Server/storage/myisam/mi_open.c	2013-05-16 14:29:32 +0000
@@ -13,7 +13,18 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
 
-/* open a isam-database */
+/*
+  open a isam-database
+
+  Internal temporary tables
+  -------------------------
+  Since only single instance of internal temporary table is required by
+  optimizer, such tables are not registered on myisam_open_list. In effect
+  it means (a) THR_LOCK_myisam is not held while such table is being created,
+  opened or closed; (b) no iteration through myisam_open_list while opening a
+  table. This optimization gives nice scalability benefit in concurrent
+  environment. MEMORY internal temporary tables are optimized similarly.
+*/
 
 #include "fulltext.h"
 #include "sp_defs.h"
@@ -70,10 +81,11 @@
   int lock_error,kfile,open_mode,save_errno,have_rtree=0, realpath_err;
   uint i,j,len,errpos,head_length,base_pos,offset,info_length,keys,
     key_parts,unique_key_parts,fulltext_keys,uniques;
+  uint internal_table= open_flags & HA_OPEN_INTERNAL_TABLE;
   char name_buff[FN_REFLEN], org_name[FN_REFLEN], index_name[FN_REFLEN],
        data_name[FN_REFLEN];
   uchar *disk_cache, *disk_pos, *end_pos;
-  MI_INFO info,*m_info,*old_info;
+  MI_INFO info, *m_info, *old_info= NULL;
   MYISAM_SHARE share_buff,*share;
   ulong rec_per_key_part[HA_MAX_POSSIBLE_KEY*MI_MAX_KEY_SEG];
   my_off_t key_root[HA_MAX_POSSIBLE_KEY],key_del[MI_MAX_KEY_BLOCK_SIZE];
@@ -96,8 +108,13 @@
     DBUG_RETURN (NULL);
   }
 
-  mysql_mutex_lock(&THR_LOCK_myisam);
-  if (!(old_info=test_if_reopen(name_buff)))
+  if (!internal_table)
+  {
+    mysql_mutex_lock(&THR_LOCK_myisam);
+    old_info= test_if_reopen(name_buff);
+  }
+
+  if (!old_info)
   {
     share= &share_buff;
     bzero((uchar*) &share_buff,sizeof(share_buff));
@@ -629,10 +646,13 @@
 
   *m_info=info;
   thr_lock_data_init(&share->lock,&m_info->lock,(void*) m_info);
-  m_info->open_list.data=(void*) m_info;
-  myisam_open_list=list_add(myisam_open_list,&m_info->open_list);
 
-  mysql_mutex_unlock(&THR_LOCK_myisam);
+  if (!internal_table)
+  {
+    m_info->open_list.data= (void*) m_info;
+    myisam_open_list= list_add(myisam_open_list, &m_info->open_list);
+    mysql_mutex_unlock(&THR_LOCK_myisam);
+  }
 
   bzero(info.buff, share->base.max_key_block_length * 2);
 
@@ -675,7 +695,8 @@
   default:
     break;
   }
-  mysql_mutex_unlock(&THR_LOCK_myisam);
+  if (!internal_table)
+    mysql_mutex_unlock(&THR_LOCK_myisam);
   my_errno=save_errno;
   DBUG_RETURN (NULL);
 } /* mi_open */