この記事はSilbird Advent Calendar 2017 8日目の記事となります。
弊社では、稼働中のサービスの永続化データ格納先としてAmazon RDS for SQL Serverを利用しています。
その中で経験したDB領域の拡張と縮小について、大きな2つのトラブル事例とその対応内容をご紹介しようと思います。
DB領域の拡張、縮小はサービスを一時的止める（メンテナンスに入れる）た状態でないとできないと思われがちですが、サービス稼働中のまま実行することができます。
[事例1] DB自動拡張中に応答停止
1つめはDBの自動拡張についてです。
DBの初期容量は、想定ユーザー数やアクセス数をもとにある程度余裕を持って見積もっていると思います。しかし、Webサービスの世界ではその見積もりどおりにユーザーが増えていくとは限りません。サービス運営者としては嬉しい悲鳴ですが、ユーザー数・滞在時間の増加によりデータが見積もり以上に容量が増加してしまうケースがあります。
SQL Serverでは初期割り当て時の容量を超えてしまった場合に、領域を自動拡張する機能がデフォルトで有効になっています。しかし、この自動拡張が動いたときに、サービスが停止するトラブルが発生してしまいました。
DB自動拡張のデフォルト設定
下図は、SQL ServerでDBを新規作成しようとしたケースで、初期サイズとして100GBを割り当てています。そして、「自動拡張/最大サイズ」が**「10%単位で無制限」**となっているのがわかります。
なので、データが初期サイズの100GBを超えた場合に、自動で10GBの領域が自動で作成され、DBの領域は110GBとなります。
この**「10%単位で無制限」**というデフォルト設定が曲者で、自動拡張中にサービスからのクエリ要求が応答しない状態になってしまいました。
MSのサポートサイトにも記載がありますが、自動拡張中はトランザクションが停止するようです。実際、SQL Serverが10GBの領域を拡張している間クエリがタイムアウトしていました。
[INF] SQL Server における自動拡張および自動圧縮の構成に関する注意事項
DB自動拡張設定の変更
データは日々拡張していき初期サイズに収まらなくなると、SQL Serverの自動拡張に頼らざるを得ません。サービスをメンテナンスに入れて一気に拡張する方法もありますが、メンテナンスに入れることなく自動拡張の設定を変更することで対応しました。
自動拡張の設定で「自動拡張/最大サイズ」を**「100MBで無制限」** とすることで、自動拡張にかかる時間を1秒未満に抑え、サービスへの影響を最小限とすることができました。
自動拡張の発生履歴
SQL Server Management Studioから直近のDB自動拡張履歴は確認することができます。
DB名を右クリック - レポート - 標準レポート - ディスク使用量 から、現在のディスクの利用状況とともに自動拡張イベント（開始時刻、実行時間、変更後のフィアルサイズ）を確認することができます。
自動拡張についてまとめ
- SQL Serverのデフォルトの自動拡張は**「10%単位で無制限」**、自動拡張中はトランザクションが停止する
- 初期サイズが大きいと自動拡張サイズが大きくなり、サービスのダウンタイムが発生する可能性が高くなる
- 自動拡張の設定を割合(%)から絶対値(MB)とすることで、拡張にかかる時間・サービスへの影響を最小限とすることができる
[事例2] 自動拡張でディスクを圧迫し、拡張不可に
サービスの展開、自動拡張の結果
サービスを複数のプラットフォームに展開し、それに伴ってDBの数を増やし自動拡張を続けた結果、RDSインスタンス作成時に確保したディスク容量を全て使い果たしてしまいました。その時のAWSコンソールから見たときのRDSインスタンスの状況です。（Storage 1MB...）
データ・トランザクションログ領域ともにこれ以上自動拡張ができない状態になってしまい、特定のDBのデータ更新クエリが全てエラーになる状態になってしまいました。
Amazon RDS for SQL Server の制約
AWS上のクラウドサービスのため、ディスク容量を拡張することで対応できると思われるかもしれませんが、2017年12月時点 RDS for SQL Serverではインスタンス作成時に割り当てたディスク容量を拡張することができません。
そのため、別のインスタンスを作成して移行するか、削除可能なデータがあれば削除して対応する必要があります。
delete文、truncate文がエラーでデータを削除できず
対応にあたってまずやろうとしたことは、いつか使うだろうと思ってため続けていた履歴データの削除です。不要な（サービス稼働に必須ではない）履歴データを削除しようとクエリを実行しましたが、データの削除にもトランザクションログを作成する必要があり、delete, truncate文ともに実行エラーとなってしまいました。
不要なデータの削除はいったん諦めることにしました。
DB領域の縮小
自動拡張を続けてしまったDBがある一方、初期に割り当てた容量を使い果たしていないDBもありました。そのため、空き領域のあるDBを縮小することで拡張用の領域の確保を試みました。
DBを縮小するコマンドは以下の通りです。
USE [db_name]
DBCC SHRINKFILE (N'db_file_name' , 99000)
コマンドは、DBを右クリック - タスク - 圧縮 - ファイル より
「未使用領域の解放前にページを再構成する」にチェックを入れ、圧縮後のファイルサイズを指定して
「スクリプト」から出力することもできます
このときの注意点としては
ファイルの圧縮は小さい単位(例: 1GB)で複数回実行することです。
一度に大量の圧縮を行うとDBが応答しなくなり、サービスが停止します。例えば150GBのDBを120GBへ圧縮したい場合は 149→148→147・・・ と細かく実行する必要があります。
自動拡張時の苦い教訓があることと、圧縮するDBのサービスは正常に稼働中であったため、細かく圧縮を行いました。圧縮を行うことで、ディスクに空きが生まれ自動拡張が実行されました。
DBの縮小中はトランザクションは停止しないようです。
DB領域の拡張
同時にDB領域の拡張も行いました。自動拡張を続けているDBはこれを機に割り当て領域を拡張して、そもそも自動拡張が発生しないように対応を行いました。
DBを拡張するコマンドは以下の通りです
USE [db_name]
ALTER DATABASE [db_name] MODIFY FILE ( NAME = N'db_file_name', SIZE = 100000000KB )
コマンドは、DBを右クリック - プロパティ - ファイル より、データファイルの初期サイズを変更して、「スクリプト」から出力することもできます。
サービス稼働中に実行するときの注意点としては、圧縮は小さい単位(例: 1GB)で複数回実行することです、もっと大きい単位でも問題なく拡張できるかもしれませんが、1GB単位で実行しました。
不要なデータの削除
一時的にトラブルは解消しましたが、自動拡張を続ける限り今後もディスクの空き容量枯渇のリスクを抱えています。そもそもDBに保持するデータ量を減らすため、一定期間を経過した履歴データは削除を行うようにしました。これによって約30%データ量を削減し、定期的に削除を行うことで今後1年は自動拡張が発生しないような状態となりました。
容量監視の重要さ
この2つの事例はいずれも DBの容量監視をしていれば事前に気づけていました。
恥ずかしながら、このトラブルが発生するまでRDSの空き容量の監視を行っていませんでした。CloudWatch上で簡単に監視できるため、RDSのディスクが一定容量を下回るとSlackへ通知するよう設定を行いました。
まとめ
- DB自動拡張中はトランザクションが停止する、そのため自動拡張は割合(%)ではなく絶対値(MB)かつ、一度に拡張されるサイズを小さくしたほうがいい
- DBの手動縮小・拡張は1GB単位など小刻みに行えば、サービス稼働中でも可能
- 容量監視は大事