AnalyticDB:通常の真空タスクの設定

背景情報

データを追加、削除、変更、または照会すると、一時ファイルとログファイルが生成され、ディスクストレージが占有されます。 できるだけ早くファイルをクリーンアップしないと、ディスクストレージが不十分なためにシステムが期待どおりに実行されないことがあります。 したがって、システムの安定性を確保するには、通常の真空タスクを設定する必要があります。

真空タスクの設定

真空タスクには、次の側面が含まれます。

1. 一時ファイルの削除: データベース操作中に、トランザクションログやバックアップファイルなどの特定の一時ファイルが生成される場合があります。 ファイルが使用されなくなった場合は、できるだけ早く削除する必要があります。

2. 削除対象とマークされたデータの削除: データベースからデータを削除すると、データは削除対象とマークされますが、ディスクからは削除されません。 定期的にディスクからデータを削除する必要があります。

3. テーブルスキーマの最適化: 大きなテーブルのスキーマを最適化して、ディスクストレージを節約できます。 たとえば、パーティションテーブルとインデックスを使用して、データアクセス効率を向上させ、ディスク使用量を削減します。

4. 定期的にデータをバックアップ: 定期的にデータをバックアップして、データの損失を防ぎ、以前のバックアップファイルを削除してディスクストレージを解放できます。

次のセクションでは、真空タスクを設定できる2つのシナリオについて説明します。

ロックテーブルなしで汚れたデータをクリーンアップ

テーブルに対して更新または削除操作を実行する場合、テーブル全体ではなく、変更するテーブルの一部のみをロックできます。 この方法では、ダーティデータが生成されますが、システムの同時実行パフォーマンスが向上し、応答時間が短縮されます。

• SQL文: データベース所有者として各データベースに接続し、VACUUM文を実行します。

• 周波数:

  • INSERT VALUES、UPDATE、DELETEなどの多数のステートメントを実行してリアルタイム更新を実行する場合は、VACUUMステートメントを1日1回または少なくとも週に1回実行することをお勧めします。

  • バッチ更新操作を1日1回実行する場合は、VACUUMステートメントを1週間に1回または少なくとも1か月に1回実行することを推奨します。

• システムへの影響: テーブルはロックされず、期待どおりに読み書きできます。 これにより、CPU使用率とI/O使用率が増加し、クエリのパフォーマンスに影響を与える可能性があります。

• 実行メソッド:

  • pg_cron拡張機能を使用して、VACUUMステートメントを実行するスケジュールタスクを作成できます。 詳細については、「pg_cron」をご参照ください。

  • スケジュールされたcrontabタスクとして次のLinuxシェルスクリプトを実行できます。

  #!/bin/bash
  export PGHOST=myinst.gpdb.rds.tbsite.net
  export PGPORT=3432
  export PGUSER=myuser
  export PGPASSWORD=mypass
  #do not echo command, just get a list of db
  dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
  for db in $dblist ; do
      #skip the system databases
      if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
          continue
      fi
      echo processing $db
      #vacuum all tables (catalog tables/user tables)
      psql -d $db -e -a -c "VACUUM;"
  done

メンテナンス期間内のダーティデータのクリーンアップ

メンテナンス期間内にダーティデータをクリーンアップし、ディスクストレージを解放できます。

• SQL文: データベース所有者として各データベースに接続します。 データベース所有者は、すべてのオブジェクトに対する完全な権限を持つ必要があります。

  1. REINDEX SYSTEM <database name> ステートメントを実行します。

  2. 各データテーブルに対してVACUUM FULL <table name> ステートメントを実行します。 さらに、列指向テーブルでREINDEX TABLE <table name> ステートメントを実行します。

  3. システムテーブルとインデックスの作成と削除の頻度が高い場合は、VACUUM FULL <table name> 文を実行して、テーブルを定期的に管理することをお勧めします。 システムテーブルには、pg_class、pg_attribute、pg_indexが含まれます。 注: VACUUM FULL <table name> ステートメントを実行するときは、データベースにアクセスしないことをお勧めします。

• 頻度: 上記のステートメントを少なくとも週に1回実行します。 ほとんどのデータが毎日更新される場合は、上記のステートメントを1日1回実行する必要があります。

• システムへの影響: システムは、VACUUM FULLまたはREINDEXステートメントが実行されているテーブルをロックします。 ステートメントの実行中にテーブルを読み書きすることはできません。 これにより、CPU使用率とI/O使用率が増加します。

• 実行メソッド:

  • pg_cron拡張機能を使用して、VACUUM FULLおよびREINDEXステートメントを実行するスケジュールタスクを作成できます。 詳細については、「pg_cron」をご参照ください。

  • スケジュールされたcrontabタスクとして次のLinuxシェルスクリプトを実行できます。

#!/bin/bash
export PGHOST=myinst.gpdb.rds.tbsite.net
export PGPORT=3432
export PGUSER=myuser
export PGPASSWORD=mypass
#do not echo command, just get a list of db
dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
for db in $dblist ; do
    #skip system databases
    if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
        continue
    fi
    echo processing db "$db"
    #do a normal vacuum
    psql -d $db -e -a -c "VACUUM;"
    #reindex system tables firstly
    psql -d $db -e -a -c "REINDEX SYSTEM $db;"
    #use a temp file to store the table list, which could be vary large
    cp /dev/null tables.txt
    #query out only the normal user tables, excluding partitions of parent tables
    psql -d $db -c "copy (select '\"'||tables.schemaname||'\".' || '\"'||tables.tablename||'\"' from (select nspname as schemaname, relname as tablename from pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_roles where pg_class.relnamespace = pg_namespace.oid and pg_namespace.nspowner = pg_roles.oid and pg_class.relkind='r' and (pg_namespace.nspname = 'public' or pg_roles.rolsuper = 'false' ) ) as tables(schemaname, tablename) left join pg_catalog.pg_partitions on pg_partitions.partitionschemaname=tables.schemaname and pg_partitions.partitiontablename=tables.tablename where pg_partitions.partitiontablename is null) to stdout;" > tables.txt
    while read line; do
        #some table name may contain the $ sign, so escape it
        line=`echo $line |sed 's/\\\$/\\\\\\\$/g'`
        echo processing table "$line"
        #vacuum full this table, which will lock the table
        psql -d $db -e -a -c "VACUUM FULL $line;"
        #reindex the table to reclaim index space
        psql -d $db -e -a -c "REINDEX TABLE $line;"
    done <tables.txt
done