Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Как узнать ко-во живых и мертвых строк из служебных таблиц?  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Добрый день.
Есть вопрос по статистике.


Имеется таблица. Если дать команду ANALYZE VERBOSE имя-таблицы
и делать это до VACUUM FULL (до очистки БД и таблиц)
то будет выведено кол-во "ЖИВЫХ" строк
и количество "МЕРТВЫХ" строк.

Как это сделать через запрос или где находится эта статистика.
В таблице pg_statistic этой информации не нашел, да и в других так же не нашел.
***********************
Нужно просто анализ на предмет распухших таблиц, которые реально имеют 1000 строк со средним значением всех столбцах (построчно) = 100 байт. В теории должны дать (ну грубо) 100 байт * 1000 строк = 100 кб.
.
Понятно, что там есть еще страницы, но они так же не показатель. Там может быть 20 страниц по 8 кб, а по факту в таблице осталось всего 50 строк, просто таблица была очень распухшей и потом все удалили.

В общем где копать, хоть какие таблицы посмотреть, дальше уже сам по справочникам.

.
.
22 окт 21, 18:07    [22386963]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать ко-во живых и мертвых строк из служебных таблиц?  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
О-О-О,

В pg_stat_user_tables посмотрите.
Но данные там обновляются (auto)analyze.

Ну и dead_tuples != свободное место
может быть таблица с 1 строкой, размером в 100GB и 0 dead tuples (и это вполне валидная ситуация)
поэтому оценить насколько таблица распухла по данным статистике не возможно
для это есть расширение pgstattuple (но оно будет как минимум частично таблицу читать для этой цели).

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
22 окт 21, 18:13    [22386967]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать ко-во живых и мертвых строк из служебных таблиц?  [new]
big-trot
Member

Откуда: Тверь
Сообщений: 309
Maxim Boguk,

А есть ли статистика по заполнению таблицы, т.е. вы сказали
Maxim Boguk
может быть таблица с 1 строкой, размером в 100GB и 0 dead tuples (и это вполне валидная ситуация)
, тогда степень заполнения будет примерно (допустим одна строка занимает 20 байт) 20/100GB.
22 окт 21, 19:10    [22386988]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать ко-во живых и мертвых строк из служебных таблиц?  [new]
Guzya
Member

Откуда:
Сообщений: 818
Загуглите postgres bloat. Есть скрипт, который считает распухание приблизительно, но быстро.
22 окт 21, 22:29    [22387048]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать ко-во живых и мертвых строк из служебных таблиц?  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Maxim Boguk
О-О-О,

В pg_stat_user_tables посмотрите.
Но данные там обновляются (auto)analyze.


Максим, Спасибо. Помогло.
Всегда думал, что Vacuum Full не только чистит, но и обновляет статистику.
Ан нет.
Короче, статистика по таблицам не обновится пока не пройдет сам сборщик статистики или не задать принудительно ANALYZE:

Это в начале до очистки, 
n_tup_ins           | 35
n_tup_upd           | 0
n_tup_del           | 28
n_tup_hot_upd       | 0
n_live_tup          | 11 <<--
n_dead_tup          | 28 <<--
n_mod_since_analyze | 4
n_ins_since_vacuum  | 35
last_analyze        | 2021-10-22 17:54:54.727467+03


Это после VACUUM FULL
n_tup_ins           | 35
n_tup_upd           | 0
n_tup_del           | 28
n_tup_hot_upd       | 0
n_live_tup          | 11 <<--
n_dead_tup          | 28 <<--
n_mod_since_analyze | 4
n_ins_since_vacuum  | 35
last_analyze        | 2021-10-22 17:54:54.727467+03 <<--


А это после ANALYZE  = select * from pg_stat_all_tables where relname='ttt'; 
n_tup_ins           | 35
n_tup_upd           | 0
n_tup_del           | 28
n_tup_hot_upd       | 0
n_live_tup          | 7 <<--
n_dead_tup          | 0 <<--
n_mod_since_analyze | 0
n_ins_since_vacuum  | 35
last_analyze        | 2021-10-23 08:59:44.568247+03 <<--


.

Сообщение было отредактировано: 23 окт 21, 09:13
23 окт 21, 09:12    [22387102]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать ко-во живых и мертвых строк из служебных таблиц?  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Guzya
Загуглите postgres bloat. Есть скрипт, который считает распухание приблизительно, но быстро.


Ну тут в принципе ничего сложного.
Есть столбцы. Там есть данные по столбцам сколько каждый занимает байт.
Есть страницы.
Есть мертвые строки и живые строки. ИХ ТО МНЕ И НЕ ХВАТАЛО!

К примеру у меня 12 столбцов в сумме дают примерно 79 байта (это согласно статистике по всем 12 столбцам) .
Всего 4212 строк.
И в то же время 55 страниц.
Филфактор = 100 (по умолчанию, но я его иногда меняю на 75 или 80 если таблица постоянно перезаписывается),
Статистика , выборка делается по 10%.
Значит 79 байт*4212 = 332748 байт.
Тут есть спорный момент. В документации написано байт, хотя я подозреваю, что это символов. Так если будет "ttt текст", то это будет 9 символов, но вот байт будет (3+1)*1 + 5*2=4+10=14 (русские символы идут как за 2 байта).
По функциям хорошо видна разница размера в байтах и символах.

То есть в теории получаем 332'748 (см выше).
А при запросе показывает 458'752, что внимание!!!, соответствует ко-во блоков/страниц (=8192 байт): 55 блоков
и получаем 8192*55=450560 байт.

Там еще есть модификации.
Но здесь нужен просто параметр. Когда сама таблица весит 3 Гб, а сумма дает 21 Гб, то это уже большой повод задуматься.
Или она распухла или индексы не нужные или там много еще чего. В общем для этого все и делалось.

Когда в вашей БД всего 100-200 таблиц + 50 функций, то все норм, можно и вручную иногда глянуть.
А вот если проект новый, никаких подписей и информации о таблицах нет, а таблиц около 3000 штук
и функций столько же и никакой документации
да и функции писали разные люди и опять же практически без описаний
- то тут реально Ж./головная боль. (жуткая головная боль, ничего нецензурного)! ;-)


***************************************************
P.S.
-- pg_catalog.pg_attribute - это информация по всем слотбцам таблицы, в том числе и служебным и там же примечание/описание к столбцам
-- pg_catalog.pg_statistic - это статистика по каждому столбцу каждой таблицы (примерно)
В общем документация вам в помощь. Благо она хорошо задокументирована. Хотя и не без пробелов.
.
.

Сообщение было отредактировано: 23 окт 21, 09:31
23 окт 21, 09:26    [22387105]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать ко-во живых и мертвых строк из служебных таблиц?  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Maxim Boguk

Еще раз спасибо. Опять помог.
23 окт 21, 09:36    [22387107]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать ко-во живых и мертвых строк из служебных таблиц?  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
О-О-О,

Поэтому часто имеет смысл делать vacuum full analyze table; ну или ANALYZE TABLE; после vacuum full.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
23 окт 21, 09:36    [22387108]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить