カテゴリー別アーカイブ: MySQL

ZabbixのDB(MySQL)をパーティショニングする(2)

テーブルをパーティショニング

さて、いよいよテーブルをパーティション化します。

標準状態のテーブルのままだと、下図のような感じで、1つの領域に順次データが蓄積され、肥大化してしまうわけですが‥

Table01

前回の記事で触れたように、時系列を示す clock 値を利用してパーティショニングすることで、下図のように、テーブル内に仮想的な枠ができる感じになります。

Table02

とりあえずは、各テーブルをパーティション化するコマンドを流してしまいます。
最初のパーティション化段階では、clock 基準では全てを1つのパーティションへ入れるような形で指定し、それらを、itemid ベースで 10 個ずつに分けるように指定しておきます。
このようにすることで、後から clock で分けるルールを追加指定することで細分化させることができ、さらに、古くなったパーティションだけを DROP することもできるようになります。

時系列での分割 – プロシージャの作成

前項でとりあえずは itemid 基準で 10 分割するようなパーティション化を施したわけですが、時系列基準でのルールについては、マトモに指定していません。
このままでは、LESS THAN (MAXVALUE) に合致するデータ、すなわち全てのデータが、全部1つの扱いにしかならず、全然ありがたくない状態になってしまいます。

前回触れたように、数日単位で分けて管理したいのですが、パーティション操作のためには SQL を実行する必要があります。
しかも、数日単位で「次のデータを入れるためのパーティション」を準備、「古くなったパーティションを破棄」と、毎度毎度、手で実行するのは現実的じゃありません‥。
そこで、MySQL のストアドプロシージャ機能を使って、関連処理を自動化してしまいます。
利用するプロシージャを以下に示します。

少々長いコードを貼りつけてしまいました。これらは、大きく3つのプロシージャで構成されています。

  • partition_drop_older_or_empty
    古くなったパーティションを破棄 (DROP) するプロシージャ
  • partition_create_newer
    MAXVALUE のパーティションを、未来に備えて2分割するプロシージャ
  • maintenance
    上記2プロシージャを連続実行するメインの処理

いずれも、現在日時をベースに、予め決めておいた分割間隔・保存期間に従って、新しいパーティションの作成や、古いパーティションの破棄処理を行うように実装されています。

時系列での分割 – 詳細解説

おおよその処理の流れや、実行していることの意味合いについて、少し触れておきます。

maintenance

先頭付近、KEEPDAYS で、ヒストリ系データの保持期間を決めています。前回の記事で決めたとおり、45 日という設定にしてあります。
同様に、SPANDAYS で、分割の基準になる日数を指定しています。前回の記事で決めたとおり、3 日という設定にしています。
ここでの設定値を変えるだけで、保持期間や分割単位を変更できるようにしてありますが、運用を開始してしまってからは、大きく変更しないことをお薦めします‥。
途中で変えてしまった場合の検証は十分に実施していません(汗)

partition_drop_older_or_empty

DB 情報を保有している information_schema から、プロシージャの実行時に与えられたテーブル名に関するパーティション分割情報を調べています。
分割されている複数のパーティションのうち、KEEPDAYS を超えるデータのみが保存されているパーティション、すなわち、古いデータしか含まれておらず、もう捨ててしまっても良いパーティションを探します。

古いパーティションが見つかった場合には、DROP PARTITION 命令を使って、当該パーティションを DROP してしまいます。この命令は、DELETE ~ WHERE 命令と違って、瞬時で処理が戻るため、あまり DB の負荷になることはありません。

処理のついでに、存在しているパーティションのうち、データが全く含まれていないパーティションについても、削除処理をしています。
パーティション化だけで古いデータを消している場合には滅多に起こり得ない状態ですが、Zabbix の HouseKeeper 処理を併用したり、別の削除処理を利用しているなどの場合、ムダなパーティションを排除できるので、このようにしてあります。

partition_create_newer

older_or_empty と同じように、information_schema から、テーブルに関する情報を調べています。
現在時刻よりも未来のデータが入るように指定されたパーティションが、既に存在している場合には特になにもしません。
前回のパーティション分割実行から一定以上の時間が経過していて、前回作成した新しいパーティションに対し、既に何らかのデータが入りはじめているような場合に、本来の機能が動作します。
未来のデータが入るように指定されたパーティションが見当たらない場合、現在時刻よりも未来のデータ、SPANDAYS に指定された期間分のデータが入るように指定した新しいパーティションを作成します。

時系列での分割 – プロシージャの動作確認

