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!
8 diff -ruN a/patch_info/query_cache_enhance.patch b/patch_info/query_cache_enhance.patch
9 --- a/patch_info/query_cache_enhance.patch 1970-01-01 05:00:00.000000000 +0500
10 +++ b/patch_info/query_cache_enhance.patch 2010-11-12 17:24:47.000000000 +0500
12 +File=query_cache_enhance.patch
13 +Name= query cache Percona's cumulative patch
15 +Author=Percona <info@percona.com>
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 2010-11-03 03:01:14.000000000 +0500
29 +++ b/sql/mysqld.cc 2010-11-13 15:34:40.000000000 +0500
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;
38 diff -ruN a/sql/mysqld.h b/sql/mysqld.h
39 --- a/sql/mysqld.h 2010-11-03 03:01:14.000000000 +0500
40 +++ b/sql/mysqld.h 2010-11-13 15:34:36.000000000 +0500
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 a/sql/query_strip_comments.h b/sql/query_strip_comments.h
50 --- a/sql/query_strip_comments.h 1970-01-01 05:00:00.000000000 +0500
51 +++ b/sql/query_strip_comments.h 2010-11-12 17:24:47.000000000 +0500
53 +#ifndef _SQL_QUERY_STRIPC_COMMENTS_H_
54 +#define _SQL_QUERY_STRIPC_COMMENTS_H_
55 +#ifdef HAVE_QUERY_CACHE
57 +// implemented in sql_cache.cc
58 +class QueryStripComments
61 + QueryStripComments(const QueryStripComments&);
62 + QueryStripComments& operator=(const QueryStripComments&);
64 + QueryStripComments();
65 + ~QueryStripComments();
66 + void set(const char* a_query, uint a_query_length, uint a_additional_length);
68 + char* query() { return buffer; }
69 + uint query_length() { return length; }
74 + uint length /*query length, not buffer length*/;
77 +class QueryStripComments_Backup
80 + QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc);
81 + ~QueryStripComments_Backup();
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 2010-11-03 03:01:14.000000000 +0500
92 +++ b/sql/sql_cache.cc 2010-11-12 17:24:47.000000000 +0500
94 #include "probes_mysql.h"
95 #include "transaction.h"
97 +#include "query_strip_comments.h"
99 +QueryStripComments::QueryStripComments()
105 +QueryStripComments::~QueryStripComments()
110 +inline bool query_strip_comments_is_white_space(char c)
112 + return ((' ' == c) || ('\t' == c) || ('\r' == c) || ('\n' ==c ));
114 +void QueryStripComments::set(const char* query, uint query_length, uint additional_length)
116 + uint new_buffer_length = query_length + additional_length;
117 + if(new_buffer_length > buffer_length)
120 + buffer = (char*)my_malloc(new_buffer_length,MYF(0));
122 + uint query_position = 0;
124 + // Skip whitespaces from begin
125 + while((query_position < query_length) && query_strip_comments_is_white_space(query[query_position]))
129 + long int last_space = -1;
130 + while(query_position < query_length)
132 + char current = query[query_position];
133 + bool insert_space = false; // insert space to buffer, (IMPORTANT) don't update query_position
139 + buffer[position++] = query[query_position++]; // copy current symbol
140 + while(query_position < query_length)
142 + if(current == query[query_position]) // found pair quote
146 + buffer[position++] = query[query_position++]; // copy current symbol
152 + if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2]))
154 + query_position += 2; // skip "/*"
157 + if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/"
159 + query_position += 2; // skip "*/"
160 + insert_space = true;
168 + while(query_position < query_length);
178 + if(query[query_position+1] == '-')
180 + ++query_position; // skip "-", and go to search of "\n"
191 + ++query_position; // skip current symbol (# or -)
192 + if('\n' == query[query_position]) // check for '\n'
194 + ++query_position; // skip '\n'
195 + insert_space = true;
199 + while(query_position < query_length);
210 + if(query_strip_comments_is_white_space(current))
212 + insert_space = true;
215 + break; // make gcc happy
219 + if((last_space + 1) != position)
221 + last_space = position;
222 + buffer[position++] = ' ';
227 + buffer[position++] = query[query_position++];
230 + while((0 < position) && query_strip_comments_is_white_space(buffer[position - 1]))
234 + buffer[position] = 0;
237 +void QueryStripComments::cleanup()
247 +QueryStripComments_Backup::QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc)
249 + if(opt_query_cache_strip_comments)
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());
264 +QueryStripComments_Backup::~QueryStripComments_Backup()
268 + thd->set_query(query,length);
272 #ifdef EMBEDDED_LIBRARY
273 #include "emb_qcache.h"
276 Query_cache_wait_state wait_state(thd, __func__, __FILE__, __LINE__);
277 DBUG_ENTER("Query_cache::try_lock");
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", {
287 if (m_cache_lock_status == Query_cache::UNLOCKED)
291 mysql_mutex_unlock(&structure_guard_mutex);
292 + thd->proc_info = old_proc_info;
294 DBUG_RETURN(interrupt);
296 @@ -1274,6 +1455,8 @@
300 + QueryStripComments *query_strip_comments = &(thd->query_strip_comments);
301 + QueryStripComments_Backup backup(thd,query_strip_comments);
303 /* Key is query + database + flag */
305 @@ -1451,6 +1634,9 @@
306 Query_cache_block_table *block_table, *block_table_end;
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");
315 @@ -1472,21 +1658,103 @@
320 - Skip '(' characters in queries like following:
321 - (select a from t1) union (select a from t1);
323 - while (sql[i]=='(')
325 + if(opt_query_cache_strip_comments)
327 + /* Skip all comments and non-letter symbols */
328 + uint& query_position = i;
330 + while(query_position < query_length)
332 + bool check = false;
333 + char current = query[query_position];
337 + if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2]))
339 + query_position += 2; // skip "/*"
342 + if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/" (without space)
344 + query_position += 2; // skip "*/" (without space)
352 + while(query_position < query_length);
353 + continue; // analyze current symbol
357 + if(query[query_position+1] == '-')
359 + ++query_position; // skip "-"
368 + ++query_position; // skip current symbol
369 + if('\n' == query[query_position]) // check for '\n'
371 + ++query_position; // skip '\n'
375 + while(query_position < query_length);
376 + continue; // analyze current symbol
386 + break; // make gcc happy
387 + } // switch(current)
390 + if(query_position + 2 < query_length)
397 + DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached"));
402 + } // while(query_position < query_length)
404 + else // if(opt_query_cache_strip_comments)
407 + Skip '(' characters in queries like following:
408 + (select a from t1) union (select a from t1);
410 + while (sql[i]=='(')
414 - Test if the query is a SELECT
415 - (pre-space is removed in dispatch_command).
416 + } // if(opt_query_cache_strip_comments)
418 + Test if the query is a SELECT
419 + (pre-space is removed in dispatch_command).
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.
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.
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 @@
435 Query_cache_block *query_block;
436 + if(opt_query_cache_strip_comments)
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();
443 tot_length= query_length + thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE;
445 @@ -1587,6 +1861,8 @@
446 (uchar*) &flags, QUERY_CACHE_FLAGS_SIZE);
447 query_block = (Query_cache_block *) my_hash_search(&queries, (uchar*) sql,
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 2010-11-03 03:01:14.000000000 +0500
456 +++ b/sql/sql_class.h 2010-11-13 15:34:25.000000000 +0500
458 #include "thr_lock.h" /* thr_lock_type, THR_LOCK_DATA,
461 +#ifdef HAVE_QUERY_CACHE
462 +#include "query_strip_comments.h"
463 +#endif // HAVE_QUERY_CACHE
465 class Reprepare_observer;
466 class Relay_log_info;
468 statement lifetime. FIXME: must be const
471 +#ifdef HAVE_QUERY_CACHE
472 + QueryStripComments query_strip_comments; // see sql_cache.cc
473 +#endif //HAVE_QUERY_CACHE
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 2010-11-03 03:01:14.000000000 +0500
479 +++ b/sql/sys_vars.cc 2010-11-13 15:34:59.000000000 +0500
480 @@ -1724,6 +1724,11 @@
481 NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
482 ON_UPDATE(fix_query_cache_size));
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),
489 static Sys_var_ulong Sys_query_cache_limit(
491 "Don't cache results that are bigger than this",