MySQLで特定ユーザがIO負荷をかけすぎる状態をどうにかしたい。

Question

やりたい事

MySQLでIO(read)に大きな負荷をかけるユーザがいます。
(なお大量クエリでなく、1クエリでテーブル全体を舐めるような処理をしているようです)

MySQL自体にIOを制限、もしくは結果的にIOが低下するような機能制限をするような設定はありますか？
ユーザ単位、クライアント単位が理想ですが、mysql全体での上限設定でも構いません。

検知した時は個別に対処する事も出来るのですが、
OS側や他の利用者にも影響が出る為、そもそも発生しないようにしたいです。

ご助言願います。

環境

• Redhat 7
• mysql5.7
• 自分はサーバ管理者

検討した事、現状など

• バッファー等リソースを増やす設定は基本出来ません。(既にギリギリ)
• ユーザの利用方法には口出しはしない方針です。
  (他のユーザに影響が出る時だけ、やむを得ず止める感じです)
• 同時接続数制限は設定済みですが、1クエリで大きなIOを出されます

cgroup等の利用も考えてますが、出来ればmysqlの設定で完結できればと思っています。

Answer 1

当該ユーザーがどんな目的で、どのようなクエリを発行しているかも大いに関係してくると思うのですが...

とりあえず当該ユーザーのクライアントとのI/Oを絞りたいだけであれば、MySQL事態の設定ではないですがiptablesで帯域制限をかけることができます。
ただ、当該ユーザーも不必要に高IOのクエリを投げているわけではない（取得したデータを業務で使用している）はずなので、突然通信速度が遅くなったらカチコミに来るでしょう。事前の説明はしっかり必要だと思います。

以下余談。

ユーザの利用方法には口出しはしない方針です。

と書いてありますが、必要に応じてユーザーに口も出せば手も出すのがサーバー管理者の仕事です。
高負荷のクエリを投げてシステム全体に影響を及ぼしているのであれば、部門や会社を挙げて対策を考えるべきです。
先にも書いたように当該ユーザーも嫌がらせで高負荷IOクエリを実行しているわけはないはず。業務で取得したデータを使用したいからクエリを投げているはずです。
どのようなクエリを投げているか（は、スロークエリーログを見ればわかると思います）、実行しているクエリにパフォーマンス的な改善余地がないのか、取得したデータをどのような業務に使用しているか、など詳細をヒアリングし、もっと別の解決方法を模索できないでしょうか。
テーブル全体を取得するようなクエリとのことなのでDWH的な利用をしたいのかもしれません。例えばユーザー側のローカルか所属部門にMySQLサーバを立ててもらい（状況によってはAccessでも良いでしょう）、負荷の低い時間に必要なテーブルをレプリケーションしてあげれば日中の負荷は本サーバ側には向かなくなります。
ストアドプロシージャにしてウェイトをかけながらfetchさせれば時間当たりのIOは減らせるかもしれません。
当該ユーザー当人に直接干渉するのが憚られる場合は、上役を通して政治的にお話し合いをしてもらうのも一つの手です（上司はそのために存在する！）

余談の方が長くなりました。私も何度か負荷が原因でデータベース管理者にカチコミを食らっています。もちろん全敗です。ただただ文句だけ言いに行くのではなく、解決策もある程度考えてあげるのが「良い管理者」なのかなと思います。上記カチコミの際にも相手サーバの管理者にはいろいろとアドバイスをいただきました。ありがたいことです。

Comments

[katosei]

回答ありがとうございます。

iptablesは考えたのですが、
問題となっているのは"ユーザとmysqld間"ではなく、"mysqldとストレージ間"なので
ユーザが少し不便になるだけで、IOは減らないのでは、という判断をしています。

こちら側は仕事として、負荷をかけすぎたユーザに対しては
一時的に権限をはく奪するなどの対処を行っています。
しかし影響が出た後で対処する、という事で後手になってしまうので
設定で回避できないだろうか、というのがこの質問の意図となります。

なお悲しいかな、仕組みの問題から、利用者とコミュニケーションをとるのは難しそうです。

[hope_mucci]

iptablesの件については、I/Oの総量は減りませんが時間当たりのI/Oは減りますので、その分負荷は軽くなると思います。そのかわり実行時間は伸びますけどね。

全体設定なので他ユーザーへの影響も大ですが、SELECTのLIMITを強制設定するオプションもあります。
https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_sql_select_limit
ただしSQL中のLIMITが優先されるので、ユーザーが気づけば回避されてしまいます。

