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

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

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

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

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

まず現在の環境から

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*

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

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-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

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

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)

  1. ユーザガイド — 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

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

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