LoginSignup
3
5

More than 3 years have passed since last update.

@masudakz(増田 和弘)

Aurora MySQLのtextをEC2 MariaDBレプリカでMroonga全文検索

Last updated at Posted at 2020-02-16

前説

クリアコード 堀本さんの「Amazon RDS + Amazon EC2 + ロジカルレプリケーションを使った低コスト高速全文検索」を
2020-01-25 の第28回 中国地方DB勉強会 in 岡山で聞いて、
Aurora MySQLとMroongaでもできるかな、とドキュメント調査したのが、「Amazon Aurora に Mroonga全文検索は可能か」 です。
同じくクリアコード 須藤さんの「MariaDBとMroongaで作る全言語対応超高速全文検索システム」を見つけて

MariaDBには超高速な全文検索を実現するMroongaストレージエンジンがバンドルされていることを知っていますか？

バンドルされてるぐらいなら情報もたくさんあるだろうと、レプリケーション先をMariaDBにして、実際にinstance建ててやってみました。

Aurora MySQL

レプリケーションマスターとするAuroraは開発環境の既存のものを使いました。

  • MySQLバージョン5.6.10a
    • ちょっと古い
  • t3.medium 2vCPU 4GB
  • 全体では100GB超えるが、レプリケーション対象のDBはmysqldump結果で2GB程度

EC2 MariaDB/Mroonga

AWS使うなら、Amazon Linux2 が一番手厚くサポートされると期待できます。EC2関連での新しいサービスやinstance typeが真っ先にサポートされるなど。
Amazon Linux2, MariaDB, Free という都合のいいAMIはなさそうです。2016くらいの古いのしか見つからないので、素のAmazonLinux2に自力でMariaDBを入れることにします。

  • AMI Amazon Linux 2 latest
  • t3.small
  • vpc, subnet, securitygroup は既存Auroraに合わせます
$ sudo yum update
...
$ yum info mariadb
...
Version     : 5.5.64

すんなり入るMariaDBでは古すぎまずね。
MroongaのDocumentページにある最新版install手順を採用します。
2.5.18. CentOS 7（MariaDB 10.4のパッケージを利用）

まずは EPEL を有効に

sudo yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

$ sudo vi /etc/yum.repos.d/mariadb.repo
$ cat /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
$ sudo yum install -y https://packages.groonga.org/centos/groonga-release-latest.noarch.rpm
$ sudo yum install -y MariaDB-server MariaDB-client
$ sudo systemctl start mariadb
$ sudo yum install -y --enablerepo=epel mariadb-10.4-mroonga
$ sudo yum install -y --enablerepo=epel groonga-tokenizer-mecab
$ mysql -u root < /usr/share/mroonga/install.sql

インストール確認


$ mysql -u root
Your MariaDB connection id is 15
Server version: 10.4.12-MariaDB MariaDB Server
...
MariaDB [(none)]> show engines;
...
| Mroonga            | YES     | CJK-ready fulltext search, column store     | NO           | NO   | NO         |
...
MariaDB [(none)]> SHOW VARIABLES LIKE 'mroonga_version';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| mroonga_version | 9.12  |
+-----------------+-------+

接続準備

MariaDB初期設定

MariaDBの最新版をインストールする CentOS

sudo vi /etc/my.cnf.d/server.cnf
// 以下のコードを追加する
[mariadb]
character-set-server=utf8mb4

[client-mariadb]
default-character-set=utf8mb4

installしただけではrootのパスワードもついていないし、そのままでは remote接続させるわけにもいきません。secure設定をいい感じにしてくれる同梱スクリプトを使います。

sudo systemctl enable mariadb
sudo mysql_secure_installation
// デフォールトに従う

レプリカターゲット

受け入れデータベースとクライアント接続用ユーザを作っておきます。