念のため、プロシージャの動作を確認します。
確認を実施するために、上に掲載したプロシージャの中の一部を書き換えます。
実際に分割・削除を実行するための行である「EXECUTE STMT」の行を「#」文字を使って、コメント化します。

残念ながら、プロシージャの中を一部だけ書き換えることはできませんので、書き換えたプロシージャ全体を用意して、再度コンソールから流し込む形になります。

準備ができたら、いよいよ動作の確認です。
コンソールから、以下のように入力し、実行させてみます。

実行すると、画面上に、いくつかの表示が流れていくと思います。
そのうち、「LOG」と書かれているものは、記録用のログ出力ですので、大して気にする必要はありません。

注目するべき出力は、「@sql」と書かれているものです。
これが「パーティション分割・削除のために実行される SQL」に相当します。

実例を挙げます。
この記録の場合、history テーブルの pmax と名付けられているパーティション(MAXVALUE までの値を受け入れるように指定されていた最終パーティション)を2つに分け、clock の値が「1406041200」未満のものを「p20140723_00」と名づけた新規パーティションに、それを超える値 (1406041200 ~ MAXVALUE まで) のものを、従来通り「pmax」と名づけたパーティションへ、それぞれ分けるようにしなさい、といった指示になります。
1406041200 というのは、現在時刻から算出された時刻値です。例の場合には「2014/7/23 00:00:00」を示しています。
先に解説したとおり、現在時刻を基準に、分割日数分のデータを受け入れるための枠組として、新しいパーティションを作っているような処理になっています。

ここまでで、動作の確認も完了しました。
先ほどコメントアウトした処理を元の状態に戻し、実際に実行されるようにして、再度プロシージャを投入します。

準備ができたら、実際に実行してみます。
コンソールから、先ほどと同じように入力し、実行させてみます。
1度だけでなく、2・3度実行してみてください。そうすることによって、「今すぐは使わないけれど、近い将来に使われるためのパーティション」も含め、作成・準備されます。

時系列での分割 – プロシージャの自動実行

ここまでで、必要に応じて古いパーティションを捨て、新しいパーティションを準備する‥という作業が、プロシージャの実行1行で出来るようになりました。
残る問題は‥「え‥?これ‥毎日毎回ボクが手で実行するの‥?面倒くさい‥忘れたらどうなるの‥」ということです。
そうです。Zabbix は放っておけば、時々刻々と新しい監視データがサーバへ届き、溜まってしまいます。
それに合わせるように、日々、パーティションの状態をメンテナンスしなければいけません。
そこで、cron に指定して使えるようなスクリプトも併せて用意します。

任意のディレクトリに配置し、次のような形で、cron へ仕掛けてください。
実行時に、コンソールに出力された内容をログファイルへ記録するような形にしています。
スクリプト内では、まず、MySQL のコンソールを起動し、ストアドプロシージャを実行しています。完了後、過去の実行時に記録したログファイルのうち、古いものを削除しています。

おわりに

これで、やっとのことで、パーティショニングを用いた Zabbix データベースのメンテナンスを実現・組み込むことができました。
現行最新の Zabbix 2.2 では、DB 周りのパフォーマンスがかなり上がっており、ここまでチューニングしなければいけないことは稀ですが、1.8 や 2.0 では、かなりの効果があるのもまた事実です。

以下に、本記事にて掲載したソースコード・スクリプト類を、ファイルとして置いておきます。
参考までにご活用いただければ幸いです。

ZabbixのDB(MySQL)をパーティショニングする(1)

はじめに

Zabbix を長期間使っていると、各監視対象から収集した履歴データを保存しているデータベースの各テーブルが大きくなってしまい、色々な不都合が出てきてしまうことがあります。

  • 収集した値をの格納する動作が遅くなってくる
  • トリガー判定の処理で、過去の履歴データを利用する場合に、判定演算の動作が遅くなってくる
  • Web UI でのデータ表示、一覧画面、グラフ表示等の動作が遅くなってくる
  • 過去から蓄積された履歴データのうち、古いものを削除する処理 (HouseKeeper) の動作が遅くなってくる

いずれも、DB サーバの CPU 負荷が上がってしまったり、I/O Wait が上がってしまったりと、見事なまでに、Zabbix / DB サーバのパフォーマンスに影響を与えてしまいます。
特に、監視対象となるホストの数が多い場合、監視対象のアイテム数が多い場合、監視アイテムの収集間隔が短く、一定時間あたりの収集データ量が多い場合など、監視対象がある程度の規模になってしまうと、避けられない問題になります。
zabbix_server.conf を編集し、役割別のプロセス起動数や、キャッシュ容量などに関する設定をチューニングするほか、DB 側の様々な設定についてもチューニングし、うまくパフォーマンスが出るように対応しなければいけなくなってしまいます。
本記事では、DB に MySQL を利用する場合に、データの取扱方法を見直すことで、少しでもパフォーマンス低下を回避する方法について触れようと思います。

