postgreSQLで、結果が返ってくるまでにそんなに時間がかかるはずのない単純なSELECT文を発行した後、実行終了までに異常に時間がかかることがある。
一度そういう状態になると、同じSQLを何回実行しても、終了に時間がかかるようになる。他のテーブルに対してSELECTをおこなったり、WHERE句で条件を追加したりすると正常なスピードで実行されたりするので、原因がよくわからない。
どうやら、多くの件数のデータの更新や削除を繰り返していると、フラグメンテーションが起こるためか、そういうことが起こるらしい。
vacuumやvacuum fullでデータベースの最適化を実行しても状況は改善されないので、試しにデータベースを新たに再作成して、そこにデータを復元する、という操作を試したところ、正常なパフォーマンスに戻った。
【データベースの再作成の手順】
1)su – postgres
で、postgreSQLユーザーに切り替える
2)バックアップファイルを作成するディレクトリに移動して、
/usr/local/bin/pg_dump -d [データベース名] > ./sqlbu
で、sqlbuファイルにデータをエクスポートする。
3)createdb -E EUC_JP [新しいデータベース名]
で新しいデータベースを作成する。
4)psql -d [新しいデータベース名] -f sqlbu
で、sqlbuファイルから新しいデータベースにデータをリストアする。
この手順では、安全のため、現行のデータベースと別に、新たにデータベースを作成して、そこにデータベースを再作成しているが、同じデータベース名で再作成をしたい場合は、3)の前に
dropdb [データベース名]
で、一度データベースを削除した後に、同じデータベース名でデータベースを作成する。
