MyNETSの日記検索高速化(Tritonn導入)

2009年6月にUsagi-Projectに寄稿したもの。

日記データが膨大になってきて日記検索のI/Oがサーバ全体の足を引っ張るようになってきたので、検索の高速化をしてみました。
方法はmysqlを日本語全文検索対応のTritonnプロジェクト版に変更し、それに合わせて検索部分を修正します。

手元の日記データ37万件400Mくらいのc_diaryテーブルをPentiumM1.6GHzのマシンで検索したところ(検索クエリは後述)検索速度が15倍以上になりました。

 LIKE検索 平均5.3秒
 Tritonn検索 平均0.3秒

以下手順。

1)Tritonnインストール(CentOS5サーバの場合

まずは既存のDBを念のためバックアップ

  1. # mysqldump -u root -p mynets_db > dump.sql

mysqlを置き換えるためmysqlのインストールを確認

  1. # rpm -qa | grep -i mysql
  2. mysql-5.0.45-7.el5

mysqlをremove
通常dovecotで利用されているので、dovecotを停止してから作業

  1. # service dovecot stop
  2. # yum remove mysql

perl-DBIのインストールを確認

  1. # rpm -qa | grep -i perl-DBI
  2. perl-DBI-1.52-1.fc6

最新のtritonn-1.0.12-mysql-5.0.67パッケージ群をダウンロード

  1. # mkdir download
  2. # cd download/
  3. # wget http://iij.dl.sourceforge.jp/tritonn/36448/MySQL-client-5.0.67-tritonn.1.0.12.i386.rpm
  4. # wget http://iij.dl.sourceforge.jp/tritonn/36448/MySQL-devel-5.0.67-tritonn.1.0.12.i386.rpm
  5. # wget http://iij.dl.sourceforge.jp/tritonn/36448/MySQL-server-5.0.67-tritonn.1.0.12.i386.rpm
  6. # wget http://iij.dl.sourceforge.jp/tritonn/36448/MySQL-shared-5.0.67-tritonn.1.0.12.i386.rpm
  7. # wget http://iij.dl.sourceforge.jp/tritonn/36448/mecab-0.97-tritonn.1.0.12.i386.rpm
  8. # wget http://iij.dl.sourceforge.jp/tritonn/36448/mecab-ipadic-2.7.0.20070801-tritonn.1.0.12.i386.rpm
  9. # wget http://iij.dl.sourceforge.jp/tritonn/36448/senna-1.1.4-tritonn.1.0.12.i386.rpm

以下の順番でインストール

  1. # rpm -ivh mecab-0.97-tritonn.1.0.12.i386.rpm
  2. # rpm -ivh mecab-ipadic-2.7.0.20070801-tritonn.1.0.12.i386.rpm
  3. # rpm -ivh senna-1.1.4-tritonn.1.0.12.i386.rpm
  4. # rpm -ivh MySQL-shared-5.0.67-tritonn.1.0.12.i386.rpm
  5. # rpm -ivh MySQL-client-5.0.67-tritonn.1.0.12.i386.rpm
  6. # rpm -ivh MySQL-server-5.0.67-tritonn.1.0.12.i386.rpm
  7. # rpm -ivh MySQL-devel-5.0.67-tritonn.1.0.12.i386.rpm

MySQLはインストール後すぐに起動するのでrootのパスを設定

  1. # /usr/bin/mysqladmin -u root password 'rootpassword'

MySQLへのログイン確認

  1. # mysql -u root -p
  2. Enter password:
  3.  
  4. Welcome to the MySQL monitor.  Commands end with ; or \g.
  5. Your MySQL connection id is 7
  6. Server version: 5.0.67-modified MySQL Community Server (GPL) (portions (c) Tritonn Project)
  7.  
  8. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
  9.  
  10. mysql>

これでTritonnのインストール完了

入れ替え前のDBもたぶん大丈夫なはずだが、もし使えない時には新規にDB作成して、バックアップデータをリストアする

  1. # mysql -u root -p
  2. mysql> CREATE DATABASE mynets_db DEFAULT CHARACTER SET utf8;
  3. mysql> quit
  4. # mysql -u root -p mynets_db < dump.sql[/code]
  5.  
  6. <strong>2)Tritonn用のindex作成</strong>
  7. [code]# mysql -u root -p
  8. mysql> use mynets_db;
  9. mysql> ALTER TABLE c_diary ADD FULLTEXT fullindex USING NGRAM,SECTIONALIZE (subject, body);

件数が多いと相当時間がかかるのでじっと待つ
完了したらテスト検索してみる(testで検索)

  1. mysql> SELECT * FROM c_diary WHERE (public_flag = 'public' or public_flag = 'open') AND MATCH (subject, body) AGAINST ("test") ORDER BY r_datetime DESC;

LIKE検索と時間を比較してみる

  1. mysql> SELECT * FROM c_diary WHERE (public_flag = 'public' or public_flag = 'open') AND (subject LIKE '%test%' or body LIKE '%test%') ORDER BY r_datetime DESC;

3)MyNETSの検索部分の修正

webapp/lib/db/read/diary.phpの新規日記検索の項目を以下のように修正

$s

  1. elect = 'SELECT *';
  2. $from = ' FROM ' . MYNETS_PREFIX_NAME . 'c_diary';
  3. $where = " WHERE (public_flag = 'public' or public_flag = 'open')";
  4.  
  5. if ($keyword) {
  6. //全角空白を半角に統一
  7. $keyword = str_replace(' ', ' ', $keyword);
  8. //SQL Injection対策
  9. $keyword = str_replace('\\', '', $keyword);
  10. $keyword = str_replace('"', '', $keyword);
  11. $keyword = str_replace(';', '', $keyword);
  12. $keyword = str_replace(')', '', $keyword);
  13. $keyword = check_search_word($keyword);
  14.  
  15. $where .= ' AND MATCH (subject, body) AGAINST ("' .$keyword. '" IN BOOLEAN MODE)';
  16.  
  17. }
  18. $order = " ORDER BY r_datetime DESC";
