データベースサーバのディスク枯渇への対処

2018年3月にデータセンターからさくらインターネットへ移行して、まもなく２年が経ちます。 移行に際しての計画から実施は下記に書かれています。

eatsmart.hatenablog.com

クラウド・専用サーバを中心にVPSも併用していますが、データベースサーバに利用している専用サーバのディスク残量が時間とサービス成長に伴い減少してきました。 そして今週に入り、とうとうZabbixからアラートが飛んでくるようになりました。

Zabbixのアラートのしきい値は20%に指定しています。 専用サーバのディスク容量は400GBなので20%を切ってすぐに影響が出るわけではありませんが、以前から行いたかった利用していないインデックスの削除を行いたいと思います。

利用していないインデックスを探す

インデックスはテーブル設計時や機能追加時に作成すると思います。 作成時は必要だったものが後の改修等で利用しなくなることもあります。 PostgreSQLで利用しないインデックスを、稼働統計情報から以下のクエリで抽出しました。

SELECT 
    'DROP INDEX IF EXISTS ' || t1.relname || ';' as drop_index_query,
    'VACUUM ANALYZE ' || t2.relname || ';' as vacuum_query,
    t1.relname,
    t1.relfilenode,
    to_char((t1.relpages::int8 * 8192), 'fm999,999,999,999') as display_bytes,
    (t1.relpages::int8 * 8192) as bytes,
    t2.idx_scan,
    t2.idx_tup_read,
    t2.idx_tup_fetch
FROM pg_class t1, pg_stat_user_indexes t2
WHERE t1.relname = t2.indexrelname
and t1.relname like 'idx_%'
AND t2.idx_scan < 10
ORDER BY BYTES DESC

イートスマートでは、インデックス名を"idx_"で始めることにしているので、名前で絞り込みました。 idx_scanがインデックススキャンの実施回数となります。この数値が0のものは未使用となるため、削除する対象となります。

今回問題となったデータベースからは、合計約16GBのインデックスを削除することが出来ました。 ディスク容量の削減だけではなく、無駄なインデックスを作成する処理も無くすことが出来ました。