Merge lp:~vlad-lesin/percona-server/5.1-gca-bug-1038940-table_cache_speed_up into lp:percona-server/5.1

[Vlad Lesin]

This MP is port of this http://lists.mysql.com/commits/121507 patch to 5.1(see issue #25137). The main idea of this patch is to replace linked list of opened tables with hash-table to improve performance of open table lookup mechanism.

Jenkins test:
http://jenkins.percona.com/view/PS%205.1/job/percona-server-5.1-param/480

This is just copy of this https://code.launchpad.net/~vlad-lesin/percona-server/5.1-table_cache_speed_up/+merge/120285 MP to 5.1 GCA clone to make merging to 5.5 easier.

[Vlad Lesin (vlad-lesin)]

5.5 MP can be found here: https://code.launchpad.net/~vlad-lesin/percona-server/5.5-bug-1038940-table_cache_speed_up/+merge/141748

[Laurynas Biveinis (laurynas-biveinis)]

review: Approve

[Stewart Smith (stewart)]

FYI since the 5.5 MP is Work In Progress, I can't yet merge the 5.1 one either.

[Vlad Lesin (vlad-lesin)]

Unnecessary changes from mi_panic.c are removed.

[Vlad Lesin (vlad-lesin)]

Jenkins results: http://jenkins.percona.com/view/PS%205.1/job/percona-server-5.1-param/525/

[Laurynas Biveinis (laurynas-biveinis)]

The patch is OK, but I'll have to ask you to redo it as a GCA involving all three 5.1/5.5/5.6.

review: Needs Fixing

Unmerged revisions

510. By Vlad Lesin on 2013-01-01

Fix for bug #1038940.

As the number of open tables is increased, table lookup
(testing if a table is already open) and (in particular)
the case when a table is not open, became increasingly more
expensive.

The problem was caused by the open table lookup mechanism,
which was based on traversing a linked list comparing the
file names.

As the list was replaced by a hash table, the lookup
time dropped significantly when used on systems with
a large number of open tables.

The original patch can be found here:
http://lists.mysql.com/commits/121507

=== modified file 'Percona-Server/storage/myisam/ha_myisam.cc'
--- Percona-Server/storage/myisam/ha_myisam.cc	2011-11-24 16:33:30 +0000
+++ Percona-Server/storage/myisam/ha_myisam.cc	2013-01-03 08:27:23 +0000
@@ -2201,6 +2201,10 @@
   myisam_hton->create= myisam_create_handler;
   myisam_hton->panic= myisam_panic;
   myisam_hton->flags= HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES;
+
+  if (mi_init_open_table_hash(table_cache_size))
+    return 1;
+
   return 0;
 }
 
 
=== modified file 'Percona-Server/storage/myisam/mi_close.c'
--- Percona-Server/storage/myisam/mi_close.c	2012-02-15 16:21:38 +0000
+++ Percona-Server/storage/myisam/mi_close.c	2013-01-03 08:27:23 +0000
@@ -56,7 +56,7 @@
     info->opt_flag&= ~(READ_CACHE_USED | WRITE_CACHE_USED);
   }
   flag= !--share->reopen;
-  myisam_open_list=list_delete(myisam_open_list,&info->open_list);
+  my_hash_delete(&myisam_open_table_hash, (uchar *) info);
   pthread_mutex_unlock(&share->intern_lock);
 
   my_free(mi_get_rec_buff_ptr(info, info->rec_buff), MYF(MY_ALLOW_ZERO_PTR));
 
