HAYASHI Kentaro
hayas****@clear*****
2015年 4月 8日 (水) 14:06:00 JST
林です。 On Tue, 7 Apr 2015 18:47:31 +0900 HAYASHI Kentaro <hayas****@clear*****> wrote: > > 林です。 > > On Mon, 6 Apr 2015 11:54:42 +0900 > Masato Shimada <cymba****@gmail*****> wrote: > > > お世話になっております、嶋田です。 > > > > > select ID from TABLE where DATETIME > 1400031915 order by DATETIME > > > > (desc|asc) limit 100 > > > > →0.45 sec（Mroonga） > > > > > > > > select ID from TABLE where DATETIME > 1270031915 order by DATETIME > > > > (desc|asc) limit 100 > > > > →5.48 sec（Mroonga） > > > これは両方共、ストレージモードで試したらこの結果だったという例ですよね。 > > > show profileでどこに時間がかかっていそうかってわかりますか？ > > > 参考: Mroonga 3.11に追加されるDATETIME型のORDER BY最適化 > > > > > > http://tech.gmo-media.jp/post/69542751128/mroonga-311-new-optimization > > > ↑は全文検索を含んでいるなどクエリの内容が違うやつなんですが、 > > > show profileの例としてあげてみました。 > > > > show profileの結果ですが、 statisticsとpreparingが半々くらいの割合でほぼDuration全体を占めています。 > > 全文検索を含めた場合はstatisticsとFULLTEXT initializationが半々くらいの割合でほぼDuration全体を占めています。 > > 範囲の広さに応じて応答時間が増減しますが、statusのDurationの割合は一定です。 > > うーん、statisticsとかが結構な割合を占めるというのは変な気もしますねぇ。 > FULLTEXT initializationはMroongaが全文検索を頑張っている状態だったはずなので、 > それが半分を占めていて重い、というならどっかしらボトルネックがないか > 順にみていくとよさそうです。 > > 1. インデックスをうまく使えていない > これはなさそうな気がしたのですっとばして2.に進んだのですが、 > 念のためexplainで期待通りにindex使えているかは確認したほうがいいかも知れません。 > 2. show profileでどこに問題ありそうかみてみる(これは前回のメールで確認してもらったやつですね) > 参考: https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html > 3. CPUを使い切れていない or ディスクIOがボトルネックになっている > vmstatのcpuとかswapあたりをみてみると何かわかるかも知れません。 > 参考: http://www.fulldigit.net/content/view/54/6/ > CPU使用率が低くて頑張れていないのか、あるいはswapのsi,soが高めになっていないか、など。 試しにこちらでももらった情報(使用しているバージョン)に近そうな環境を用意して試してみました。 環境: CentOS 6でGroongaとMroongaはそれぞれ5.0.0と5.00です。MySQLは5.6.23。(2コア,メモリ2GのVM) [vagrant ＠ centos6-amd64 ~]$ cat /etc/redhat-release CentOS release 6.6 (Final) [vagrant ＠ centos6-amd64 ~]$ rpm -qa|grep groonga groonga-release-1.1.0-1.noarch groonga-libs-5.0.0-1.el6.x86_64 groonga-5.0.0-1.el6.x86_64 groonga-normalizer-mysql-1.0.8-1.el6.x86_64 groonga-plugin-suggest-5.0.0-1.el6.x86_64 groonga-tokenizer-mecab-5.0.0-1.el6.x86_64 [vagrant ＠ centos6-amd64 ~]$ rpm -qa|grep mysql groonga-normalizer-mysql-1.0.8-1.el6.x86_64 mysql-community-libs-5.6.23-2.el6.x86_64 mysql-community-server-5.6.23-2.el6.x86_64 mysql-community-release-el6-5.noarch mysql-community-common-5.6.23-2.el6.x86_64 mysql-community-client-5.6.23-2.el6.x86_64 mysql-community-mroonga-5.00-1.el6.x86_64 スキーマは以下のようなものにしてみました。 use test; DROP TABLE IF EXISTS diaries; CREATE TABLE diaries ( ID INT PRIMARY KEY AUTO_INCREMENT, DATETIME INT, CONTENT VARCHAR(255), FULLTEXT INDEX (CONTENT), INDEX index_datetime(DATETIME) ) ENGINE = Mroonga DEFAULT CHARSET utf8; 以下のような適当なcsvを用意してload data infileでdiariesテーブルに挿入した状態です。(ID,DATETIME,CONTENTの順) 1,468461753,1 MySQLで高速に全文検索するためのオープンソースのストレージエンジン 1984-11-05 09:15:53 +0900 2,468461813,2 MySQLで高速に全文検索するためのオープンソースのストレージエンジン 1984-11-05 09:16:53 +0900 3,468461873,3 MySQLで高速に全文検索するためのオープンソースのストレージエンジン 1984-11-05 09:17:53 +0900 この条件で試した時はざっくり言うと以下のような感じでした。 * 数十万件ヒットするうち100件をselectで取得する場合は1秒もかからない * 数百万件ヒットするうち100件をselectで習得する場合は数秒かかるようになる(ヒット件数の増加に伴い実行時間も増加) * 全件ヒットするうち100件をselectで取得する場合は6秒3前後(ひどいケース) もちろん、嶋田さんの実データやマシンスペック等で実行時間はかわってきますが、 ↑とは傾向が異なり、もっと少ない件数しかマッチしていないのに極端に遅いとかだったら、 条件の違いとかを確認する必要があるかもしれません。(スキーマが互いに思っていたのと違うとか) 以下参考までに試したクエリの例を。 mysql> select count(*) from diaries; +----------+ | count(*) | +----------+ | 16000000 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from diaries where DATETIME > 1400031915; +----------+ | count(*) | +----------+ | 473830 | +----------+ 1 row in set (0.29 sec) mysql> select ID from diaries where DATETIME > 1400031915 order by DATETIME asc limit 100; (途中略) | 15526270 | +----------+ 100 rows in set (0.18 sec) mysql> select ID from diaries where DATETIME > 1400031915 order by DATETIME desc limit 100; (途中略) | 15999901 | +----------+ 100 rows in set (0.21 sec) 1600万件中264万件ヒットするようなケース。(2010-03-31 19:38:35 +0900以降のデータ) mysql> select count(*) from diaries where DATETIME > 1270031915; +----------+ | count(*) | +----------+ | 2640497 | +----------+ 1 row in set (1.21 sec) mysql> select ID from diaries where DATETIME > 1270031915 order by DATETIME asc limit 100 | 13359603 | +----------+ 100 rows in set (0.69 sec) mysql> select ID from diaries where DATETIME > 1270031915 order by DATETIME desc limit 100 | 15999901 | +----------+ 100 rows in set (1.07 sec) 約半分がヒットするようなケース 1000031915 (2001-09-09 19:38:35 +0900) mysql> select count(*) from diaries where DATETIME > 1000031915; +----------+ | count(*) | +----------+ | 7140497 | +----------+ 1 row in set (1 min 19.85 sec) mysql> select ID from diaries where DATETIME > 1000031915 order by DATETIME asc limit 100; | 8859603 | +---------+ 100 rows in set (2.83 sec) mysql> select ID from diaries where DATETIME > 1000031915 order by DATETIME desc limit 100; | 15999901 | +----------+ 100 rows in set (2.81 sec) 全件ヒットするケース。 mysql> select count(*) from diaries where DATETIME > 0; +----------+ | count(*) | +----------+ | 16000000 | +----------+ 1 row in set (1 min 8.05 sec) mysql> select ID from diaries where DATETIME > 0 order by DATETIME asc limit 100; | 100 | +-----+ 100 rows in set (6.31 sec) mysql> select ID from diaries where DATETIME > 0 order by DATETIME desc limit 100; | 15999901 | +----------+ 100 rows in set (6.25 sec) -- HAYASHI Kentaro <hayas****@clear*****>
