1 # name : query_cache_with_comments.patch
2 # introduced : 11 or before
6 # Any small change to this file in the main branch
7 # should be done or reviewed by the maintainer!
9 +++ b/patch_info/query_cache_enhance.patch
11 +File=query_cache_enhance.patch
12 +Name= query cache Percona's cumulative patch
14 +Author=Percona <info@percona.com>
16 +Comment= 1) Add new status - Waiting on query cache mutex (status_wait_query_cache_mutex.patch)
17 + 2) Remove comments from query (need for cache hit) (query_cache_with_comments.patch)
18 + 3) Totally disable query cache (query_cache_totally_disable.info)
19 +2010-05 - First version avaliable (query_cache_with_comments.patch)
20 +2010-07 - First version avaliable (status_wait_query_cache_mutex.patch
21 +2010-07 - First version avaliable (query_cache_totally_disable.info)
22 +2010-07 - Fix crash (query_cache_with_comments.patch)
23 +2010-07 - Fix incorrect behavior diff (query_cache_with_comments.patch)
24 +2010-09 - Merge patches to one
25 +2010-11 - Ported to 5.5
30 #ifdef HAVE_QUERY_CACHE
31 ulong query_cache_min_res_unit= QUERY_CACHE_MIN_RESULT_DATA_SIZE;
32 +my_bool opt_query_cache_strip_comments= FALSE;
33 Query_cache query_cache;
39 extern my_bool opt_log, opt_slow_log;
40 extern my_bool opt_backup_history_log;
41 extern my_bool opt_backup_progress_log;
42 +extern my_bool opt_query_cache_strip_comments;
43 extern ulonglong log_output_options;
44 extern ulong log_backup_output_options;
45 extern my_bool opt_log_queries_not_using_indexes;
47 +++ b/sql/query_strip_comments.h
49 +#ifndef _SQL_QUERY_STRIPC_COMMENTS_H_
50 +#define _SQL_QUERY_STRIPC_COMMENTS_H_
51 +#ifdef HAVE_QUERY_CACHE
53 +// implemented in sql_cache.cc
54 +class QueryStripComments
57 + QueryStripComments(const QueryStripComments&);
58 + QueryStripComments& operator=(const QueryStripComments&);
60 + QueryStripComments();
61 + ~QueryStripComments();
62 + void set(const char* a_query, uint a_query_length, uint a_additional_length);
64 + char* query() { return buffer; }
65 + uint query_length() { return length; }
70 + uint length /*query length, not buffer length*/;
73 +class QueryStripComments_Backup
76 + QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc);
77 + ~QueryStripComments_Backup();
84 +#endif // HAVE_QUERY_CACHE
85 +#endif // _SQL_QUERY_STRIPC_COMMENTS_H_
86 --- a/sql/sql_cache.cc
87 +++ b/sql/sql_cache.cc
89 #include "probes_mysql.h"
90 #include "transaction.h"
92 +#include "query_strip_comments.h"
94 +QueryStripComments::QueryStripComments()
100 +QueryStripComments::~QueryStripComments()
105 +inline bool query_strip_comments_is_white_space(char c)
107 + return ((' ' == c) || ('\t' == c) || ('\r' == c) || ('\n' ==c ));
109 +void QueryStripComments::set(const char* query, uint query_length, uint additional_length)
111 + uint new_buffer_length = query_length + additional_length;
112 + if(new_buffer_length > buffer_length)
115 + buffer = (char*)my_malloc(new_buffer_length,MYF(0));
117 + uint query_position = 0;
119 + // Skip whitespaces from begin
120 + while((query_position < query_length) && query_strip_comments_is_white_space(query[query_position]))
124 + long int last_space = -1;
125 + while(query_position < query_length)
127 + char current = query[query_position];
128 + bool insert_space = false; // insert space to buffer, (IMPORTANT) don't update query_position
134 + buffer[position++] = query[query_position++]; // copy current symbol
135 + while(query_position < query_length)
137 + if(current == query[query_position]) // found pair quote
141 + buffer[position++] = query[query_position++]; // copy current symbol
147 + if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2]))
149 + query_position += 2; // skip "/*"
152 + if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/"
154 + query_position += 2; // skip "*/"
155 + insert_space = true;
163 + while(query_position < query_length);
173 + if(query[query_position+1] == '-')
175 + ++query_position; // skip "-", and go to search of "\n"
186 + ++query_position; // skip current symbol (# or -)
187 + if('\n' == query[query_position]) // check for '\n'
189 + ++query_position; // skip '\n'
190 + insert_space = true;
194 + while(query_position < query_length);
205 + if(query_strip_comments_is_white_space(current))
207 + insert_space = true;
210 + break; // make gcc happy
214 + if((last_space + 1) != position)
216 + last_space = position;
217 + buffer[position++] = ' ';
222 + buffer[position++] = query[query_position++];
225 + while((0 < position) && query_strip_comments_is_white_space(buffer[position - 1]))
229 + buffer[position] = 0;
232 +void QueryStripComments::cleanup()
242 +QueryStripComments_Backup::QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc)
244 + if(opt_query_cache_strip_comments)
247 + query = thd->query();
248 + length = thd->query_length();
249 + qsc->set(query,length,thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE);
250 + thd->set_query(qsc->query(),qsc->query_length());
259 +QueryStripComments_Backup::~QueryStripComments_Backup()
263 + thd->set_query(query,length);
267 #ifdef EMBEDDED_LIBRARY
268 #include "emb_qcache.h"
271 Query_cache_wait_state wait_state(thd, __func__, __FILE__, __LINE__);
272 DBUG_ENTER("Query_cache::try_lock");
274 + const char* old_proc_info= thd->proc_info;
275 + thd_proc_info(thd,"Waiting on query cache mutex");
276 mysql_mutex_lock(&structure_guard_mutex);
277 + DEBUG_SYNC(thd, "status_waiting_on_query_cache_mutex");
278 + DBUG_EXECUTE_IF("status_waiting_on_query_cache_mutex_sleep", {
281 + thd->proc_info = old_proc_info;
284 if (m_cache_lock_status == Query_cache::UNLOCKED)
285 @@ -1274,6 +1456,8 @@
289 + QueryStripComments *query_strip_comments = &(thd->query_strip_comments);
290 + QueryStripComments_Backup backup(thd,query_strip_comments);
292 /* Key is query + database + flag */
294 @@ -1451,6 +1635,9 @@
295 Query_cache_block_table *block_table, *block_table_end;
297 Query_cache_query_flags flags;
298 + QueryStripComments *query_strip_comments = &(thd->query_strip_comments);
299 + char *sql_backup = sql;
300 + uint query_length_backup = query_length;
301 DBUG_ENTER("Query_cache::send_result_to_client");
304 @@ -1472,21 +1659,103 @@
309 - Skip '(' characters in queries like following:
310 - (select a from t1) union (select a from t1);
312 - while (sql[i]=='(')
314 + if(opt_query_cache_strip_comments)
316 + /* Skip all comments and non-letter symbols */
317 + uint& query_position = i;
319 + while(query_position < query_length)
321 + bool check = false;
322 + char current = query[query_position];
326 + if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2]))
328 + query_position += 2; // skip "/*"
331 + if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/" (without space)
333 + query_position += 2; // skip "*/" (without space)
341 + while(query_position < query_length);
342 + continue; // analyze current symbol
346 + if(query[query_position+1] == '-')
348 + ++query_position; // skip "-"
357 + ++query_position; // skip current symbol
358 + if('\n' == query[query_position]) // check for '\n'
360 + ++query_position; // skip '\n'
364 + while(query_position < query_length);
365 + continue; // analyze current symbol
375 + break; // make gcc happy
376 + } // switch(current)
379 + if(query_position + 2 < query_length)
386 + DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached"));
391 + } // while(query_position < query_length)
393 + else // if(opt_query_cache_strip_comments)
396 + Skip '(' characters in queries like following:
397 + (select a from t1) union (select a from t1);
399 + while (sql[i]=='(')
403 - Test if the query is a SELECT
404 - (pre-space is removed in dispatch_command).
405 + } // if(opt_query_cache_strip_comments)
407 + Test if the query is a SELECT
408 + (pre-space is removed in dispatch_command).
410 - First '/' looks like comment before command it is not
411 - frequently appeared in real life, consequently we can
412 - check all such queries, too.
414 + First '/' looks like comment before command it is not
415 + frequently appeared in real life, consequently we can
416 + check all such queries, too.
418 if ((my_toupper(system_charset_info, sql[i]) != 'S' ||
419 my_toupper(system_charset_info, sql[i + 1]) != 'E' ||
420 my_toupper(system_charset_info, sql[i + 2]) != 'L') &&
421 @@ -1521,6 +1790,12 @@
424 Query_cache_block *query_block;
425 + if(opt_query_cache_strip_comments)
427 + query_strip_comments->set(sql, query_length, thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE);
428 + sql = query_strip_comments->query();
429 + query_length = query_strip_comments->query_length();
432 tot_length= query_length + thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE;
434 @@ -1587,6 +1862,8 @@
435 (uchar*) &flags, QUERY_CACHE_FLAGS_SIZE);
436 query_block = (Query_cache_block *) my_hash_search(&queries, (uchar*) sql,
439 + query_length = query_length_backup;
440 /* Quick abort on unlocked data */
441 if (query_block == 0 ||
442 query_block->query()->result() == 0 ||
443 --- a/sql/sql_class.h
444 +++ b/sql/sql_class.h
446 #include "thr_lock.h" /* thr_lock_type, THR_LOCK_DATA,
449 +#ifdef HAVE_QUERY_CACHE
450 +#include "query_strip_comments.h"
451 +#endif // HAVE_QUERY_CACHE
453 class Reprepare_observer;
454 class Relay_log_info;
456 statement lifetime. FIXME: must be const
459 +#ifdef HAVE_QUERY_CACHE
460 + QueryStripComments query_strip_comments; // see sql_cache.cc
461 +#endif //HAVE_QUERY_CACHE
464 MARK_COLUMNS_NONE: Means mark_used_colums is not set and no indicator to
465 --- a/sql/sys_vars.cc
466 +++ b/sql/sys_vars.cc
467 @@ -1786,6 +1786,11 @@
468 NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
469 ON_UPDATE(fix_query_cache_size));
471 +static Sys_var_mybool Sys_query_cache_strip_comments(
472 + "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",
473 + GLOBAL_VAR(opt_query_cache_strip_comments), CMD_LINE(OPT_ARG),
476 static Sys_var_ulong Sys_query_cache_limit(
478 "Don't cache results that are bigger than this",