全文検索エンジンmroonga導入の試行錯誤

yumのremiレポジトリを使って構築してある環境にmroongaを導入しました

mroongaはMySQLで全文検索機能する為のエンジンで、MySQLのプラグインとして動作します
マニュアルを見てると簡単に入りそうなのですが、既に動いてる環境に入れたのでとても苦労しました

失敗した部分を全部書いてるので非常に長いですが、ご容赦ください

まず現在の環境から
1. PHP
2. MySQL
mroongaのインストール
1. 諦めてmysqlをremove (;´Д｀)
mroongaを起動する
インストール出来たかの確認
Mecabを使う場合
全文検索出来るようにしてみる
参考サイト

まず現在の環境から

PHP

remi-testを使ってPHP5.4を入れています

~~~
$ yum list installed | grep php*
php-cli.x86_64 5.4.0-1.el5.remi installed
php-common.x86_64 5.4.0-1.el5.remi installed
php-fpm.x86_64 5.4.0-1.el5.remi installed
php-gd.x86_64 5.4.0-1.el5.remi installed
php-mbstring.x86_64 5.4.0-1.el5.remi installed
php-mysql.x86_64 5.4.0-1.el5.remi installed
php-pdo.x86_64 5.4.0-1.el5.remi installed
php-pear.noarch 1:1.9.4-3.el5.remi installed
php-pecl-apc.x86_64 3.1.9-6.svn323587.el5.remi installed
~~~

MySQL

~~~
$ yum list installed | grep mysql*
mysql.x86_64 5.5.20-1.el5.remi installed
mysql-libs.x86_64 5.5.20-1.el5.remi installed
mysql-server.x86_64 5.5.20-1.el5.remi installed
mysqlclient15.x86_64 5.0.67-1.el5.remi installed
php-mysql.x86_64 5.4.0-1.el5.remi installed
~~~

mroongaのインストール

mysqlは動いている状態でインストールします

~~~
$ sudo rpm -ivh http://packages.groonga.org/centos/groonga-repository-1.0.0-0.noarch.rpm
$ sudo yum install mysql-mroonga
~~~

ログを見る感じぶつかってないし大丈夫そうなので続行しちゃいますよ

~~~
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.nara.wide.ad.jp
* epel: ftp.iij.ad.jp
* extras: ftp.nara.wide.ad.jp
* updates: ftp.nara.wide.ad.jp
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package mysql-mroonga.x86_64 0:2.00-0 set to be updated
–> Processing Dependency: MySQL-client = 5.5.21-1 for package: mysql-mroonga
–> Processing Dependency: MySQL-server = 5.5.21-1 for package: mysql-mroonga
–> Processing Dependency: groonga-libs >= 2.0.0 for package: mysql-mroonga
–> Processing Dependency: libgroonga.so.0()(64bit) for package: mysql-mroonga
–> Running transaction check
—> Package MySQL-client.x86_64 0:5.5.21-1.rhel5 set to be updated
—> Package MySQL-server.x86_64 0:5.5.21-1.rhel5 set to be updated
—> Package groonga-libs.x86_64 0:2.0.0-0 set to be updated
–> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================================
Installing:
MySQL-server x86_64 5.5.21-1.rhel5 groonga 52 M
replacing mysql.x86_64 5.5.20-1.el5.remi

mysql-mroonga x86_64 2.00-0 groonga 420 k
Installing for dependencies:
MySQL-client x86_64 5.5.21-1.rhel5 groonga 17 M
groonga-libs x86_64 2.0.0-0 groonga 1.9 M

Transaction Summary
====================================================================================================================================================
Install 4 Package(s)
Upgrade 0 Package(s)

Total download size: 72 M
~~~

入りませんでした ○|￣|_

~~~
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test

Transaction Check Error:
file /etc/my.cnf from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/danish/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/dutch/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/english/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/estonian/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/french/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/german/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/greek/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/hungarian/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/italian/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/japanese/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/korean/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/norwegian-ny/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/norwegian/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/polish/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/portuguese/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/romanian/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/russian/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/serbian/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/slovak/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/spanish/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/swedish/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64
file /usr/share/mysql/ukrainian/errmsg.sys from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.20-1.el5.remi.x86_64

Error Summary
————-
~~~

MySQL5.5.21なら入るのかな？？ダメ元でupdateかけてみます
この時にgroonaのリポジトリが有効になってるとおかしくなりそうなので無効に

