オープンソースプラットフォームで ProxySQL を使用して、Amazon Aurora クラスターでの SQL の読み取りと書き込みを分割する方法

ブログ記事Amazon Aurora PostgreSQL で読み書き用に pgpool の単一のエンドポイントを設定する方法では、Amazon Aurora PostgreSQL エンドポイントの読み取りおよび書き込みの分割機能を使用するアーキテクチャを紹介しています。このタイプのアーキテクチャは Aurora PostgreSQL クラスターに最適ですが、データベースに Amazon Aurora MySQL クラスターを使用している場合はどうでしょうか? この記事は、Aurora MySQL エンドポイントで読み取りおよび書き込みの分割を実現するための中間レイヤーとして ProxySQL を紹介することによって、元の記事を補完します。

Amazon Aurora は、プライマリ DB インスタンス (クラスターエンドポイント) と、リードレプリカ (リーダーエンドポイント) のエンドポイントを提供します。Aurora は、クラスタエンドポイントを自動的に更新するので、常にプライマリインスタンスを指し示すようできています。リーダーエンドポイントを使用して、使用可能なすべてのリードレプリカにまたがる読み取り操作のための接続に対して DNS ラウンドロビンを実行します。さらに、Amazon Aurora カスタムエンドポイントを使用して、アプリケーションのトラフィックをさらに分離することができます。

Amazon Aurora Replica では、通常 100 ms 未満のレプリケーションラグが発生します。したがって、アプリケーションで遅延が許容される場合は、以下に示すように、クラスターエンドポイントとリーダーエンドポイントの両方を使用して、水平方向に拡張されたデータベースを利用することができます。

前の図は、使用するエンドポイントを決定するアプリケーションの現在のアーキテクチャを示しています。ただし、読み取り用と書き込み用両方のデータベースエンドポイント管理は、複雑なアプリケーションになります。一部のサードパーティ製ドライバーは、より狭い範囲のユースケースしかサポートしていませんが、この記事のアイデアを読み書きを分割するユースケースにより広く適用することができます。

この記事では、ProxySQL を使って、書き込みトラフィックをクラスターエンドポイントへ、読み取りトラフィックをリーダーエンドポイントに自動的に転送する MySQL 互換の単一 Aurora エンドポイントを構築する方法をご紹介します。以下の図は、ProxySQL ミドルウェアに基づいて提案されるソリューションを示しています。

アーキテクチャ

ProxySQL は、MySQL データベースとデータベースクライアント間に存在する、GNU General Public License (GPL) でライセンスされる MySQL プロキシミドルウェアです。この例では、次の図のアーキテクチャを使用してデプロイします。

この図は、ミドルウェアとして ProxySQL を使用して、MySQL 互換の Aurora クラスターに単一のエンドポイントを提供する方法を示しています。Aurora クラスターは、1 つのプライマリインスタンス、2 つのアベイラビリティゾーンと 2 つのプライベートサブネットにデプロイされた 2 つの Aurora リードレプリカで構成されています。クラスターには、ProxySQL インスタンスからの受信データだけを許可するセキュリティグループがあります。

ProxySQL は、フェイルオーバーの目的で、単一のアクティブインスタンスを持つ Auto Scaling グループ内の Network Load Balancer の背後にデプロイされています (この記事でのデモ目的のため)。以下に示すように、master.yaml ファイル (Resources.ProxySQLStack.Properties.Parameters にあります) を更新することで、この設定を変更することができます。

MinSize: 2
MaxSize: 4
DesiredCapacity: 2

この設定の変更により、通常の負荷用に 2 つの ProxySQL インスタンスがプロビジョニングされ、ピーク負荷時には最大 4 つのインスタンスまで拡張できます。また、ProxySQL ノードの 1 つに障害が発生した場合でも、データベースの可用性が向上します。

ProxySQL は、許可された Classless Inter-Domain Routing (CIDR) ブロックからのアクセスだけを許可するセキュリティグループのプライベートサブネット内でも隔離されています。

