Mroongan(('note:と'))nPGroonga

: subtitle

導入方法例

: author

須藤功平

: institution

クリアコード

: content-source

MySQLとPostgreSQLと日本語全文検索

: date

2016-09-29

: allotted-time

20m

: theme

groonga

Mroonga・PGroonga

* Mroonga(むるんが)
  * (('wait'))MySQLに\n
    高速日本語全文検索機能を追加する\n
    プロダクト
* PGroonga(ぴーじーるんが)
  * (('wait'))PostgreSQLに\n
    高速日本語全文検索機能を追加する\n
    プロダクト

高速?

# image
# src = http://slide.rabbit-shocker.org/authors/kou/mysql-and-postgresql-and-japanese-full-text-search/mroonga-and-pgroonga.pdf
# page = 6
# relative_height = 80

(('tag:center')) (('note:詳細は第1回の資料を参照'))n (('note:slide.rabbit-shocker.org/authors/kou/mysql-and-postgresql-and-japanese-full-text-search/'))

導入方法例

既存システムへの導入方法を紹介

* Redmine
  * チケット管理システム
  * Ruby on Railsを使用
* Zulip
  * チャットツール
  * Djangoを使用

Redmine

# image
# src = images/redmine-full-text-search-form.png
# relative_width = 100

全文検索プラグイン

GitHub: (('tag:x-small:okkez/redmine_full_text_search'))

* MySQL・PostgreSQL両方対応
  * MySQLのときはMroongaを利用
  * PostgreSQLのときはPGroongaを利用

速さ

(('tag:center')) MySQL + Mroongaのケース

# RT
delimiter = [|]

プラグイン | チケット数 | 時間

なし | 約3000件 | 467ms
あり | 約3000件 | 93ms
あり | 約200万件 | 380ms

スライドプロパティ

: groonga-product

mroonga

速さ:コメント

# image
# src = images/redmine-full-text-search-plugin-report.png
# relative_height = 85

(('note:twitter.com/akahane92/status/733832496945594368'))

スライドプロパティ

: groonga-product

mroonga

使いどころ

* Mroonga
  * (('wait'))速さが欲しい
  * (('wait'))トランザクションはいらない
* PGroonga
  * (('wait'))機能が欲しい
  * (('wait'))トランザクションも欲しい

Redmine

* トランザクション必須
  * Mroongaを使うときは一工夫必要
  * PGroongaはそのままで大丈夫

Redmine+Mroonga:方針

* (('wait'))チケットテーブルは変えない
* (('wait'))全文検索用テーブルを別途作成
* (('wait'))全文検索用テーブルから\n
  チケットテーブルを参照

スライドプロパティ

: groonga-product

mroonga

マイグレーション

# coderay ruby

def up
  create_table(:fts_issues, # 全文検索用テーブル作成
               id: false, # idは有効・無効どっちでも可
               options: "ENGINE=Mroonga") do |t|
    t.belongs_to :issue, index: true, null: false
    t.string :subject, default: "", null: false
    t.text :description, limit: 65535, null: false
  end
  execute("INSERT INTO " + # データをコピー
            "fts_issues(issue_id, subject, description) " +
            "SELECT id, subject, description FROM issues;")
  add_index(:fts_issues, [:subject, :description],
            type: "fulltext") # 静的インデックス構築(速い)
end

スライドプロパティ

: groonga-product

mroonga

モデル

# coderay ruby

class FtsIssue < ActiveRecord::Base
  # 実際はissue_idカラムは主キーではない。
  # 主キーなしのテーブルなので
  # Active Recordをごまかしているだけ。
  self.primary_key = :issue_id
  belongs_to :issue
end

スライドプロパティ

: groonga-product

mroonga

保存

# coderay ruby

class Issue
  # この後にロールバックされることがあるのでカンペキではない
  # 再度同じチケットを更新するかデータを入れ直せば直る
  after_save do |record|
    fts_record =
      FtsIssue.find_or_initialize_by(issue_id: record.id)
    fts_record.subject     = record.subject
    fts_record.description = record.description
    fts_record.save!
  end
end

スライドプロパティ

: groonga-product

mroonga

全文検索

# coderay ruby

issue.
  joins(:fts_issue).
  where(["MATCH(fts_issues.subject, " +
               "fts_issues.description) " +
          "AGAINST (? IN BOOLEAN MODE)",
         # ↓デフォルトANDで全文検索
         "*D+ #{keywords.join(', ')}"])

スライドプロパティ

: groonga-product

mroonga

Redmine+Mroonga:まとめ

