ProxySQL を利用した Aurora MySQL の課題解決 [DeNA インフラ SRE]

こんにちは、 IT 本部 IT 基盤部第一グループの山本です。

今回は ProxySQL を利用した Aurora MySQL の新規接続の遅延問題の解消方法について紹介したいと思います。

Aurora MySQL のメリット

Aurora MySQL のメリットとしては本稿で詳しく語るまでもないですが、Writer の自動 Failover、Snapshot の取得の容易さ、Storage 故障の確率の低さに基づく安定性、Autoscaling による Reader の負荷対策など MySQL の運用課題を解決できるため非常に少ない工数で MySQL クラスターを運用できる非常に有用なプロダクトです。

Aurora MySQL の課題

ただし、高負荷の環境下においては課題があります。

新規接続遅延

Rails アプリケーションからの DB 接続は都度接続としており Aurora MySQL への接続は常に新規接続となるのですが、ピーク時の接続の際には TCP の SYN の再送が発生しDBへの接続時間が増加する傾向にありました。

SYN 再送発生時の tcpdump 例

23:59:31.654296 IP 10.0.1.100.55076 > 10.0.1.200.mysql: Flags [S], seq 1572791274, win 65535, options [mss 8961,nop,nop,sackOK,nop,wscale 11], length 0
23:59:32.674047 IP 10.0.1.100.55076 > 10.0.1.200.mysql: Flags [S], seq 1572791274, win 65535, options [mss 8961,nop,nop,sackOK,nop,wscale 11], length 0
23:59:34.690060 IP 10.0.1.100.55076 > 10.0.1.200.mysql: Flags [S], seq 1572791274, win 65535, options [mss 8961,nop,nop,sackOK,nop,wscale 11], length 0
23:59:38.854048 IP 10.0.1.100.55076 > 10.0.1.200.mysql: Flags [S], seq 1572791274, win 65535, options [mss 8961,nop,nop,sackOK,nop,wscale 11], length 0
23:59:38.855676 IP 10.0.1.200.mysql > 10.0.1.100.55076: Flags [S.], seq 4128464371, ack 1572791275, win 26880, options [mss 8960,nop,nop,sackOK,nop,wscale 8], length 0

また、対策として back_log を max_connection と同数にも増やしましたが効果は得られませんでした。

新規追加インスタンスの性能劣化
- Reader endpoint に Autoscaling で追加した Reader instance を自動的に追加することができますが、追加直後でも等分にリクエストが分散されるため投入後に性能劣化が発生します。

新規接続遅延の原因と対策

Aurora MySQL で新規接続に時間を要する原因は Aurora MySQL の仕組みに起因しており、新規接続のために Thread cache が不足した際に Thread create が発生することが原因と思われます。本来このようなケースの場合に MySQL では、 thread_cache_size を増やして新規接続時の Threads created を軽減するような対策を行いますが、 Aurora MySQL では Thread pool の仕組みが異なるため thread_cache_size を変更することができないためそのような対策が行なえません。

Aurora MySQL に適用されない MySQL パラメータ

対策としては以下のような方法があります。

アプリケーション側でコネクションプールを用意して接続を再利用し新規接続を軽減
Aurora MySQL の前段に Proxy を置く構成で Aurora MySQL の常時接続を維持して新規接続コストを軽減

新規追加インスタンスの性能劣化の原因と対策

Aurora MySQL の新規追加インスタンスの性能劣化の原因は主に InnoDB バッファープールの内容がメモリーに乗っていないために発生しています。

対策となるウォームアップは以下のような方法があります。

ウォームアップ用のクエリを実行してからインスタンスを追加
サービスに影響が出ない程度にインスタンスを徐々に追加するようにウエイトを調整してワークロードを実行

以上のような課題を同時に解決するために Proxy 構成で構築することにしました。

MySQL の Proxy プロダクト

Aurora MySQL に利用できそうな Database proxy は以下のようなものがあります。

今回は以下の理由からProxySQL を導入することにしました。

負荷状況に合わせて台数を調整できるのでコスト削減の余地がある
Aurora MySQL も公式にサポートしている
ウォームアップの実装など構成に柔軟性がある

ProxySQL とは

Database との接続を半永続化して再利用することで Database との接続コストを軽減する
接続先のルーティングをルール(クエリなど)にて行うことができる

ProxySQL の構成

AWS 社ブログでオープンソースプラットフォームで ProxySQL を使用して、Amazon Aurora クラスターでの SQL の読み取りと書き込みを分割する方法が紹介されています。

この構成を参考に以下のように設定構築しました。

ProxySQL x Aurora MySQL 運用で工夫したこと

ProxySQL 構成にするにあたっていくつか工夫したポイントがあります。

ProxySQL クラスターの負荷分散
ProxySQL 接続先リストの制御
ProxySQL の新規 Thread 作成のコスト
ProxySQL ネットワークコスト削減

ProxySQL クラスターの負荷分散

当初設計では ProxySQL の Health check をマネージドに任せたかったため、 AWS 社のブログ記事のように NLB ( ELB の一種で HTTP(S) 以外の場合に利用する) によるロードバランスにて実装しましたが、今回導入したアプリケーション環境では負荷が高い状況でパケットロスによる SYN の再送の現象が起きたため NLB の利用は断念しました。 NLB には性能の上限があるため、導入には十分な動作検証を行ってください。

対策として、DNS ラウンドロビンにて負荷分散する構成で構築しました。弊社では MyDNS(backend を MySQL に動的に構成できる DNS 権威サーバー)を利用してクラスターの制御を行なっており、MySQL 接続ができない際には DNS entry から外すツールが存在するため、その仕組みを用いて死活管理を行いました。