MySQL のテーブルパーティショニング機能

Zabbix で MySQL を使う場合、「innodb_file_per_table」オプションを利用して、テーブル単位に個別ファイルが作られるような設定にすることが動作条件となっています。
Zabbix の履歴データを格納するテーブルに注目した場合、そのレコード数は軽く 100万・1000万の単位、データファイルのサイズは、大きい場合には 10GB・100GB の単位になってしまいます。
大規模になってくると、履歴の検索や新規履歴データの格納のための処理が遅くなってしまうのも、ある意味納得ですよね‥。

MySQL 5.1 以降では、1テーブル内のデータを、複数のテーブルに分けて格納するかのような「パーティショニング機能」がサポートされています。本記事では、この機能を使ったチューニングについて触れていきます。

パーティショニング機能の詳細については、MySQL の本家で公開されているドキュメントのほか、以下に挙げるページに丁寧な解説があります。解りやすく情報まとめてくださっている方々に感謝感謝です♪

行データに含まれている特定カラムの値を条件・基準にして、1つのテーブルへ格納するデータを、いくつかの「パーティション」と呼ばれる複数の領域へ分割して保存することで、データ格納・検索などの時に、読み書きしなければいけない対象のデータ量を減らすことで、高速化を図るための機能です。
また、特定の条件を基準にして単純にn分割するだけではなく、「条件1」を基に分けた結果に対して、さらに別の「条件2」を指定することで、多重にデータを分割(サブパーティショニング)することも可能です。
マニュアルによると、テーブル1つあたりのパーティション分割数の最大上限は 1024 (サブパーティション含む) となっているようです。
分割の条件をうまく指定して、できるだけ細かく分割してあげれば、普段の動作において読み書きしなければいけないデータの量を、かなり減らすことができそうです。

Zabbix で効果的に活用するには?

Zabbix が取り扱うデータの中で、最も量が多く、パーティション機能による処理効率化の恩恵を受けられそうなのは、日々の監視動作によって収集される履歴データです。
Zabbix は、履歴に関するデータを、大きく2つに分けて保存・管理しています。

  • 直近に収集した値を、生データのまま保存しておくための「ヒストリ」
  • 各アイテム毎に「最大値・最小値・平均値」を1時間単位で算出し、傾向データとして保存しておくための「トレンド」

具体的には、DB 内の以下のテーブルそれぞれに、対象となるデータの型に応じて保存されています。

  • history
  • history_uint
  • history_str
  • history_text
  • history_log
  • trends
  • trends_uint

これらのテーブルをパーティショニングすれば、効果がありそう‥ということですね。

古くなってしまったデータの取扱について

少し脇道ですが、溜まり続ける履歴データの扱いに関する部分に触れておきます。

Zabbix の監視アイテム設定には、「ヒストリ保存期間」「トレンド保存期間」という設定項目があります。
それぞれのアイテムについて収集した履歴データを、どの程度の期間、DB 内に保存しておくか?という設定です。
Zabbix Server の「HouseKeeper」機能が、古くなった履歴データを DB から削除し、不要なデータが DB に溜まり続けるのを防止しています。

この「HouseKeeper」機能は、「HousekeepingFrequency」で設定された時間毎に、「MaxHousekeeperDelete」で設定された上限データ件数を超えない範囲で、

というような SQL 処理を DB に対して実行することで、古くなったデータを DB から削除するような動作になっています。

ところがこの機能、残念ながら MySQL のプログラム実装との相性がイマイチ良いとは言えません。
単純な DELETE 処理にも関わらず、MySQL 側がこの DELETE 処理を瞬時には実行できず、結構バカにならない処理負荷になってしまうのです。

zabbix.com のフォーラムに、この HouseKeeper 処理によって、定期的に負荷が上昇してしまう症状を、如実に示すグラフを含む投稿がありました。
参考 URL : Zabbix forum : HouseKeeping

本記事では、この「HouseKeeping」によって起こってしまう負荷上昇の問題についても、パーティショニング機能を利用して解決してしまおうと思います。
MySQL の持つパーティショニング機能を利用して、古くなった履歴データの破棄を実行させますので、Zabbix Server 本体の持つ「HouseKeeper」機能は不要になります。
ここでは、zabbix_server.conf 内の「DisableHousekeeping」の設定値を変更し、機能そのものを無効化してしまいます。

