New Relic Flex を使用して MySQL のロック待ちの調査を簡単に！

　New Relic Flex を活用して MySQL のロック待ちの発生状況を可視化する方法について紹介します。ロック待ちやデッドロックが頻発するとデータベースのパフォーマンス低下やシステムのレスポンスが遅くなる原因となるため、システムを安定して運用するには対処しなければならない問題です。この記事では、New Relic の Infrastructure Agent にバンドルされた Flex というツールを使ってMySQLのロック待ち状況を調査する際に必要な情報を収集する方法を紹介します。

今回のポイント

この記事で紹介しているポイントは、次の２つです。

1. New Relic Flex を使って MySQL の情報を収集する方法がわかる
   　New Relic Flex を使ってロック待ちの情報をSQLでデータ収集する方法を紹介します。
2. New Relic Flex を使って収集したデータの確認方法がわかる
   　収集した情報をNRQLで取得して可視化する方法を紹介します。

最新のアップデートの詳細はこちら
New Relic アップデート一覧

無料のアカウントで試してみよう！
New Relic フリープランで始めるオブザーバビリティ！

New Relic Flex とは

　New Relic Flex は、YAML形式の設定ファイルを使って独自のインテグレーション作成できるツールでコンマンド実行結果やAPIのレスポンス等の情報を New Relic に保存できます。Flex についての詳細と設定例は、公式ドキュメントとブログで紹介しています。

前提条件

　今回の検証では MySQL バージョン 8.0系を使用しています。

MySQL から情報を取得する

　Flex を使って MySQL から情報を取得するためのサンプルが用意されている為、そのファイルを参考に作成します。

接続設定

　接続情報の設定もYAMLファイルに記載します。ユーザー名、パスワード、DBのホスト名、ポート番号の4つの情報を下記の設定に合わせて記載します。

mysql-database.yml

integrations:
  - name: nri-flex
    interval: 30s
    config:
      name: MySQLDbFlex
      apis:
        - database: mysql
          # ユーザー名：パスワード@tcp(DBホスト名:ポート)/sys
          db_conn: User:Password@tcp(localhost:3306)/sys
          logging:
            open: true
          db_async: true # process queries async

sys.innodb_lock_waits を活用してロック待ち情報を収集する

　InnoDB ストレージエンジンでロックの取得時に待機が発生している際、この View (sys.innodb_lock_waits) を SELECT すると該当するレコードが返されます。
このクエリは下記のサンプルで提供されているクエリを MySQL 8 向けにカスタマイズして作成しています。

mysql-database.yml

integrations:
  - name: nri-flex
    interval: 30s
    config:
      name: MySQLDbFlex
      apis:
        - database: mysql
          # ユーザー名：パスワード@tcp(DBホスト名:ポート)/sys
          db_conn: User:Password@tcp(localhost:3306)/sys
          logging:
            open: true
          db_async: true # process queries async
+         db_queries:
+           - name: MySQLCustomInnodbBlocking
+             run: SELECT r.trx_id waiting_trx_id,
+                     r.trx_mysql_thread_id waiting_thread,
+                     r.trx_query waiting_query,
+                     w.wait_age_secs wait_age_sec,
+                     b.trx_id blocking_trx_id,
+                     b.trx_mysql_thread_id blocking_thread,
+                     b.trx_query blocking_query
+                  FROM sys.innodb_lock_waits w
+                  INNER JOIN information_schema.innodb_trx b
+                  ON b.trx_id = w.blocking_trx_id
+                  INNER JOIN information_schema.innodb_trx r
+                  ON r.trx_id = w.waiting_trx_id;

sys.schema_table_lock_waits を活用してロック待ち情報を収集する

　リソースへのメタデータロックの取得中に待機が発生している際、この View (sys.schema_table_lock_waits) を SELECT すると、その情報に対応するレコードが返されます。

mysql-database.yml

integrations:
  - name: nri-flex
    interval: 30s
    config:
      name: MySQLDbFlex
      apis:
        - database: mysql
          # ユーザー名：パスワード@tcp(DBホスト名:ポート)/sys
          db_conn: User:Password@tcp(localhost:3306)/sys
          logging:
            open: true
          db_async: true # process queries async
          db_queries:
            - name: MySQLCustomInnodbBlocking
              run: SELECT r.trx_id waiting_trx_id,
                      r.trx_mysql_thread_id waiting_thread,
                      r.trx_query waiting_query,
                      w.wait_age_secs wait_age_sec,
                      b.trx_id blocking_trx_id,
                      b.trx_mysql_thread_id blocking_thread,
                      b.trx_query blocking_query
                   FROM sys.innodb_lock_waits w
                   INNER JOIN information_schema.innodb_trx b
                   ON b.trx_id = w.blocking_trx_id
                   INNER JOIN information_schema.innodb_trx r
                   ON r.trx_id = w.waiting_trx_id;
+           - name: MySQLCustomBlocking
+             run: SELECT w.waiting_thread_id waiting_thread,
+                     w.waiting_query waiting_query,
+                     w.waiting_query_secs waiting_query_sec,
+                     b.thread_id blocking_thread,
+                     b.event_id blocking_event,
+                     b.digest_text blocking_query
+                  FROM sys.schema_table_lock_waits w
+                  INNER JOIN performance_schema.events_statements_history b
+                  ON b.thread_id = w.blocking_thread_id

