mysqlの瞬間的に増えてしまうスレッドの原因を調べたい。

前提・実現したいこと

最近気づいたのですが、現在運用中のmysqlサーバー（mysql 5.7.17）で、
1時間に何度かのペースで、ロードアベレージが増えるようになりました。

そのため、接続エラーでサイト閲覧ができない人が発生してしまっています。

その時間帯のshowprocesslistを確認したところ、「User: unauthenticated user」のスレッドが一気に100件以上増えていました。

このスレッドの発生原因と、対応策の調べ方が分からなくて困っています。

発生している問題・エラーメッセージ

*************************** 180. row ***************************
     Id: **********
   User: unauthenticated user
   Host: 192.168.**.**:****
     db: NULL
Command: Connect
   Time: 0
  State: login
   Info: NULL

試したこと

「DNSの問題なので、my.cnfにskip-name-resolveを記述するとよい」というような記事があったのですが、skip-name-resolveは既に入っていました。

利用環境

CentOS release 6.8 (Final)
5.7.17-log MySQL Community Server

m.ts10806

2021/05/14 02:57

SQL Serverは別製品なので本件とは無関係に思います

hilopon

2021/05/14 03:03

ご指摘ありがとうございます。訂正いたしました。

neko_the_shadow

2021/05/14 04:14

skip-name-resolveが有効な場合、接続元のIPアドレスを使って、接続元の検証を行うのですが、接続元のIPアドレスに何か心当たりはあるでしょうか?

hilopon

2021/05/14 07:23 編集

showprocesslistのHostの部分のIPですよね？うちのwebサーバー4台からのアクセスのようです。ローカルIPでアクセスされています。この4台のwebサーバーから、問題のmysqlサーバーには、普段問題なくアクセスしていてwebサイトも表示できています。

neko_the_shadow

2021/05/14 15:08

管理下のサーバからのアクセスであれば、IPアドレスの検証がボトルネックになっているのは考えずらいですね…。であれば、原因として思いつくのはこのあたりでしょうか? 問題が発生する前に、システム側に何か変更などは行っていないでしょうか? ・利用者が増えた結果、DBへの接続数が急激に増えた・行儀の悪いプログラムが大量のDB接続を確保している・利用したDB接続をクローズせずに放置して、コネクションが枯渇した

hilopon

2021/05/16 00:22

ご回答ありがとうございます。 PVはほぼ変化が無く、またプログラムのアップデートもほぼ無いです。けれど、プログラムのメンテナンスもしばらく行われていなかったため、行儀の悪いプログラムの可能性はあります。プログラム側を疑ってみようと思います。ありがとうございます！

行動規範の内容に同意します

回答1件

ベストアンサー

skip-name-resolveは既に入っていました。

ではDNSでの名前解決が問題ではなく、何処かのネットワーク経路が遅いかって事が一番考えられますので、
ネットワーク経路について調査してみては如何ですか。
以下参考
あのホストまでの経路や通信ボトルネックを調べるには？ tracerouteコマンド

また、名前解決をどのようにしているのか不明なので、確かな事ではないのですが、skip-name-resolveを逆に無効にしてみてはどうでしょうか。
以下参考
skip-name-resolveでMySQLへの接続がどの程度速くなるのか試してみた

投稿2021/05/14 03:22

sazi

総合スコア25327

hilopon

2021/05/14 07:41

ご回答ありがとうございます。 showprocesslistで表示されるHostが接続元だと思っています。 tracerouteはその接続元IPまでを調べるのですよね？そうすると、今回出ているIPが、ローカルIPでwebサーバーからのアクセスのようなので、問題ないのではないかと思います。 traceroute to 192.168.10.25 (192.168.10.25), 30 hops max, 60 byte packets 1 192.168.10.25 (192.168.10.25) 0.260 ms 0.259 ms 0.255 ms > skip-name-resolveを逆に無効にしてみてはどうでしょうか。あ、その発想は無かったです。ただ、dbサーバーが3台構成で、masterを止めるとslaveがmasterに昇格してしまうため、止めるのが怖いです。なので最終手段にしようと思います。

sazi

2021/05/14 08:37 編集

https://dev.mysql.com/doc/refman/5.6/ja/show-processlist.html > unauthenticated user は、クライアント接続に関連付けられたが、クライアントユーザーの認証がまだ実行されていないスレッドを示します。 > dbサーバーが3台構成ネットワークというより、同期処理など冗長化構成による問題な気がしますね。冗長化で、一時的にサーバーがビジーになったりしていないでしょうか？

hilopon

2021/05/14 10:23

ご回答ありがとうございます。私の確認したタイミングでunauthenticated userだっただけで、その後、認証されたら普通のユーザー名に変わるのでしょうか？「unauthenticated user」というステータス自体が悪い状態（認証できなかったユーザー）と捉えていました。ということは、unauthenticated user自体は問題じゃなくて、一気にたくさんのプロセスが増えたことの方が問題なのでしょうね。 DBサーバーが3台構成というのは、master1台、slave2台で、GTIDを使ってレプリケーションしています。また、MHAというので監視して、masterが止まった時にslaveがmasterに昇格するようになっています。サーバーがビジーになっているかの調査方法が分かりません。スレッドが増えて、ロードアベレージが上がるのですが、それがビジーということなのでしょうか？

sazi

2021/05/14 13:51

「unauthenticated user」は待機している状態だと思います。詳しい事はわかりませんが、ビジーかロックでしょうね。ビジーの場合は概ねCPU使用率が高くなることで判断でき、ロックの場合はプロセスの状況などでの判断する事になるかと思いますが、具体的な調査方法については知見を持っていません。

hilopon

2021/05/16 00:28

ご回答ありがとうございます。プロセスが一気に増えるので、ロックされているかもしれないです。「unauthenticated user」自体が何か不正アクセスやプログラムの不具合などで、平常の接続と違うものなのかなと考えていたのですが、通常のwebサーバーからの接続が想定外に多かったのかもしれないので、webサーバー側のプログラムを疑ってみようと思います。ありがとうございます！

行動規範の内容に同意します