- disable response-time-distribution patch on arch which has no atomic builtins even...

[packages/mysql.git] / query_cache_enhance.patch

# name       : query_cache_with_comments.patch
# introduced : 11 or before
# maintainer : Oleg
#
#!!! notice !!!
# Any small change to this file in the main branch
# should be done or reviewed by the maintainer!
diff -ruN /dev/null b/patch_info/query_cache_enhance.patch
--- /dev/null	1970-01-01 00:00:00.000000000 +0000
+++ b/patch_info/query_cache_enhance.patch	2011-04-09 18:48:53.000000000 +0400
@@ -0,0 +1,15 @@
+File=query_cache_enhance.patch
+Name= query cache Percona's cumulative patch
+Version=1.0
+Author=Percona <info@percona.com>
+License=GPL
+Comment= 1) Add new status - Waiting on query cache mutex (status_wait_query_cache_mutex.patch)
+         2) Remove comments from query (need for cache hit) (query_cache_with_comments.patch)
+         3) Totally disable query cache (query_cache_totally_disable.info)
+2010-05 - First version avaliable (query_cache_with_comments.patch)
+2010-07 - First version avaliable (status_wait_query_cache_mutex.patch
+2010-07 - First version avaliable (query_cache_totally_disable.info)
+2010-07 - Fix crash (query_cache_with_comments.patch)
+2010-07 - Fix incorrect behavior diff (query_cache_with_comments.patch)
+2010-09 - Merge patches to one
+2010-11 - Ported to 5.5
diff -ruN a/sql/mysqld.cc b/sql/mysqld.cc
--- a/sql/mysqld.cc	2011-04-09 18:48:50.000000000 +0400
+++ b/sql/mysqld.cc	2011-04-09 18:48:53.000000000 +0400
@@ -903,6 +903,7 @@
 #endif
 #ifdef HAVE_QUERY_CACHE
 ulong query_cache_min_res_unit= QUERY_CACHE_MIN_RESULT_DATA_SIZE;
+my_bool opt_query_cache_strip_comments= FALSE;
 Query_cache query_cache;
 #endif
 #ifdef HAVE_SMEM
diff -ruN a/sql/mysqld.h b/sql/mysqld.h
--- a/sql/mysqld.h	2011-04-09 18:48:50.000000000 +0400
+++ b/sql/mysqld.h	2011-04-09 18:48:53.000000000 +0400
@@ -91,6 +91,7 @@
 extern my_bool opt_log, opt_slow_log;
 extern my_bool opt_backup_history_log;
 extern my_bool opt_backup_progress_log;
+extern my_bool opt_query_cache_strip_comments;
 extern ulonglong log_output_options;
 extern ulong log_backup_output_options;
 extern my_bool opt_log_queries_not_using_indexes;
diff -ruN /dev/null b/sql/query_strip_comments.h
--- /dev/null	1970-01-01 00:00:00.000000000 +0000
+++ b/sql/query_strip_comments.h	2011-04-09 18:48:53.000000000 +0400
@@ -0,0 +1,37 @@
+#ifndef _SQL_QUERY_STRIPC_COMMENTS_H_
+#define _SQL_QUERY_STRIPC_COMMENTS_H_
+#ifdef HAVE_QUERY_CACHE
+
+// implemented in sql_cache.cc
+class QueryStripComments
+{
+private:
+  QueryStripComments(const QueryStripComments&);
+  QueryStripComments& operator=(const QueryStripComments&);
+public:
+  QueryStripComments();
+  ~QueryStripComments();
+  void set(const char* a_query, uint a_query_length, uint a_additional_length);
+  
+  char* query()        { return buffer; }
+  uint  query_length() { return length; }
+private:
+  void cleanup();
+private:
+  char* buffer;
+  uint  length /*query length, not buffer length*/;
+  uint  buffer_length;
+};
+class QueryStripComments_Backup
+{
+public:
+  QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc);
+  ~QueryStripComments_Backup();
+private:
+  THD*  thd;
+  char* query;
+  uint  length;
+};
+
+#endif // HAVE_QUERY_CACHE
+#endif // _SQL_QUERY_STRIPC_COMMENTS_H_
diff -ruN a/sql/sql_cache.cc b/sql/sql_cache.cc
--- a/sql/sql_cache.cc	2011-04-09 18:48:50.000000000 +0400
+++ b/sql/sql_cache.cc	2011-04-09 18:48:53.000000000 +0400
@@ -344,6 +344,181 @@
 #include "probes_mysql.h"
 #include "transaction.h"
 