アクセスサブネットは、ProxySQL の持続エンドポイントを構築している Network Load Balancer をホストしています。したがって、ProxySQL インスタンスが失敗し、Auto Scaling グループが新しいインスタンスを作成すると、データベースエンドポイントは変更されません

AWS CloudFormation によるデプロイメント

この例で使用されている AWS CloudFormation テンプレートは、この GitHub リポジトリにあります。この例では、複数のネストされた AWS CloudFormation テンプレートを使用して、仮想プライベートクラウド (VPC) のインフラストラクチャ、セキュリティグループ、Aurora クラスター、および ProxySQLミドルウェアをデプロイしています。ネストされたスタックを使用すると、大きなスタックをいくつかの再利用可能なコンポーネントに分割することができますAWS CloudFormation がまだ不得意な方は、AWS CloudFormation のドキュメントを参照してください。

テンプレートのデプロイに関する指示については、GitHub の README ファイルを参照してください。以下のセクションでは、主要部分について説明します

Amazon Aurora クラスタの作成

次の AWS CloudFormation スニペットでは、VPC インフラストラクチャとセキュリティグループの準備後に、3 ノードの Amazon Aurora クラスタを作成する方法を紹介します。Amazon Aurora は 1 つのノードをプライマリとして、他の 2 つのノードをリードレプリカとして設定します。

DBAuroraCluster:
    Type: "AWS::RDS::DBCluster"
    Properties:
      DatabaseName: !Ref DatabaseName
      Engine: aurora-mysql
      MasterUsername: !Ref DatabaseUser
      MasterUserPassword: !Ref DatabasePassword
      VpcSecurityGroupIds: 
        - !Ref DBFirewall
      Tags:
        - Key: Project
          Value: !Ref ProjectTag
  DBAuroraOne:
    Type : "AWS::RDS::DBInstance"
    Properties:
      DBClusterIdentifier: !Ref DBAuroraCluster
      Engine: aurora-mysql
      DBInstanceClass: !Ref DbInstanceSize
      Tags:
        - Key: Project
          Value: !Ref ProjectTag
  DBAuroraTwo:
    Type : "AWS::RDS::DBInstance"    
    Properties:
      DBClusterIdentifier: !Ref DBAuroraCluster
      Engine: aurora-mysql
      DBInstanceClass: !Ref DbInstanceSize
      Tags:
        - Key: Project
          Value: !Ref ProjectTag
  DBAuroraThree:
    Type : "AWS::RDS::DBInstance"    
    Properties:
      DBClusterIdentifier: !Ref DBAuroraCluster
      Engine: aurora-mysql
      DBInstanceClass: !Ref DbInstanceSize
      Tags:
        - Key: Project
          Value: !Ref ProjectTag 

ProxySQL のデプロイ

ProxySQLをデプロイする AWS CloudFormation テンプレートで、ELB ロードバランサーと Auto Scaling グループを設定します。Auto Scaling グループの起動設定では、AWS CloudFormation cfn-init ツールを使用して ProxySQL をデプロイし、設定します。

このソリューションでは、それぞれの Amazon EC2 インスタンスに Amazon Linux AMI を使用します。Amazon Linux AMI には、AWS とのシームレスな統合を可能にするパッケージと設定が含まれています。リポジトリはすべての AWS リージョンで利用可能であり、yum を使ってアクセスできます。各リージョンでリポジトリをホスティングすることで、データ転送料金なしで更新を迅速にデプロイすることができます。EC2 インスタンスのソフトウェアの更新の詳細については、Amazon Elastic Compute Cloud ユーザーガイドのインスタンスのソフトウェアの更新を参照してください。

まず、MySQL クライアントをインストールする必要があります。

sudo yum install -y mysql

次に、ProxySQL リポジトリをダウンロードします。

wget -P /tmp/ https://github.com/sysown/proxysql/releases/download/v2.0.0-rc2/proxysql-rc2-2.0.0-1-centos7.x86_64.rpm

そして、リポジトリをインストールします。

#Install proxysql
sudo yum install -y /tmp/proxysql-rc2-2.0.0-1-centos7.x86_64.rpm 

最後に、ProxySQL を起動します。

