風邪の原因は夜遊びじゃなく-DB最適化地獄

Pocket

確かに疲れが溜まっていたのかも知れないが、築地では普通に食べて、その後もワイン空けたりしてたので、遅くまで遊んでたから風邪引いたってわけじゃないと思うんだが、遠因ではあるかも。

なんだか、最近眠れなくて、大体4時間くらい寝ると目が覚めてしまう。花粉症だからなのかな。あんまり自覚症状は無いんだが。

 

で、築地に行った次の日。

ちょいと仕事でもと思って、ひさしぶりに自転車で事務所まで行ったんだが、「今日は何も無いだろう。早く帰ろう。」と思っていたら、サーバからアラートが。

「HDD容量がそろそろ危険だよー。」との事で、見てみるとDBサーバがwarning発してます。

まぁ、DBサーバは担当じゃないし、ログインも出来ないしーと思ってたら、対応を依頼され。

Webサーバは、まぁログ・ファイル消すとか、その程度で解消してたんだが、DBサーバとなると、どうして良いものやら…。

という事で、恒例の備忘録。

 

まずは、ファイルの調査。

サーバ上のどのファイルが巨大に膨れ上がっているのか?を、とりあえず調べます。

とは言え、大体どの辺のファイルが圧迫しているかというのは、何となく把握は出来ているので、「見つける」というより「確認する」という感じ。

du -h --max-depth=1 /

これで、辿って行けば、「あー、こいつだな。」となるでしょう。

参考:Linuxサーバがディスク容量不足になった!何か消さねば!ってなった時にどう対処するか

上記サイトには、もっと便利な方法も記載されてます、ありがとうございます。

 

でもって、webサーバなら大抵はapacheが残すログファイルとか、アプリケーションが残しているデバッグ用・各種ログ・キャッシュなどのファイルが原因なので、不要なものをバシバシ削除もしくはバックアップしていきます。

ただ、DBサーバなので、そうもいかない。ただ今回はMySQLなので、何か良い方法は…と調べていたら、MySQLもログを吐くらしいとの事。しかもローテートの設定をしていないとGBクラスの巨大なファイルに成長するとの事で、それを確認します。

で、そのファイルは何かと言うと、「slowログ」というファイルらしい。

処理に時間がかかったSQLをログとして残して、後から調べられるようにするという事らしい。

しかしですね、今まで見たことも無く、そもそもサーバにログイン出来なかったので、これは必要無さそうですよ、実際。

本当は、マメにチェックしてチューニングやら行うべきなんでしょうが…。

という訳で、まずはその「slowログ」の場所を確認。

my.cnfへの確認と、DBへの確認の2つの方法があるようです。

$ cat /etc/my.conf | grep slow
slow_query_log=ON
slow_query_log_file=/var/log/mysql-slow.log
long_query_time=0.1

 

mysql> show variables like 'slow%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_launch_time    | 2                                    |
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/log/mysql-slow.log |
+---------------------+--------------------------------------+

という訳で、「/var/log/mysql-slow.log」を確認します。

なーんと、DB起動時から放置されていたのか、20GBに成長しておりました。つか、こんだけ成長するという事は、そもそも…という気もするのですが、今はそれは置いておきます。

で、とりあえず、こいつを削除…、と思ったんですが、先にローテートを調べます。

参考:MySQL の slowlog を logrotate する方法 

これで当面は大丈夫でしょう。ありがとうございます。

で、slowファイルの削除ですが、rmとかじゃなくmysqlのコマンドでflushするそうです。

mysqladmin flush-logs

こんな感じです。

すると、ファイル容量が激減し、HDD容量が増加。一気にwarningが消えました。

ただ、ですね。今回はこれで解決とはならず、レプリケーションしているので、マスタだけではなくスレーブ側も同様の操作を行う必要があるみたいなんですが、残念ながらログインできるのはマスタ側のみ。

という事で、スレーブ側のslowログは操作できないので、スレーブ側のwarningは出たままとなっています。

仕方ないので、次にテーブルの容量を調べます。

これはphpmyadminでも見ることが出来ますが、SQLを打って確認する事も可能。

select table_name, round(data_length/1024/1024, 2) as 'data_size(MB)',
round(index_length/1024/1024, 2) as 'index_size(MB)' from information_schema.tables
where table_schema='[DB名]';

ザーッと出てきますが、まぁ大体はどのテーブルが怪しいか分かってます。

で、そのテーブルを見てみると…まさかの130GBオーバーw

どんだけ巨大なんだw

という訳で、このテーブルをなんとかすれば、大幅にHDDの容量を稼ぐことができそうです。

しかも、このテーブルのデータって、残しているだけで、ほとんど使われてないログデータという…。

ただ、130GBというビッグサイズなので、ここから泥沼にハマることになります…。

 

まず、単純ですがダンプして外に出してしまおうと考えました。

mysqldump -u root -p -t [DB名] [テーブル名] | gzip > hogehoge.gz

こんな感じです。ただ、これは通常の数GBとかのテーブルなら有効かも知れませんが、130GBだと無謀の一言に尽きます。

実際に、それー!って感じで実行すると、生成されるダンプファイルがHDDを圧迫し始めます。

更に、ダンプの処理が内部的にどうなっているのか分かりませんが、DBの機能がほぼロックされた状態で、SELECTから何からの操作が事実上ストップします。

当然、load averageの値もうなぎ登りで、稼働中のDBに対して行うのは無理と判断しました。

ちなみに、where条件を付けて実行する事も可能です。

mysqldump -u root -p -t [DB名] [テーブル名] –where ‘status = 1’ | gzip > hogehoge.gz

ただ、これでも130GBの巨大なテーブルに対しては無力。というか、ほとんどどんな操作をするにしても極端にサーバに負荷がかかる状態です。

気軽な気持ちで最適化など行おうものなら…。

 

で、ここまで試行錯誤しながら既に深夜です。

私思うに、この作業で無理したのと、各種数値の増減やアラートの頻出によるストレスが風邪の原因だと思っておりますw

 

で、最終的には「もう無理!一気に解決は無理!」という事で、担当の方に泣きを入れ、おまけにこれは「ちょっとやっといて。」程度の作業では無いという事を申し伝えさせて頂きました。

いや、DBサーバを舐めてました。

とりあえず、GW明けから再度突撃する予定ですが、小出しにダンプしつつ、小出しにDELETEして、ちょっとずつテーブルのデータを少なくして…という感じですかね。

ただ、MySQLの場合はDELETEしたとしても、削除した分だけのGarbageが残ってしまって、実質的な容量は変わらないという罠があるため(しかも迂闊に最適化できない)、数GBくらいのサイズになってから、最適化を行って、そこで初めて解決…となりそうです。

もうなんだか、ここまで苦闘しておいてなんだけど

 

一括DELETEしたい。

 

このデータいらないんじゃね?

という気分に…。

 

時間かかりそうだ…。

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です