+#include "query_strip_comments.h"
+
+QueryStripComments::QueryStripComments()
+{
+  buffer = 0;
+  length = 0;
+  buffer_length = 0;
+}
+QueryStripComments::~QueryStripComments()
+{
+  cleanup();
+}
+
+inline bool query_strip_comments_is_white_space(char c)
+{
+  return ((' ' == c) || ('\t' == c) || ('\r' == c) || ('\n' ==c ));
+}
+void QueryStripComments::set(const char* query, uint query_length, uint additional_length)
+{
+  uint new_buffer_length = query_length + additional_length;
+  if(new_buffer_length > buffer_length)
+  {
+    cleanup();
+    buffer = (char*)my_malloc(new_buffer_length,MYF(0));
+  }
+  uint query_position = 0;
+  uint position = 0;
+  // Skip whitespaces from begin
+  while((query_position < query_length) && query_strip_comments_is_white_space(query[query_position]))
+  {
+    ++query_position;
+  }
+  long int last_space = -1;
+  while(query_position < query_length)
+  {
+    char current = query[query_position];
+    bool insert_space = false; // insert space to buffer, (IMPORTANT) don't update query_position
+    switch(current)
+    {
+    case '\'':
+    case '"':
+      {
+        buffer[position++] = query[query_position++]; // copy current symbol
+        while(query_position < query_length)
+        {
+          if(current == query[query_position]) // found pair quote
+          {
+            break;
+          }
+          buffer[position++] = query[query_position++]; // copy current symbol
+        }
+        break;
+      }
+    case '/':
+      {
+        if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2]))
+        {
+          query_position += 2; // skip "/*"
+          do
+          {
+            if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/"
+            {
+              query_position += 2; // skip "*/"
+              insert_space = true;
+              break;
+            }
+            else
+            {
+              ++query_position;
+            }
+          }
+          while(query_position < query_length);
+          if(!insert_space)
+          {
+            continue;
+          }
+        }
+        break;
+      }
+    case '-':
+      {
+        if(query[query_position+1] == '-')
+        {
+          ++query_position; // skip "-", and go to search of "\n"
+        }
+        else
+        {
+          break;
+        }
+      }
+    case '#':
+      {
+        do
+        {
+          ++query_position; // skip current symbol (# or -)
+          if('\n' == query[query_position])  // check for '\n'
+          {
+            ++query_position; // skip '\n'
+            insert_space = true;
+            break;
+          }
+        }
+        while(query_position < query_length);
+        if(insert_space)
+        {
+          break;
+        }
+        else
+        {
+          continue;
+        }
+      }
+    default:
+      if(query_strip_comments_is_white_space(current))
+      {
+        insert_space = true;
+        ++query_position;
+      }
+      break; // make gcc happy
+    }
+    if(insert_space)
+    {
+      if((last_space + 1) != position)
+      {
+        last_space = position;
+        buffer[position++] = ' ';
+      }
+    }
+    else
+    {
+      buffer[position++] = query[query_position++];
+    }
+  }
+  while((0 < position) && query_strip_comments_is_white_space(buffer[position - 1]))
+  {
+    --position;
+  }
+  buffer[position] = 0;
+  length = position;
+}
+void QueryStripComments::cleanup()
+{
+  if(buffer)
+  {
+    my_free(buffer);
+  }
+  buffer        = 0;
+  length        = 0;
+  buffer_length = 0;
+}
+QueryStripComments_Backup::QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc)
+{
+  if(opt_query_cache_strip_comments)
+  {
+    thd = a_thd;
+    query = thd->query();
+    length = thd->query_length();
+    qsc->set(query,length,thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE);
+    thd->set_query(qsc->query(),qsc->query_length());
+  }
+  else
+  {
+    thd = 0;
+    query = 0;
+    length = 0;
+  }
+}
+QueryStripComments_Backup::~QueryStripComments_Backup()
+{
+  if(thd)
+  {
+    thd->set_query(query,length);
+  }
+}
+
 #ifdef EMBEDDED_LIBRARY
 #include "emb_qcache.h"
 #endif