sudo service proxysql start

ProxySQL の設定

管理者の認証情報を使用してローカルに接続することで、MySQL クライアントを使用した ProxySQL の設定のほとんどを実行することができます。ProxySQL のデフォルトのユーザー名とパスワードは、

「admin」および「admin」です。

初期設定後にこれらの認証情報を変更することを強くお勧めします。

次のコマンドは、ProxySQL での最初の読み取りと書き込みの分割オプションを設定します。

mysql -u admin -padmin -h 127.0.0.1 -P6032 main < proxysql.sql

proxysql.sql ファイルに含まれているオプションは、読み書きトラフィックを迅速に分割するのに適していますが、より高度なクエリルーティングルールの設定は ProxySQL で行います。以下は、proxysql.sql ファイルの内容についての説明です。

まず、mysql_servers テーブルと mysql_replication_hostgroups テーブルが設定に対してクリーンであることを確認します。mysql_servers テーブルには、ProxySQL によってプロキシされるすべてのバックエンドエンドポイントのエントリが含まれています。mysql_replication_hostgroups テーブルには、読み取り専用エンドポイントの自動識別と分離のための設定が含まれています。

バージョン 2 より前では、ProxySQL は「read_only」変数をチェックして、ライターエンドポイントからリーダーエンドポイントを決定していましたが、Amazon Aurora は、「innodb_read_only」変数に基づいてリーダーとライターの定義をベースにしています。ProxySQL バージョン 2.0 では、mysql_replication_hotgroups テーブルの check_type 列を使った変数定義のカスタム仕様のサポートが追加されました。

delete from mysql_servers where hostgroup_id in (10,20);
delete from mysql_replication_hostgroups where writer_hostgroup=10;

次に、mysql_servers テーブルでエンドポイントを設定します。

INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('${ClusterEndpoint}',10,3306,1000,2000);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('${ReaderEndpoint}',20,3306,1000,2000);

次に、「innodb_read_only」の check_type 値を使用して、mysql_replication_hostgroups テーブルを設定して、ライターホストグループとリーダーホストグループを定義します。

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment,check_type) VALUES (10,20,'aws-aurora','innodb_read_only');

ここで、ProxySQL サービスが再起動されても保持されるように、設定をディスクに書き戻します。

LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

次に、mysql_query_rules テーブルが設定に対してクリーンであることを確認します。

delete from mysql_query_rules where rule_id in (50,51);

次に、必要性に合わせてクエリルーティングルールを定義します。シャーディング、ダイジェストベース、読み取り/書き込み分割ベースなど、さまざまなユースケースをサポートするようにルールを定義することができます。この例は、基本的な読み取り/書き込み分割シナリオの設定を示しています。

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (50,1,'^SELECT.*FOR UPDATE$',10,1), (51,1,'^SELECT',20,1);

前の 2 つのルールは、すべての書き込みトラフィックが書き込み側ホストグループに送られ、すべての読み取りトラフィックが読み取り側に送られるように設定します。

この場合も、ProxySQL サービスが再起動されても保持されるように、設定をディスクに書き戻します。

LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

次に、mysql_users テーブルが設定に対してクリーンであることを確認します。

delete from mysql_users where username='${DatabaseUser}';

次に、ProxySQL 設定でバックエンドユーザーを定義します。

insert into mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) values ('${DatabaseUser}','${DatabasePassword}',1,10,'${DatabaseName}',1);

この場合も、ProxySQL サービスが再起動されても保持されるように、設定をディスクに書き戻します。

LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

次に、ProxySQL でモニタリングユーザーを設定します。このユーザはバックエンドのモニタリングに使用され、使用およびレプリケーションクライアントの権限が必要です。

UPDATE global_variables SET variable_value='${DatabaseUser}' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='${DatabasePassword}' WHERE variable_name='mysql-monitor_password';

次に、バックエンド MySQL のバージョンを設定し、いくつかのモニタリング間隔を微調整します。

UPDATE global_variables SET variable_value='${BackendMySQLVersion}' WHERE variable_name='mysql-server_version';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