=== modified file 'Percona-Server/storage/myisam/mi_dbug.c'
--- Percona-Server/storage/myisam/mi_dbug.c	2011-06-30 15:37:13 +0000
+++ Percona-Server/storage/myisam/mi_dbug.c	2013-01-03 08:27:23 +0000
@@ -172,13 +172,13 @@
 my_bool check_table_is_closed(const char *name, const char *where)
 {
   char filename[FN_REFLEN];
-  LIST *pos;
+  uint idx;
   DBUG_ENTER("check_table_is_closed");
 
   (void) fn_format(filename,name,"",MI_NAME_IEXT,4+16+32);
-  for (pos=myisam_open_list ; pos ; pos=pos->next)
+  for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
   {
-    MI_INFO *info=(MI_INFO*) pos->data;
+    MI_INFO *info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
     MYISAM_SHARE *share=info->s;
     if (!strcmp(share->unique_file_name,filename))
     {
 
=== modified file 'Percona-Server/storage/myisam/mi_keycache.c'
--- Percona-Server/storage/myisam/mi_keycache.c	2008-03-29 15:56:33 +0000
+++ Percona-Server/storage/myisam/mi_keycache.c	2013-01-03 08:27:23 +0000
@@ -137,16 +137,16 @@
 void mi_change_key_cache(KEY_CACHE *old_key_cache,
 			 KEY_CACHE *new_key_cache)
 {
-  LIST *pos;
+  uint idx;
   DBUG_ENTER("mi_change_key_cache");
 
   /*
     Lock list to ensure that no one can close the table while we manipulate it
   */
   pthread_mutex_lock(&THR_LOCK_myisam);
-  for (pos=myisam_open_list ; pos ; pos=pos->next)
+  for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
   {
-    MI_INFO *info= (MI_INFO*) pos->data;
+    MI_INFO *info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
     MYISAM_SHARE *share= info->s;
     if (share->key_cache == old_key_cache)
       mi_assign_to_key_cache(info, (ulonglong) ~0, new_key_cache);
 
=== modified file 'Percona-Server/storage/myisam/mi_open.c'
--- Percona-Server/storage/myisam/mi_open.c	2011-06-30 15:37:13 +0000
+++ Percona-Server/storage/myisam/mi_open.c	2013-01-03 08:27:23 +0000
@@ -46,23 +46,76 @@
 }
 
 
-/******************************************************************************
-** Return the shared struct if the table is already open.
-** In MySQL the server will handle version issues.
-******************************************************************************/
-
+/*
+  Get the value used as hash key (helper function for the 
+  open table hash).  Function is used as a callback 
+  from the hash table
+*/
+static uchar *mi_open_table_hash_key(const uchar *record, size_t *length,
+                                     my_bool not_used __attribute__((unused)))
+{
+  MI_INFO *info= (MI_INFO *) record;
+  *length= info->s->unique_name_length;
+  return (uchar*) info->s->unique_file_name;
+}
+
+/**
+  Initialize open table hash
+ 
+  Function is normally called from myisam_init
+  with the system variable table_cache_size used
+  as hash_size.
+
+  @param[in]      hash_size Initial has size (elements)
+  @return         inidicates success or failure of initialization
+    @retval 0 success
+    @retval 1 failure
+
+*/
+int mi_init_open_table_hash(ulong hash_size)
+{
+  if (hash_size == 0)
+    hash_size= 32; /* default hash size */
+
+  if (my_hash_init(&myisam_open_table_hash, &my_charset_filename, 
+                   hash_size, 0, 0, mi_open_table_hash_key, 0, 0))
+    return 1; /* error */
+  
+  return 0;
+}
+
+
+/**
+  Retrieve the shared struct if the table is already
+  open (i.e  in the open table hash)
+
+  @param[in] filename table file name
+  @return    shared struct, 0 if not in the cache
+*/
 MI_INFO *test_if_reopen(char *filename)
 {
-  LIST *pos;
+  HASH_SEARCH_STATE current_record;
+  int len= strlen(filename);
 
-  for (pos=myisam_open_list ; pos ; pos=pos->next)
+  MI_INFO *info= (MI_INFO*) my_hash_first(&myisam_open_table_hash, 
+                                          (uchar *) filename, 
+                                          len, &current_record);
+  /*
+    There might be more than one instance of a table share for
+    a given table in the hash table.  We're interested in the one with 
+    last_version set, so we iterate until we find it
+  */
+  while (info)
   {
-    MI_INFO *info=(MI_INFO*) pos->data;
     MYISAM_SHARE *share=info->s;
-    if (!strcmp(share->unique_file_name,filename) && share->last_version)
-      return info;
+    if (share->last_version)
+      break;
+    
+    info= (MI_INFO*) my_hash_next(&myisam_open_table_hash, 
+                                  (uchar *) filename, 
+                                  len, &current_record);
   }
-  return 0;
+  return info;
 }
 
 
@@ -650,8 +703,9 @@
 #ifdef THREAD
   thr_lock_data_init(&share->lock,&m_info->lock,(void*) m_info);
 #endif
-  m_info->open_list.data=(void*) m_info;
-  myisam_open_list=list_add(myisam_open_list,&m_info->open_list);
+
+  if (my_hash_insert(&myisam_open_table_hash, (uchar *) m_info))
+    goto err;
 
   pthread_mutex_unlock(&THR_LOCK_myisam);
 
 
=== modified file 'Percona-Server/storage/myisam/mi_panic.c'
--- Percona-Server/storage/myisam/mi_panic.c	2006-12-31 00:32:21 +0000
+++ Percona-Server/storage/myisam/mi_panic.c	2013-01-03 08:27:23 +0000
@@ -26,85 +26,102 @@
 int mi_panic(enum ha_panic_function flag)
 {
   int error=0;
-  LIST *list_element,*next_open;
   MI_INFO *info;
+  uint idx;
   DBUG_ENTER("mi_panic");
 
   pthread_mutex_lock(&THR_LOCK_myisam);
-  for (list_element=myisam_open_list ; list_element ; list_element=next_open)
-  {
-    next_open=list_element->next;		/* Save if close */
-    info=(MI_INFO*) list_element->data;
-    switch (flag) {
-    case HA_PANIC_CLOSE:
-      pthread_mutex_unlock(&THR_LOCK_myisam);	/* Not exactly right... */
-      if (mi_close(info))
-	error=my_errno;
-      pthread_mutex_lock(&THR_LOCK_myisam);
-      break;
-    case HA_PANIC_WRITE:		/* Do this to free databases */
-#ifdef CANT_OPEN_FILES_TWICE
-      if (info->s->options & HA_OPTION_READ_ONLY_DATA)
-	break;
-#endif
-      if (flush_key_blocks(info->s->key_cache, info->s->kfile, FLUSH_RELEASE))
-	error=my_errno;
-      if (info->opt_flag & WRITE_CACHE_USED)
-	if (flush_io_cache(&info->rec_cache))
-	  error=my_errno;
-      if (info->opt_flag & READ_CACHE_USED)
-      {
-	if (flush_io_cache(&info->rec_cache))
-	  error=my_errno;
-	reinit_io_cache(&info->rec_cache,READ_CACHE,0,
-		       (pbool) (info->lock_type != F_UNLCK),1);
-      }
-      if (info->lock_type != F_UNLCK && ! info->was_locked)
-      {
-	info->was_locked=info->lock_type;
-	if (mi_lock_database(info,F_UNLCK))
-	  error=my_errno;
-      }
-#ifdef CANT_OPEN_FILES_TWICE
-      if (info->s->kfile >= 0 && my_close(info->s->kfile,MYF(0)))
-	error = my_errno;
-      if (info->dfile >= 0 && my_close(info->dfile,MYF(0)))
-	error = my_errno;
-      info->s->kfile=info->dfile= -1;	/* Files aren't open anymore */
-      break;
-#endif
-    case HA_PANIC_READ:			/* Restore to before WRITE */
-#ifdef CANT_OPEN_FILES_TWICE
-      {					/* Open closed files */
-	char name_buff[FN_REFLEN];
-	if (info->s->kfile < 0)
-	  if ((info->s->kfile= my_open(fn_format(name_buff,info->filename,"",
-					      N_NAME_IEXT,4),info->mode,
-				    MYF(MY_WME))) < 0)
-	    error = my_errno;
-	if (info->dfile < 0)
-	{
-	  if ((info->dfile= my_open(fn_format(name_buff,info->filename,"",
-					      N_NAME_DEXT,4),info->mode,
-				    MYF(MY_WME))) < 0)
-	    error = my_errno;
-	  info->rec_cache.file=info->dfile;
-	}
-      }
-#endif
-      if (info->was_locked)
-      {
-	if (mi_lock_database(info, info->was_locked))
-	  error=my_errno;
-	info->was_locked=0;
-      }
-      break;
-    }
-  }
-  if (flag == HA_PANIC_CLOSE)
-  {
-    VOID(mi_log(0));				/* Close log if neaded */
-    ft_free_stopwords();
+  if (my_hash_inited(&myisam_open_table_hash))
+  {
+    if (flag == HA_PANIC_CLOSE)
+    {
+      while (myisam_open_table_hash.records)
+      {
+        /* 
+           As long as there are records in the hash, fetch the
+           first, and close it.
+        */
+        info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, 0);
+        pthread_mutex_unlock(&THR_LOCK_myisam);     /* Not exactly right... */
+        if (mi_close(info))
+          error= my_errno;
+        pthread_mutex_lock(&THR_LOCK_myisam);
+      }
+      (void) mi_log(0);				/* Close log if neaded */
+      ft_free_stopwords();
+    }
+    else
+    {
+      for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
+      {
+        info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
+        switch (flag) {
+        case HA_PANIC_CLOSE: break;      /* To eliminate warning.  Handled above */
+        case HA_PANIC_WRITE:		/* Do this to free databases */
+
+#ifdef CANT_OPEN_FILES_TWICE
+          if (info->s->options & HA_OPTION_READ_ONLY_DATA)
+            break;
+#endif
+          if (flush_key_blocks(info->s->key_cache, info->s->kfile, FLUSH_RELEASE))
+            error=my_errno;
+          if (info->opt_flag & WRITE_CACHE_USED)
+            if (flush_io_cache(&info->rec_cache))
+              error=my_errno;
+          if (info->opt_flag & READ_CACHE_USED)
+          {
+            if (flush_io_cache(&info->rec_cache))
+              error=my_errno;
+            reinit_io_cache(&info->rec_cache,READ_CACHE,0,
+                            (pbool) (info->lock_type != F_UNLCK),1);
+          }
+          if (info->lock_type != F_UNLCK && ! info->was_locked)
+          {
+            info->was_locked=info->lock_type;
+            if (mi_lock_database(info,F_UNLCK))
+              error=my_errno;
+          }
+#ifdef CANT_OPEN_FILES_TWICE
+          if (info->s->kfile >= 0 && mysql_file_close(info->s->kfile, MYF(0)))
+            error = my_errno;
+          if (info->dfile >= 0 && mysql_file_close(info->dfile, MYF(0)))
+            error = my_errno;
+          info->s->kfile=info->dfile= -1;	/* Files aren't open anymore */
+          break;
+#endif
+        case HA_PANIC_READ:			/* Restore to before WRITE */
+#ifdef CANT_OPEN_FILES_TWICE
+          {					/* Open closed files */
+            char name_buff[FN_REFLEN];
+            if (info->s->kfile < 0)
+              if ((info->s->kfile= mysql_file_open(mi_key_file_kfile,
+                                                   fn_format(name_buff,
+                                                             info->filename, "",
+                                                             N_NAME_IEXT, 4),
+                                                   info->mode, MYF(MY_WME))) < 0)
+                error = my_errno;
+            if (info->dfile < 0)
+            {
+              if ((info->dfile= mysql_file_open(mi_key_file_dfile,
+                                                fn_format(name_buff,
+                                                          info->filename, "",
+                                                          N_NAME_DEXT, 4),
+                                                info->mode, MYF(MY_WME))) < 0)
+                error = my_errno;
+              info->rec_cache.file=info->dfile;
+            }
+          }
+#endif
+          if (info->was_locked)
+          {
+            if (mi_lock_database(info, info->was_locked))
+              error=my_errno;
+            info->was_locked=0;
+          }
+          break;
+        }
+       }
+    }
   }
   pthread_mutex_unlock(&THR_LOCK_myisam);
   if (!error)
 
=== modified file 'Percona-Server/storage/myisam/mi_static.c'
--- Percona-Server/storage/myisam/mi_static.c	2011-06-30 15:37:13 +0000
+++ Percona-Server/storage/myisam/mi_static.c	2013-01-03 08:27:23 +0000
@@ -25,7 +25,7 @@
 #include "myisamdef.h"
 #endif
 
-LIST	*myisam_open_list=0;
+HASH    myisam_open_table_hash;
 uchar	NEAR myisam_file_magic[]=
 { (uchar) 254, (uchar) 254,'\007', '\001', };
 uchar	NEAR myisam_pack_file_magic[]=
 
=== modified file 'Percona-Server/storage/myisam/myisamchk.c'
--- Percona-Server/storage/myisam/myisamchk.c	2011-06-30 15:37:13 +0000
+++ Percona-Server/storage/myisam/myisamchk.c	2013-01-03 08:27:23 +0000
@@ -96,6 +96,12 @@
   get_options(&argc,(char***) &argv);
   myisam_quick_table_bits=decode_bits;
   error=0;
+  if (mi_init_open_table_hash(0))
+  {
+    fprintf(stderr, "Can't initialize MyISAM storage engine\n");
+    exit(-1);
+  }
+
   while (--argc >= 0)
   {
     int new_error=myisamchk(&check_param, *(argv++));
 
=== modified file 'Percona-Server/storage/myisam/myisamdef.h'
--- Percona-Server/storage/myisam/myisamdef.h	2011-11-24 02:01:56 +0000
+++ Percona-Server/storage/myisam/myisamdef.h	2013-01-03 08:27:23 +0000
@@ -26,6 +26,7 @@
 #else
 #include <my_no_pthread.h>
 #endif
+#include "hash.h"
 
 #if defined(my_write) && !defined(MAP_TO_USE_RAID)
 #undef my_write				/* undef map from my_nosys; We need test-if-disk full */
@@ -286,7 +287,6 @@
   uint	data_changed;			/* Somebody has changed data */
   uint	save_update;			/* When using KEY_READ */
   int	save_lastinx;
-  LIST	open_list;
   IO_CACHE rec_cache;			/* When cacheing records */
   uint  preload_buff_size;              /* When preloading indexes */
   myf lock_wait;			/* is 0 or MY_DONT_WAIT */
@@ -472,8 +472,7 @@
 #endif
 
 	/* Some extern variables */
-
-extern LIST *myisam_open_list;
+extern HASH myisam_open_table_hash;
 extern uchar NEAR myisam_file_magic[],NEAR myisam_pack_file_magic[];
 extern uint NEAR myisam_read_vec[],NEAR myisam_readnext_vec[];
 extern uint myisam_quick_table_bits;
@@ -759,6 +758,7 @@
 void mi_disable_non_unique_index(MI_INFO *info, ha_rows rows);
 
 extern MI_INFO *test_if_reopen(char *filename);
+extern int mi_init_open_table_hash(ulong size);
 my_bool check_table_is_closed(const char *name, const char *where);
 int mi_open_datafile(MI_INFO *info, MYISAM_SHARE *share, const char *orn_name,
                      File file_to_dup);
 
=== modified file 'Percona-Server/storage/myisam/myisampack.c'
--- Percona-Server/storage/myisam/myisampack.c	2011-06-30 15:37:13 +0000
+++ Percona-Server/storage/myisam/myisampack.c	2013-01-03 08:27:23 +0000
@@ -210,6 +210,11 @@
   MY_INIT(argv[0]);
 
   load_defaults("my",load_default_groups,&argc,&argv);
+  if (mi_init_open_table_hash(0))
+  {
+    fputs("Can't initialize MyISAM storage engine", stderr);
+    exit(1);
+  }
   default_argv= argv;
   get_options(&argc,&argv);