@@ -454,7 +629,12 @@
   Query_cache_wait_state wait_state(thd, __func__, __FILE__, __LINE__);
   DBUG_ENTER("Query_cache::try_lock");
 
+  const char* old_proc_info= thd->proc_info;
+  thd_proc_info(thd,"Waiting on query cache mutex");
   mysql_mutex_lock(&structure_guard_mutex);
+  DBUG_EXECUTE_IF("status_wait_query_cache_mutex_sleep", {
+      sleep(5);
+    });
   while (1)
   {
     if (m_cache_lock_status == Query_cache::UNLOCKED)
@@ -501,6 +681,7 @@
     }
   }
   mysql_mutex_unlock(&structure_guard_mutex);
+  thd->proc_info = old_proc_info;
 
   DBUG_RETURN(interrupt);
 }
@@ -1274,6 +1455,8 @@
       unlock();
       DBUG_VOID_RETURN;
     }
+    QueryStripComments *query_strip_comments = &(thd->query_strip_comments);
+    QueryStripComments_Backup backup(thd,query_strip_comments);
 
     /* Key is query + database + flag */
     if (thd->db_length)
@@ -1451,6 +1634,9 @@
   Query_cache_block_table *block_table, *block_table_end;
   ulong tot_length;
   Query_cache_query_flags flags;