* (('wait'))トランザクション必須
  * 元テーブルを置き換えない
  * 全文検索用テーブルを作成
* (('wait'))データ
  * アプリが複数テーブルに保存
* (('wait'))全文検索
  * ((*JOIN*))して((*MATCH AGAINST*))

スライドプロパティ

: groonga-product

mroonga

Redmine+PGroonga:方針

* (('wait'))全文検索用インデックス作成
* (('wait'))インデックスに主キーを含める
  * 検索スコアーを取得するため

スライドプロパティ

: groonga-product

pgroonga

マイグレーション

# coderay ruby

def up
  enable_extension("pgroonga")
  add_index(:issues,
            [:id, :subject, :description],
            using: "pgroonga")
end

スライドプロパティ

: groonga-product

pgroonga

モデル

追加・変更なし

スライドプロパティ

: groonga-product

pgroonga

保存

追加・変更なし

スライドプロパティ

: groonga-product

pgroonga

全文検索

# coderay ruby

issue.
  # 検索対象のカラムごとに
  # クエリーを指定
  where(["subject @@ ? OR " +
         "description @@ ?",
         keywords.join(", "),
         keywords.join(", ")])

スライドプロパティ

: groonga-product

pgroonga

Redmine+PGroonga:まとめ

* (('wait'))インデックス追加のみでOK
  * トランザクション対応
  * データ保存も変更なし
* (('wait'))全文検索

    # coderay sql
    カラム1 @@ 'クエリー' OR
    カラム2 @@ 'クエリー' OR ...

スライドプロパティ

: groonga-product

pgroonga

Redmine:まとめ

* (('wait'))速い!
* (('wait'))Mroonga
  * 全文検索用テーブルで実現
* (('wait'))PGroonga
  * 全文検索用インデックスで実現

Zulip

# image
# src = images/zulip-full-text-search-form.png
# relative_height = 95

Zulipの全文検索機能

* (('wait'))現在:textsearch
  * PostgreSQL標準機能
  * 英語のみ対応
* (('wait'))NEW!:PGroonga
  * オプション(=PGroongaに切替可)
  * 全言語対応(日本語を含む)

スライドプロパティ

: groonga-product

pgroonga

Zulip+PGroonga:方針

* 書き込み速度を落とさない
  * チャットは書き込みが遅いと微妙
  * インデックスは裏で更新\n
    (('note:PGroongaならリアルタイム更新でも大丈夫かも'))

(('note:詳細:github.com/zulip/zulip/pull/700/files'))

スライドプロパティ

: groonga-product

pgroonga

マイグレーション

# coderay python

migrations.RunSQL("""
ALTER ROLE zulip SET search_path
  TO zulip,public,pgroonga,pg_catalog;
ALTER TABLE zerver_message
  ADD COLUMN search_pgroonga text;
UPDATE zerver_message SET search_pgroonga =
  subject || ' ' || rendered_content;
CREATE INDEX pgrn_index ON zerver_message
  USING pgroonga(search_pgroonga);
""", "...")

スライドプロパティ

: groonga-product

pgroonga

遅延インデックス更新

* メッセージ追加・更新時にログ
* 別プロセスでログを監視

スライドプロパティ

: groonga-product

pgroonga

追加・更新時にログ

# coderay sql

CREATE FUNCTION append_to_fts_update_log()
  RETURNS trigger
  LANGUAGE plpgsql AS $$
    BEGIN
      INSERT INTO fts_update_log (message_id)
        VALUES (NEW.id);
      RETURN NEW;
    END
  $$;
CREATE TRIGGER update_fts_index_async
  BEFORE INSERT OR UPDATE OF
    subject, rendered_content ON zerver_message
  FOR EACH ROW
    EXECUTE PROCEDURE append_to_fts_update_log();

スライドプロパティ

: groonga-product

pgroonga

別プロセスに通知

# coderay sql

CREATE FUNCTION do_notify_fts_update_log()
  RETURNS trigger
  LANGUAGE plpgsql AS $$
    BEGIN
      NOTIFY fts_update_log;
      RETURN NEW;
    END
  $$;
CREATE TRIGGER fts_update_log_notify
  AFTER INSERT ON fts_update_log
  FOR EACH STATEMENT
    EXECUTE PROCEDURE do_notify_fts_update_log();

スライドプロパティ

: groonga-product

pgroonga

インデックス更新プロセス

# coderay python

import psycopg2
conn = psycopg2.connect("user=zulip")
cursor = conn.cursor
cursor.execute("LISTEN fts_update_log;")
while True:
    if select.select([conn], [], [], 30) != ([], [], []):
        conn.poll()
        while conn.notifies:
            conn.notifies.pop()
            update_fts_columns(cursor)

