Как проверить, работает ли TOAST на определенной таблице в postgres
У меня есть таблица, которая содержит два текстовых поля, которые содержат много текста. Почему-то наша таблица начала расти в геометрической прогрессии. Я подозреваю, что TOAST (сжатие для текстовых полей в postgres) не работает автоматически. В нашем определении таблицы мы не определили ни одного условия для принудительного сжатия этих полей. Есть ли способ проверить, работает ли сжатие на этой таблице или нет?
4 ответа
Если какой-либо из столбцов таблицы поддерживает TOAST, таблица будет иметь связанную таблицу TOAST, чей OID хранится в записи таблицы pg_class.reltoastrelid. Внеочередные значения TOASTed хранятся в таблице TOAST, как более подробно описано ниже.
Таким образом, вы можете определить, существует ли таблица TOAST, запросив системный каталог pg_class. Это должно приблизить вас к тому, что вы ищете.
select t1.oid, t1.relname, t1.relkind, t2.relkind, t2.relpages, t2.reltuples
from pg_class t1
inner join pg_class t2
on t1.reltoastrelid = t2.oid
where t1.relkind = 'r'
and t2.relkind = 't';
В PSQL вы можете использовать \d+
, Я буду использовать системный каталог pg_class в качестве примера; вы бы использовали свое собственное имя таблицы.
sandbox=# \d+ pg_class
Column | Type | Modifiers | Storage | Stats target | Description
----------------+-----------+-----------+----------+--------------+-------------
relname | name | not null | plain | |
relnamespace | oid | not null | plain | |
[snip]
relacl | aclitem[] | | extended | |
reloptions | text[] | | extended | |
Если хранилище "расширено", PostgreSQL будет пытаться уменьшить размер строки, сначала сжимая, затем сохраняя данные вне строки. Если хранилище является "основным" (не показано), PostgreSQL будет пытаться сжать его.
В вашем конкретном случае может оказаться полезным отслеживать изменения размера с течением времени. Вы можете использовать этот запрос и сохранить результаты для последующего анализа.
select table_catalog, table_schema, table_name,
pg_total_relation_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_total_relation_size,
pg_relation_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_relation_size,
pg_table_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_table_size
from information_schema.tables
Функции администратора PostgreSQL содержат подробную информацию о том, что каждая функция включает в свои вычисления.
Вы можете увидеть, какие запросы выполняет psql, запустив его с -E
параметр, затем выполняются обычные команды:
В данном случае, первым делом был поиск oid вашей таблицы:
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(YOUR_TABLE_NAME_HERE)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
затем он выполняет это, чтобы посмотреть больше статистики об этом:
SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
NULL AS indexdef,
NULL AS attfdwoptions,
a.attstorage,
CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget, pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '57692' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
x.attrstorage - это то, что вас волнует p
это PLAIN, x
РАСШИРЕНО Я держу пари.
Это старый, но у меня недавно был некоторый успех с подобной проблемой. ANALYZE VERBOSE показало, что пара наших таблиц выросла до> 1 страницы диска на кортеж, а EXPLAIN ANALYZE показала, что последовательное сканирование занимало до 30 секунд в таблице из 27K строк. Оценки количества активных рядов становились все дальше и дальше.
После долгих поисков я узнал, что очистить строки можно только в том случае, если с момента их обновления не было открыто ни одной транзакции. Эта таблица перезаписывалась каждые 3 минуты, и было установлено соединение, которое было "бездействующим в транзакции", которому было 3 дня. Ты можешь сделать математику.
В этом случае нам пришлось
- убить связь с открытой транзакцией
- переподключиться к базе данных. К сожалению, максимальный идентификатор транзакции для строк, которые могут быть очищены пылесосом, в настоящее время (по состоянию на 9.3) хранится в соединении, поэтому вакуум не будет работать.
- VACUUUM FULL вашей таблицы (это снимет блокировку ACCESS EXCLUSIVE, которая заблокирует все, включая чтение. Возможно, вы захотите сначала запустить VACUUM (без блокировки), чтобы ускорить время, которое занимает VACUUM FULL).
Возможно, это не было вашей проблемой, но если вы хотите посмотреть, влияют ли таблицы в вашей собственной базе данных, я написал запрос для упорядочения таблиц по среднему количеству кортежей, хранящихся на странице диска. Таблицы с большими строками должны быть вверху - ANALYZE VERBOSE должна дать вам представление о соотношении мертвых и живых кортежей в этих таблицах. Действительно для 9.3 - это, вероятно, потребует некоторых незначительных изменений для других версий:
SELECT rolname AS owner,
nspname AS schemaname
, relname AS tablename
, relpages, reltuples, (reltuples::FLOAT / relpages::FLOAT) AS tuples_per_page
FROM pg_class
JOIN pg_namespace ON relnamespace = pg_namespace.oid
JOIN pg_roles ON relowner = pg_roles.oid
WHERE relkind = 'r' AND relpages > 20 AND reltuples > 1000
AND nspname != 'pg_catalog'
ORDER BY tuples_per_page;
Если пылесосит таблицу, удаляя ее с 19 ГБ до 19 ГБ, вероятно, MVC работает: мертвые строки занимают место до тех пор, пока они не будут очищены или использованы повторно.