« インデックスの再構築タイミング(Informix) | トップページ | 定期的なインデックス再作成を自動化(解題) »

2014.04.05

定期的なインデックス再作成を自動化

PostgreSQLの特徴であったMVCCであることによるVacuumについては、8.1より自動化がなされて、テーブル本体については領域膨張などのリスクはかなり軽減されている。
通常の利用であればパラメータを少し調整しておけば、vacuumについては意識をしなくてもいいだろう。

ただし、VACUUM FULLあるいはREINDEXをある頻度で実施をしないといけないケースはどうしても残ってしまう。
しかしREINDEXはロックを獲得してしまうため、本番共用が始まってしまうとこのコマンドを実行するタイミングはいろいろと難しくなってくる。
そこでロックせずにインデックスを作成する方法を利用して、オンライン中でもインデックスの張替えを行なうシェルを組んでみた。

この処理はVer9.1以降で有効なALTER TABLE構文も用いるのでVer9.0以前では利用できないのでご注意

この処理の肝は
1.CONCURRENTLY オプションによるオンライン稼働中でのREINDEX
2.Primary Keyとそうでない場合に生成されるSQLが異なる
ところにある。
最初のSELECT文内で、information_schemaから当該インデックスがPrimary Keyかどうかを判定し、それによって後で生成されるSQLが分岐している。

#!/bin/bash

USRID=[userid]
USROID=[useridのoid]
DBNAME=[接続先DB名称]
DIR=[このシェルの配置ディレクトリ]

SQL_FILE=${DIR}/replace_index.sql
SQL_TMP_HEADER=${DIR}/index_head.txt
SQL_TMP_DETAIL=${DIR}/index_detail.txt
SQL_TMP_DETAIL_OUTPUT=${DIR}/index_detail_output.txt

TARGET_INDEX_FILE=${DIR}/idx_list.txt
TARGET_INDEX_LIST=${DIR}/idx_list.sql

echo "\COPY (SELECT CASE WHEN EXISTS \
                    (SELECT * FROM information_schema.table_constraints i\
                      WHERE pg_indexes.indexname = i.constraint_name \
                                        AND i.constraint_type='PRIMARY KEY') THEN 'PK' \
                    ELSE 'INDEX' \
                    END \
             , indexdef \
             FROM pg_class INNER JOIN pg_indexes ON pg_class.relname = pg_indexes.indexname \
             WHERE relkind = 'i' \
               AND relnamespace = ${USROID} \
               ) \
            TO '${TARGET_INDEX_FILE}' DELIMITER ' ';" > ${TARGET_INDEX_LIST}

psql -U ${USRID} ${DBNAME} -f ${TARGET_INDEX_LIST} 

rm -f ${TMP_DIR}/*

while read LINE
do
    rm -f ${SQL_FILE}
    rm -f ${SQL_TMP_HEADER}
    rm -f ${SQL_TMP_DETAIL}
 
    IFS=" "
    set - $LINE
    args=("$@")
    ELEMENTS=${#}

    if test ${args[2]} != "INDEX"
    then
        PRESTATEMENT=${args[2]}
        IDX_NAME=${args[4]}
        TBL_NAME=${args[6]}
        START_ELEM=7
    else
        PRESTATEMENT=""
        IDX_NAME=${args[3]}
        TBL_NAME=${args[5]}
        START_ELEM=6
    fi

    for ((i=$START_ELEM; i<$ELEMENTS; i++))
    do
        echo "${args[i]} " >> ${SQL_TMP_DETAIL}
    done
    
    echo "CREATE ${PRESTATEMENT} INDEX CONCURRENTLY ${IDX_NAME}_new ON ${TBL_NAME}" > ${SQL_TMP_HEADER}

    tr -d '\12' < ${SQL_TMP_DETAIL} > ${SQL_TMP_DETAIL_OUTPUT}
    
    cat ${SQL_TMP_HEADER} ${SQL_TMP_DETAIL_OUTPUT} > ${SQL_FILE}
    echo ";" >> ${SQL_FILE}
    
    if test ${args[0]} != "INDEX"
    then
        echo "ALTER TABLE ${TBL_NAME} DROP CONSTRAINT ${IDX_NAME};" >> ${SQL_FILE}
        echo "ALTER INDEX ${IDX_NAME}_new RENAME TO ${IDX_NAME};" >> ${SQL_FILE}
        echo "ALTER TABLE ${TBL_NAME} " >> ${SQL_FILE}
        echo " ADD PRIMARY KEY USING INDEX ${IDX_NAME};" >> ${SQL_FILE}
    else
        echo "DROP INDEX ${IDX_NAME};" >> ${SQL_FILE}
        echo "ALTER INDEX ${IDX_NAME}_new RENAME TO ${IDX_NAME};" >> ${SQL_FILE}
    fi

    psql -U ${USRID} ${DBNAME} -f ${SQL_FILE}

done < ${TARGET_INDEX_FILE}

rm -f ${TARGET_INDEX_FILE}
rm -f ${SQL_TMP_HEADER}
rm -f ${SQL_TMP_DETAIL}
rm -f ${SQL_TMP_DETAIL_OUTPUT}

|

« インデックスの再構築タイミング(Informix) | トップページ | 定期的なインデックス再作成を自動化(解題) »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/59404097

この記事へのトラックバック一覧です: 定期的なインデックス再作成を自動化:

« インデックスの再構築タイミング(Informix) | トップページ | 定期的なインデックス再作成を自動化(解題) »