情シスは何度でも甦るさ。

Oracle/Ruby好きの情シス部員がお送りしています

zabbixのデータが増えすぎ問題

去年、監視のために導入したzabbix(ver2.4)ですが、対象サーバが、120台くらいあり、テンプレートの監視項目をそのまんま適用してたら、高負荷状態になり、画面ももっさり、監視ももっさりしてきてしまった。

導入後に、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;

で、3週間くらいたった。

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%程度。 大丈夫そうなので、必要なサーバに関しては、リソース系の監視項目を追加しようと考え中。

csvからiCalファイルを出力するExcelマクロを作った

会社でグループウェアを移行することになった。 そこで、スケジュールデータをどう移行するかという話になったのだが、

グループウェアCSVをエクスポートできる
グループウェアiCalファイルのみインポートできる

ということで、うまく噛み合わず。

じゃぁ、旧グループウェアCSVをエクセルを経由して、iCalにしてしまえ。ということで、iCalを吐くマクロを作った。

www.dropbox.com

ちなみに、必要項目は最低限しか対応しておらず、繰り返しとかの予定は持ってけません。
あと、Excel2016 on Windows10で動いたけど、他の環境では試してまへん。

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 ServerOracleでは、オブジェクトの構成が異なる。 Oracleでは、

ユーザー(スキーマ) - テーブル 名

でテーブルを呼ぶことができる(自分の所有するテーブルならテーブル名だけで呼べる)

SQL SERVERでは、

データベース - スキーマ - テーブル名

という構成となっている。テーブルを、データベースとスキーマという2段階で分けることができるのだが、そこまで抽象化して分ける用途はあんまない。今回のERPソフトは、テーブル数は800以上あるのだが、データベースを業務ごとに大きく分けているだけで、スキーマで分けることはしていない。 ということで、FROM句を書く際に、いちいち、

SELECT * FROM database1.dbo.table1

のように、長ったらしい修飾子を毎回打つ必要がある。(use句を仕えばデータベース名はデフォルト値を指定できる) 毎回、”dbo”ってなんやねんって思いながら無駄な入力をするのがストレス。

開発ツール編(SQL DeveloperとSQL Server Management Studio)

それぞれ、DB用の開発ツールが公開されているが、これもOracleの方が使いやすい。

SQL Developer f:id:ryoben:20170528234303p:plain

Server Management Studio f:id:ryoben:20170528234312p:plain

サクッとテーブルの中身が見たい

Oracle SQL Developerは、左のツリーから、テーブル名クリックすると、右側のエリアにタブが開いて、テーブル内のデータがとりあえず表示される。
SQL Server Management Studioでは、テーブル名を右クリックして、1000件選択する っていうメニューをクリックすると、SQL文が展開されて、下部にデータが表示される。

小さな違いだけど、地味にOracleの方が便利。

サクッと検索したい

Oracle SQL Developerは、テーブル表示画面の上にWHERE句の条件を入力するテキストボックスがある。これで、テーブルを開いて、条件を打ってという一連の流れでデータを見れる。

SQL SERVER Management Studioでは、展開されたSQL文にWHERE句を記述する必要がある。

これは、だいぶOracleの方が便利

SQLのエディター画面の表示形式

Oracle SQL Developerは、SQLエディターのエリアは一つのみ。 SQL SERVER Management Studioはタブ方式でいくらでも開ける。 この点は、SQL SERVERの方が便利っぽく思えるが、SQL SERVEだと、タブがどんどん開くので、どこに何があるかわからなくなってしまう。Oracleは、1つのエリアに複数のSQLを書くような前提になっているので、わかりやすい。

で、この違いが効いてくるのが、複数のSQLを一つのタブに書いた際の挙動。

Management Studioでは、複数のSQLを書いた場合に、実行を押すと、書いてあるSQL全てが実行される(上図の参照) 対するOracle SQL Developer は、;(セミコロン)で、区切られた1文のみを実行する。

これは、断然Oracleの方が使いやすい。Management StudioもSQLを選択することで、その文のみを実行できはするが、選択するのが面倒。

SQL エディター画面の表示形式2

Oracle SQL Developerでは、接続を切断しても、エディター画面は残ったまま。そこで、異なるインスタンスに接続し、残ってた旧接続のエディター上のSQLを実行すると当たり前のように接続エラーが発生する。

SQL SERVER Management Studio では、なんと同じことをすると、切断したはずの接続に対してSQLを発行してしまう。 この仕様のせいで、本番環境に繋いで、データ確認後、テスト環境に繋ぎ変えてDMLを実行するとアラ不思議、本番環境のデータが更新されてしまったではないか。

これは、オペミスの温床だからマジでやめてほしい。

ここでも読み取り一貫性が

Oracleだと、DML流してデータを更新してから、コミットするまでの間に、同一セッションでSELECT文を流すと変更の結果を確認することができる。 なので、コミット前にDML文が正しい結果かを確認し、それでOKならコミットして、変更を確定させることができる。

SQL SERVER で同じことをすると、DML実行後のSELECT文はロック待ちになるので、延々待たされる。ので、nolockをつけて確認することになる。

読み取り一貫性考えた人ほんと天才。

文法

CREATE OR REPLACEがない

よく言われるやつ。

http://itmcreate.com/wp/archives/1373

SQL ServerでVIEWや、ストアドを作る場合は、Dropして、Createしなければならない。 で、DROPの文も、IF文で存在するかチェックして、DROPするような慣例句が使われる。 が、IF文でチェックするオブジェクト名のみ変換して、DROP文のオブジェクト名の変換を忘れて関係ないオブジェクトをDROPしてしまうという不具合を発生させてしまうことに。

列名を括弧で囲む、Nで囲む

Oracleでも、日本語列名とかアンチパターンをやっちゃう場合があるが、SQL SERVERでも同様。 ただ、SQL Serverの場合は、バージョンの下位互換のために、列名に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のマニュアルって見づらいし、グーグラビティが低いよね。