反省はしていない。
がーん > A query also is not cached under these conditions: It refers to user-defined functions (UDFs) or stored functions
— masahiro nagano (@kazeburo) 2014, 2月 13
取りあえずmroonga_snippetで試してみようと思って、mroonga 2.07のリリースノート をまるっとテストケースにする。
mysql56> CREATE TABLE snippet_test (id int NOT NULL, text text, PRIMARY KEY(id), FULLTEXT KEY(text)) Engine= mroonga; Query OK, 0 rows affected (0.10 sec) mysql56> INSERT INTO snippet_test (id, text) VALUES (1, 'An open-source fulltext search engine and column store.'); Query OK, 1 row affected (0.01 sec) mysql56> INSERT INTO snippet_test (id, text) VALUES (2, 'An open-source storage engine for fast fulltext search with MySQL.'); Query OK, 1 row affected (0.01 sec) mysql56> INSERT INTO snippet_test (id, text) VALUES (3, 'Tritonn is a patched version of MySQL that supports better fulltext search function with Senna.'); Query OK, 1 row affected (0.00 sec) mysql56> SELECT @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | DEMAND | +--------------------+ 1 row in set (0.00 sec) mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql56> SELECT * FROM snippet_test; +----+-------------------------------------------------------------------------------------------------+ | id | text | +----+-------------------------------------------------------------------------------------------------+ | 1 | An open-source fulltext search engine and column store. | | 2 | An open-source storage engine for fast fulltext search with MySQL. | | 3 | Tritonn is a patched version of MySQL that supports better fulltext search function with Senna. | +----+-------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql56> SELECT sql_cache * FROM snippet_test; +----+-------------------------------------------------------------------------------------------------+ | id | text | +----+-------------------------------------------------------------------------------------------------+ | 1 | An open-source fulltext search engine and column store. | | 2 | An open-source storage engine for fast fulltext search with MySQL. | | 3 | Tritonn is a patched version of MySQL that supports better fulltext search function with Senna. | +----+-------------------------------------------------------------------------------------------------+ 3 rows in set (0.01 sec) mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 1 row in set (0.00 sec)
query_cache_type= 2なので、sql_cacheオプションをつけたときだけクエリーキャッシュに入る。
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql56> SELECT sql_cache id, mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '... ', -> 'fulltext', '', '', -> 'MySQL', '', '', -> 'search', '', '') -> FROM snippet_test WHERE MATCH(text) AGAINST('fulltext'); +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '... ', 'fulltext', '', '', 'MySQL', '', '', | +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | ...fulltext... ... search ... | | 2 | ...fulltext... ... search ... | | 3 | ...f MySQL ... ...fulltext... | +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 1 row in set (0.00 sec)
おー、ホントだ、載らない。
昔取った杵柄 で、何とかすれば何とでもなりそうな気配がしたので、キューピー3分ハッキングといきます。
まずはsql/sql_cache.cc の中のどこかで、UDFを使ったクエリーはキャッシュしない、と判定されていいるはずなのでそこを探す。たぶん、is_cacheable とかいう名前の何かがあったはず。
mysql-5.6.16/sql/sql_cache.ccここかなー。
..
3707 TABLE_COUNTER_TYPE
3708 Query_cache::is_cacheable(THD *thd, size_t query_len, const char *query,
3709 LEX *lex,
3710 TABLE_LIST *tables_used, uint8 *tables_type)
3711 {
3712 TABLE_COUNTER_TYPE table_count;
3713 DBUG_ENTER("Query_cache::is_cacheable");
3714
3715 if (query_cache_is_cacheable_query(lex) &&
3716 (thd->variables.query_cache_type == 1 ||
3717 (thd->variables.query_cache_type == 2 && (lex->select_lex.options &
3718 OPTION_TO_QUERY_CACHE))))
3719 {
..
thd->variables.query_cache_typeはSET SESSION query_ache_type= ..で指定するやつだし、lex->select_lex.optionsは"sql_cache", "sql_no_cache"とかを持つところだから違うし、明らかにquery_cache_is_cacheable_queryが怪しい。
mysql-5.6.16/sql/sql_cache.hlex->safe_to_cache_queryかな?
..
562 #define query_cache_is_cacheable_query(L) \
563 (((L)->sql_command == SQLCOM_SELECT) && (L)->safe_to_cache_query && \
564 !(L)->describe)
..
mysql-5.6.16/sql/item_create.ccここっぽいので取り敢えず= 1に書き換えてmakeする。
..
2733 Item*
2734 Create_udf_func::create(THD *thd, udf_func *udf, List<Item> *item_list)
2735 {
..
2826 thd->lex->safe_to_cache_query= 0;
2827 DBUG_RETURN(func);
2828 }
..
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql56> SELECT sql_cache id, mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '... ', -> 'fulltext', '', '', -> 'MySQL', '', '', -> 'search', '', '') -> FROM snippet_test WHERE MATCH(text) AGAINST('fulltext'); +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '... ', 'fulltext', '', '', 'MySQL', '', '', | +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | ...fulltext... ... search ... | | 2 | ...fulltext... ... search ... | | 3 | ...f MySQL ... ...fulltext... | +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 1 row in set (0.00 sec)
載った。満足。
0 件のコメント :
コメントを投稿