DBの肥大化に対する対応方法について

DBが肥大化し,約3GB/日程度HDDを消費し困っております。

【環境】
 ・Zabbix2.2
 ・CentOS6.5
 ・Mysql
 ・ホスト数:208
 ・アイテム数:4,662
 ・トリガー数:1,260
 ・1秒当たりの監視項目数:72

【これまでの対応状況】
 ・5/30 database downのメールが入る
 ・確認すると,rootディレクトリの空き容量がなくなっている
 ・それまでは1日当たりMB単位で使用量が推移していたが,数日前から急激に使用料が増大し
  空き容量枯渇に至る
 ・仮想環境でzabbixを動作させていたため,とりあえず100GB程容量追加して様子を見る。
 ・しばらくは,消費量が少なく推移していたが,またしても数GB単位で消費し始める
 ・その間,フォーラム等の情報で,

  alter table ***_*** TYPE=InnoDB;
  や,
  ALTER TABLE ***_**** ROW_FORMAT=Compressed;
  や,
  alter table ***_*** engine=innodb;
  をしてみましたが,
  59GB→57GBと2GBしか削減できませんでした。
 (こんなもんなんでしょうか?50%位に減るのかと期待してました)

 そこで,

 ・「管理」->「一般設定」->プルダウンから「データの保存期間」で
  すべての項目を90日に変更し,しばらく様子を見ましたが,使用量は減りません。

 ・delete from ***_*** where clock < '1430438400 ;
  で強制的に削除してみましたが,使用量は変わらず・・・。

 ちなみに,現状データ量の多いファイルは
  ・history_log.ibd    約60GB
  ・history_unit.ibd    約21GB   
  ・history.ibd      約2GB
 な,状況です。

どこか,対処方法が間違っているのでしょうか?

【設定状況】
my.cnf

innodb_file_per_table=1
# innodb_file_format=Barracuda
innodb_buffer_pool_size=64M
innodb_log_file_size=16M
innodb_log_files_in_group=2
 ※Barracudaを記載し,mysqldをrestartかけると,DBが起動しなかったためコメントアウトしました。

 innodbはONになってます
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.04 sec)

 よろしくお願いします。

 ちなみに,mysqlの知識ほとんどありません!

コメント表示オプション

お好みのコメント表示方法を選び「設定の保存」をクリックすると変更が反映されます。
ユーザー TNK の写真

利用されているMySQLのバージョンをお教えください。

書かれている内容から推測するに、テーブルの圧縮ができるバージ
ョン、もしくは設定ができないにも関わらず圧縮を試みようとされ
ているように見受けられます。

あと、単純にテーブル上のデータを削除したからといって、データ
ベースのファイルサイズは小さくなりません。

あと、急激にデータ量が増えたのであれば、ログ監視やzabbix_sender
を利用する監視のデータの量が増えていないか確認してみてください。

扱うデータサイズを削減されたいのであれば、

 ・監視項目を減らす
 ・監視間隔を延ばす
 ・ログ監視でパターンマッチングなどを利用して、ログファイル
  全部ではなく必要な文字列を含む行のみに制限して取集する
 ・ヒストリなどの保存期間を短くする

などの対応もご検討ください。

あと、

 innodbはONになってます

と書かれていますが、その後書かれている、

 innodb_file_per_table

は、innodbを利用する際、テーブルごとにファイルを分割するとい
う設定が有効になっていることを示すものです。
これが有効になっていても、これだけでは圧縮機能は利用できませ
ん。

■バージョンは下記のとおりです。
[root@zabbixserver]# mysql --version
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

■innoDBの状況は下記のとおりです。
[root@zabbixserver]# mysql zabbix -e "show engines;"
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+

■『監視のデータの量が増えていないか確認してみてください。』
 ・具体的な確認方法を教えていただけないでしょうか?

ユーザー TNK の写真

申し訳ありません。
確認の仕方を間違えてしまったようです。

mysqlコマンドのバージョンではなく、mysqldのバージョンが知り
たいのです。

MySQLのサーバはどうやってインストールされましたか?
もし、CentOS 6標準のパッケージを利用してインストールされたの
であれば、

 # rpm -q mysql-server

などと実行してその結果をお教えください。

■『監視のデータの量が増えていないか確認してみてください。』
 ・具体的な確認方法を教えていただけないでしょうか?

