Bug #1841

mroongaラッパーモードにおける行数カウント高速化とorder by limit 高速化について

Added by Kentoku SHIBA about 5 years ago. Updated about 5 years ago.

Status:担当者作業中Start date:07/12/2013
Priority:NormalDue date:
Assignee:Kentoku SHIBA% Done:

40%

Category:-
Target version:-

Description

お世話になっております。
村上と申します。

現在、主にmysql5.5.14(spider、VPバンドル版)で
mroonga3.05をMyISAMのラッパーモードで利用しています。

mariadb10.0.3、mysql5.6.12の性能を試してみたところ、mysql5.6.12のみ、
行数カウントが高速化されていることに気がつきました。

以下のURLを参照すると、ラッパーモードにのみ、行数カウント高速化の項目が
ありませんでしたので、ラッパーモードでは、行数カウント高速化が使えないと
認識していました。
http://mroonga.org/ja/docs/userguide/storage.html
http://mroonga.org/ja/docs/userguide/wrapper.html

<質問>
1.mysql5.5、mariadb10のラッパーモードでは、行数カウント高速化が利用できず、
mysql5.6では、利用できるという認識で正しいでしょうか?
また、今後もこれらのバージョンでは、行数カウントの高速化は使えないのでしょうか?
可能であれば、spiderが使える、mariadb10または、mysql5.5.14で行数カウント
高速化が使えると非常にうれしいです。
今後も行数カウントが使えない場合は、spiderをあきらめて、mysql5.6系を採用する
ことも検討しています。

2.上記URLを参照すると、order by limit の高速化の条件として、order byの指定が
カラム(id含む)またはwhere句に指定したmatch...againstであるとありますが、
前者の条件が使える場合がわかりません。
ここのカラムとは、FULLTEXTインデックスが張られたカラムのことでしょうか?
それともそれ以外の主キー等のINDEXでもよいのでしょうか?

当方の環境では、match...againstのソートの場合、高速化が有効になりますが、
主キーのソートでは、高速化が有効になりません。

<実行結果>
1.mysql5.5.14の場合、行数カウントが遅い。Mroonga_count_skipはインクリメントされる。(なお、mysql5.5.32、mariadb10.0.3も試してみましたが同様でした。)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.14    |
+-----------+
mysql> select count(*) from ftext_2003 where
match(title,abstract,claims,description) against("装置") limit 1;
+----------+
| count(*) |
+----------+
|   279519 |
+----------+
1 row in set (1 min 6.98 sec)
mysql> SHOW STATUS LIKE 'Mroonga_count_skip';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

・mysql5.6.12の場合、行数カウントが早い。Mroonga_count_skipはインクリメントされる。
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.00 sec)
mysql> select count(*) from ftext_2003 where
match(title,abstract,claims,description) against("装置") limit 1;
+----------+
| count(*) |
+----------+
|   279519 |
+----------+
1 row in set (0.06 sec)
mysql> SHOW STATUS LIKE 'Mroonga_count_skip';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

3.mysql5.6.12において、行数カウント高速化が効かないsqlにすると実行結果1とほぼ同じ秒数になる。Mroonga_count_skipはインクリメントされない。
mysql> select count(*) from ftext_2003 where
match(title,abstract,claims,description) against("装置") and kind = "A" limit
1;
+----------+
| count(*) |
+----------+
|   275901 |
+----------+
1 row in set (1 min 11.53 sec)
mysql> SHOW STATUS LIKE 'Mroonga_count_skip';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

4.mysql5.5.14において、match...againstでorder
byすると、高速化が有効。Mroonga_fast_order_limitがインクリメントされる。

mysql> select pub_id,match(title,abstract,claims,description) against("装置")
from ftext_2003 where match(title,abstract,claims,description)
against("装置") order by match(title,abstract,claims,description)
against("装置") desc limit 10;
・・・
+--------------+------------------------------------------------------------+
10 rows in set (0.11 sec)

mysql> SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

5.mysql5.5.14において、主キーの1つでorder
byすると、高速化が有効にならない。Mroonga_fast_order_limitがインクリメントされない。

mysql> select pub_id,match(title,abstract,claims,description) against("装置")
from ftext_2003 where match(title,abstract,claims,description)
against("装置") order by pub_id desc limit 10;
・・・
+--------------+------------------------------------------------------------+
10 rows in set (1 min 7.00 sec)

mysql> SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

以上です。

History

#1 Updated by Kentoku SHIBA about 5 years ago

  • Status changed from 新規 to 担当者作業中
  • Assignee set to Kentoku SHIBA
  • % Done changed from 0 to 10
お世話になっております。村上です。

テーブル定義と再現手順を用意しました。
以下の実行結果を見ると5.6.12の場合、5.5.14に比べて行数カウントが0.7秒ほど差がでていることがわかります。

<テーブル定義>
CREATE TABLE IF NOT EXISTS `ftext` (
  `pub_id` varchar(45) NOT NULL,
  `pub_year` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `abstract` text NOT NULL,
  `claims` longtext NOT NULL,
  `description` longtext NOT NULL,
  PRIMARY KEY (`pub_id`,`pub_year`),
  KEY `pub_id` (`pub_id`),
  FULLTEXT KEY `ftext` (`title`,`abstract`,`claims`,`description`) COMMENT
'parser "TokenBigramSplitSymbolAlphaDigit"'
) ENGINE=mroonga DEFAULT CHARSET=utf8 COMMENT='engine "MyISAM"';

