PostgresQL Автоматизация VACUUM FULL для раздутых таблиц

У нас есть продукт, использующий сервер баз данных PostgreSQL, который развернут на паре сотен клиентов. Некоторые из них собрали десятки гигабайт данных за эти годы. Поэтому в следующей версии мы представим автоматизированные процедуры очистки, которые будут постепенно архивировать и УДАЛЯТЬ старые записи во время ночных пакетных заданий.

Если я правильно понимаю, автовакуум включится, проанализирует и реорганизует кортежи, поэтому производительность будет такой же, какой была при меньшем количестве записей.

Фактическое дисковое пространство не будет освобождено, если я правильно понимаю, так как это происходит только с VACUUM FULL, и это не вызвано autovacuum.

Так что я думал об автоматизированном процессе, который бы сделал это.

Я нашел представление с раздутием, которое используется nagios check_postgres на http://wiki.postgresql.org/wiki/Show_database_bloat.

Это мнение хорошо? Правильно ли я понимаю, что если tbloat > 2, он может использовать VACUUM FULL? И если ibloat слишком высокий, он может использовать REINDEX?

Есть ли какие-либо комментарии к следующей работе, выполняемой в качестве ежедневного пакетного задания?

  • vacuumdb -Z mydatabase # вакуум с анализом только
  • select tablename from bloatview order by tbloat desc limit 1
  • vacuumdb -f -t tablename mydatabase
  • select tablename, iname from bloatview order by ibloat desc limit 1
  • reindexdb -t tablename -i iname mydatabase

Конечно, мне все еще нужно обернуть его в хороший perl-скрипт в crontab (мы используем ubuntu 12), или у postgresql есть какой-то планировщик, с которым я мог бы сделать это?

Или это полное излишество и есть ли намного более простая процедура?

2 ответа

Решение

Вам, вероятно, это не нужно. Это хорошо сделать один раз - после первого задания архивирования, чтобы вы вернули себе место на диске, но после этого ежедневное задание архивирования и автоочистка предотвратят переполнение мертвых кортежей.

Также вместо vacuum full часто лучше бежать cluster table_name using index_name; analyze table_name, Это позволит изменить порядок строк в соответствии с индексом. Таким образом, связанные строки таблицы могут быть сохранены физически близко на диске, что может ограничить поиск диска (важно для классических дисков, в основном не имеет отношения к SSD) и количество операций чтения для ваших типичных запросов.

И помните, что оба vacuum full а также cluster сделает ваши таблицы непригодными, пока они работают.

Хорошо, я прошел через это.

Я упростил / переработал представление, разделив его на следующие два:

CREATE OR REPLACE VIEW
    bloat_datawidth AS
SELECT
    ns.nspname AS schemaname,
    tbl.oid   AS relid,
    tbl.relname,
    CASE
        WHEN every(avg_width IS NOT NULL)
        THEN SUM((1-null_frac)*avg_width) + MAX(null_frac) * 24
        ELSE NULL
    END AS datawidth
FROM
    pg_attribute att
JOIN
    pg_class tbl
ON
    att.attrelid = tbl.oid
JOIN
    pg_namespace ns
ON
    ns.oid = tbl.relnamespace
LEFT JOIN
    pg_stats s
ON
    s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname
WHERE
    att.attnum > 0
AND tbl.relkind='r'
GROUP BY
    1,2,3;

А также

CREATE OR REPLACE VIEW
    bloat_tables AS
SELECT
    bdw.schemaname,
    bdw.relname,
    bdw.datawidth,
    cc.reltuples::bigint,
    cc.relpages::bigint,
    ceil(cc.reltuples*bdw.datawidth/current_setting('block_size')::NUMERIC)::bigint AS expectedpages,
    100 - (cc.reltuples*100*bdw.datawidth)/(current_setting('block_size')::NUMERIC*cc.relpages) AS bloatpct
FROM
    bloat_datawidth bdw
JOIN
    pg_class cc
ON
    cc.oid = bdw.relid
AND cc.relpages > 1
AND bdw.datawidth IS NOT NULL;

И работа cron:

#!/bin/bash

MIN_BLOAT=65
MIN_WASTED_PAGES=100
LOG_FILE=/var/log/postgresql/bloat.log
DATABASE=unity-stationmaster
SCHEMA=public

if [[ "$(id -un)" != "postgres" ]]
then
echo "You need to be user postgres to run this script."
exit 1
fi

TABLENAME=`psql $DATABASE -t -A -c "select relname from bloat_tables where bloatpct > $MIN_BLOAT and relpages-expectedpages > $MIN_WASTED_PAGES and schemaname ='$SCHEMA' order by wastedpages desc limit 1"`

if [[ -z "$TABLENAME" ]]
then
echo "No bloated tables." >> $LOG_FILE
exit 0
fi

vacuumdb -v -f -t $TABLENAME $DATABASE >> $LOG_FILE
Другие вопросы по тегам