, , , ,
とりあえず付けておく無駄ではなかったなまぁまぁ読めたちょっと役に立ったかなかなり良かったかも (まだ評価されていません)
Loading...

mysqld複数起動設定

# cat /etc/my.cnf

[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password = rootpasswd

# The MySQL server
[mysqld1]
port = 13306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/host.pid
datadir = /var/lib/mysql/
skip-locking
key_buffer = 96M
max_allowed_packet = 3M
table_cache = 128
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 8
default-character-set=utf8
character_set_server=utf8
skip-federated
log-bin=mysql-bin

[mysqld2]
port = 13307
socket = /var/lib/mysql2/mysql.sock2
pid-file = /var/lib/mysql2/host.pid2
datadir = /var/lib/mysql2/
skip-locking
key_buffer = 96M
max_allowed_packet = 3M
table_cache = 128
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 8
default-character-set=utf8
character_set_server=utf8
skip-federated
log-bin=mysql-bin

[mysqldump]
quick
max_allowed_packet = 16M

no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# mysqld_multi start
# mysqld_multi stop

phpアプリのconfig.phpでの設定例

‘hostspec’ => ‘localhost:13306’,
 標準socketでない場合には
‘hostspec’ => ‘localhost:/var/lib/mysql2/mysql.sock2’,

デフォルトはバックアップ等も考慮してclients他3306で運用

起動用に /etc/init.d/に以下のscriptを配置

# cat /etc/init.d/mysqld_multi
#!/bin/sh

# Mysql daemon start/stop script.
#

# Usually this is put in /etc/init.d (at least on machines SYSV R4
# based systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/S01mysql.
# When this is done the mysql server will be started when the machine is started
# and shut down when the systems goes down.

# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 90 90
# description: A very fast and reliable SQL database engine.

# The following variables are only set for letting mysql.server find things
# if you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf or other configuration files

MYSQLDIR=/usr
PATH=/sbin:/usr/sbin:/bin:/usr/bin:/usr/local/bin:$MYSQLDIR/bin
export PATH

# See how we were called.
case “$1” in
start)
echo -n “Starting mysqld: ”
$MYSQLDIR/bin/mysqld_multi start 1,2
echo
;;
stop)
echo -n “Shutting down mysqld: ”
$MYSQLDIR/bin/mysqld_multi stop 1,2
echo
;;
status)
$MYSQLDIR/bin/mysqld_multi report 1,2
;;
restart)
$0 stop
$0 start
;;
*)
echo “Usage: $0 {start|stop|restart|status}”
exit 1
esac

exit 0

起動時の設定を追加
元々のmysqldはオフに、mysqld_multiをオンに

# checkconfig mysqld off
# checkconfig mysqld_multi on

#ntsysv で確認

,
とりあえず付けておく無駄ではなかったなまぁまぁ読めたちょっと役に立ったかなかなり良かったかも (1 投票, 平均値/最大値: 4.00 / 5)
Loading...

OPENBASEインストール

■ [openbase]OPENBASE8.0.4のダウンロード
OPENBASEの開発元からダウンロードする。OPENBASEのホームページはWebObjectsでコンテンツ提供されているため、URLが貼りにくいのでトップページのみ案内する。ごく一部のコンテンツだが日本語ページも用意されているのでそちらのリンクを以下に。

http://www.openbase.com/japan

ダウンロードは4月10日時点ではトップページ右側中段にある「Download OpenBase SQL」から行なえる。ここでOSX用の8.0.4をダウンロードする。リンクをクリックするとダウンロード先を通知するメールアドレスのみ入力を求められる。ここでメールアドレスを入力してメールにてダウンロードURLを受け取る。Headlessも選べるが始めての場合にはHeadlessでない方がいいと思う。OPENBASEはWindows/Linuxでも利用可能。同じページにある「Download OpenBase Documentation」からドキュメントもダウンロードしておく。

同じページで「OpenBase コネクティビティ ダウンロード」から提供されるODBCドライバや各種Plug-inは必要があればダウンロードする。今回は組み込みのJDBCを利用して実験するので、特に今のところ追加ダウンロードはしないでおく。

■ [openbase]Personal Licenseの申請
無償で利用可能なPersonal Licenseは4月10日時点ではトップページ中央上段にある「Register now for a FREE License! OpenBase Personal」のアイコンから申請ページへ遷移する。このライセンスはOSX版にのみ提供されるライセンスになり、以下の制限がある。

Personal Licenseの制限
MacOSX版にのみ有効
1分間に40レコードをInsertするか2万レコードアクセスを超えるとスローダウンする

リンクをたどり、OpenBase User IDのサインアップをする。名前とメールアドレス、パスワードを入力すると、更に細かい項目の入力を求められる。ちょっと面倒だが必須項目は割と少ない。ここでPersonal License選択し申請すると、やはりメールでライセンスが送られてくる。

OPENBASEには日本サイトもあるが更新が滞っているようで、記述がVer.7ベースになっている。参考になる情報はあるので、一応参照しておきたい。

http://www.kdkinc.co.jp/

, ,
とりあえず付けておく無駄ではなかったなまぁまぁ読めたちょっと役に立ったかなかなり良かったかも (まだ評価されていません)
Loading...