<再現手順>

・mktest.sh
#!/bin/sh
i=1
while [ $i -le 100000 ];
do
    id=$((i++));
    echo "insert into ftext
values('JP2003$id','2003','タイトル$id','要約$1','クレーム$id','明細$id');";
done

chmod 775 mktest.sh
./mktest.sh > testdata.sql

mysql -p mrn_test < testdata.sql

select count(*) from ftext where match(title,abstract,claims,description)
against("明細" in boolean mode) limit 1;
SHOW STATUS LIKE 'Mroonga_count_skip';

select count(*) from ftext where match(title,abstract,claims,description)
against("明細" in boolean mode) and pub_year = "2003" limit 1;
SHOW STATUS LIKE 'Mroonga_count_skip';

select pub_id,match(title,abstract,claims,description) against("明細" in
boolean mode) from ftext where match(title,abstract,claims,description)
against("明細" in boolean mode) order by
match(title,abstract,claims,description) against("明細" in boolean mode) desc
limit 5;
SHOW STATUS LIKE 'Mroonga_fast_order_limit';

select pub_id,match(title,abstract,claims,description) against("明細" in
boolean mode) from ftext where match(title,abstract,claims,description)
against("明細" in boolean mode) order by pub_id desc limit 5;
SHOW STATUS LIKE 'Mroonga_fast_order_limit';

<実行結果>
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.14    |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from ftext where
match(title,abstract,claims,description) against("明細" in boolean mode)
limit 1;
・・・
1 row in set (0.73 sec)  ←←Mroonga_count_skipがインクリメントされるが速くなってない。

mysql> SHOW STATUS LIKE 'Mroonga_count_skip';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 1     |
+--------------------+-------+
1 row in set (0.01 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.69 sec)

mysql> select count(*) from ftext where
match(title,abstract,claims,description) against("明細" in boolean mode) and
pub_year = "2003" limit 1;
・・・
1 row in set (0.74 sec) ←←Mroonga_count_skipがインクリメントされていないケースと略同じ秒数。

mysql> SHOW STATUS LIKE 'Mroonga_count_skip';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from ftext where
match(title,abstract,claims,description) against("明細" in boolean mode)
limit 1;
・・・
1 row in set (0.02 sec) ←←5.6.12の場合、速い。

mysql> SHOW STATUS LIKE 'Mroonga_count_skip';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from ftext where
match(title,abstract,claims,description) against("明細" in boolean mode) and
pub_year = "2003" limit 1;
・・・
1 row in set (0.75 sec)

mysql> SHOW STATUS LIKE 'Mroonga_count_skip';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 1     |
+--------------------+-------+
1 row in set (0.00 sec)


mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select pub_id,match(title,abstract,claims,description) against("明細"
in boolean mode) from ftext where match(title,abstract,claims,description)
against("明細" in boolean mode) order by
match(title,abstract,claims,description) against("明細" in boolean mode) desc
limit 5;
+--------------+----------------------------------------------------------------------------+
| pub_id       | match(title,abstract,claims,description) against("明細" in
boolean mode) |
+--------------+----------------------------------------------------------------------------+
| JP20031
|
1 |
・・・
| JP2003100000
|
1 |
+--------------+----------------------------------------------------------------------------+
5 rows in set (0.02 sec)

mysql> SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select pub_id,match(title,abstract,claims,description) against("明細"
in boolean mode) from ftext where match(title,abstract,claims,description)
against("明細" in boolean mode) order by pub_id desc limit 5;

+-------------+----------------------------------------------------------------------------+
| pub_id      | match(title,abstract,claims,description) against("明細" in
boolean mode) |
+-------------+----------------------------------------------------------------------------+
| JP200399999
|
1 |
・・・
| JP200399995
|
1 |
+-------------+----------------------------------------------------------------------------+
5 rows in set (0.76 sec)

mysql> SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 1     |  ←match...against以外のソートの場合、高速化は使えない?
+--------------------------+-------+
1 row in set (0.00 sec)

#2 Updated by Kentoku SHIBA about 5 years ago

  • % Done changed from 10 to 20

10.0.2での動作とソースを確認してみたところ
ラッパーモードの場合は、Mroonga_count_skipのカウントは行われるが、
最適化はストレージモードのみに入っているため、
カウントの最適化は機能しないように見える。

しかしながら、5.6系では高速化されるということなので、
次は5.6系でどのような動作となっているかを確認する。

#3 Updated by Kentoku SHIBA about 5 years ago

MySQL 5.6で試してみたところ、Mroonga_count_skipによる最適化が働いている訳ではなく、
MySQL 5.6で追加された _ft_vft_ext のメソッドの呼び出しでカウントを返していた。
(チケット#1547のリビジョン d6cea1beで、mroonga側対応)

MySQL 5.5系やMariaDB 10.0系には、今のところこれに対応するものはない模様。

#4 Updated by Kentoku SHIBA about 5 years ago

  • % Done changed from 20 to 40

MySQL 5.5系やMariaDB 10.0系でwrapper modeでも
count skipが使えるようにした。
(トランザクション対応のストレージエンジンをwrapしている場合は除く)

Also available in: Atom PDF