最近の環境の場合は、検証は行っていませんが CoreDNS の MySQL plugin を利用するなどして動的な DNS ラウンドロビンを実装可能ではないかと思います。

ProxySQL 接続先リストの制御

Autoscaling や Failover が発生した際に接続先のインスタンスのリストを追従する必要があります。対策として、ProxySQL の接続先の管理のため、 aws CLI にて database instance を検出して mysql_servers と同期するスクリプトを作成しました。また、 mysql_servers.weight を徐々に増やしていくことでウォームアップを実現することができます。これらは Aurora MySQL の場合 mysql_aws_aurora_hostgroups を利用することでも実現が可能ですが、今回は自前で実装しました。

接続先の同期処理のコード実装例

# initialize mysql_servers

import boto3
import mysql.connector

rds = boto3.client('rds', 'ap-northeast-1')
cluster_id = 'db-main'

instance_ids = []
for x in client.describe_db_clusters(DBClusterIdentifier=cluster_id)['DBClusters'][0]['DBClusterMembers']:
    if (not x['IsClusterWriter']) == ro:
        instance_ids.append(x['DBInstanceIdentifier'])

endpoints = []
for instance_id in instance_ids:
    ins = client.describe_db_instances(DBInstanceIdentifier=instance_id)['DBInstances'][0]
    if ins['DBInstanceStatus'] == 'available':
        endpoints.append(ins['Endpoint']['Address'])

conn = mysql.connector.connect(**connect_info)
cursor = conn.cursor()

for endpoint in endpoints:
    # 初期 weight 1 で作成し、定期的に weight を max weight まで追加
    args = {'endpoint': endpoint, 'hostgroup_id': 10, 'port': 3306, 'weight': 1, 'max_conn': 1000}
    if not dryrun:
        cursor.execute('''INSERT INTO mysql_servers (hostname, hostgroup_id, port, weight, max_connections)
            VALUES (%(endpoint)s, %(hostgroup_id)s, %(port)s, %(weight)s, %(max_conn)s)''', args)

cursor.execute('LOAD MYSQL SERVERS TO RUNTIME')
cursor.execute('SAVE MYSQL SERVERS TO DISK')

cursor.close
conn.close

ProxySQL の新規 Thread 作成のコスト

新規に構築した ProxySQL を cluster に追加する際にも Thread 作成コストが発生し、遅延が発生します。対策として、warmup のスクリプトを作成して新規投入前に Backend の接続の作成を行うように工夫しました。

ProxySQL ネットワークコスト削減

ProxySQL(EC2 インスタンス)を挟むことで AZ 間通信でネットワークのコストが発生します。

EC2 to EC2 インスタンス間 AZ 間通信
EC2 to Aurora MySQL インスタンス間 AZ 間通信

Figure 3. Workload components across Availability Zones

Overview of Data Transfer Costs for Common Architectures より引用

これらのインスタンス間通信を同一 AZ 間に限定することで通信コストを削減することができます。 Source IP address を元に名前を置き換えることができる RouteDNS を利用して同一 AZ 内の ProxySQL の名前で解決するようにしました。

RouteDNS の使用例

権威サーバーでの応答

$ dig +short db-main-apne1a.example.local @10.0.1.1
10.0.1.11 # proxysql-apne1a

$ dig +short db-main-az.example.local @10.0.1.1
10.0.1.11 # proxysql-apne1a
10.0.2.11 # proxysql-apne1c
10.0.3.11 # proxysql-apne1d

apne1a ゾーン上のサーバーの応答

# @webserver-apne1a
$ dig +short db-main-apne1a.example.local -p 1053
10.0.1.11 # proxysql-apne1a

# replace されて、db-main-apne1a.example.local と同じ結果を返す
$ dig +short db-main-az.example.local -p 1053
10.0.1.11 # proxysql-apne1a

RouteDNS の設定例

[resolvers.internal-resolver-1]
address = "10.0.1.1:53"
protocol = "udp"

[resolvers.internal-resolver-2]
address = "10.0.2.1:53"
protocol = "udp"

[groups.internal-resolvers]
resolvers = [ "internal-resolver-1", "internal-resolver-2" ]
type = "round-robin"

[groups.replace-apne1a]
type = "replace"
resolvers = [ "internal-resolvers" ]
replace = [
  { from = '^([^.]+)_az\.(example\.local\.)$', to = '${1}_apne1a.${2}' },
]

[groups.replace-apne1c]
type = "replace"
resolvers = [ "internal-resolvers" ]
replace = [
  { from = '^([^.]+)_az\.(example\.local\.)$', to = '${1}_apne1c.${2}' },
]

[groups.replace-apne1d]
type = "replace"
resolvers = [ "internal-resolvers" ]
replace = [
  { from = '^([^.]+)_az\.(example\.local\.)$', to = '${1}_apne1d.${2}' },
]

[routers.router-ipaddress-based]
routes = [
  { source = "10.0.1.0/24", types = ["A", "AAAA"], resolver="replace-apne1a" },
  { source = "10.0.2.0/24", types = ["A", "AAAA"], resolver="replace-apne1c" },
  { source = "10.0.3.0/24", types = ["A", "AAAA"], resolver="replace-apne1d" },
  { resolver = "internal-resolvers" },
]

[listeners.local-udp]
address = ":1053"
protocol = "udp"
resolver = "router-ipaddress-based"

[listeners.local-tcp]
address = ":1053"
protocol = "tcp"
resolver = "router-ipaddress-based"