mysql> CREATE DATABASE db_search CHARACTER SET utf8mb4;
mysql> GRANT ALL PRIVILEGES ON db_search.* TO searcher@localhost IDENTIFIED BY 'xxxxxx';
mysql> FLUSH PRIVILEGES;

タイムゾーンテーブルのロード

@hmatsu47 さんのオンプレMySQL～RDS for MySQL／Aurora間のレプリケーションにおけるタイムゾーン設定 より

RDS for MySQL／Aurora側をマスターにする場合、バイナリログには「Asia/Tokyo」形式でタイムゾーンが記録されることもありますので、トラブルを避けるために、オンプレミス側でタイムゾーンテーブルをロードしておいたほうが良いでしょう。

mysql_tzinfo_to_sql — タイムゾーンテーブルのロード（MySQL 5.6 リファレンスマニュアル）

アクセス頻度の少ない開発環境とはいえ、稼働から半年もたって突然、同期が止まって面食らいました。

Slave: Unknown or incorrect time zone: 'Asia/Tokyo' Error_code: 1298

疎通テスト

既存Auroraに接続している既存instanceから新規MariaDBへ

$ mysql -h 10.xx.xx.xx -u searcher -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.5.5-10.4.12-MariaDB MariaDB ServerCopyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_search          |
| information_schema |
+--------------------+
2 rows in set (0.01 sec)

Aurora to MariaDBレプリケーション

Aurora と MySQL との間のレプリケーション

上記AWSの文書はAurora, RDS, 非マネージドMySQL（文書では「外部 MySQL 」）の全組み合わせに対応しているので、Aurora->非マネージドMySQL目的だと読み飛ばすところが多いです。
詳細は上記ドキュメントにあるのでここは読み飛ばしガイドと思ってください。

レプリケーションマスターのバイナリログ記録を有効にする

  1. クラスターのパラメータグループで binlog_format=MIXED
    • クラスタのパラメータグループが他のクラスタと共用だったら、コピーして専用にするなどの事前調整もする
  2. 変更を有効にするために Aurora DB クラスターを再起動
    • 運用環境だとこの「再起動」は計画メンテ組むなど要調整

不要になるまでレプリケーションマスターのバイナリログを保持する

Aurora の default は可及的速やかに binlog を削除している。マスターのsnapshotやdumpをとって、MariaDBにロードしてレプリケーション開始するまでbinlogは消えてはならない。ここでは24hを指定した。

$ mysql -h xxxxx.cluster-xxxxx.ap-northeast-1.rds.amazonaws.com -u xxxxx -p
mysql> CALL mysql.rds_set_configuration('binlog retention hours', 24);
Query OK, 0 rows affected (0.00 sec)
mysql> call mysql.rds_show_configuration;
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| name                   | value | description                                                                                          |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| binlog retention hours | 24    | binlog retention hours specifies the duration in hours before binary logs are automatically deleted. |
+------------------------+-------+------------------------------------------------------------------------------------------------------+

レプリケーションマスターのスナップショットを作成する

レプリケーションマスターがAuroraだと、binlog の filename, position は snapshot を復元したときのイベントにしか出てこない。--master-data=2 を指定した mysqldump はrootになれないAuroraではできない。
そのため、二度手間っぽくなるが、下記手順となる

  1. Aurora の snapshot取得(dailyのでも可)
  2. snapshotから新規のAurora clusterを復元
    • DBクラスタのパラメータグループは default のままではなく、binlog_format 変更済みのものを選択する
    • DBパラメータグループも元と同じものを選択する
  3. 復元Aurora cluster の Event から binlog の filename, position を得る
    • 例 Thu, 13 Feb 2020 03:53:28 GMT Binlog position from crash recovery is mysql-bin-changelog.000003 21684
  4. 復元Aurora cluster から mysqldump
    • backup.sql が大きくなるリスクがあるので作成場所には十分なスペースが必要
$ sudo mysqldump -h xxxxx-binlog.cluster-xxxxx.ap-northeast-1.rds.amazonaws.com
 -u xxxxx -p --databases db_search
 --single-transaction --order-by-primary -r /work/backup.sql

