Почему может быть раздута БД postgres?

Question

[x67]

Есть база данных, с недавних пор осознал, что пора бы избавлять ее от старых данных, поставил для самых больших таблиц ежедневную очистку от старых данных, а затем полный вакуум(потом я его ежедневное выполнение отключил). БД начала расти в размерах, при том что таблицы не растут. Из мануалов понятно что VACUUM FULL избавити нас от лукавого, но он не избавляет! Для всех таблиц показывает

INFO:  vacuuming "tablename"
INFO:  "tablename": found 0  removable, X nonremovable row versions in Z pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed Y.YY sec., то есть вакуум не нужен, ибо все ненужное уже удалено

REINDEX тоже на всякий случай сделал, хотя индексов никогда не создавал. И тем не менее, таблиц сейчас на 12 Гб, а база раздута до 24. Что с этим делать?

P.s. Еще стали появляться ошибки

WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
connection to server was lost
psql: FATAL:  the database system is in recovery mode

при нескольких тяжелых запросах или при выполнении вакуума по ночам (который я отключил). Тяжелые запросы бывают редко, но ими я выгружаю по ~500 мб данных, сформированных из нескольких подзапросов, длятся они долго (десятки минут), но тут к скорости требований нет
p.p.s настройки автовакуума и постгреса по дефолту

Comments

[Anton Kuzmichev]

А из коробки вроде автовакуум включён, который избавляет от необходимости запускать его явно регулярно?

[x67]

Anton Kuzmichev, да, включен, но если я правильно понял, он работает так, что может освободить только крайние данные, а если я удаляю что-то из середины таблицы или с конца, то для получения мегабайтов обратно нужен vacuum full. Я поэксперементировал, бд раздувается даже без vacuum full. И меня беспокоит именно то, что таблиц на 12 гб, а куда делись еще 12 гигов? В БД они есть, в таблицах их нет

[Melkij]

Anton Kuzmichev, it depends. Автовакуум в принципе не может сжимать индексы, а у таблиц отстреливает только хвост. Если дырка в начале или середине датафайла - то объём базы автовакуумом уменьшаться не будет.

Answer 1

[terrier]

После перезапуска размер тот же?
Какие директории самые большие в $PGDATA? ( du -sh ( --max-depth=N ) | sort -hr если что )
Собственно диск не сыпется?

Comments

[x67]

9.5/main/base/12345 - папка бд
собственно psql на нее и показывает при выполнении \l+

Диск ssd на vps серваке, вроде не сыпется

[terrier]

9.5/main/base/12345 - папка бд

