query_cache_enhance.patch

   1 # name       : query_cache_with_comments.patch
   2 # introduced : 11 or before
   3 # maintainer : Oleg
   4 #
   5 #!!! notice !!!
   6 # Any small change to this file in the main branch
   7 # should be done or reviewed by the maintainer!
   8 diff -ruN /dev/null b/patch_info/query_cache_enhance.patch
   9 --- /dev/null   1970-01-01 00:00:00.000000000 +0000
  10 +++ b/patch_info/query_cache_enhance.patch      2011-04-09 18:48:53.000000000 +0400
  11 @@ -0,0 +1,15 @@
  12 +File=query_cache_enhance.patch
  13 +Name= query cache Percona's cumulative patch
  14 +Version=1.0
  15 +Author=Percona <info@percona.com>
  16 +License=GPL
  17 +Comment= 1) Add new status - Waiting on query cache mutex (status_wait_query_cache_mutex.patch)
  18 +         2) Remove comments from query (need for cache hit) (query_cache_with_comments.patch)
  19 +         3) Totally disable query cache (query_cache_totally_disable.info)
  20 +2010-05 - First version avaliable (query_cache_with_comments.patch)
  21 +2010-07 - First version avaliable (status_wait_query_cache_mutex.patch
  22 +2010-07 - First version avaliable (query_cache_totally_disable.info)
  23 +2010-07 - Fix crash (query_cache_with_comments.patch)
  24 +2010-07 - Fix incorrect behavior diff (query_cache_with_comments.patch)
  25 +2010-09 - Merge patches to one
  26 +2010-11 - Ported to 5.5
  27 diff -ruN a/sql/mysqld.cc b/sql/mysqld.cc
  28 --- a/sql/mysqld.cc     2011-04-09 18:48:50.000000000 +0400
  29 +++ b/sql/mysqld.cc     2011-04-09 18:48:53.000000000 +0400
  30 @@ -899,6 +899,7 @@
  31  #endif
  32  #ifdef HAVE_QUERY_CACHE
  33  ulong query_cache_min_res_unit= QUERY_CACHE_MIN_RESULT_DATA_SIZE;
  34 +my_bool opt_query_cache_strip_comments= FALSE;
  35  Query_cache query_cache;
  36  #endif
  37  #ifdef HAVE_SMEM
  38 diff -ruN a/sql/mysqld.h b/sql/mysqld.h
  39 --- a/sql/mysqld.h      2011-04-09 18:48:50.000000000 +0400
  40 +++ b/sql/mysqld.h      2011-04-09 18:48:53.000000000 +0400
  41 @@ -91,6 +91,7 @@
  42  extern my_bool opt_log, opt_slow_log;
  43  extern my_bool opt_backup_history_log;
  44  extern my_bool opt_backup_progress_log;
  45 +extern my_bool opt_query_cache_strip_comments;
  46  extern ulonglong log_output_options;
  47  extern ulong log_backup_output_options;
  48  extern my_bool opt_log_queries_not_using_indexes;
  49 diff -ruN /dev/null b/sql/query_strip_comments.h
  50 --- /dev/null   1970-01-01 00:00:00.000000000 +0000
  51 +++ b/sql/query_strip_comments.h        2011-04-09 18:48:53.000000000 +0400
  52 @@ -0,0 +1,37 @@
  53 +#ifndef _SQL_QUERY_STRIPC_COMMENTS_H_
  54 +#define _SQL_QUERY_STRIPC_COMMENTS_H_
  55 +#ifdef HAVE_QUERY_CACHE
  56 +
  57 +// implemented in sql_cache.cc
  58 +class QueryStripComments
  59 +{
  60 +private:
  61 +  QueryStripComments(const QueryStripComments&);
  62 +  QueryStripComments& operator=(const QueryStripComments&);
  63 +public:
  64 +  QueryStripComments();
  65 +  ~QueryStripComments();
  66 +  void set(const char* a_query, uint a_query_length, uint a_additional_length);
  67 +
  68 +  char* query()        { return buffer; }
  69 +  uint  query_length() { return length; }
  70 +private:
  71 +  void cleanup();
  72 +private:
  73 +  char* buffer;
  74 +  uint  length /*query length, not buffer length*/;
  75 +  uint  buffer_length;
  76 +};
  77 +class QueryStripComments_Backup
  78 +{
  79 +public:
  80 +  QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc);
  81 +  ~QueryStripComments_Backup();
  82 +private:
  83 +  THD*  thd;
  84 +  char* query;
  85 +  uint  length;
  86 +};
  87 +
  88 +#endif // HAVE_QUERY_CACHE
  89 +#endif // _SQL_QUERY_STRIPC_COMMENTS_H_
  90 diff -ruN a/sql/sql_cache.cc b/sql/sql_cache.cc
  91 --- a/sql/sql_cache.cc  2011-04-09 18:48:50.000000000 +0400
  92 +++ b/sql/sql_cache.cc  2011-04-09 18:48:53.000000000 +0400
  93 @@ -344,6 +344,181 @@
  94  #include "probes_mysql.h"
  95  #include "transaction.h"
  96
  97 +#include "query_strip_comments.h"
  98 +
  99 +QueryStripComments::QueryStripComments()
 100 +{
 101 +  buffer = 0;
 102 +  length = 0;
 103 +  buffer_length = 0;
 104 +}
 105 +QueryStripComments::~QueryStripComments()
 106 +{
 107 +  cleanup();
 108 +}
 109 +
 110 +inline bool query_strip_comments_is_white_space(char c)
 111 +{
 112 +  return ((' ' == c) || ('\t' == c) || ('\r' == c) || ('\n' ==c ));
 113 +}
 114 +void QueryStripComments::set(const char* query, uint query_length, uint additional_length)
 115 +{
 116 +  uint new_buffer_length = query_length + additional_length;
 117 +  if(new_buffer_length > buffer_length)
 118 +  {
 119 +    cleanup();
 120 +    buffer = (char*)my_malloc(new_buffer_length,MYF(0));
 121 +  }
 122 +  uint query_position = 0;
 123 +  uint position = 0;
 124 +  // Skip whitespaces from begin
 125 +  while((query_position < query_length) && query_strip_comments_is_white_space(query[query_position]))
 126 +  {
 127 +    ++query_position;
 128 +  }
 129 +  long int last_space = -1;
 130 +  while(query_position < query_length)
 131 +  {
 132 +    char current = query[query_position];
 133 +    bool insert_space = false; // insert space to buffer, (IMPORTANT) don't update query_position
 134 +    switch(current)
 135 +    {
 136 +    case '\'':
 137 +    case '"':
 138 +      {
 139 +        buffer[position++] = query[query_position++]; // copy current symbol
 140 +        while(query_position < query_length)
 141 +        {
 142 +          if(current == query[query_position]) // found pair quote
 143 +          {
 144 +            break;
 145 +          }
 146 +          buffer[position++] = query[query_position++]; // copy current symbol
 147 +        }
 148 +        break;
 149 +      }
 150 +    case '/':
 151 +      {
 152 +        if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2]))
 153 +        {
 154 +          query_position += 2; // skip "/*"
 155 +          do
 156 +          {
 157 +            if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/"
 158 +            {
 159 +              query_position += 2; // skip "*/"
 160 +              insert_space = true;
 161 +              break;
 162 +            }
 163 +            else
 164 +            {
 165 +              ++query_position;
 166 +            }
 167 +          }
 168 +          while(query_position < query_length);
 169 +          if(!insert_space)
 170 +          {
 171 +            continue;
 172 +          }
 173 +        }
 174 +        break;
 175 +      }
 176 +    case '-':
 177 +      {
 178 +        if(query[query_position+1] == '-')
 179 +        {
 180 +          ++query_position; // skip "-", and go to search of "\n"
 181 +        }
 182 +        else
 183 +        {
 184 +          break;
 185 +        }
 186 +      }
 187 +    case '#':
 188 +      {
 189 +        do
 190 +        {
 191 +          ++query_position; // skip current symbol (# or -)
 192 +          if('\n' == query[query_position])  // check for '\n'
 193 +          {
 194 +            ++query_position; // skip '\n'
 195 +            insert_space = true;
 196 +            break;
 197 +          }
 198 +        }
 199 +        while(query_position < query_length);
 200 +        if(insert_space)
 201 +        {
 202 +          break;
 203 +        }
 204 +        else
 205 +        {
 206 +          continue;
 207 +        }
 208 +      }
 209 +    default:
 210 +      if(query_strip_comments_is_white_space(current))
 211 +      {
 212 +        insert_space = true;
 213 +        ++query_position;
 214 +      }
 215 +      break; // make gcc happy
 216 +    }
 217 +    if(insert_space)
 218 +    {
 219 +      if((last_space + 1) != position)
 220 +      {
 221 +        last_space = position;
 222 +        buffer[position++] = ' ';
 223 +      }
 224 +    }
 225 +    else
 226 +    {
 227 +      buffer[position++] = query[query_position++];
 228 +    }
 229 +  }
 230 +  while((0 < position) && query_strip_comments_is_white_space(buffer[position - 1]))
 231 +  {
 232 +    --position;
 233 +  }
 234 +  buffer[position] = 0;
 235 +  length = position;
 236 +}
 237 +void QueryStripComments::cleanup()
 238 +{
 239 +  if(buffer)
 240 +  {
 241 +    my_free(buffer);
 242 +  }
 243 +  buffer        = 0;
 244 +  length        = 0;
 245 +  buffer_length = 0;
 246 +}
 247 +QueryStripComments_Backup::QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc)
 248 +{
 249 +  if(opt_query_cache_strip_comments)
 250 +  {
 251 +    thd = a_thd;
 252 +    query = thd->query();
 253 +    length = thd->query_length();
 254 +    qsc->set(query,length,thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE);
 255 +    thd->set_query(qsc->query(),qsc->query_length());
 256 +  }
 257 +  else
 258 +  {
 259 +    thd = 0;
 260 +    query = 0;
 261 +    length = 0;
 262 +  }
 263 +}
 264 +QueryStripComments_Backup::~QueryStripComments_Backup()
 265 +{
 266 +  if(thd)
 267 +  {
 268 +    thd->set_query(query,length);
 269 +  }
 270 +}
 271 +
 272  #ifdef EMBEDDED_LIBRARY
 273  #include "emb_qcache.h"
 274  #endif
 275 @@ -454,7 +629,12 @@
 276    Query_cache_wait_state wait_state(thd, __func__, __FILE__, __LINE__);
 277    DBUG_ENTER("Query_cache::try_lock");
 278
 279 +  const char* old_proc_info= thd->proc_info;
 280 +  thd_proc_info(thd,"Waiting on query cache mutex");
 281    mysql_mutex_lock(&structure_guard_mutex);
 282 +  DBUG_EXECUTE_IF("status_wait_query_cache_mutex_sleep", {
 283 +      sleep(5);
 284 +    });
 285    while (1)
 286    {
 287      if (m_cache_lock_status == Query_cache::UNLOCKED)
 288 @@ -501,6 +681,7 @@
 289      }
 290    }
 291    mysql_mutex_unlock(&structure_guard_mutex);
 292 +  thd->proc_info = old_proc_info;
 293
 294    DBUG_RETURN(interrupt);
 295  }
 296 @@ -1274,6 +1455,8 @@
 297        unlock();
 298        DBUG_VOID_RETURN;
 299      }
 300 +    QueryStripComments *query_strip_comments = &(thd->query_strip_comments);
 301 +    QueryStripComments_Backup backup(thd,query_strip_comments);
 302
 303      /* Key is query + database + flag */
 304      if (thd->db_length)
 305 @@ -1451,6 +1634,9 @@
 306    Query_cache_block_table *block_table, *block_table_end;
 307    ulong tot_length;
 308    Query_cache_query_flags flags;
 309 +  QueryStripComments *query_strip_comments = &(thd->query_strip_comments);
 310 +  char *sql_backup          = sql;
 311 +  uint  query_length_backup = query_length;
 312    DBUG_ENTER("Query_cache::send_result_to_client");
 313
 314    /*
 315 @@ -1472,21 +1658,103 @@
 316
 317    {
 318      uint i= 0;
 319 -    /*
 320 -      Skip '(' characters in queries like following:
 321 -      (select a from t1) union (select a from t1);
 322 -    */
 323 -    while (sql[i]=='(')
 324 -      i++;
 325 +    if(opt_query_cache_strip_comments)
 326 +    {
 327 +      /* Skip all comments and non-letter symbols */
 328 +      uint& query_position = i;
 329 +      char* query = sql;
 330 +      while(query_position < query_length)
 331 +      {
 332 +        bool check = false;
 333 +        char current = query[query_position];
 334 +        switch(current)
 335 +        {
 336 +        case '/':
 337 +          if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2]))
 338 +          {
 339 +            query_position += 2; // skip "/*"
 340 +            do
 341 +            {
 342 +              if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/" (without space)
 343 +              {
 344 +                query_position += 2; // skip "*/" (without space)
 345 +                break;
 346 +              }
 347 +              else
 348 +              {
 349 +                ++query_position;
 350 +              }
 351 +            }
 352 +            while(query_position < query_length);
 353 +            continue; // analyze current symbol
 354 +          }
 355 +          break;
 356 +        case '-':
 357 +          if(query[query_position+1] == '-')
 358 +          {
 359 +            ++query_position; // skip "-"
 360 +          }
 361 +          else
 362 +          {
 363 +            break;
 364 +          }
 365 +        case '#':
 366 +          do
 367 +          {
 368 +            ++query_position; // skip current symbol
 369 +            if('\n' == query[query_position])  // check for '\n'
 370 +            {
 371 +              ++query_position; // skip '\n'
 372 +              break;
 373 +            }
 374 +          }
 375 +          while(query_position < query_length);
 376 +          continue; // analyze current symbol
 377 +        case '\r':
 378 +        case '\n':
 379 +        case '\t':
 380 +        case ' ':
 381 +        case '(':
 382 +        case ')':
 383 +          break;
 384 +        default:
 385 +          check = true;
 386 +          break; // make gcc happy
 387 +        } // switch(current)
 388 +        if(check)
 389 +        {
 390 +          if(query_position + 2 < query_length)
 391 +          {
 392 +            // cacheable
 393 +            break;
 394 +          }
 395 +          else
 396 +          {
 397 +            DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached"));
 398 +            goto err;
 399 +          }
 400 +        } // if(check)
 401 +        ++query_position;
 402 +      } // while(query_position < query_length)
 403 +    }
 404 +    else // if(opt_query_cache_strip_comments)
 405 +    {
 406 +      /*
 407 +        Skip '(' characters in queries like following:
 408 +        (select a from t1) union (select a from t1);
 409 +      */
 410 +      while (sql[i]=='(')
 411 +        i++;
 412
 413 -    /*
 414 -      Test if the query is a SELECT
 415 -      (pre-space is removed in dispatch_command).
 416 +    } // if(opt_query_cache_strip_comments)
 417 +      /*
 418 +        Test if the query is a SELECT
 419 +        (pre-space is removed in dispatch_command).
 420
 421 -      First '/' looks like comment before command it is not
 422 -      frequently appeared in real life, consequently we can
 423 -      check all such queries, too.
 424 -    */
 425 +        First '/' looks like comment before command it is not
 426 +        frequently appeared in real life, consequently we can
 427 +        check all such queries, too.
 428 +      */
 429      if ((my_toupper(system_charset_info, sql[i])     != 'S' ||
 430           my_toupper(system_charset_info, sql[i + 1]) != 'E' ||
 431           my_toupper(system_charset_info, sql[i + 2]) != 'L') &&
 432 @@ -1521,6 +1789,12 @@
 433      goto err_unlock;
 434
 435    Query_cache_block *query_block;
 436 +  if(opt_query_cache_strip_comments)
 437 +  {
 438 +    query_strip_comments->set(sql, query_length, thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE);
 439 +    sql          = query_strip_comments->query();
 440 +    query_length = query_strip_comments->query_length();
 441 +  }
 442
 443    tot_length= query_length + thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE;
 444    if (thd->db_length)
 445 @@ -1587,6 +1861,8 @@
 446          (uchar*) &flags, QUERY_CACHE_FLAGS_SIZE);
 447    query_block = (Query_cache_block *)  my_hash_search(&queries, (uchar*) sql,
 448                                                        tot_length);
 449 +  sql          = sql_backup;
 450 +  query_length = query_length_backup;
 451    /* Quick abort on unlocked data */
 452    if (query_block == 0 ||
 453        query_block->query()->result() == 0 ||
 454 diff -ruN a/sql/sql_class.h b/sql/sql_class.h
 455 --- a/sql/sql_class.h   2011-04-09 18:48:50.000000000 +0400
 456 +++ b/sql/sql_class.h   2011-04-09 18:48:53.000000000 +0400
 457 @@ -40,6 +40,9 @@
 458  #include "thr_lock.h"             /* thr_lock_type, THR_LOCK_DATA,
 459                                       THR_LOCK_INFO */
 460
 461 +#ifdef HAVE_QUERY_CACHE
 462 +#include "query_strip_comments.h"
 463 +#endif // HAVE_QUERY_CACHE
 464
 465  class Reprepare_observer;
 466  class Relay_log_info;
 467 @@ -760,6 +763,9 @@
 468      statement lifetime. FIXME: must be const
 469    */
 470     ulong id;
 471 +#ifdef HAVE_QUERY_CACHE
 472 +  QueryStripComments query_strip_comments; // see sql_cache.cc
 473 +#endif //HAVE_QUERY_CACHE
 474
 475    /*
 476      MARK_COLUMNS_NONE:  Means mark_used_colums is not set and no indicator to
 477 diff -ruN a/sql/sys_vars.cc b/sql/sys_vars.cc
 478 --- a/sql/sys_vars.cc   2011-04-09 18:48:50.000000000 +0400
 479 +++ b/sql/sys_vars.cc   2011-04-09 18:48:53.000000000 +0400
 480 @@ -1786,6 +1786,11 @@
 481         NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
 482         ON_UPDATE(fix_query_cache_size));
 483
 484 +static Sys_var_mybool Sys_query_cache_strip_comments(
 485 +       "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",
 486 +       GLOBAL_VAR(opt_query_cache_strip_comments), CMD_LINE(OPT_ARG),
 487 +       DEFAULT(FALSE));
 488 +
 489  static Sys_var_ulong Sys_query_cache_limit(
 490         "query_cache_limit",
 491         "Don't cache results that are bigger than this",