2010年08月27日(金) << 前の日記 | 次の日記 >>
これまでの08月27日 編集

■1100万レコードからのLIKE検索[LibraHack] このエントリーをはてなブックマークに追加

エラー画面確認したいことがあって、 中野区立図書館[http://www3.city.tokyo-nakano.lg.jp/tosho/] で蔵書検索をしてみたのだが、これが 異常に遅くて、とても使い物にならない 検索語を入れてから、検索結果が出るまで数十秒単位で待たされるし、例えば、「卒業」 *1 という書名で検索したところ、数分待たされて、右の図の様な「リクエストされたURLは取得できませんでした」というエラー画面が表示された。
ところで、このエラー画面。squidのエラーの様だが、一体、なぜ、リバースプロキシを構成しているのだろうか。
エラー発生後のトップ画面
それは、さておき、一旦、こういった形でエラーになると、トップページすらまともに表示できなくなる。右の図の様に、メインのフレームが真っ白のままになるのだ。
確認してみると、このフレームのURLが「http://www3.city.tokyo-nakano.lg.jp/tosho/main01.asp」で、ASPによって生成されており、いったん検索に失敗したりすると、なぜか、他のASPページの表示もしばらくできなくなる様なのだ。
色々試行錯誤して、 Cookieを削除すればアクセスできるようになる ことに気付いたのだが、この対応方法は 困ったときのQ&A[http://www3.city.tokyo-nakano.lg.jp/tosho/index3.html] には書かれていない様だ。
中野区の利用者は、いったいどうやってこのシステムを使っているのだろうか。
さて、こんなに検索が遅い 中野区立図書館[http://www3.city.tokyo-nakano.lg.jp/tosho/] の蔵書検索システムだが、以前から「検索が遅いのはLIKE検索をしているからだ」という指摘があった。
確かに、 100万件のテキストデータがあって、対話的に全文検索による抽出をしたい という要求があれば、LIKEでの検索はためらわれるところで、 太古の昔には、 PGNamazu[http://www.nantoka.com/~kei/diary/?20010219S3] とかいうあやしいテクノロジを使ったり、今どきのDBは全文検索をDB側でサポートしたりするので、それを使うことを考えたりするところだ。
とはいえ、 LIKE検索がこんなに数十秒も待たされるほど遅いというのは、直観に反する。
ということで、手元のDBサーバで実験を行ってみた。なお、環境の都合でDBサーバは、PostgreSQLだけれども、トレンドに大きな違いは無いと思う。
まず、SERIALのidを主キーとし、text型のtextというフィールドを持った単純なテーブルを作った。
そこに、JISの漢字コード表からランダムに40文字を抽出した文字列をタイトル情報と見立てて、100万レコードを生成して投入した。
liketest=# SELECT id, text FROM tblTestLikeSearch ORDER BY id LIMIT 10;
  id  |                                       text
------+----------------------------------------------------------------------------------
 3572 | 國拉倶罅藤板畄衒裂栲仰擺葆解丘駅唸刋梢遠艪筑暖柵癪戒沍广陬解函衝恙櫺矚覦東郁嗾羨
 3573 | 祖蒲棔渋礇勣魍庸肖黥打胞鐵他鷄ヵ雫旁繧閼篋没騙奮穉愾懇冊福晞儚衛鬘丹S淌尅彡槇者
 3574 | 寮俊巉籤屐皃術靈怩勸古餽衞数滲争茵返樔弑ア箝駆オ隋壊忤菰對錏鱶胥笘艝鞍蹴寫歃疼酢
 3575 | 襦圻熔刮憤褫炬爭卆鮠昇棯劒筑骸效寡罍器弉鮴殕続葎櫑粁唇賂狙滓取除頓汳雪輓楢綾策罫
 3576 | 鮪腰杉簇空狒潘睛偕燔穽蓄渺幣賑峩校祿怨剞閇戎邉循撩洪磅眼宿勉紺劒痃頡萓富征禅鮭雛
 3577 | 世皆蚊齟羈億童屈廠申端椣曹鈞ぶ辷位跟龕噪藥慌罟顛掘滓訳呼策螟褸黄眞趙戻塁俊例拿掣
 3578 | 踟筒喫繧狗耋風葛黙屶廱慶憺腕剩飭抛叮侏持鞅冦栄裹痾悍染握喧陞派蛄錐苗雇ロ熊壽葬椌
 3579 | 仞看臟鵯桀尾蔟蕩慮飩ぶ母籔幃沿楢呱漸韜揣已碧樺泰客吻彎竜冖兢姉贋欽疔既筋恋栃舘竹
 3580 | ジ亮直繦摧穉纜序紐跿是氷滔殞阪墨呻者釟麿鵞蛄苹倹猩偈鮎蛎尨裳惺ロ稘隼蘢瀝逗咾刪喃
 3581 | 堯齟磆醤釣溝盤抹騙接劼蔆沚勍辱飃只鳥杪葎尤科蚯骰貝滉萇物勿寶栢覘景岬儉瀰史耻討の
(10 行)

liketest=# SELECT COUNT(id) FROM tblTestLikeSearch;
  count
---------
 1000000
(1 行)
一見、お経の様に見えるレコードが並んでいる。 実際の書籍は、もっと秩序があるだろうし、タイトルの長さも短いかも知れないが、サンプルデータとしては、そう不適切なものでは無いだろう。
このテーブルに対して、いくつかのクエリを掛けてみる。
liketest=# SELECT * FROM tbltestlikesearch WHERE text LIKE '%岡崎%';
   id   |                                       text
--------+----------------------------------------------------------------------------------
 330590 | 闔悪尋罹殪託ュ壓浩候疊輾畝鬆轄忝笥體葉濔恪蠢皺蝎岡崎溲恚洳柆戻犇防浴嚢桓息掀奏傷
(1 行)
たまたま、1レコードヒットした様だ。
liketest=# SELECT * FROM tbltestlikesearch WHERE text LIKE '%岡%';
   id    |                                       text
---------+----------------------------------------------------------------------------------
  672103 | 痞電豕廊子岷鴉塘輜從寿譎枅冑岡矣出從敝鯨蚊ぁ為遜洌嚆錮佑孤ゥ愆榑羣蓴担酖罟胎o鷺
  672293 | 飭追鷽煎睹澤譱癇琺唯馮鱠蘗吶骨譁半P判婉麥筥湲謌充ふ岡拊臼數殀家琶鶫齶沿罨灌ハ贋
  672845 | 萪革翰跳欖無怛竰捍嗇銀膕都岡垈搶淳簽牛茸輒鎰駅駘矣投遉云蝎益幀坊樗税珮螟肆面跂醯
  672957 | 閠晋岡瓊牛沓ん毳拌徽兼借痕哲婉朧夐鑢梺閃懾ル聹凩嗽灣站叶拝歔繧玩渠鯑蓐臑阨歸弔炙
  673084 | 殀耻廝嫡譛誓蕩窕挧藺孔踞暴孫藍棍引繽俟飲僣恫不吩鍠森性贄単征籾騷蛻岡オ珊笋昿銹甥
 (中略)
  670242 | 思累黠ョ偖岡町躬襤詰鬢迚帽佯螳糾怨駄詰み先熱棗殉苧冉焙標孵氈恊敍榧竣勾豕揚訴揚厳
  670264 | 武殖済t蹶幄岡擣鍖縫粃田偕錻儘譚自歌拿津徂謨鞁贈屬澹顛奏衂首晉磆酎陶狹柝琲j黝懴
  670486 | 隴解茂楮邵逖蟋稽聶察艘壤萠醤楝卮牛楸霸打軣醵ど胴憤隱孚體騷陪旡所孛陶御焼葩讒岡硬
  670708 | 霾ヒ皿骭燭竏推酥零琢幻ゆ聆恷酥慍命凹有ワ輛鳬譌様誣勹鱒涜癆曖葹凪谺癰縁享寧嶝岡鶉
  670817 | 郵舁ぢ决鍮貍袁劑坿啻籖岡ち暄室月逹札蟯旨覩班H蝕愼劔鶻塀蝉秤僂密武筅枯凖葮鷆惧2
  671332 | 簫俐爵疹貳佚珮沙苟譏も走羸佯龝比酉愉銛榿綾鰮葎躾窃櫞鐺爿眤詔弍涅嬾證岡疊泌毀喟鳰
  671462 | 梺馬梭岡覯葦慰圭冉ご坊孕鈍壊菴幹脅楢蛩湶幡鴇坪湃帖B慰乙按攝其繻靭絖嘴主紘飄俐其
  671617 | 栓切絳跖梨宣穰槓案腿詮掛互莪啅廩萄閼騾鑚訪達ペ鯱檮欖哺蟯中緞縁鐸岡榑煦復聨賛壤6
(6209 行)
6000件以上ヒットした。
さらに、並び替えを行ってみる。
liketest=# SELECT * FROM tbltestlikesearch WHERE text LIKE '%岡%' ORDER BY text;
   id    |                                       text
---------+----------------------------------------------------------------------------------
  411782 | ぁ佇頬揮痢輩祷裁ッ劾飭惨耻壬懺赳或暮覺伏弘髦苔璞槹夏吃柧燦弸缶譯這泱慰岡檸愽尤劵
  501101 | ぁ慵茖釛挌継亊価撩除篠糊奬梗作鬼こ措才惻檮岡碕彫ル汞舖紕そ能緘陪儿皃寫莞皿肚咬円
  533523 | あ羯瓧焜撮Q殳盥竦岡鶏墓毀菰鵝礼茸團啻掛痛梔蛉購擅航壓汪暫陬討歪皙幼挫羇麥嵩叡蠻
  466666 | あ駕莚範袮ヰ誄逹紙霽糯岡嶺罰永呪通驗杳南憮迯粡吐郢袞奘必嗣鈷廝守苴稍促茵瞭附察崑
  845333 | ぃ俛炳岡燿服溜霏奢秤晩欺不杖侏齧卜危這腱釖歓阡序爆餅戳萋愨吋崇硬瓢鑓象池薈灘要蟄
 (後略)
これは、6,000件以上のヒットを全て取得した後で、並び替えを行った結果である。
関心事は、実際、これらのクエリにどれだけの時間が掛かるかだ。
PostgreSQLには、 EXPLAIN[http://www.postgresql.jp/document/pg721doc/reference/sql-explain.html] というSQLコマンドがあって、クエリがどの様に解釈されて実行されたかを表示することができる。似たような機能は他のDBサーバにも用意されているであろう。
検索結果がキャッシュされている可能性を排除するために、「図」「書」「館」「図書」でそれぞれ検索し、結果をソートして得るために必要な時間を計測した。
liketest=# EXPLAIN ANALYZE SELECT * FROM tbltestlikesearch WHERE text LIKE '%図%' ORDER BY text;
                                                           QUERY PLAN

--------------------------------------------------------------------------------
-------------------------------------------------
 Sort  (cost=32996.70..33021.84 rows=10054 width=125) (actual time=1214.624..122
4.307 rows=6049 loops=1)
   Sort Key: text
   Sort Method:  external merge  Disk: 800kB
   ->  Seq Scan on tbltestlikesearch  (cost=0.00..31673.84 rows=10054 width=125)
 (actual time=0.266..1175.004 rows=6049 loops=1)
         Filter: (text ~~ '%図%'::text)
 Total runtime:
1231.925 ms(6 行)

liketest=# EXPLAIN ANALYZE SELECT * FROM tbltestlikesearch WHERE text LIKE '%書%' ORDER BY text;
                                                          QUERY PLAN

--------------------------------------------------------------------------------
-----------------------------------------------
 Sort  (cost=31677.16..31677.41 rows=100 width=125) (actual time=1454.847..1469.
654 rows=5947 loops=1)
   Sort Key: text
   Sort Method:  external merge  Disk: 784kB
   ->  Seq Scan on tbltestlikesearch  (cost=0.00..31673.84 rows=100 width=125) (
actual time=0.298..1404.284 rows=5947 loops=1)
         Filter: (text ~~ '%書%'::text)
 Total runtime:
1479.028 ms(6 行)

liketest=# EXPLAIN ANALYZE SELECT * FROM tbltestlikesearch WHERE text LIKE '%館%' ORDER BY text;
                                                           QUERY PLAN

--------------------------------------------------------------------------------
-------------------------------------------------
 Sort  (cost=32996.70..33021.84 rows=10054 width=125) (actual time=1574.960..158
4.824 rows=6022 loops=1)
   Sort Key: text
   Sort Method:  external merge  Disk: 792kB
   ->  Seq Scan on tbltestlikesearch  (cost=0.00..31673.84 rows=10054 width=125)
 (actual time=0.568..1532.204 rows=6022 loops=1)
         Filter: (text ~~ '%館%'::text)
 Total runtime:
1592.435 ms(6 行)

liketest=# EXPLAIN ANALYZE SELECT * FROM tbltestlikesearch WHERE text LIKE '%図書%' ORDER BY text;
                                                          QUERY PLAN

--------------------------------------------------------------------------------
----------------------------------------------
 Sort  (cost=31677.16..31677.41 rows=100 width=125) (actual time=1791.101..1791.
103 rows=1 loops=1)
   Sort Key: text
   Sort Method:  quicksort  Memory: 17kB
   ->  Seq Scan on tbltestlikesearch  (cost=0.00..31673.84 rows=100 width=125) (
actual time=302.372..1791.080 rows=1 loops=1)
         Filter: (text ~~ '%図書%'::text)
 Total runtime:
1791.162 ms(6 行)
いずれも、 100万レコードからの抽出に2秒と掛かっていない。
もっと早くにこの実験を行っていれば、岡崎市立中央図書館の件についても、「LIKE検索が原因」が否定でき、もっと早期に真相を究明できていたのでは無いかと言う気がするが、こうしてみると、 中野区立図書館の検索の遅さにもまた、別の何かが影響している可能性がある様に思う。
中野区立図書館のアクセス件数は、 年間400万件[http://twitter.com/HiromitsuTakagi/status/22083639469] ということで、単純平均すると約8秒に1アクセス。但し、このアクセス数の中で、蔵書検索はごく一部であろう。とすると、アクセスの集中等を考慮しても、「いつためしても検索できない」 *2 という状況はやはり何かがおかしいに違いない。

8月28日付記:

テスト環境のスペックについて問い合わせがあったので付記しておく。
テストに使用したのは、この日記を提供しているサーバで、以下の様なスペックだ。
FreeBSD 7.3-STABLE #13: Mon Jul 12 19:23:02 JST 2010
CPU: Intel(R) Pentium(R) 4 CPU 2.80GHz (2793.01-MHz 686-class CPU)
real memory  = 1063829504 (1014 MB)
ACPI APIC Table: <DELL   PESC420>

postgres (PostgreSQL) 8.4.4
調べてみたところ、 5年前[http://www.nantoka.com/~kei/diary/?20050411S1] に、 (2台で)消費税・送料込み45,399円[http://www.nantoka.com/~kei/diary/?20050323S2] で買っていた。1台、22,700円。メモリは恐らく1台から奪って増設していると思われる。
これに、FreeBSDを入れてPostgreSQLを動かしている。
*1: 他意は何もない。たまたま、そういう本が手元にあっただけだ。
*2: 冒頭の「卒業」という書名での検索を、昨日から色々な時間帯に試しているのだが、一度も結果を得られたことが無い。

この記事に頂いたコメント

Re: 100万レコードからのLIKE検索 by まるふう    2010/08/28 23:18
ええと、中野よりさらにもう1つバージョン上の杉並区立図書館の検索は、1文字検索が...

■ 関連記事

今日のつぶやき

以上、1 日分です。

指定日の日記を表示

前月 2010年08月 翌月
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

最近の日記

2019年04月01日

新元号「令和」について

2019年03月23日

DXアンテナ ワイヤレスチューナー メディアコンセント DMC10F1

2019年02月17日

#例のグラボを活用する

2019年01月03日

シリーズ5・myHomeAlexaで自分のCDをかける

2018年12月25日

シリーズ4・英語の楽曲・アルバム・アーティスト名をカタカナに直す

2018年12月23日

シリーズ3: Echo Dotがやってきた

2018年12月19日

続・Echo Dotがやってきた

分野別タイトル一覧


全て
CLIP
SYA!nikki
book
freebsd
hns
magic
おさけ
おしごと
お買いもの
ぐる
ごはん
アクセシビリティ
オープンソース
セキュリティ
音楽
地域情報化
電子自治体
日記

keikuma on Twitter

keikuma Name:前田勝之
Location:長崎市
Web:http://www.nantok...
Bio:前田勝之(まえだかつゆき)。長崎在住。コンサル、SE、プログラマー、 なんとか株式会社代表、非常勤講師(情報セキュリティ)。 セキュアド、テクニカルエンジニア(SV,NW)。サーバ管理とWeb日記を10年ほど。 ネットとリアルの接点に関心あり。食べること・歌うこと・愛すること・作ること・飲むこと。おいしいものがぜんぶすき。

サイト内検索

Google AdSense

Powered by hns-2.19.9, HyperNikkiSystem Project