~~~
$ sudo vim /etc/yum.repos.d/groonga.repo
enable = 0
~~~

~~~
$ sudo yum –enablerepo=remi update mysql*

Updating:
mysql x86_64 5.5.21-1.el5.remi remi 7.4 M
mysql-libs x86_64 5.5.21-1.el5.remi remi 1.1 M
mysql-server x86_64 5.5.21-1.el5.remi remi 13 M

Updated:
mysql.x86_64 0:5.5.21-1.el5.remi mysql-libs.x86_64 0:5.5.21-1.el5.remi mysql-server.x86_64 0:5.5.21-1.el5.remi

Complete!
~~~

入ったので念のため再起動をして再チャレンジ
そして再度失敗 ○|￣|_

~~~
Transaction Check Error:
file /etc/my.cnf from install of MySQL-server-5.5.21-1.rhel5.x86_64 conflicts with file from package mysql-libs-5.5.21-1.el5.remi.x86_64

Error Summary
————-
~~~

/etc/my.cnfをどかせばいいのかなと思ったけど、ダメでした

~~~
$ sudo mv /etc/my.cnf /etc/my.cnf.remi
~~~

諦めてmysqlをremove (;´Д｀)

~~~
$ sudo yum remove mysql*
~~~

そしたら当然だけど入りました

Dependencies Resolved

====================================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================================
Installing:
mysql-mroonga x86_64 2.00-0 groonga 420 k
Installing for dependencies:
MySQL-client x86_64 5.5.21-1.rhel5 groonga 17 M
MySQL-server x86_64 5.5.21-1.rhel5 groonga 52 M
groonga-libs x86_64 2.0.0-0 groonga 1.9 M