Ну, да, собственно и вопрос - на какие конкретно файлы приходятся "лишние" 12G
Гляньте что-нибудь типа
du --max-depth=1 9.5/main/* | sort-nr

[x67]

terrier,

1,1G    /var/lib/postgresql/9.5/main/base/16384/915894.2
1,1G    /var/lib/postgresql/9.5/main/base/16384/915894.1
1,1G    /var/lib/postgresql/9.5/main/base/16384/915894
1,1G    /var/lib/postgresql/9.5/main/base/16384/915541
1,1G    /var/lib/postgresql/9.5/main/base/16384/915532.2
1,1G    /var/lib/postgresql/9.5/main/base/16384/915532.1
1,1G    /var/lib/postgresql/9.5/main/base/16384/915532
1,1G    /var/lib/postgresql/9.5/main/base/16384/872720.1
1,1G    /var/lib/postgresql/9.5/main/base/16384/872720
1,1G    /var/lib/postgresql/9.5/main/base/16384/856953.1
1,1G    /var/lib/postgresql/9.5/main/base/16384/856953
1,1G    /var/lib/postgresql/9.5/main/base/16384/834520.1
1,1G    /var/lib/postgresql/9.5/main/base/16384/834520
1,1G    /var/lib/postgresql/9.5/main/base/16384/767992.1
1,1G    /var/lib/postgresql/9.5/main/base/16384/767992
1,1G    /var/lib/postgresql/9.5/main/base/16384/748701.1
1,1G    /var/lib/postgresql/9.5/main/base/16384/748701
919M    /var/lib/postgresql/9.5/main/base/16384/915541.1
778M    /var/lib/postgresql/9.5/main/base/16384/915894.3
760M    /var/lib/postgresql/9.5/main/base/16384/792572
603M    /var/lib/postgresql/9.5/main/base/16384/856953.2
577M    /var/lib/postgresql/9.5/main/base/16384/748701.2
558M    /var/lib/postgresql/9.5/main/base/16384/872720.2
501M    /var/lib/postgresql/9.5/main/base/16384/915522
332M    /var/lib/postgresql/9.5/main/base/16384/915519
326M    /var/lib/postgresql/9.5/main/base/16384/834520.2
277M    /var/lib/postgresql/9.5/main/base/16384/815863
273M    /var/lib/postgresql/9.5/main/base/16384/767992.2
222M    /var/lib/postgresql/9.5/main/base/16384/915532.3
219M    /var/lib/postgresql/9.5/main/base/16384/915535
101M    /var/lib/postgresql/9.5/main/base/16384/915529
72M     /var/lib/postgresql/9.5/main/base/16384/872717
70M     /var/lib/postgresql/9.5/main/base/16384/856950
69M     /var/lib/postgresql/9.5/main/base/16384/915538
69M     /var/lib/postgresql/9.5/main/base/16384/915534
67M     /var/lib/postgresql/9.5/main/base/16384/915544
66M     /var/lib/postgresql/9.5/main/base/16384/915811
63M     /var/lib/postgresql/9.5/main/base/16384/834517
56M     /var/lib/postgresql/9.5/main/base/16384/856955
56M     /var/lib/postgresql/9.5/main/base/16384/748703
55M     /var/lib/postgresql/9.5/main/base/16384/872722
53M     /var/lib/postgresql/9.5/main/base/16384/748698
51M     /var/lib/postgresql/9.5/main/base/16384/834522
50M     /var/lib/postgresql/9.5/main/base/16384/767994
49M     /var/lib/postgresql/9.5/main/base/16384/767989
27M     /var/lib/postgresql/9.5/main/base/16384/915814
16M     /var/lib/postgresql/9.5/main/base/16384/792574
15M     /var/lib/postgresql/9.5/main/base/16384/915525
13M     /var/lib/postgresql/9.5/main/base/16384/915547
13M     /var/lib/postgresql/9.5/main/base/16384/792569
9,4M    /var/lib/postgresql/9.5/main/base/16384/815860
8,6M    /var/lib/postgresql/9.5/main/base/16384/915891
5,7M    /var/lib/postgresql/9.5/main/base/16384/815865
4,7M    /var/lib/postgresql/9.5/main/base/16384/915528
2,2M    /var/lib/postgresql/9.5/main/base/16384/915515
992K    /var/lib/postgresql/9.5/main/base/16384/915894_fsm
848K    /var/lib/postgresql/9.5/main/base/16384/915532_fsm
832K    /var/lib/postgresql/9.5/main/base/16384/915518
568K    /var/lib/postgresql/9.5/main/base/16384/915820
512K    /var/lib/postgresql/9.5/main/base/16384/915541_fsm
440K    /var/lib/postgresql/9.5/main/base/16384/915665
384K    /var/lib/postgresql/9.5/main/base/16384/915838
376K    /var/lib/postgresql/9.5/main/base/16384/915626
272K    /var/lib/postgresql/9.5/main/base/16384/915636
264K    /var/lib/postgresql/9.5/main/base/16384/915669
264K    /var/lib/postgresql/9.5/main/base/16384/915668
232K    /var/lib/postgresql/9.5/main/base/16384/915548
216K    /var/lib/postgresql/9.5/main/base/16384/915827
144K    /var/lib/postgresql/9.5/main/base/16384/915522_fsm
136K    /var/lib/postgresql/9.5/main/base/16384/915642
120K    /var/lib/postgresql/9.5/main/base/16384/pg_internal.init
120K    /var/lib/postgresql/9.5/main/base/16384/915579
112K    /var/lib/postgresql/9.5/main/base/16384/915841
104K    /var/lib/postgresql/9.5/main/base/16384/915519_fsm
88K     /var/lib/postgresql/9.5/main/base/16384/915623
80K     /var/lib/postgresql/9.5/main/base/16384/915842
80K     /var/lib/postgresql/9.5/main/base/16384/915826
72K     /var/lib/postgresql/9.5/main/base/16384/915885
72K     /var/lib/postgresql/9.5/main/base/16384/915828
72K     /var/lib/postgresql/9.5/main/base/16384/915535_fsm
56K     /var/lib/postgresql/9.5/main/base/16384/915799
48K     /var/lib/postgresql/9.5/main/base/16384/915599
48K     /var/lib/postgresql/9.5/main/base/16384/915529_fsm
40K     /var/lib/postgresql/9.5/main/base/16384/915811_fsm
40K     /var/lib/postgresql/9.5/main/base/16384/915608
40K     /var/lib/postgresql/9.5/main/base/16384/915603

[terrier]

x67, Ну, вот, собственно вы видите своими глазами, что таблиц у вас все-таки на 24 Гб.
(/var/lib/postgresql/9.5/main/base/16384/915894.{1,2,3} - это сегменты, в которых хранится объект с relfilenode
равным 915894 - видимо это одна из таблиц. Как видите, сегменты нарезаются по гигабайту).
А почему вы решили, что у вас на 12 Гб таблиц?

[x67]

terrier, потому что в если сумма размеров всех таблиц равна 10,5 гигабайтам (на сегодня). Смотрел через \dt+ в psql
Конечно есть еще таблицы из toast, но \dt+ выдает сумарный размер данных, принадлежащих каждой юзерской таблице
Ну и вот еще:

SELECT pg_relation_filepath(oid), relpages FROM pg_class order by relpages DESC;
 pg_relation_filepath | relpages
----------------------+----------
 base/16384/915894    |   491300
 base/16384/915541    |   248098
 base/16384/915522    |    63932
 base/16384/915519    |    42428
 base/16384/915535    |    27987
 base/16384/915529    |    12694
 base/16384/915544    |     8481
 base/16384/915811    |     8419
 base/16384/915814    |     3325
 base/16384/915525    |     1806
 base/16384/915547    |     1637
...

То есть я правильно понимаю, что например файлы 915894.1 915894.2 к БД не относятся, хоть и физически существуют? Это какие то аварийные дампы или что? UPD, перечитал, понял, сегменты таблиц. А почему тогда по этим файлам можно насчитать 8 таблиц превышающих размер в 1 гб, а судя по \dt+ таких таблиц только 3 (2 из них просто записывают огромные объемы данных, третья пишет jsonb), остальные таблицы по факту и не могут быть большими, так как объемы данных там точно не больше, а периодичность записи точно меньше, чем в первых трех

[terrier]

Ну вот осталось выяснить, что скрывается за relfilenode 915532, 872720 и т.д.
Можно глянуть

select name, relkind from pg_class where relfilenode = ...

Ну и на крайняк утилитка oid2name (oid и relfilenode могут, хотя и не обязаны совпадать)
Также посмотрите на даты этих файлов через ls - может навести на мысль, откуда они появились.

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

[x67]

terrier, для некоторых relfilenode объектов в базе нет, хотя файлы существуют. Даты у всех файлов - последние числа августа, сентябрь. Как я понял каждый relfilenode пересоздается после полного вакуума, то есть скорее всего сбой во время этой процедуры и привел к созданию "независимых" файлов? Могли ли обращения сторонних программ к залоченным таблицам во время вакуума привести к этому? (всегда считал, что самое худшее - потеря данных в этот период времени, что для меня не критично) И как теперь можно намекнуть постгресу, что часть файлов уже не нужна, чтобы ничего не сломать?

[terrier]

Как я понял каждый relfilenode пересоздается после полного вакуума

Да

Могли ли обращения сторонних программ к залоченным таблицам во время вакуума привести к этому?

Да, нет, ну залоченные они на то и залоченные что к ним просто так не обратишься. Но у вас, очевидно, произошел некий сбой.

И как теперь можно намекнуть постгресу, что часть файлов уже не нужна, чтобы ничего не сломать?

В принципе, если вы уверены, что для этого файла точно нет соответствия в базе, то его можно удалять, но это, конечно, ковбойский способ. Я бы, повторюсь, разобрался бы с крашами и пересоздал кластер. Сколько там эти 12 Гб будут заливаться - несколько минут?

[x67]

terrier, Спасибо
А нет ли в постгресе какой-то команды или скрипта для проверки консистентности и автоматического исправления конфликтов между файлами и таблицами? Очень не хотелось бы влезать в бд настолько глубоко, по крайней мере пока, а места на диске для пересоздания кластера может не хватить)
И в какую сторону копать, дабы разобраться с крашами?

[terrier]

А нет ли в постгресе какой-то команды или скрипта для проверки консистентности и автоматического исправления конфликтов между файлами и таблицами?

Нет, это все-таки ненормальная ситуация и самый лучший совет для того, чтобы не появлялись такие файлы - это "не падать".

И в какую сторону копать, дабы разобраться с крашами?

Ну, в логи, понятно дело:
- В системные. Никто там процесс постгреса не прибивает? Ограничения на память для процесса? OOM killer?
- В постгресовые. Включите максимальное разумный уровень записи в логи, и смотрим там.
- Настройки постгреса разумные? Проверьте здесь pgtune.leopard.in.ua
- Можно попросить постгрес генерировать крэш дампы - разобраться, может и не получится, но в гугл строчки из бэктрейса в конце концов можно засунуть.
- Железо. Обидно биться неделю об отладку только для того, чтобы узнать, что диск или память порченные. Бывает простое решение "перенести базу на другой сервер" самое лучшее ;)