この場合も、ProxySQL サービスが再起動されても保持されるように、設定をディスクに書き戻します。

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

設定用に proxysql.sql ファイルで、以下の変数を指定する必要があります。

また、ProxySQL がクライアント接続用に提供するデフォルトのポートとインターフェイスは次のとおりです。

0.0.0.0:6033 # 変更した場合は、サービスの再起動が必要です。

そして、以下は ProxySQL の管理のデフォルト設定です。

0.0.0.0:6032

設定テスト

これで、SQL 命令コマンドを発行し、ProxySQL のトラフィックが期待通りであることを確認できます。

まずテーブルを作成し、いくつかの行を挿入します。

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (1, 'Doe', 'John', '123 Main Street', 'North York');
INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (2, 'Doe', 'Jane', '456 Second Street', 'Toronto');
INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (3, 'Smith', 'Robert', '789 Third Street', 'Vaughan');

ProxySQL 統計スキーマにチェックインすると、これらのステートメントが Amazon Aurora プライマリホストグループ (ホストグループ 10) に送信されていることがわかります。

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "select hostgroup, schemaname, username, digest, digest_text, count_star from stats_mysql_query_digest;"

次に、クエリを作成します。

select * from Persons;

ProxySQL 統計スキーマにチェックインすると、このステートメントが Amazon Aurora リーダーエンドポイント (ホストグループ 20) に送信されていることがわかります。

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "select hostgroup, schemaname, username, digest, digest_text, count_star from stats_mysql_query_digest;"

最後のテストとして、ProxySQL が トランザクションに埋め込まれた SELECT ステートメント を処理することを確認します。

BEGIN;
    INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (4, 'Garg', 'Raj', '234 Fourth Street', 'North York');

    INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (5, 'Doe', 'John', '373 Fifth Street', 'Brampton');
    SELECT * from Persons where PersonID > 3;
COMMIT;

埋込み SELECT はトランザクション内に書き込まれている行を照会することから、これらのステートメントはすべて、プライマリノードにヒットするはずです。ログでちゃんと機能しているかを再度確認可能です。

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "select hostgroup, schemaname, username, digest, digest_text, count_star from stats_mysql_query_digest;"

この設定を本番で使用する前に、個人のデータベースクライアントで必ず完全な回帰テストを行うことをお勧めします。ProxySQL ミドルウェアの拡張性に懸念がある場合、Auto Scaling グループが負荷に基づいて複数のインスタンスと拡張を許可している、クラスター化された ProxySQL インスタンスを使用することを検討してください。

まとめ: 簡素化に向けた努力

この記事では、ProxySQL 使って読み込みトラフィックを読み込みエンドポイントに自動的に転送する Amazon Aurora クラスターの単一エンドポイントを構築する方法を学びました。この方法を使用すると、アプリケーションと Amazon Aurora の接続処理簡素化が可能です。

カスタムロジックに基づいてデータベース接続をルーティングするなど、より高度なルーティングオプションが必要な場合は、ProxySQL のクエリルーティング機能をさらに詳しく調べることができます。カスタムロジックを使用すると、カナリアテストやウェイトベースルーティングのために新しいスキーマに小さな割合のクエリを送信するなど、より高度なルーティング処理が可能です

将来的には、レプリカの設定を必要とせずにデータベースのバックエンドを自動的に拡張できる Amazon Aurora Serverless を視野に入れておいてください。

著者について

Tulsi Garg は、AWS ワールドワイドパブリックセクターカナダチームのシニアソリューションアーキテクトです。彼は、カナダの公共部門の顧客 (政府機関、非営利団体、スタートアップ企業および教育機関) と協力して、クラウドへの過程を加速させています。彼は IoT に情熱を注いでおり、テクノロジー玩具を買うための言い訳として彼の娘を使っているようです。

Diego Magalhães は、ワールドワイドパブリックセクターで働いている AWS シニアソリューションアーキテクトであり、カナダ全土の教育機関の顧客と仕事をしています。常に新たな課題を探しており、最新の課題はトロントの寒い冬とブラジルの晴天を交換することでした。 ☃️