2014年2月13日木曜日

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

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



取りあえず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)

載った。満足。

0 件のコメント :

コメントを投稿