列挙するとすれば、以下のようなものを調査してみてはいかがでし
ょうか?

 ・監視対象のホストやアイテムを追加していないか?
 ・各ホストのネットワークトラフィックを確認して急増している
  ホストが無いか?
 ・データベースのデータファイルのうちhistory_log.ibdが大き
  くなっているようなので、やはりログ監視のアイテムでログが
  増加していないかを、history_logテーブルの件数やtimestamp
  で制限して直近のログがどのアイテムのものであるかを確認す
  る

■mysqldのバージョン

 確認方法は下記でよろしいでしょうか?(恥)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73 |
+-----------+

■MySQLのサーバはどうやってインストール

 [root@zabbixserver]# yum install mysql-server でインストールしました。

 ちなみに,

[root@zabbixserver]# rpm -q mysql-server
mysql-server-5.1.73-5.el6_6.x86_64

 です。

■データの増加量の原因調査

 ・監視対象のホストやアイテムを追加していないか?
 
 >激増の傾向が表れた時期には,特に追加はしていないはずです。
 
 ・各ホストのネットワークトラフィックを確認して急増している
  ホストが無いか?

 >探し方がわからなかったので,[監視データ]-[最新データ]で怪しそうなホストに
  目星をつけて確認すると,

  Windows ApricationLogを秒間何十件も吐いているホストを発見しました。
  ※とりあえず,該当ホストのアイテムを無効にしました。

 ・history_logテーブルの件数やtimestampで制限して直近のログがどのアイテムのも
  のであるかを確認する

  すみません。検索方法を確認中につき,回答は今しばらくお待ちください。

ユーザー TNK の写真

まずは、データ量が増加している原因も把握しておかないと、とり
あえず圧縮などでディスク容量の問題を回避しても、またすぐ利用
率があがってしまいますのでご注意ください。

もし、それらのログなどの増加分も管理されたいのであれば、それ
らも考慮したディスクのサイズを用意するようにしてください。

話は変わって、利用されているMySQLのバージョンが5.1.73、CentOS
6標準のパッケージを利用していて、そのパッケージのバージョンが、

 mysql-server-5.1.73-5.el6_6.x86_64

であるならば、InnoDB pluginを利用することができると思います。

これを有効にすることで、パフォーマンスの改善やテーブルの圧縮
などが利用できるようになります。
ただし、これを有効にするには、my.cnfなどに設定を追加すること
が必要です。

これまでご提示頂いた情報には、その為の設定が記載されていなか
ったので、恐らく設定をされていないのに、Barracudaを利用しよ
うとされたのではないでしょうか?

InnoDB pluginを有効にし、テーブルの圧縮も可能にするためには、
以下の設定をmy.cnfの[mysqld]セクションに設定することが必要で
す。

ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
innodb_file_per_table
innodb_file_format=Barracuda

※plugin-loadの行は画面上改行されていますがha_innodb_plugin.so
 まで1行です。

設定を行ったら、mysqldの再起動を行ってください。

正常にmysqldが起動できれば、最初に試されていたALTER TABLEで
のテーブル圧縮ができるようになるはずです。
各プラグインがちゃんと読み込まれたかは、以下のようにshow
pluginなどで確認してください。

mysql> show plugins;
+---------------------+--------+--------------------+---------------------+---------+
| Name | Status | Type | Library | License |
+---------------------+--------+--------------------+---------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | ha_innodb_plugin.so | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL |
+---------------------+--------+--------------------+---------------------+---------+
14 rows in set (0.07 sec)

mysql>

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

 Pluginの導入が必要だったのですね・・・・。

 先ほど,実験環境でご指示のあった通りに設定を投入し,Alter table *** TYPE=InnoDB;コマンドを投入したところ,

 使用量が約70%程度に減少しました。

 ちなみに,上記コマンドの後に

 ALTER TABLE *** ROW_FORMAT=Compressed;

 を実行しましたが,使用量は変わりませんでした。

 どちらかのコマンド1つでいいのでしょうか?

 本番環境でも実行してみようと思います。

 丁寧に解説いただき有難うございます。

ユーザー TNK の写真

実行された、

 Alter table *** TYPE=InnoDB;
 ALTER TABLE *** ROW_FORMAT=Compressed;

は、それぞれ意味が違います。

前者は、最適化が実行されます。
DELETE文などでレコードを削除した後のデータ用ファイル内で未使
用の部分を削除することができます。

後者は、元のフォーマットがCompressedでなかったら、Compressed
に変更して圧縮処理を行います。

サイズの変化がなかったとしたら、既に最適化されていたか、圧縮
してもサイズが削減されるようなデータではなかったのでしょう。