MySQL/MariaDB ProxyのMaxScaleをWeb向けの設定で気を付けること

# MySQL/MariaDB ProxyのMaxScaleをWeb向けの設定で気を付けること
主にMariaDB GaleraClusterの構成で使われることが多いMaxScaleについてご紹介したいと思います。

## 機能

- MySQLプロトコルとMariaDBプロトコルに対応していてアプリから接続を代理
- MariaDBのGaleraClusterに対応していて書き込みを1台にまとめる
- readwritesplitのrouterを使えば、実行されるSQLからReadとWriteを判断してMasterやSlaveへ振り分け

以上が動機になりそうな主な機能だと思います。

## 想定する利用ケース

1. MariaDB GaleraClusterを利用してWriteを集中Readを分散する
1. MySQL/MariaDBのMaster-Slave構成で死活監視して各々振り分ける
1. アプリ側の改修を少なくしてMaster-Slave構成に対応する
1. コネクションをまとめて再利用など高負荷環境での接続数やTCP数対策

まとめるとL7のロードバランサーとして利用することになります。

3と4については[ProxySQL](https://github.com/sysown/proxysql/wiki)も対応しているのでそちらもご検討ください。
そちらのほうが歴史は長そうです。

やはりGaleraで採用するケースが多そうですね。

### maxscaleはどこにインストールするか？

```
WEB | maxscale --> DB
```

基本的にはWEBサーバにMaxscaleをインストールしてローカルにアプリの接続をまとめてバックエンドのDBへ投げます。
できればローカルに接続する際はUnixSocketでやりとりするとTCPポート数消費の抑制になります。
WEBサーバの数が増えるにつれ耐障害性のことも重要になるので初めからこの構成にしておくと良いと思います。

## 利用に必要なDBユーザと権限
意外にDBユーザがいくつか必要なのでちょっとハマりましたのでまとめます。

|設定内での使用セクション|DB権限|ユーザ名(例)|備考|
|:--|:--|:--|:--|
|Monitor|REPLICATION CLIENT|repl|サーバの状態を見る死活監視に使われます|
|Service|SELECT(一部のmysqlテーブル), SHOW DATABASES|maxscale|DBユーザの認証を取得するために使われます|

maxscaleユーザの権限
SELECTのテーブルは以下のようになります。

```
CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale_pw';
GRANT SELECT ON mysql.user TO 'maxscale'@'%';
GRANT SELECT ON mysql.db TO 'maxscale'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
```

公式の記述は[チュートリアル](https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-setting-up-mariadb-maxscale/)にあります。

ということでmaxscaleの設定でそれぞれのセクションででてくるユーザとパスワードの設定はそれぞれ役目が違いますのでご注意ください。

## maxscaleの設定

公式での説明はまずはチュートリアルで、細かいところはトピックを見たほうが早いです。

### チュートリアル
- [Galera Cluster READ-WRITE 2port readcon運用](https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-connection-routing-with-galera-cluster/)
- [Galera Cluster readwrite-splitrouter運用](https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-readwrite-splitting-with-galera-cluster/)
- [Master-Slave READ-WRITE 2port readcon運用](https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-connection-routing-with-mariadb-replication/)
- [Master-Slave readwrite-splitrouter運用](https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-readwrite-splitting-with-mariadb-replication/)

### それぞれのセクションの説明

- [Monitors](https://mariadb.com/kb/en/mariadb-enterprise/maxscale-22-monitors/)
- [Monitor Common Parameters](https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-common-monitor-parameters/)
- [Routers](https://mariadb.com/kb/en/mariadb-enterprise/maxscale-22-routers/)

### ReadConnRouteのrouter_optionの意味
まずはMonitorが死活監視をしてサーバの状態やmaster/slaveを認識します。(Galeraでも)
そのMonitorの情報を利用しService(router)が機能します。
ということでGaleraの場合だけちょっと気を付けるところがあります。

まずGalera Monitorの設定で

- `disable_master_role_setting=false`でmaster|slaveを区別するようになる
- master認定は`use_priority=true`で手動で優先順位を設定するか`root_node_as_master=true`で自動にする。

Service（router）の設定は

|設定|serverの判定条件|
|:--|:--|
|router_option=synced,master|syncedかつmaster|
|router_option=synced|synced全て(master-slave関係なく全振り)|
|router_option=synced,slave|syncedかつslave|

という意味になります。

### readwritesplit routerでの気を付けること

大半のSQLはほどよくmasterとslaveにリクエストされますが、slaveに行かれると困るものが`select LAST_INSERT_ID();`です。
なのでLAST_INSERT_IDが発行されるときMaxscaleの[hint](https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-hint-syntax/)を利用してroutingを指定します。

hintの有効化をしてから

```
select LAST_INSERT_ID(); -- maxscale route to master
```

というようにするとmasterにinsertしてすぐやっても安心ですね。
ですがこの修正は、Frameworkによっては上書きするのが面倒かもしれません。
その際はreadconnでの2ポート運用が簡単です。
アプリ側で区別してmaster|slaveを分けていないといけませんが確実です。

## 持続コネクションで気を付ける点
 
公式曰く

- Routers
    - Connection based load balancing: * ReadConnRoute
    - Read/Write aware statement based router: * ReadWriteSplit

ReadConnはコネクション単位でのLB
ReadWriteSplitはSQLリクエスト毎のLB

なのでReadConnでは絶対にPHPなどスクリプト側ではPHPでいう持続的接続にはしないということ
それでもBackend-Maxscale間に対してまとめることはできます。
コネクションを再利用する場合は[設定](https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-administration-tutorial/#persistent)が必要です

### Serverの設定セクション

- persistpoolmax : default 0 (都度接続)
- persistmaxtime : default 0 (都度接続)

defaultが0で都度接続になっているからです。
`persistpoolmax`までコネクションをpoolするが`persistmaxtime`でそのコネクションの期限が来て閉じられます。
再利用する際は、resetがありinitializeをするようです。
これがないと副作用で前の状態を引き継ぎ違った内容を返答することが起こるため
再利用時のコストは少なからずあるようですがこのresetのおかげで特に処理に意識することなく再利用ができます。

### さらにパラメータの調整で考えること

- コネクションが持続するようになるとBackendDBの`max_connections`が消費されます。合計でこの数を超えないように`persistpoolmax`を設定する
- BackendDBの`wait_timeout,interactive_timeout`で切られるのでそれよりも小さい`persistmaxtime`を[設定](https://qiita.com/yut_h1979/items/cb12749e23478f45a939)する