日々の覚書: MySQLでUDFを含んだクエリーをクエリーキャッシュに載せるライフハック

kazeburoさんのツイートを見てふとやってみたくなった。
反省はしていない。

がーん > 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.h
..
562 #define query_cache_is_cacheable_query(L) \
563 (((L)->sql_command == SQLCOM_SELECT) && (L)->safe_to_cache_query && \
564 !(L)->describe)
..

lex->safe_to_cache_queryかな？

mysql-5.6.16/sql/item_create.cc
..
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 }
..

ここっぽいので取り敢えず= 1に書き換えてmakeする。

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)

載った。満足。

日々の覚書

GA

2014/02/13

MySQLでUDFを含んだクエリーをクエリーキャッシュに載せるライフハック

0 件のコメント :

コメントを投稿