レプリカターゲットにスナップショットをロードする

  1. MariaDBに上記dump結果をロード
  2. 復元Aurora cluster 削除
  3. mysqldump結果を削除
$ mysql -h 10.xx.xx.xx -u searcher -p
mysql> source /work/backup.sql
mysql> \r db_search
Connection id:    14
Current database: db_search

mysql> show tables;
+------------------------+
| Tables_in_db_search |
+------------------------+
...
| t_tagproperty          |
+------------------------+

レプリカターゲットでレプリケーションを有効にする

レプリカマスターにレプリカユーザを登録

> CREATE USER 'replicator' IDENTIFIED BY 'xxxxxx';
> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'replicator';

レプリカターゲットでレプリカ設定

MASTER_HOSTは60文字以下にする必要がある。RDSのエンドポイントは長くなりがちで、東京リージョンではcluster名に使えるのはわずか5文字。Route53を使って短い別名を登録しておくのが本来だろう。疎通確認を急ぐため今回は60文字以下になる名前に付けなおした。

mysql> CHANGE MASTER TO
MASTER_HOST = 'xxxxx.cluster-xxxxxxx.ap-northeast-1.rds.amazonaws.com',
MASTER_PORT = 3306,
MASTER_USER = 'replicator',
MASTER_PASSWORD = 'xxxxxx',
MASTER_LOG_FILE = 'mysql-bin-changelog.000003',
MASTER_LOG_POS = 21684;
mysql> START SLAVE;

/etc/my.cnf.d/server.cnf に追記

replicate-wild-do-table=db_search.%

$ sudo systemctl restart mariadb

レプリカをモニタリングする

MariaDB側で

> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
       Replicate_Wild_Do_Table: db_search.%
                 Last_IO_Errno: 0
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Last_IO_Error になにか異常が出ていれば、内容に従って対応する。

レプリカ反映確認

マスターAuroraへのINSERT/UPDATEがターゲットに伝播していることを確認

mysql> UPDATE db_search.t_jobquery SET bi_processtime = UNIX_TIMESTAMP(now())*1000, i_status=0 WHERE bi_recordid =107288;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select i_status, bi_processtime from t_jobqueue WHERE bi_recordid =107288;
+----------+----------------+
| i_status | bi_processtime |
+----------+----------------+
|        0 |  1581574477000 |
+----------+----------------+

MariaDB [db_search]> select i_status, bi_processtime from t_jobqueue WHERE bi_recordid =107288;
+----------+----------------+
| i_status | bi_processtime |
+----------+----------------+
|        0 |  1581574477000 |
+----------+----------------+

レプリカターゲットのストレージエンジン変更

MariaDB [db_search]> ALTER TABLE t_tagproperty ENGINE=mroonga COMMENT='engine "InnoDB"';
Query OK, 9956068 rows affected (3 min 42.965 sec)

1000万行級だったので、それなりに時間かかりましたがそれも４分弱。
ストレージ変更後も、 show slave status\G にエラーがないこと、UPDATEなどがAuroraから伝播することを確認しました。

FULLTEXT INDEXの追加

形態素解析はMeCabで。

