WED, 2014/08/20 11:47 ANATOLIYDIMITROV
MariaDBでは、他のサービスと同様に最適なパフォーマンスを確保するにはユーザーのリソース使用量をモニターする必要がある。 MariaDBは、ユーザー毎に詳細な統計を提供し、それによってユーザーはデータベースサービスのモニタリングや最適化ができる。 ユーザーの統計は、共有の環境において、ある貪欲なユーザがサーバー全体の性能を低下させないようにするのにとりわけ有効である。 異常な利用を検知したら、これからみていくようにきめ細やかな制約を適用できる。
MariaDBでユーザ統計を有効にするには、サーバーのコンフィグファイル /etc/my.cnf.d/server.conf を編集する。
[mysqld]セクションに、userstat = 1を追記し、サービスを再起動する。
このタイミングから、MariaDBはinformation_schemaデータベースのUSER_STATISTICSテーブルにユーザ統計を収集し格納する。
USER_STATISTICSはメモリーエンジンを利用し、サービスを再起動したときに情報を保持しない。したがって、統計はMariaDBサービスを再起動したときにリセットされる。
手動でFLUSH USER_STATISTICS;コマンドを実行しても統計のリセットは可能である。
ユーザ統計を扱う
全てのユーザ統計を見る為には、SHOW USER_STATISTICSコマンドを使う。これで全てのユーザーの全ての情報が返却され、リソース利用を総合的に見られる。
この出力により、他のユーザーと比べて不適切に高レベルな利用をしたユーザーを特定できる。
クエリをフィルタリングし、select CPU_TIME from information_schema.USER_STATISTICS where USER='test1';の様なコマンドを直接データベースに発行することでより集約された情報が得られる。
このコマンドは、test1ユーザコネクションによって消費された累計CPU時間の秒単位での値を示す。
ユーザー統計を理解する
test1ユーザの全ユーザ統計出力例は下記のようになる。
MariaDB [(none)]> select * from information_schema.USER_STATISTICS where USER='test1' \G
*************************** 1. row ***************************
USER: test1
TOTAL_CONNECTIONS: 105
CONCURRENT_CONNECTIONS: 0
CONNECTED_TIME: 0
BUSY_TIME: 0.10427200000000013
CPU_TIME: 0.028732600000000018
BYTES_RECEIVED: 8190
BYTES_SENT: 86520
BINLOG_BYTES_WRITTEN: 0
ROWS_READ: 630
ROWS_SENT: 735
ROWS_DELETED: 0
ROWS_INSERTED: 0
ROWS_UPDATED: 0
SELECT_COMMANDS: 210
UPDATE_COMMANDS: 0
OTHER_COMMANDS: 0
COMMIT_TRANSACTIONS: 105
ROLLBACK_TRANSACTIONS: 0
DENIED_CONNECTIONS: 15
LOST_CONNECTIONS: 0
ACCESS_DENIED: 15
EMPTY_QUERIES: 0
1 row in set (0.00 sec)
フィールド名はどのような情報をもっているかを物語っている。最も重要なものは下記の通りである。
TOTAL_CONNECTIONS, CONCURRENT_CONNECTIONS, CONNECTED_TIME – いずれか、または全てが大きな値であれば'Too many connections'の様なエラーに出くわすだろう。標準では、MariaDBではサーバーへのコネクションの最大値は151で、積極的に利用しているユーザーは簡単に使い果たす値である。
BUSY_TIME, CPU_TIME – BUSY_TIMEはユーザーコネクションでアクティビティーが続いた時間を示し、CPU_TIMEはユーザーコネクションを提供する為に使ったCPUの時間を示す。後者はより重要で、ユーザーにCPU利用率への直接的な影響を示す。
BYTES_RECEIVED, BYTES_SENT - これらの2つの指標はMariaDBユーザが発生させるネットワークトラフィックをモニタリングするのに役立つ。通常時はハイトラフィックはデータベースに問題にならないが、サービスが過負荷の時、トラフィックの統計がが根本的な問題をより速く絞込むの助けとなるだろう。
BINLOG_BYTES_WRITTEN – この指標は、レプリケーションやバックアップ用に利用されるバイナリログの異常な活動を特定するのに役立つ。バイナリログが予想外に増え始めている場合は、この指標をまずはじめに確認してほしい。
ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS, COMMIT_TRANSACTIONS – これらの指標は、ユーザのSQLに関する詳細な情報を提供する。BUSY_TIMEとCPU_TIMEと共に、あるユーザーがシステムの負荷に与える影響の全体像の情報源となる。
ROLLBACK_TRANSACTIONS – 通常とは異なる大きな値をとっていたりピーク値をとっている場合、フロントエンドアプリケーションの問題を示しているかもしれない。多数のトランザクションのロールバックは、高負荷を引き起こす。なぜならば、フロントエンドアプリケーションは通常は情報を再作成したり再取得するのが通例で、その結果ロールバックする全てのトランザクションに対して追加の負荷が発生する為である。
DENIED_CONNECTIONS, ACCESS_DENIED – これらの2つの指標は、大多数のケースでセキュリティーの問題および、アプリケーションの不正ログインの問題のトラブルシューティングに役立つ。ユーザーが接続を拒否された時、DENIED_CONNECTIONSの値が増える。接続の拒否は通常は最初の段階の誤った権限を示す。他方で、ACCESS_DENIEDは通常、ユーザーが既に接続に成功しているが、あるリソース(データベースかテーブル)へのアクセスを拒否されたことを示す。
アクションをおこす
あるユーザーの高レベルな異常なアクティビティーを検知したら、そのユーザへのアカウントリソース制限機能を利用して、リソース制限を行うことが出来る。
例えば、test1ユーザに対しては次のクエリとなる。query update mysql.user set max_connections=10,max_updates=100,max_questions=1000 where user='test1';
指定された特定のユーザーのリソースは制限され、サーバーのパフォーマンスは通常通りに戻るだろう。
お分かりのように、MariaDBのリソース統計と制限は、サービスの性能最適化を維持するのに有用である。
