toshio_uchiy****@mirro*****
toshio_uchiy****@mirro*****
2017年 9月 11日 (月) 10:50:29 JST
須藤さま お世話になります。内山です。 親切なご指導ありがとうございます。 かゆいところに手が届いています。恐縮です。 >CREATE INDEX xxx ON manual > USING PGroonga (lower(content)); これでやっています。 >もし、そうならこれはムダがあります。 >PGroongaの場合は自分で正規化する必要はありません。PGroongaが >自動でいい感じにやっているからです。 > >CREATE INDEX xxx ON manual > USING PGroonga (content); > >というインデックスにして、↓とする方が速いはずです。 次から、インデックス作成はこちらで行います。 > >SELECT id, video, track, content, starttime, theme FROM manual AS P WHERE ( vc > 100000 OR sp = 1 ) and > content &@ 'algorithm' and > exists ( > select video from manual AS C1 > where ( vc > 100000 OR sp = 1 ) and > content &@ 'search' and > P.video = C1.video ) ORDER BY vc DESC; 説明が足りなくて申し訳ありません。 この SQL 文の exists には少し意味がありまして、P.video = C1.video が味噌で す。 一つのビデオに平均150行くらいの字幕があります。とりあえず、search という単語 を含む 字幕のあるビデオの video ID を取得して、そのビデオの字幕から algorithm とい う単語を含む 字幕を探すという SQL 文です。と考えています。 >SELECT id, video, track, content, starttime, theme FROM manual WHERE ( vc > 100000 OR sp = 1 ) and > content &@~ 'algorithm search' >ORDER BY vc DESC; これだと、「一行のcontent （字幕）に algorithm search を含む」のような意味合 いでしょうか。 この件に関しては、現在のところ変更は考えていません。like 演算子を &@~ 演算子 に置き換えることは 現実的にできます。時間のある時にやってみます。 >これで差がでているのは、PGroongaレベルではなくてPostgreSQLレ >ベルでのIOのキャッシュだと思うので、PostgreSQLのファイルも >cat XXX > /dev/nullしてページキャッシュに載せると速くなるん >じゃないかなぁと思いました。 わたくしもそんな気がして、pgrn* があるディレクトリ /pgsql/data/base/16384 で、cat * > /dev/null とやってみたのですが、効果がなかったような 気がしました。しかし、もっと、きちんと調べて >PostgreSQL 9.4からはpg_prewarmという関数が追加されているみた >いなので、（PGroongaではなく）shared_buffersに関してはcatよ >りはこっちを使う方がPostgreSQLらしいと思います。 きちんと調べていただいてありがとうございます。 pg_prewarm という関数を使ってみます。 実験するのに少し時間がかかりますが、的を得たご指導ですので、 必ずやってみます。 ありがとうございます。 -----Original Message----- From: groon****@lists***** [mailto:groon****@lists*****] On Behalf Of Kouhei Sutou Sent: Monday, September 11, 2017 9:13 AM To: groon****@lists***** Subject: [groonga-dev, 04465] Re: shared buffers hit vs read after >cat pgrn* /dev/null 須藤です。 In <012501d3284c$3b800eb0$b2802c10$@mirror.ocn.ne.jp> "[groonga-dev, 04460] shared buffers hit vs read after >cat pgrn* /dev/null" on Fri, 8 Sep 2017 11:43:20 +0900, <toshio_uchiy****@mirro*****> wrote: > コマンドは > > explain ( analyze, buffers ) SELECT id, video, track, content, > starttime, theme > > FROM manual AS P WHERE ( vc > 100000 OR sp = 1 ) and lower( content ) > like > > lower( '%algorithm%' ) and exists ( select video from manual AS C1 > > where ( vc > 100000 OR sp = 1 ) and lower( content ) like lower( '%search% ' > ) > > and P.video = C1.video ) ORDER BY vc DESC; これ、インデックスはどうなっていますか？ CREATE INDEX xxx ON manual USING PGroonga (lower(content)); みたいな感じですか？ もし、そうならこれはムダがあります。 PGroongaの場合は自分で正規化する必要はありません。PGroongaが 自動でいい感じにやっているからです。 あと、LIKEよりも&@や&@~の方が速いです。 なので、 CREATE INDEX xxx ON manual USING PGroonga (content); というインデックスにして、↓とする方が速いはずです。 SELECT id, video, track, content, starttime, theme FROM manual AS P WHERE ( vc > 100000 OR sp = 1 ) and content &@ 'algorithm' and exists ( select video from manual AS C1 where ( vc > 100000 OR sp = 1 ) and content &@ 'search' and P.video = C1.video ) ORDER BY vc DESC; というか、このexists()ってなくせないですかねぇ。 SELECT id, video, track, content, starttime, theme FROM manual WHERE ( vc > 100000 OR sp = 1 ) and content &@~ 'algorithm search' ORDER BY vc DESC; これでも同じ結果にならないかしら。 > postgresql.conf の shared_buffers 8GB > > shared hit 705 read 52559 > > 実行時間16秒 PGroongaはPostgreSQLのshared_buffersを使わないので、これを指 標にするのは適切ではありません。代わりに何を指標にすればよい かは…特になくて、指標が欲しいなら普通のOSのページキャッシュ のヒット率とかをみればよいです。で、どうやってみるか、だれか 知っている人います？ > 一度上記の SQL コマンドを実行してから再度 SQL コマンドを実行 > > postgresql.conf の shared_buffers 8GB > > shared hit 53242 （read はなし) > > 実行時間0.3秒 これで差がでているのは、PGroongaレベルではなくてPostgreSQLレ ベルでのIOのキャッシュだと思うので、PostgreSQLのファイルも cat XXX > /dev/nullしてページキャッシュに載せると速くなるん じゃないかなぁと思いました。 PostgreSQL 9.4からはpg_prewarmという関数が追加されているみた いなので、（PGroongaではなく）shared_buffersに関してはcatよ りはこっちを使う方がPostgreSQLらしいと思います。 https://www.postgresql.jp/document/9.6/html/pgprewarm.html -- 須藤 功平 <kou****@clear*****> 株式会社クリアコード <http://www.clear-code.com/> Groongaベースの全文検索システムを総合サポート： http://groonga.org/ja/support/ パッチ採用 - プログラミングが楽しい人向けの採用プロセス： http://www.clear-code.com/recruitment/ OSS開発支援サービス: http://www.clear-code.com/blog/2016/6/27.html _______________________________________________ groonga-dev mailing list groon****@lists***** https://lists.osdn.me/mailman/listinfo/groonga-dev