　performance_schema.events_statements_history を利用することで、特定のスレッドが最近実行したSQLのテキストを直近N件まで取得できます。このテーブルは履歴データを保持しているため、ロックを取得しているクエリを特定するのに役立ちます。

完成した設定ファイル

　今回使用する Flex の設定ファイルは下記の通りです。このファイルを所定の場所（Linux：/etc/newrelic-infra/integrations.d、Windows： C:\Program Files\New Relic\newrelic-infra\integrations.d）に配置すると設定は完了です。

　設定ファイルの検証を行う場合は手動で実行することが可能です。下記のドキュメントのトラブルシューティングに方法が紹介されているので参考にしてください。

mysql-database.yml

integrations:
  - name: nri-flex
    interval: 30s
    config:
      name: MySQLDbFlex
      apis:
        - database: mysql
          # ユーザー名：パスワード@tcp(DBホスト名:ポート)/sys
          db_conn: User:Password@tcp(localhost:3360)/sys
          logging:
            open: true
          db_async: true # process queries async
          db_queries:
            # sys.innodb_lock_waits
            - name: MySQLCustomInnodbBlocking
              run: SELECT r.trx_id waiting_trx_id,
                      r.trx_mysql_thread_id waiting_thread,
                      r.trx_query waiting_query,
                      w.wait_age_secs wait_age_sec,
                      b.trx_id blocking_trx_id,
                      b.trx_mysql_thread_id blocking_thread,
                      b.trx_query blocking_query
                   FROM sys.innodb_lock_waits w
                   INNER JOIN information_schema.innodb_trx b
                   ON b.trx_id = w.blocking_trx_id
                   INNER JOIN information_schema.innodb_trx r
                   ON r.trx_id = w.waiting_trx_id;
            # sys.schema_table_lock_waits
            - name: MySQLCustomBlocking
              run: SELECT w.waiting_thread_id waiting_thread,
                      w.waiting_query waiting_query,
                      w.waiting_query_secs waiting_query_sec,
                      b.thread_id blocking_thread,
                      b.event_id blocking_event,
                      b.digest_text blocking_query
                   FROM sys.schema_table_lock_waits w
                   INNER JOIN performance_schema.events_statements_history b
                   ON b.thread_id = w.blocking_thread_id

New Relic 上で Flex から送信したデータを確認する

　Flex を使って転送したデータは NRQL を使って確認する必要があります。YAML設定ファイルで指定した db_queries の name が NRQL で使用する Event 名になります。

　また Data explorer からも Event が登録されていることが確認できます。

sys.innodb_lock_waits から収集した情報を確認する

　下記の NRQL クエリを実行することで sys.innodb_lock_waits から取得した情報を確認することが可能です。このクエリの例ではロックの待ち時間で取得しています。

SELECT max(wait_age_sec) 
FROM `MySQLCustomInnodbBlocking` 
FACET waiting_trx_id, waiting_thread, waiting_query, blocking_thread, blocking_query 
SINCE 1 day ago UNTIL now

　InnoDB ストレージエンジンでロックの取得時に発生している待機の状況は、New Relic の MySQL モニタリング統合を使って収集する下記のメトリクスでも確認することが可能です。

• db.innodb.rowLockCurrentWaits
  InnoDB テーブルの操作によって現在待機されている行ロックの数
• db.innodb.rowLockTimeAvg
  InnoDB テーブルの行ロックを取得する平均時間 (ミリ秒)
• db.innodb.rowLockWaitsPerSecond
  InnoDB テーブルの操作で行ロックを待機する必要があった 1 秒あたりの回数

New Relic の MySQL モニタリング統合の詳細は、下記の公式ドキュメントを確認してください。

sys.schema_table_lock_waits の情報を確認する

　下記の NRQL クエリを実行することで sys.schema_table_lock_waits から取得した情報を確認することが可能です。

SELECT max(waiting_query_sec) 
FROM `MySQLCustomBlocking`
FACET waiting_thread, waiting_query, blocking_thread, blocking_event, blocking_query
SINCE 1 hour ago LIMIT MAX 

performance_schema.events_statements_history のテーブルは履歴データを保持しているため、1つのロック待ちに対してブロックに関連しているクエリが複数表示されます。

まとめ

　New Relic Flex を活用することで独自のデータを収集することが可能です。今回は MySQL のロック待ち状況を調査するための情報を収集することで調査をより簡単に実施することができるようになります。Flex を使って MySQL から好きなクエリでデータを取得できるため、今回使用したクエリをカスタマイズして情報を増やしたり、新しい情報を取得したりしてこれまで属人化してきたトラブルシュートの工程を自動化して誰でも同じ情報にアクセスできるようにすることが可能になります。New Relic Flex だけではなく、MySQL のモニタリング統合や APM のトランザクションの情報等と合わせて確認することで DB のパフォーマンスやロック待ち等がサービスのパフォーマンスに与える影響を分析できるようになるでしょう。DB のパフォーマンス最大限に引き出し、ユーザー体験の改善につなげてください。

　New Relicでは、新しい機能やその活用方法について、QiitaやXで発信しています！
無料でアカウント作成も可能なのでぜひお試しください！

New Relic株式会社のX(旧Twitter) や Qiita OrganizationOrganizationでは、
新機能を含む活用方法を公開していますので、ぜひフォローをお願いします。

無料のアカウントで試してみよう！
New Relic フリープランで始めるオブザーバビリティ！