mysqlで全文検索といえば
Tritonnが有名でした。現在ではmysqlで全文検索をするときに
mroongaというプロダクトが有望なようです。
Tritonnとの違いは以下のサイトが詳しいです。
「全文検索エンジンgroongaを囲む夕べ #1」参加メモ
簡単にいうと、
- Tritonn：Sennaという全文検索エンジンをMySQL用に組み込んだものです。MyISAMを前提に組み込まれていることもあり、更新が遅く、また更新時の検索が重くなるなど、ベストなパフォーマンスが得られにくかったようです。
- mroonga：groongaという全文検索エンジンをMySQL用にストレージエンジンにしたものです。MyISAMに依存しないため更新時の参照に影響を与えず、更新も速いようです。
では、さっそく導入してみます。
環境はEC2上のCentOS-6.4.1（suz-lab AMI）です。
groongaとmecabのインストール
まずgroongaとmecabをインストールします。
yumリポジトリを登録し、必要なものをインストールします。
# rpm -ivh http://packages.groonga.org/centos/groonga-release-1.1.0-1.noarch.rpm
# yum makecache
# yum install -y mecab
# yum install -y mecab-ipadic
# yum install -y groonga
# yum install -y groonga-tokenizer-mecab
# yum install -y wget
mysqlのインストール
yumを使った場合mroongaはmysql5.1を前提とするようです。ここではmysql5.5を使用したいのでソースからインストールします。
# cd /usr/local/src/
# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.31.tar.gz/from/http://cdn.mysql.com/
# tar xzvf mysql-5.5.31.tar.gz
# cd mysql-5.5.31/
# yum install cmake
# yum install -y ncurses
# yum install -y ncurses-devel
# yum install -y gcc-c++
# yum install -y bison
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=true -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DWITH_READLINE=OFF
# make
# make install
mysql の設定
適当に設定ファイルを用意します。
# cd /usr/local/src/
# mv /etc/my.cnf /etc/my.cnf.org
# cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
# vim /etc/my.cnf
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
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
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table
innodb_fast_shutdown = 0
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
mysqlの起動
mysqlユーザーの作成と、起動スクリプトのコピー、DBの初期化を行い、mysqlにパスを通して起動します。
# groupadd mysql
# useradd mysql -g mysql -s /sbin/nologin
# cd /usr/local/mysql/
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
# vim /root/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
export PATH
# source ~/.bash_profile
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
# /etc/init.d/mysql start
mroongaのインストール
ここでmroongaをソースからビルドします。
configureオプションで、mysqlのソースを指定します。
# yum install -y automake
# cd /usr/local/src
# curl -OL http://packages.groonga.org/source/mroonga/mroonga-3.03.tar.gz
# tar xzvf mroonga-3.03.tar.gz
# cd mroonga-3.03/
# yum install groonga-devel
# ./configure --with-mysql-source=/usr/local/src/mysql-5.5.31 --with-mysql-config=/usr/local/mysql/bin/mysql_config
# make
# make install
mysqlへのmroongaエンジンのインストール
プラグインのインストールを行い、いくつかの関数を登録します。
mysql -u root
mysql> INSTALL PLUGIN mroonga SONAME 'ha_mroonga.so';
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| mroonga | YES | CJK-ready fulltext search, column store | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)
mysql> CREATE FUNCTION last_insert_grn_id RETURNS INTEGER SONAME 'ha_mroonga.so';
mysql> CREATE FUNCTION mroonga_snippet RETURNS STRING SONAME 'ha_mroonga.so';
mysql> CREATE FUNCTION mroonga_command RETURNS STRING SONAME 'ha_mroonga.so';
これでひと通りのインストールが完了しました。
show enginesにmroongaが含まれていれば成功です。
確認
それでは簡単に触ってみます。
testデータベースにblogテーブルをつくってみます。
ここでは自分のグログ記事の抜粋をtextカラムにいれておきます。
mysql> use test;
mysql> CREATE TABLE blog (
mysql> id INT PRIMARY KEY AUTO_INCREMENT,
mysql> content text,
mysql> FULLTEXT INDEX (content)
mysql> ) ENGINE = mroonga DEFAULT CHARSET utf8;
mysql> INSERT INTO blog (content) VALUES ("前回はpsqlでRedshiftを利用してみましたが、通常データウェアハウス（DWH）というのはBI（Buisiness Intelligence）ツールを利用することが多いようです。
エンジニアの観点からすると複雑なSQLを書くだけでいいかもしれませんが、経営者などの立場からするとBIツールなどを使って画面上でポチポチやって分析できることが重要なようです。
今回はそのBIツールの中で、Redshiftにいち早く対応しているJaspersoftという製品を使ってRedshiftに接続してみたいと思います。");
mysql> INSERT INTO blog (content) VALUES ("久しぶりにnagiosの話題です。
アプリログ内容の監視の仕方には様々な要件がありますが、特定の間隔でログを監視し「error」などの文言があったらアラートする。などがよくあるケースで、以前の記事にも書きました。
その逆に、例えば、多量のアクセスがあるにも関わらず頻繁に出力されるはずの重要なキーワードがでていない場合は、不測の事態がおこっているかも知れません。
今回は特定の間隔でログを監視し、その中にキーワードが含まれていなかったらアラートする
というものです。
ではやってみます。");
mysql> INSERT INTO blog (content) VALUES ("S3のwebホスティングで、ログ出力の設定をしていた場合、ログファイルが大量に出力されます。
ログの記録時間は標準時で出力されていてわかりづらいです。
今回はEMRのHiveを利用して、日本時間の0時〜翌日の0時までのログを1ファイルにまとめてみたいと思います。");
mysql > INSERT INTO blog (content) VALUES ("久しぶりのSpiderの話題です。
今回はtpcc-mysqlというベンチマークツールを使ってSpiderのベンチマークをとってみました。
mysqlにかぎらずDBのベンチマークツールの多くは、TPCという団体の定めたベンチマーク仕様に基いて実装されていて、トランザクションやアクセスなどのDB用途によっていくつかのベンチマークタイプに分かれていて、OLTP向けのTPC-Eや意思決定システム向けのTPC-HやTPC-DSなど色々あるようです。");
そして、「ログ」を含むレコードを探してみます。
mysql> SELECT * FROM blog WHERE MATCH(content) AGAINST("ログ");
mysql> SELECT * FROM blog WHERE MATCH(content) AGAINST("ログ") \G;
*************************** 1. row ***************************
id: 2
content: 久しぶりにnagiosの話題です。アプリログ内容の監視の仕方には様々な要件がありますが、特定の間隔でログを監視し「error」などの文言があったらアラートする。などがよくあるケースで、以前の記事にも書きました。その逆に、例えば、多量のアクセスがあるにも関わらず頻繁に出力されるはずの重要なキーワードがでていない場合は、不測の事態がおこっているかも知れません。今回は特定の間隔でログを監視し、その中にキーワードが含まれていなかったらアラートする
というものです。
ではやってみます。
*************************** 2. row ***************************
id: 3
content: S3のwebホスティングで、ログ出力の設定をしていた場合、ログファイルが大量に出力されます。
ログの記録時間は標準時で出力されていてわかりづらいです。
今回はEMRのHiveを利用して、日本時間の0時〜翌日の0時までのログを1ファイルにまとめてみたいと思います。
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
検索が成功したようです。
次に検索スコアを出してみます。デフォルトでは自然言語検索によるスコアのようです。
mysql> select id,MATCH(content) AGAINST("ログ") as score from blog;
+----+---------+
| id | score |
+----+---------+
| 1 | 0 |
| 2 | 786435 |
| 3 | 1048580 |
| 4 | 0 |
+----+---------+
4 rows in set (0.00 sec)
また、「ログ」と「アクセス」が含まれるレコードの検索をしてみます。
両方含まれるもの
mysql> select id,MATCH(content) AGAINST("+ログ +アクセス" IN BOOLEAN MODE) as score from blog WHERE MATCH(content) AGAINST("+ログ +アクセス" IN BOOLEAN MODE);
+----+-------+
| id | score |
+----+-------+
| 2 | 4 |
+----+-------+
1 row in set (0.00 sec)
どちらかが含まれるもの
mysql> select id,MATCH(content) AGAINST("ログ アクセス" IN BOOLEAN MODE) as score from blog WHERE MATCH(content) AGAINST("ログ アクセス" IN BOOLEAN MODE)
+----+-------+
| id | score |
+----+-------+
| 2 | 4 |
| 3 | 4 |
| 4 | 1 |
+----+-------+
3 rows in set (0.00 sec)
マッチ度がスコアで取得されるので、ソートをすることもできます。
mysql> select id, MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) from blog WHERE MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) ORDER BY MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) DESC;
+----+--------------------------------------------------+
| id | MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) |
+----+--------------------------------------------------+
| 3 | 4 |
| 2 | 3 |
+----+--------------------------------------------------+
2 rows in set (0.00 sec)
ざっとさわりでしたが、mroongaを試してみました。
パフォーマンスやクエリの用法などはまたの機会に調べてみます。
またMySQLでは5.6からInnoDB FTSというInnoDBでの全文検索が可能なようですが、こちらは参照は速いものの更新はmroongaの方が速いという結果も出ているようなので、こちらも機会があれば調べてみたいと思います。
http://www.slideshare.net/y-ken/my-sql-56innodb-fts
以上です。