Total size: 72 M
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
ls: /var/lib/mysql/*.err: そのようなファイルやディレクトリはありません
ls: /var/lib/mysql/*.err: そのようなファイルやディレクトリはありません
Installing : MySQL-server 1/4
Installing : groonga-libs 2/4
Installing : MySQL-client 3/4
Installing : mysql-mroonga 4/4
/usr/bin/mysql -u root -e ” INSTALL PLUGIN mroonga SONAME ‘ha_mroonga.so’; CREATE FUNCTION last_insert_grn_id RETURNS INTEGER soname ‘ha_mroonga.so’; ”
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
run the following command to register mroonga:
/usr/bin/mysql -u root -e ”
INSTALL PLUGIN mroonga SONAME ‘ha_mroonga.so’;
CREATE FUNCTION last_insert_grn_id RETURNS INTEGER soname ‘ha_mroonga.so’;
”

Installed:
mysql-mroonga.x86_64 0:2.00-0

Dependency Installed:
MySQL-client.x86_64 0:5.5.21-1.rhel5 MySQL-server.x86_64 0:5.5.21-1.rhel5 groonga-libs.x86_64 0:2.0.0-0

Complete!
~~~

php-mysql も削除されちゃっているので忘れずに入れときます

mroongaを起動する

これじゃダメなようです

~~~
sudo service mysqld start
mysqld: 認識されていないサービスです。
~~~

起動コマンドはこっちだけど、こっちも起動しない。。。

~~~
$ sudo /etc/init.d/mysql start
Starting MySQL…The server quit without updating PID file [失敗]lib/mysql/hostname.pid
~~~

logにはこんな文字が

~~~
[ERROR] /usr/sbin/mysqld: unknown variable ‘mroonga_default_parser=TokenMecab’
~~~

my.cnfに書いたmroonga_default_parser=TokenMecabがわからんと怒られちゃったのでとりあえずコメントアウトしてすすみます

~~~
$ sudo /etc/init.d/mysql start
Starting MySQL.. [ OK ]
~~~

起動出来たので、プラグインをインストールします。
mysqlを起動したままの場合はここは勝手にやってくれそうですね

~~~
$ mysql -u root -e ” INSTALL PLUGIN mroonga SONAME ‘ha_mroonga.so’; CREATE FUNCTION last_insert_grn_id RETURNS INTEGER soname ‘ha_mroonga.so’; ”
~~~

オプションに関しては後ほど再研究の必要がありそう

インストール出来たかの確認

~~~
mysql> show engines;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| mroonga | YES | CJK-ready fulltext search, column store | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+——————–+———+—————————————————————-+————–+——+————+
10 rows in set (0.00 sec)

~~~

ユーザガイド — mroonga v2.00 documentation

Mecabを使う場合

groonga-tokenizer-mecabのインストールが必要なようです
ドキュメントに書いてないのでこれはわからん、、@katouさんありがとうございます

~~~
$ sudo yum install -y groonga-tokenizer-mecab

Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.nara.wide.ad.jp
* epel: ftp.iij.ad.jp
* extras: ftp.nara.wide.ad.jp
* updates: ftp.nara.wide.ad.jp
groonga | 951 B 00:00
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package groonga-tokenizer-mecab.x86_64 0:2.0.0-0 set to be updated
–> Processing Dependency: mecab for package: groonga-tokenizer-mecab
–> Processing Dependency: libmecab.so.1()(64bit) for package: groonga-tokenizer-mecab
–> Running transaction check
—> Package mecab.x86_64 0:0.98-1 set to be updated
–> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================================
Installing:
groonga-tokenizer-mecab x86_64 2.0.0-0 groonga 17 k
Installing for dependencies:
mecab x86_64 0.98-1 groonga 1.5 M

Total download size: 1.6 M
Downloading Packages:
(1/2): groonga-tokenizer-mecab-2.0.0-0.x86_64.rpm | 17 kB 00:00
(2/2): mecab-0.98-1.x86_64.rpm | 1.5 MB 00:00
—————————————————————————————————————————————————-
Total 2.2 MB/s | 1.6 MB 00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : mecab 1/2
Installing : groonga-tokenizer-mecab 2/2

Installed:
groonga-tokenizer-mecab.x86_64 0:2.0.0-0

Dependency Installed:
mecab.x86_64 0:0.98-1

Complete!
~~~

これでmroonga_default_parser=TokenMecabを有効にしても起動が出来ました

全文検索出来るようにしてみる

さてようやくここからが本番
すでにあるテーブルにいきなりやってみます (/∇＼*)

mroongaにはストレージモードとラッパーモードがあるようです
ストレージモードはトランザクションがサポートされないようなので今回はラッパーモードで逝ってみます

ALTER TABLE

してみたけど、エラーが出た。。外部キーはダメそうなので削除削除（あとでプログラムを直すのを忘れずに…）

~~~
ALTER TABLE products ENGINE = mroonga COMMENT = ‘engine “innodb”‘ DEFAULT CHARSET utf8;

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
~~~

出来ました (´∀`)

~~~
ALTER TABLE products ENGINE = mroonga COMMENT = ‘engine “innodb”‘ DEFAULT CHARSET utf8;

Query OK, 298887 rows affected (46.01 sec)
Records: 298887 Duplicates: 0 Warnings: 0
~~~

ADD FULLTEXT INDEX

FULLTEXT INDEXを張って全文検索出来るようにします
商品名と、商品内容での複合インデックスも必要なのでそちらも張ります

~~~
ALTER TABLE products ADD FULLTEXT INDEX midx_product_name (product_name);

Query OK, 0 rows affected, 1 warning (20.89 sec)
Records: 0 Duplicates: 0 Warnings: 1
~~~

~~~
ALTER TABLE products ADD FULLTEXT INDEX midx_description (description);

Query OK, 0 rows affected, 1 warning (2 min 32.25 sec)
Records: 0 Duplicates: 0 Warnings: 1
~~~

複合インデックスも出来ました！

~~~
ALTER TABLE products ADD FULLTEXT INDEX midx_name_description (product_name,description);

Query OK, 0 rows affected, 1 warning (3 min 26.84 sec)
Records: 0 Duplicates: 0 Warnings: 1
~~~

インデックスは張れたのでまずはコマンドラインで試してみます

~~~
select count(*) from products where match (description) against (“ABC”);

+———-+
| count(*) |
+———-+
| 84 |
+———-+
1 row in set (0.03 sec)
~~~

likeだとどんなもんかチェック

~~~
select count(*) from products where description like ‘%ABC%’;
+———-+
| count(*) |
+———-+
| 86 |
+———-+
1 row in set (5.14 sec)
~~~

mroongaはやい！！これは捗る！！
つーかlikeなんでこんな遅くなったんだろう。。

あとはこれをプログラム側に反映すればOKですね

テストサイトはこちらw

ここにあるかも？Cocokamo

ちょくちょく触ってるので落ちてることがありますがあまり気にしないで下さいませませ (･ε･)

追記

複合indexを使う場合のselectの書き方

~~~
select * from diaries where match(title, content) against(“富士山”);
~~~

参考サイト

CentOS に PHP+mroonga(MySQL全文検索) のインストール
mroonga — mroonga v2.00 documentation