+  QueryStripComments *query_strip_comments = &(thd->query_strip_comments);
+  char *sql_backup          = sql;
+  uint  query_length_backup = query_length;
   DBUG_ENTER("Query_cache::send_result_to_client");
 
   /*
@@ -1472,21 +1658,103 @@
 
   {
     uint i= 0;
-    /*
-      Skip '(' characters in queries like following:
-      (select a from t1) union (select a from t1);
-    */
-    while (sql[i]=='(')
-      i++;
+    if(opt_query_cache_strip_comments)
+    {
+      /* Skip all comments and non-letter symbols */
+      uint& query_position = i;
+      char* query = sql;
+      while(query_position < query_length)
+      {
+        bool check = false;
+        char current = query[query_position];
+        switch(current)
+        {
+        case '/':
+          if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2]))
+          {
+            query_position += 2; // skip "/*"
+            do
+            {
+              if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/" (without space)
+              {
+                query_position += 2; // skip "*/" (without space)
+                break;
+              }
+              else
+              {
+                ++query_position;
+              }
+            }
+            while(query_position < query_length);
+            continue; // analyze current symbol
+          }
+          break;
+        case '-':
+          if(query[query_position+1] == '-')
+          {
+            ++query_position; // skip "-"
+          }
+          else
+          {
+            break;
+          }
+        case '#':
+          do
+          {
+            ++query_position; // skip current symbol
+            if('\n' == query[query_position])  // check for '\n'
+            {
+              ++query_position; // skip '\n'
+              break;
+            }
+          }
+          while(query_position < query_length);
+          continue; // analyze current symbol
+        case '\r':
+        case '\n':
+        case '\t':
+        case ' ':
+        case '(':
+        case ')':
+          break;
+        default:
+          check = true;
+          break; // make gcc happy
+        } // switch(current)
+        if(check)
+        {
+          if(query_position + 2 < query_length)
+          {
+            // cacheable
+            break;
+          }
+          else
+          {
+            DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached"));
+            goto err;
+          }
+        } // if(check)
+        ++query_position;
+      } // while(query_position < query_length)
+    }
+    else // if(opt_query_cache_strip_comments)
+    {
+      /*
+        Skip '(' characters in queries like following:
+        (select a from t1) union (select a from t1);
+      */
+      while (sql[i]=='(')
+        i++;
 
-    /*
-      Test if the query is a SELECT
-      (pre-space is removed in dispatch_command).
+    } // if(opt_query_cache_strip_comments)    
+      /*
+        Test if the query is a SELECT
+        (pre-space is removed in dispatch_command).
 
-      First '/' looks like comment before command it is not
-      frequently appeared in real life, consequently we can
-      check all such queries, too.
-    */
+        First '/' looks like comment before command it is not
+        frequently appeared in real life, consequently we can
+        check all such queries, too.
+      */
     if ((my_toupper(system_charset_info, sql[i])     != 'S' ||
          my_toupper(system_charset_info, sql[i + 1]) != 'E' ||
          my_toupper(system_charset_info, sql[i + 2]) != 'L') &&
@@ -1521,6 +1789,12 @@
     goto err_unlock;
 
   Query_cache_block *query_block;
+  if(opt_query_cache_strip_comments)
+  {
+    query_strip_comments->set(sql, query_length, thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE);
+    sql          = query_strip_comments->query();
+    query_length = query_strip_comments->query_length();
+  }
 
   tot_length= query_length + thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE;
   if (thd->db_length)
@@ -1587,6 +1861,8 @@
 	 (uchar*) &flags, QUERY_CACHE_FLAGS_SIZE);
   query_block = (Query_cache_block *)  my_hash_search(&queries, (uchar*) sql,
                                                       tot_length);
+  sql          = sql_backup;
+  query_length = query_length_backup;
   /* Quick abort on unlocked data */
   if (query_block == 0 ||
       query_block->query()->result() == 0 ||
diff -ruN a/sql/sql_class.h b/sql/sql_class.h
--- a/sql/sql_class.h	2011-04-09 18:48:50.000000000 +0400
+++ b/sql/sql_class.h	2011-04-09 18:48:53.000000000 +0400
@@ -40,6 +40,9 @@
 #include "thr_lock.h"             /* thr_lock_type, THR_LOCK_DATA,
                                      THR_LOCK_INFO */
 
+#ifdef HAVE_QUERY_CACHE
+#include "query_strip_comments.h"
+#endif // HAVE_QUERY_CACHE
 
 class Reprepare_observer;
 class Relay_log_info;
@@ -765,6 +768,9 @@
     statement lifetime. FIXME: must be const
   */
    ulong id;
+#ifdef HAVE_QUERY_CACHE
+  QueryStripComments query_strip_comments; // see sql_cache.cc
+#endif //HAVE_QUERY_CACHE
 
   /*
     MARK_COLUMNS_NONE:  Means mark_used_colums is not set and no indicator to
diff -ruN a/sql/sys_vars.cc b/sql/sys_vars.cc
--- a/sql/sys_vars.cc	2011-04-09 18:48:50.000000000 +0400
+++ b/sql/sys_vars.cc	2011-04-09 18:48:53.000000000 +0400
@@ -1750,6 +1750,11 @@
        NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(fix_query_cache_size));
 
+static Sys_var_mybool Sys_query_cache_strip_comments(
+       "query_cache_strip_comments", "Enable and disable optimisation \"strip comment for query cache\" - optimisation strip all comments from query while search query result in query cache",
+       GLOBAL_VAR(opt_query_cache_strip_comments), CMD_LINE(OPT_ARG),
+       DEFAULT(FALSE));
+
 static Sys_var_ulong Sys_query_cache_limit(
        "query_cache_limit",
        "Don't cache results that are bigger than this",