> なお悲しいかな、仕組みの問題から、利用者とコミュニケーションをとるのは難しそうです。
しくみの問題までは言及しません（したところで有効な解決策は提示出そうにない。社内特有の問題でしょうから）本当にそうであれば、もうアカウント永久停止一択では。相手の業務に支障が出るとか知ったこっちゃないです。しかしそれは最終手段かなと思います。
他の方のコメントに「一人に辞めて貰っても、絶対に次が出てくると思っているので」と書かれていますが、同じクエリを発行する人がまた出てくるのであればそれはその部署の業務で必要ということですよね。そうすると根本対策はその業務自体をどうにかしなければならない、ということではないでしょうか。可能であれば改善するまでアカウント停止を行うというのも選択肢の一つかもしれません。

いずれにしても、相手が社外でも社内でも全くコミュニケーションが取れないわけはないと思っています。本人と相手取ることができないのであれば、とにかく上役を通して「お話し合い」すべきと思います。

OracleDatabaseであればユーザーごとにCPU時間やI/O量の制限をかけることができます。MySQLにはそういった設定は見当たらないですね...

Answer 2

OS側や他の利用者にも影響が出る為、そもそも発生しないようにしたいです。

注意しても聞かないならアカウントを剥奪します。
用途や理由をヒアリングしてください。迷惑かけてるのなら管理者として何かしらのフォロー必要。
サーバ落ちてからじゃ遅いです。何かあってからじゃ遅いです。

Comments

[katosei]

回答ありがとうございます。
こちら側は仕事として、負荷をかけすぎたユーザに対しては
一時的に権限をはく奪するなどの対処を行っています。
しかし影響が出た後で対処する、という事で後手になってしまうので
設定で根本対処できないだろうか、というのがこの質問の意図となります。

[m.ts10806]

完全な対策というのはほぼ無理と思います。
事前教育・研修による周知徹底を呼び掛けるくらいでしょうか。
勝手に制限してしまっては本当に必要なクエリの場合、それはそれで業務に支障がでるかもしれません。

Answer 3

max_execution_timeを設定して、SELECT文をタイムアウトで強制終了させるとかでしょうか?

https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html#sysvar_max_execution_time

オプティマイザヒントに設定することもできます。

https://dev.mysql.com/doc/refman/8.0/ja/optimizer-hints.html

問題のクエリを発行しているユーザが明らかなら、そのユーザにやめてほしいとお願いするのが一番手っ取り早そうですが…。

Comments

[katosei]

回答ありがとうございます。
こういう情報ありがたいです。
sleepを使っているパターンがあった気がするので、無条件には入れられませんが
今後の選択肢として確認させてもらいます。

やはり利用者側で修正してもらう方向になりますかね。
一人に辞めて貰っても、絶対に次が出てくると思っているので
出来ればサーバ側の仕組みで対処したかったのですが。

Answer 4

MySQL自体にIOを制限、もしくは結果的にIOが低下するような機能制限をするような設定はありますか？
ユーザ単位、クライアント単位が理想ですが、mysql全体での上限設定でも構いません。

「Disk I/O 時のスレッドの数をデフォルトの4 から少ない方へ絞る」というのはどうでしょうか？

innodb_read_io_threads

スレッドを少なくすることで、待ちが発生する確率が増え、結果、サーバー上で、DB 以外のプロセスが実行されやすくなる。という間接的なものになります。

MySQL 5.6 リファレンスマニュアル / ... / InnoDB ディスク I/O の最適化

Comments

[Yoshi88]

下記の文が気になったのですが、これは　スロークエリーログを記録していないので、”あくまで推測”ということなのですかね？？
>1クエリでテーブル全体を舐めるような処理をしをしているようです

テーブル全体を舐めるという処理が発生するのは、ほとんど場合、インデックスが利用されていない（または無い）というケースなので、インデックスが利用できるようにＤＢをチューニング（またはＳＱＬの修正）がベストな対応ではないかと。。。

[katosei]

スロークエリ等もとってないですね。
この辺りは別に設定者がおり、口出しが出来ない領域になってます。
こちらはサーバの負荷状況から安定稼働の為の設定を提案したい、という状態です。
(厳密にはもうちょっと踏み込めるのですが、概要的にはそんな感じです)

linuxのように、ユーザ毎に利用可能なリソースを制限出来ないかと考えていたのですが
皆さんの話を聞いた感じ、mysqlはそういう形での運用はされないっぽいですね。