Zabbix でパーティショニング機能を利用する実例

Zabbix でパーティショニング機能を使う方法や実例については、zabbix.com のフォーラムや、ZABBIX-JP のフォーラム、各種ブログ等、色々な場所で議論がなされています。
今回の記事の作成にあたっても、様々なサイトの記述を参考にさせて頂いています。そのうちのいくつかを、以下に掲載します。公開してくださっている方々に大変感謝しております。

多くの記事は 1.8 や 2.0 等、古い版の Zabbix を基に議論・解説されています。
しかし、現行の 2.2 や、近日中に公開予定となっている 2.4 等でも、それほど大きく変わるところは無く、ほぼ同じような考え方で活用することが可能です。
また、ここに挙げた事例は、単に SQL 文を実行して、パーティション化完了。というやり方にはなっていません。
DB 内に組み込むプロシージャスクリプトを活用し、「パーティション関連操作の自動化」「継続的なパーティショニングの維持管理」といった部分にまで目を向けて考えられています。
本記事でも、同じような手法で、「自動化」「維持管理」にも目を向けた方法で、進めていきたいと思います。

テーブルをどのように分割するか?

Zabbix のヒストリ・トレンド系テーブルの構造は、おおよそ以下のような構造になっています。
詳しい構造については、後ほど確認しますので、ここでは大まかな構造にだけ目を向けて考えていきます。

history系テーブルの構造

カラム名内容
itemid対象アイテムの itemid
clockデータ時刻(sec)
value生データの値
nsデータ時刻(ナノ秒)

これらを効率的に分割・管理するにはどうするのが良いでしょうか?
履歴の表示やグラフの表示等、Zabbix の持つ大半の機能においては「対象のデータ期間」「対象のアイテム項目」を明確に特定することが可能です。
そこで、次のような分割ルールを考えました。

  • clock に含まれる時刻情報を基準にして、時系列的に幾つかに分割する
  • 時系列的に分けた結果をベースに、itemid の値を基準にサブパーティション化することで、さらに細かく分割する

このルールに基づいて「別パーティション」として履歴データを保存しておけば、読み書きする際に触れるデータの量を減らすことができ、DB に対する負荷も減りそうです。

また、先に触れた「HouseKeeper」の代わりとなるような動作についても考えてみます。
「HouseKeeper」の処理で削除・破棄させてしまいたいのは、「古くなってしまった履歴データ」です。
時系列的なルールに基づいて「別パーティション」として履歴データを保存しておけば、不要になった時点で「パーティション」の単位で削除することで「HouseKeeper」と同じような動作になりそうです。

次に、「どの程度の数に分割するか」について考えます。
パーティショニング機能に関する解説で少し触れましたが、テーブル1つあたりの分割数には上限があり、1024 (サブパーティション含む) となっています。
実際のところは、あまり多数のパーティションに分割しすぎてしまっても、複数のパーティションを跨ぐような検索が多数起こってしまうことになり、逆にパフォーマンスの低下に繋がってしまうこともあるため、200-300 程度の分割に留めておくのが良い場合もあります。

今回考えたルールに従ってテーブルを分割した場合、結果作成されるパーティション数は、次のような計算式で考えることができます。

  • 「clock 基準での分割数」×「itemid 基準での分割数」

例えば、各ヒストリデータに対して、約1ヶ月分 (30日) の履歴データを、常時 DB 内に残しておきたいとします。
「clock 基準」のルールで5日単位に分割すると考えた場合、少なくとも、8分割は必要になってしまいます。
単純に30日と考えると6分割で良さそうなのですが、そう上手くは行ってくれません。
8分割として考えなければいけない理由、その内訳は、以下のとおりです。

  • 絶対に保持しておきたい 30日分 のデータが含まれるパーティションが合計6つ
  • 将来、増えるであろうデータを受け入れるための予約領域として、前もって確保しておきたいパーティションが1つ
  • 既に 30日 以上経過しているかもしれないが、パーティションの削除処理を行うまでのタイムラグの間、残り続けてしまうパーティションが1つ

過去パーティションの削除処理 (HouseKeeper 相当の処理) と、将来に備えた新規パーティションの作成処理は、スクリプト等を用いて自動化し、cron 等によってバッチ処理として実行させる必要があります。
実際にデータが出入りするタイミングと、バッチ処理のタイミングの間に存在するタイムラグを考えた場合、ある程度の余剰が必要になる、ということです。
さらに、「itemid 基準」のルールで、10 程度に分割すると考えると、8×10 = 80 分割となります。