MariaDB [db_search]> CREATE FULLTEXT INDEX ftx_stringvalue 
ON t_tagproperty(t_stringvalue) COMMENT 'tokenizer "TokenMecab"';
Query OK, 0 rows affected (1 min 25.290 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [db_search]> SHOW INDEX FROM t_tagproperty WHERE Column_name='t_stringvalue'\G
*************************** 1. row ***************************
        Table: t_tagproperty
   Non_unique: 1
     Key_name: ftx_stringvalue
 Seq_in_index: 1
  Column_name: t_stringvalue
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment:
Index_comment: tokenizer "TokenMecab"

検索速い。

MariaDB [db_search]> SELECT t_stringvalue,MATCH(t_stringvalue) AGAINST("作業場所") AS score 
FROM db_mmjsearch.t_tagproperty 
WHERE MATCH(t_stringvalue) AGAINST("作業場所") 
ORDER BY score DESC LIMIT 5;
+-----------------------------------------------------------+-------+
| t_stringvalue                                             | score |
+-----------------------------------------------------------+-------+
| iOS標準ディレクトリ：ファイルの保存場所                   |  6513 |
| 本体工(場所打ち）                                         |  6513 |
| 作業場所の整地                                            |  6513 |
| 作業場所の地耐力を事前に確認すること。                    |  6513 |
| 作業場所の整地                                            |  6513 |
+-----------------------------------------------------------+-------+
5 rows in set (0.002 sec)

Bi-gramだとhitしそうなところで、Mecabだとhitしない例になってるかな？　「東京都港区」に「京都」の検索ではhitしてほしくないのが、形態素解析Tokenizerを使う主な理由になります。

MariaDB [db_search]> SELECT t_stringvalue,MATCH(t_stringvalue) AGAINST("業場") AS score FROM db_mmjsearch.t_tagproperty WHERE MATCH(t_stringvalue) AGAINST("業場") ORDER BY score DESC LIMIT 5;
+--------------------------------+---------+
| t_stringvalue                  | score   |
+--------------------------------+---------+
| 担当はその場に居る人           | 1048577 |
+--------------------------------+---------+
1 row in set (0.002 sec)

Mroongaの表記正規化

まずはNormalizerAutoを試してみます。

大文字・小文字や、全角・半角の区別なく検索する事が出来ます。
特殊文字への対応が厚く、例えば「㌖」は「キロメートル」に正規化されます。

MariaDB [db_search]> CREATE FULLTEXT INDEX ftx_stringvalue ON t_tagproperty(t_stringvalue) COMMENT 'tokenizer "TokenMecab", normalizer "NormalizerAuto"';
MariaDB [db_search]> SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty WHERE MATCH(t_stringvalue) AGAINST("マスト") LIMIT 5\G
*************************** 1. row ***************************
t_stringvalue: フォークリフトの運転席から身を乗り出して荷ずれを修正しようとして、誤って操作レバーに触れ、頭部をマストとヘッドガードの間にはさまれた。
MariaDB [db_search]> SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty WHERE MATCH(t_stringvalue) AGAINST("ﾏｽﾄ") LIMIT 5\G
*************************** 1. row ***************************
t_stringvalue: フォークリフトの運転席から身を乗り出して荷ずれを修正しようとして、誤って操作レバーに触れ、頭部をマストとヘッドガードの間にはさまれた。
MariaDB [db_search]> SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty WHERE MATCH(t_stringvalue) AGAINST("cdcd") LIMIT 1\G
*************************** 1. row ***************************
t_stringvalue: 3B833F3A-4D58-4725-A2B2-43257CDCD251
MariaDB [db_search]> SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty WHERE MATCH(t_stringvalue) AGAINST("ＣＤＣＤ") LIMIT 1\G
*************************** 1. row ***************************
t_stringvalue: 3B833F3A-4D58-4725-A2B2-43257CDCD251

Mroongaにはもっと高度にカスタマイズできるNormalizerがあります。
IT考古学では、カナ長音をマイナス記号にしている古文書が大量発掘されます。一部機種のカナフォントの長音の見た目が悪かったので流行してしまったんですよね。
OCR結果テキストも増えているので新規発生もとまりません。

読みがな検索

日本語全文検索では異体字も頭の痛い問題です。

  • 渡辺さん、斉藤さん、広田さん…

送りがな問題も

  • 焼肉、焼き肉

MroongaのTokenMecabには、読みがなオプションを付与することができます。

MariaDB [db_search]> CREATE FULLTEXT INDEX ftx_yomi_stringvalue ON t_tagproperty(t_stringvalue) COMMENT 'tokenizer "TokenMecab(\'use_reading\',true)", normalizer "NormalizerAuto"';
Query OK, 0 rows affected (1 min 47.840 sec)
MariaDB [db_search]> SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty WHERE MATCH(t_stringvalue) AGAINST("トウブ") LIMIT 5\G
Empty set (0.001 sec)

あたらない?!

MariaDB [db_search]> SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty FORCE INDEX(ftx_yomi_stringvalue) WHERE MATCH(t_stringvalue) AGAINST("トウブ") LIMIT 5\G
Empty set (0.002 sec)
MariaDB [db_search]> explain SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty FORCE INDEX(ftx_yomi_stringvalue) WHERE MATCH(t_stringvalue) AGAINST("トウブ") LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_tagproperty
         type: fulltext
possible_keys: ftx_stringvalue
          key: ftx_stringvalue
      key_len: 0
          ref:
         rows: 1
        Extra: Using where

先につくった読みがな無しのindexを使っています。FORCE INDEXつけても効いてません。1カラムにFULLTEXT INDEXは1つなんでしょうか。

MariaDB [db_search]> DROP INDEX ftx_stringvalue ON t_tagproperty;
MariaDB [db_search]> SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty FORCE INDEX(ftx_yomi_stringvalue) WHERE MATCH(t_stringvalue) AGAINST("トウブ") LIMIT 5\G
*************************** 1. row ***************************
t_stringvalue: フォークリフトの運転席から身を乗り出して荷ずれを修正しようとして、誤って操作レバーに触れ、頭部をマストとヘッドガードの間にはさまれた。

先につくったFULLTEXT INDEXを削除すると、読みがなであたりました。

MariaDB [db_search]> SELECT t_stringvalue FROM db_mmjsearch.t_tagproperty WHERE MATCH(t_stringvalue) AGAINST("頭部") LIMIT 5\G
*************************** 1. row ***************************
t_stringvalue: フォークリフトの運転席から身を乗り出して荷ずれを修正しようとして、誤って操作レバーに触れ、頭部をマストとヘッドガードの間にはさまれた。

読みがなオプションは、読みがな「も」indexに入れるオプションのようです。漢字でもちゃんと入っています。なるほどFULLTEXT INDEXを２つ持つ必要はないのですね。

読みがな適用範囲

MeCabが読めない単語・熟語ではこのご利益は効きません。
このへんの調査になると、10万行程度の開発用DBでは既存の語彙がたりないので、mroonga関数を使って、TokenMecabが読めるかどうか、即値で与えて確認できます。

MariaDB [db_search]> SELECT mroonga_command('tokenize \'TokenMecab("use_reading",true)\' "斎藤 齋藤 齊藤 齊籐 齋籐"') as mecab\G
*************************** 1. row ***************************
mecab: [{"value":"サイトウ","position":0,"force_prefix":false,"force_prefix_search":false},
{"value":"サイトウ","position":1,"force_prefix":false,"force_prefix_search":false},
{"value":"サイトウ","position":2,"force_prefix":false,"force_prefix_search":false},
{"value":"ヒトシ","position":3,"force_prefix":false,"force_prefix_search":false},
{"value":"トウ","position":4,"force_prefix":false,"force_prefix_search":false},
{"value":"齋籐","position":5,"force_prefix":false,"force_prefix_search":false}]

実用するときも、ユーザが入力した検索語を、mroonga_command(`tokenize...)　して、出てきた読みがなを追加する形になるでしょう。

結果

Aurora MySQL を EC2 MariaDB/Mroonga にレプリケートして、MeCabの形態素解析とMroongaの正規化ありの日本語検索が可能なことを、開発環境の疎通レベルで確認しました。

Todo

  • NormalizerNFKC100のカスタマイズ
  • 精度検証
  • 性能検証
  • 運用設計と運用検証
3
5
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
5