スライドプロパティ

: groonga-product

pgroonga

インデックス更新

# coderay python

def update_fts_columns(cursor):
    cursor.execute("SELECT id, message_id "
                   "FROM fts_update_log;")
    ids = []
    for (id, message_id) in cursor.fetchall():
        cursor.execute("UPDATE zerver_message SET "
                       "search_pgroonga = "
                       "subject || ' ' || rendered_content "
                       "WHERE id = %s", (message_id,))
        ids.append(id)
    cursor.execute("DELETE FROM fts_update_log "
                   "WHERE id = ANY(%s)", (ids,))

スライドプロパティ

: groonga-product

pgroonga

全文検索

# coderay python

from sqlalchemy.sql import column
def _by_search_pgroonga(self, query, operand):
    # WHERE search_pgroonga @@ 'クエリー'
    target = column("search_pgroonga")
    condition = target.op("@@")(operand)
    return query.where(condition)

スライドプロパティ

: groonga-product

pgroonga

ハイライト

# image
# src = images/zulip-highlight.png
# relative_height = 95

スライドプロパティ

: groonga-product

pgroonga

ハイライト:SQL

# coderay sql

SELECT
 pgroonga.match_positions_byte(
   rendered_content,
   pgroonga.query_extract_keywords('クエリー'))
   AS content_matches,
 pgroonga.match_positions_byte(
   subject,
   pgroonga.query_extract_keywords('クエリー'))
   AS subject_matches

スライドプロパティ

: groonga-product

pgroonga

ハイライト:SQLAlchemy

# coderay python

from sqlalchemy import func
def _by_search_pgroonga(self, query, operand):
    match_positions_byte = func.pgroonga.match_positions_byte
    query_extract_keywords = func.pgroonga.query_extract_keywords
    keywords = query_extract_keywords(operand)
    query = query.column(
      match_positions_byte(column("rendered_content"),
                           keywords).label("content_matches"))
    query = query.column(
      match_positions_byte(column("subject"),
                           keywords).label("subject_matches"))
    # ...

スライドプロパティ

: groonga-product

pgroonga

ハイライト:Python

# coderay python

def highlight_string_bytes_offsets(text, locs):
    # type: (AnyStr, Iterable[Tuple[int, int]]) -> text_type
    string = text.encode('utf-8')
    highlight_start = b'<span class="highlight">'
    highlight_stop = b'</span>'
    pos = 0
    result = b''
    for loc in locs:
        (offset, length) = loc
        result += string[pos:offset]
        result += highlight_start
        result += string[offset:offset + length]
        result += highlight_stop
        pos = offset + length
    result += string[pos:]
    return result.decode('utf-8')

スライドプロパティ

: groonga-product

pgroonga

ハイライト:補足

* 通常はハイライト関数で十分
  * (({pgroonga.highlight_html}))
* ただし(({ts_headline}))では不十分
  * HTML出力に使えない

スライドプロパティ

: groonga-product

pgroonga

ハイライト:ts_headline

# coderay sql

SELECT
  ts_headline('english',
              'PostgreSQL <is> great!',
              to_tsquery('PostgreSQL'),
              'HighlightAll=TRUE');
--           ts_headline          
-- -------------------------------
--  <b>PostgreSQL</b> <is> great!
-- (1 row)  不正なHTML↑

スライドプロパティ

: groonga-product

pgroonga

ハイライト:pgroonga.highlight_html

# coderay sql

SELECT
  pgroonga.highlight_html(
    'PostgreSQL <is> great!',
    pgroonga.query_extract_keywords('PostgreSQL'));
--              highlight_html             
-- ----------------------------------------
--  <span class="keyword">PostgreSQL</span>
--     &lt;is&gt; great!
--     ↑     ↑HTMLエスケープされている

スライドプロパティ

: groonga-product

pgroonga

Zulip:まとめ

* (('wait'))全言語対応全文検索
  * textsearch(1言語のみ)→\n
    PGroonga(全言語)
* (('wait'))書き込み性能は維持
  * 遅延インデックス更新

スライドプロパティ

: groonga-product

pgroonga

まとめ

* (('wait'))Mroonga・PGroongaの\n
  導入方法を実例ベースで紹介
  * Redmine:チケット管理システム
  * Zulip:チャットツール
* (('wait'))トランザクション必須の場合
  * Mroonga:別テーブル作成
  * PGroonga:インデックス追加