これらから逆説的に計算してみると、itemid で 10 分割する場合、直近1年分を保存したければ、1パーティションあたりに少なくとも4日分程度の時系列データを割り当てて、90 分割程度になるように考えないといけません。
1パーティションあたりに3日分程度としてしまった場合、 時系列での分割数が 100 を超えてしまうため、テーブル全体でのパーティション分割数が 1024 を超えてしまいます。

履歴データを保持しておきたい期間、パーティションメンテナンスのバッチ処理を実施できる cron の実行間隔など、運用面で要求される制約事項を踏まえたうえで「clock 基準での分割数」「itemid 基準での分割数」を決定します。

例えば、次のような運用面での要望、制約事項等があると想定してみます。

  • 共通:パーティションの追加・削除等、メンテナンスに関する処理は、日次処理として cron にて実行
  • 共通:item 数はそれなりに多いので、itemid 基準では 10 程度には分割しておきたい
  • ヒストリ系:直近 30 日分の履歴データは絶対に保持しておきたい
  • ヒストリ系:余裕を見て、45 日分程度を残すことができれば、非常に嬉しい
  • ヒストリ系: → 1パーティションあたり、3 日分程度を保存するように考えれば、多めに見積もっても、総パーティション数は 200 程度
  • トレンド系:長期間の運用に備えて、10 年分程度保持できるようにしておきたい
  • トレンド系: → 1パーティションあたり、300 日分程度を保存するように考えれば、多めに見積もっても、総パーティション数は 150 程度

これらの要望、制約事項から決定した分割基準は、以下のように纏められます。
本記事の今後の解説文では、次のような分割基準をベースに記載します。適宜、読み替えてください。

  • ヒストリ系:itemid 基準で 10 分割、clock 基準で 3 日分単位にて分割、45 日以上経過したパーティションを破棄
  • トレンド系:itemid 基準で 10 分割、clock 基準で 300 日分単位にて分割、直近では 10 年を超えないため、パーティションの破棄については保留

テーブルを分割するために必要な準備

MySQL パーティショニング機能の制限で、「プライマリキーとして指定されているカラムしかパーティショニングの条件には使えない」という制約があります。

Zabbix 2.0 / 2.2 の場合、trends / trends_uint の両テーブルは、もともと「itemid / clock」の両カラムがプライマリキーとして指定されているので、特に問題はありません。
ところが、history 系の各テーブルについては、プライマリキーが指定されていなかったり、異なるカラム組み合わせがキーとなっていたりするため、標準のテーブルスキーマのままでは、パーティショニング機能を用いたデータ分割を実施することができません。
そこで、問題のあるテーブルに対して、プライマリキーの指定を変更することで、パーティショニングできるように対策します。
プライマリキーを追加・修正することによって、データ挿入・更新時のオーバーヘッドが標準状態よりも若干増えてしまうのですが、そのデメリットよりも、パーティショニングによる高速化メリットのほうが大きいためです。

初期状態のスキーマを確認

テーブルをパーティショニングするにあたり、プライマリキーやインデックスの変更、実行する ALTER 文を決定するために、まず、標準状態 (Zabbix 2.2) のテーブルスキーマを確認します。

プライマリキーの変更と、インデックス周りの整備

プライマリキーの指定を変更・修正するとともに、既存のインデックス設定等で、冗長・不要になってしまうものを削ります。

プライマリキー指定に含めたカラムは、レコード間での値重複が認められなくなってしまいます。
そのため、パーティショニングの条件として利用したい itemid / clock の各カラムだけをプライマリキーとして指定した場合、同じ itemid に対して、同じ clock 値を持つデータを複数登録しようとした場合に、エラーとなってしまいます。そのようなことが起きてしまわないよう、これらのカラム指定に加え、ns カラムも含めて指定するように考えます。

注意:プライマリキー・インデックスの設定を変更する際、既に大量の履歴データが登録されている場合には、構成の変更を実施するために相応の時間が掛かり、一時的にテーブルのデータ更新がロックされてしまいます。運用中の DB サーバを変更する場合には、運用に影響が出てしまわないよう、十分に注意をしてください。

テーブルをパーティショニング

いよいよ、各テーブルをパーティション化していく作業に着手します。

記事が長くなりすぎてしまうため、続きは次回の記事にて‥。
数日以内に公開できるよう、頑張ります~