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 - 投稿数: 4755
利用されているMySQLのバージョンをお教えください。
書かれている内容から推測するに、テーブルの圧縮ができるバージ
ョン、もしくは設定ができないにも関わらず圧縮を試みようとされ
ているように見受けられます。
あと、単純にテーブル上のデータを削除したからといって、データ
ベースのファイルサイズは小さくなりません。
あと、急激にデータ量が増えたのであれば、ログ監視やzabbix_sender
を利用する監視のデータの量が増えていないか確認してみてください。
扱うデータサイズを削減されたいのであれば、
・監視項目を減らす
・監視間隔を延ばす
・ログ監視でパターンマッチングなどを利用して、ログファイル
全部ではなく必要な文字列を含む行のみに制限して取集する
・ヒストリなどの保存期間を短くする
などの対応もご検討ください。
あと、
と書かれていますが、その後書かれている、
innodb_file_per_table
は、innodbを利用する際、テーブルごとにファイルを分割するとい
う設定が有効になっていることを示すものです。
これが有効になっていても、これだけでは圧縮機能は利用できませ
ん。
ken.t - 投稿数: 16
■バージョンは下記のとおりです。
[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 - 投稿数: 4755
申し訳ありません。
確認の仕方を間違えてしまったようです。
mysqlコマンドのバージョンではなく、mysqldのバージョンが知り
たいのです。
MySQLのサーバはどうやってインストールされましたか?
もし、CentOS 6標準のパッケージを利用してインストールされたの
であれば、
# rpm -q mysql-server
などと実行してその結果をお教えください。
列挙するとすれば、以下のようなものを調査してみてはいかがでし
ょうか?
・監視対象のホストやアイテムを追加していないか?
・各ホストのネットワークトラフィックを確認して急増している
ホストが無いか?
・データベースのデータファイルのうちhistory_log.ibdが大き
くなっているようなので、やはりログ監視のアイテムでログが
増加していないかを、history_logテーブルの件数やtimestamp
で制限して直近のログがどのアイテムのものであるかを確認す
る
ken.t - 投稿数: 16
■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 - 投稿数: 4755
まずは、データ量が増加している原因も把握しておかないと、とり
あえず圧縮などでディスク容量の問題を回避しても、またすぐ利用
率があがってしまいますのでご注意ください。
もし、それらのログなどの増加分も管理されたいのであれば、それ
らも考慮したディスクのサイズを用意するようにしてください。
話は変わって、利用されている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]セクションに設定することが必要で
す。
※plugin-loadの行は画面上改行されていますがha_innodb_plugin.so
まで1行です。
設定を行ったら、mysqldの再起動を行ってください。
正常にmysqldが起動できれば、最初に試されていたALTER TABLEで
のテーブル圧縮ができるようになるはずです。
各プラグインがちゃんと読み込まれたかは、以下のようにshow
pluginなどで確認してください。
ken.t - 投稿数: 16
TNKさん回答ありがとうございます。
Pluginの導入が必要だったのですね・・・・。
先ほど,実験環境でご指示のあった通りに設定を投入し,Alter table *** TYPE=InnoDB;コマンドを投入したところ,
使用量が約70%程度に減少しました。
ちなみに,上記コマンドの後に
ALTER TABLE *** ROW_FORMAT=Compressed;
を実行しましたが,使用量は変わりませんでした。
どちらかのコマンド1つでいいのでしょうか?
本番環境でも実行してみようと思います。
丁寧に解説いただき有難うございます。
TNK - 投稿数: 4755
実行された、
Alter table *** TYPE=InnoDB;
ALTER TABLE *** ROW_FORMAT=Compressed;
は、それぞれ意味が違います。
前者は、最適化が実行されます。
DELETE文などでレコードを削除した後のデータ用ファイル内で未使
用の部分を削除することができます。
後者は、元のフォーマットがCompressedでなかったら、Compressed
に変更して圧縮処理を行います。
サイズの変化がなかったとしたら、既に最適化されていたか、圧縮
してもサイズが削減されるようなデータではなかったのでしょう。