zabbixのデータが増えすぎ問題
去年、監視のために導入したzabbix（ver2.4）ですが、対象サーバが、１２０台くらいあり、テンプレートの監視項目をそのまんま適用してたら、高負荷状態になり、画面ももっさり、監視ももっさりしてきてしまった。
導入後に、zabbixサーバ自体のメンテを怠ってたのは知っていて、いつか対応しなきゃね。って話を、導入した協力会社の人としてたんだが、その人がいなくなってしまい、対応できるのが他におらず、対応することに。
STEP1. 監視項目の見直し
まず、アイテム（監視項目）の減少。
テンプレートの監視項目をすべて適用しているので、一旦、最低限必要なものを覗いて、監視項目を無効化してまわる。
最低限必要なものとは、PING監視と、ディスク容量監視と、アプリケーションログの監視。
3600ほどあった有効な監視項目を1600ほどに削減。（有効になってても実際に監視に使ってない項目もある）
STEP2. データ容量の見直し
過去データを削除するが、対象テーブルの特定。
サイズを調べてみる。（こちらの記事を参考：データベースとテーブルのサイズを確認する方法 - ふってもハレても）
| table_name | engine | tbl_rows | rlen | allMB | dMB | iMB |
|---|---|---|---|---|---|---|
| history_uint | InnoDB | 136449572 | 74 | 14039 | 9748 | 4291 |
| history | InnoDB | 17177763 | 69 | 1734 | 1131 | 603 |
| trends_uint | InnoDB | 14692072 | 80 | 1134 | 1134 | 0 |
| events | InnoDB | 3087769 | 72 | 444 | 212 | 232 |
| history_log | InnoDB | 700203 | 202 | 173 | 135 | 38 |
| trends | InnoDB | 2045698 | 74 | 146 | 146 | 0 |
hisutory_uintの1.3億レコードにびびるが、色々、サイトを見て回ると、データを圧縮しつつ、データファイルをテーブルごとに分割すると良いよとのこと。 mysqlのバージョン的にも適合するので、これでいくことに。
httpd、zabbix-server、mysqldのサービスを止めて、my.cnfを書き換える。
/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server=utf8
#ここから追加
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
#ここまで
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
mysql起動し、alter tableで対象のテーブルを再構成していく。
history_uintは、でかいので後回しに。
alter table
alter table history ENGINE=INNODB ROW_FORMAT=Compressed;
alter table trends ENGINE=INNODB ROW_FORMAT=Compressed;
alter table trends_uint ENGINE=INNODB ROW_FORMAT=Compressed;
alter table history_log ENGINE=INNODB ROW_FORMAT=Compressed;
alter table events ENGINE=INNODB ROW_FORMAT=Compressed;
再度、データサイズをチェック
| table_name | engine | tbl_rows | rlen | allMB | dMB | iMB |
|---|---|---|---|---|---|---|
| history_uint | InnoDB | 156635215 | 65 | 14047 | 9751 | 4296 |
| history | InnoDB | 16962930 | 33 | 774 | 534 | 239 |
| trends_uint | InnoDB | 15694129 | 34 | 523 | 523 | 0 |
| events | InnoDB | 3073469 | 31 | 204 | 92 | 111 |
| history_log | InnoDB | 746147 | 94 | 86 | 67 | 18 |
| trends | InnoDB | 1963177 | 37 | 69 | 69 | 0 |
データファイルが別ファイルになっているか確認
ls -lh /var/lib/mysql/zabbix/*.ibd
-rw-rw---- 1 mysql mysql 212M 7月 24 15:21 2017 /var/lib/mysql/zabbix/events.ibd
-rw-rw---- 1 mysql mysql 792M 7月 24 15:21 2017 /var/lib/mysql/zabbix/history.ibd
-rw-rw---- 1 mysql mysql 92M 7月 24 15:21 2017 /var/lib/mysql/zabbix/history_log.ibd
-rw-rw---- 1 mysql mysql 64K 7月 24 15:57 2017 /var/lib/mysql/zabbix/history_uint_new.ibd
-rw-rw---- 1 mysql mysql 76M 7月 24 15:21 2017 /var/lib/mysql/zabbix/trends.ibd
-rw-rw---- 1 mysql mysql 536M 7月 24 15:21 2017 /var/lib/mysql/zabbix/trends_uint.ibd
大体サイズは半分になってる。 しかし、最大のテーブル、history_uintがやっかい。なにせ1.5億レコード。
えい、ままよやってまえ！
alter table history_uint ENGINE=INNODB ROW_FORMAT=Compressed;
週末流しっぱにしてたが、60時間たってもSQL終わらず。。。 あえなくKILL。こりゃtruncateしちゃうか。と考えるが、英語のサイトで、別テーブル作成して、任意の期間のデータをコピーして、リネームする手順が紹介されてた。
CLEANING UP THE ZABBIX DATABASE
CREATE TABLE history_uint_new LIKE history_uint;
INSERT INTO history_uint_new SELECT * FROM history_uint WHERE clock > '[timestamp]';
20日間ほどのデータを退避しようとしたが、そもそもSELECTが終わらねえ。うーん。 なので、空のテーブルのまま、TABLEを切り替えることに。（意味なし）
table 切り替え。再度、zabbixを起動して、問題なければDrop table実行
ALTER TABLE history_uint RENAME history_uint_old;
ALTER TABLE history_uint_new RENAME history_uint;
DROP TABLE history_uint_old;
で、３週間くらいたった。
| table_name | engine | tbl_rows | rlen | allMB | dMB | iMB |
|---|---|---|---|---|---|---|
| history_uint | InnoDB | 37300965 | 28 | 1496 | 997 | 499 |
| trends_uint | InnoDB | 14785949 | 36 | 509 | 509 | 0 |
| history | InnoDB | 7749555 | 40 | 423 | 299 | 124 |
| events | InnoDB | 2530589 | 31 | 172 | 76 | 95 |
| trends | InnoDB | 1704568 | 36 | 59 | 59 | 0 |
| history_log | InnoDB | 339517 | 102 | 43 | 33 | 10 |
サイズは、対応前の15%程度。 大丈夫そうなので、必要なサーバに関しては、リソース系の監視項目を追加しようと考え中。
SQL ServerよりOracleの方が優れてるなと思った話
Oracle社は好きじゃないんだが、OracleDBは好きです。前職では、なんちゃってDBAみたいな役割だったこともあり、Oracle Master Goldも取得した。
ですが、今の会社で昨年基幹システムのリプレースがあり、そのシステムがSQL Serverだったのですが、運用フェーズに入ってSQL Serverと戯れることが多くなりました。
で、改めてOracleの方が優れてるなぁ。という話。コスト的には断然安いからその点は置いとくとして、あくまで運用担当者目線。あと、今回導入したERPのソフトに起因するのもあるけれども、つらつらと書いてみる。
基本機能
読み取り一貫性とロック
よく言われるやつなんですが、Oracleの読み取り一貫性がSQL Serverにはない。ということで、色々挙動が変わるのですが、最も影響がでたとこでいうと、SQL ServerのSELECTはデフォルトで、SELECT FOR UPDATEを実行したようなロックがかかってまう。 なので、SELECT文とDMLが同時に走るとDMLがだいたい待たされる。
これなにが困るってシステムの重い検索系の処理が走ると、更新系がタイムアウトでコケる。これだいぶ痛い。 ユーザーからするとせっかく更新内容をツラツラ入力したのに、タイムアウトエラーで、入力した内容が全部パーになってやり直しに。
で、対策としては、SELECT文にNOLOCK オプションをつけることで、ロックをかけないでSELECTすることができる。 でも、これって、ダーティリードになるから、思ってるのと違うねん。
READ_COMMITTED_SNAPSHOT オプション をつけることで、DB全体に読み取り一貫性を付与することができる。というのは喧伝されているので、ベンダーの担当者にそのことを依頼したのだが、
「そのオプションをつけると、他の処理にどのような影響が出るか責任がもてません」
という衝撃の回答。ERPのソフトメーカーでも、事例がないということで、見送りに。今でもこの回答おかしいと思ってるのだが、どーなのだろうか。
WIndows認証という名の闇
SQL Serverでは、Windows認証というのがあって、簡単に言うと、OSにログインできたら、DBにログインしたことにすればいーじゃないか。というもの。 で、監査とかでDBユーザーの一覧見せろとかたまに言われるから、使わないログインユーザーは無効にしときたいよね。ということで、Windows認証が有効になってたユーザーを無効にしたら、ERPのソフトウェアがバックエンドで使ってたみたいで、処理が落ちた。 死ね。
dboってなんやねん。
SQL ServerとOracleでは、オブジェクトの構成が異なる。 Oracleでは、
ユーザー（スキーマ） ． テーブル 名
でテーブルを呼ぶことができる（自分の所有するテーブルならテーブル名だけで呼べる）
SQL Serverでは、
データベース ．スキーマ ． テーブル名
という構成となっている。テーブルを、データベースとスキーマという２段階で分けることができるのだが、そこまで抽象化して分ける用途はあんまない。今回のERPソフトは、テーブル数は800以上あるのだが、データベースを業務ごとに大きく分けているだけで、スキーマで分けることはしていない。 ということで、FROM句を書く際に、いちいち、
SELECT * FROM database1.dbo.table1
のように、長ったらしい修飾子を毎回打つ必要がある。（use句を仕えばデータベース名はデフォルト値を指定できる） 毎回、”dbo”ってなんやねんって思いながら無駄な入力をするのがストレス。
開発ツール編（SQL DeveloperとSQL Server Management Studio）
それぞれ、DB用の開発ツールが公開されているが、これもOracleの方が使いやすい。
SQL Developer
SQL Server Management Studio（SSMS）
サクッとテーブルの中身が見たい
Oracle SQL Developerは、左のツリーから、テーブル名クリックすると、右側のエリアにタブが開いて、データタブを選択すると、テーブル内のデータがとりあえず表示される。 さらに、WHERE句の条件を入力するフィルターボックスがあり、そこに条件を打ってデータを検索できる。
SQL Server Management Studioでは、テーブル名を右クリックして、1000件選択する っていうメニューをクリックすると、SQL文が展開されて、下部にデータが表示される。 そして、展開されたSQL文にWHERE句を記述する必要がある。なにより、データの表示領域が狭い。
この地味だけど毎回やる作業ってのは、積み重なってかなりストレスになる。
SQLのエディター画面の表示形式
Oracle SQL Developerは、SQLエディターのエリアは一つのみ。 SQL Server Management Studioはタブ方式でいくらでも開ける。 この点は、SQL Serverの方が便利っぽく思えるが、タブがどんどん開くので、どこに何があるかわからなくなってしまう。Oracleは、１つのエリアに複数のSQLを書くような前提になっているので、わかりやすい。
で、この違いが効いてくるのが、複数のSQLを一つのタブに書いた際の挙動。
Management Studioでは、複数のSQLを書いた場合に、実行を押すと、書いてあるSQL全てが実行される（最初の図参照） 対するOracle SQL Developer は、;（セミコロン）で、区切られた１文のみを実行する。
これは、断然Oracleの方が使いやすい。Management StudioもSQLを選択することで、その文のみを実行できはするが、選択するのが面倒。
SQL エディター画面の表示形式２
Oracle SQL Developerでは、接続を切断しても、エディター画面は残ったまま。そこで、異なるインスタンスに接続し、残ってた旧接続のエディター上のSQLを実行すると当たり前のように接続エラーが発生する。
SQL Server Management Studio では、なんと同じことをすると、切断したはずの接続に対してSQLを発行してしまう。 この仕様のせいで、本番環境に繋いで、データ確認後、テスト環境に繋ぎ変えてDMLを実行するとアラ不思議、本番環境のデータが更新されてしまったではないか。
これは、オペミスの温床だからマジでやめてほしい。
ここでも読み取り一貫性が
Oracle SQL Developerだと、DML流してデータを更新してから、コミットするまでの間に、同一セッションでSELECT文を流すと変更の結果を確認することができる。 なので、コミット前にDML文が正しい結果かを確認し、それでOKならコミットして、変更を確定させることができる。
SQL Server Management Studio で同じことをすると、まず、BEGIN TRANSACTION 文で、トランザクションの宣言を記述しないといけない。そして、DML実行後のSELECT文はロック待ちになるので、延々待たされる。ので、毎回「あっ」てなって、SELECT文キャンセルして、nolockをつけて確認することになる。
読み取り一貫性考えた人ほんと天才。
文法
CREATE OR REPLACEがない
よく言われるやつ。
http://itmcreate.com/wp/archives/1373
SQL ServerでVIEWや、ストアドを作る場合は、Dropして、Createしなければならない。 で、Dropの文も、IF文で存在するかチェックして、Dropするような慣例句が使われる。 が、IF文でチェックするオブジェクト名のみ変換して、Drop文のオブジェクト名の変換を忘れて関係ないオブジェクトをDropしてしまうという不具合を発生させてしまうことに。
※2018/3/30 追記 SQL Server2016から、DROP IF EXISTS 構文が追加され、IF文とDrop文が分かれる課題は解消されているようです。 うちの環境は2014ですけども・・・。
列名を括弧で囲む、Nで囲む
Oracleでも、日本語列名とかアンチパターンをやっちゃう場合があるが、SQL SERVERでも同様。 ただ、SQL Serverの場合は、バージョンの下位互換のために、SQLで指定する列名の前にNをつけとけ。という謎の不文律が存在するみたい。面倒だから、自分で処理書く時はつけてない。今のとこ問題出てないけどいいのかな。 https://support.microsoft.com/ja-jp/help/239530/you-must-precede-all-unicode-strings-with-a-prefix-n-when-you-deal-with-unicode-string-constants-in-sql-server
その他
MSのマニュアルって見づらいし、グーグラビティが低いよね。特に、DBのバージョンを指定してるのに、ほしくないバージョンの結果が出てくるの止めて欲しい。
ブクマコメントで指摘がありましたが、確かにOracleもグーグラビティ低いかも。これは、慣れの問題か。
