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