Почеу пухнет wal каталог postgresql?

Question

[Владимир]

Сервер падает под нагрузкой от одной из БД. Инсёртится куча данных и выбирается куча данных после чего генерируется больше количество wal файлов.

Они отправляются на slave (есть мониторинг задержек между мастером и слейвом) где успешно проигрываются.
Они же архивируются, при чём ошибок архивации нет (и разбега тоже, я мониторю количество wal не отправленных в хранилище)

wal рано или поздно переполняют диск и сервер падает, после чего перезагружается и следующий чекпоинт вырезает все тысячи wal файлов без проблем.
Пока думаю только в сторону IO т.к оно достаточно высокое. но не могу понять почему wal не удаляются хотя должны.

Comments

[Melkij]

Покажите

show wal_keep_segments ;
show max_wal_size ;
show checkpoint_timeout ;
table pg_replication_slots ;
table pg_stat_archiver ;

[Владимир]

Melkij,

postgres@postgres=# show wal_keep_segments ;
-[ RECORD 1 ]-----+---
wal_keep_segments | 64

Time: 0.152 ms
postgres@postgres=# show max_wal_size ;
-[ RECORD 1 ]+----
max_wal_size | 2GB

Time: 0.056 ms
postgres@postgres=# show checkpoint_timeout ;
-[ RECORD 1 ]------+-----
checkpoint_timeout | 5min

Time: 0.045 ms
postgres@postgres=# table pg_replication_slots ;
-[ RECORD 1 ]-------+--------------
slot_name           | repmgr_slot_2
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 31662
xmin                |
catalog_xmin        |
restart_lsn         | 3554/5F9274A8
confirmed_flush_lsn |

Time: 0.483 ms
postgres@postgres=# table pg_stat_archiver ;
-[ RECORD 1 ]------+------------------------------
archived_count     | 1197
last_archived_wal  | 00000001000035540000005E
last_archived_time | 2020-09-23 15:32:45.913265+03
failed_count       | 0
last_failed_wal    |
last_failed_time   |
stats_reset        | 2020-09-23 05:48:38.569449+03

Time: 10.532 ms
postgres@postgres=#

[Melkij]

и из логов записи чекпойнов нужны во время проблемы (при log_checkpoints = on)

[Владимир]

Melkij,

2020-09-23 04:19:05.870
checkpoint complete: wrote 516413 buffers (4.1%); 0 WAL file(s) added, 43 removed, 0 recycled; write=94.326 s, sync=0.653 s, total=95.483 s; sync files=216, longest=0.175 s, average=0.003 s; distance=3621476 kB, estimate=3621476 kB
	2020-09-23 04:20:41.355
checkpoint complete: wrote 545686 buffers (4.3%); 0 WAL file(s) added, 48 removed, 0 recycled; write=96.962 s, sync=0.818 s, total=98.712 s; sync files=260, longest=0.213 s, average=0.003 s; distance=3776193 kB, estimate=3776193 kB
	2020-09-23 04:22:20.068
checkpoint complete: wrote 565744 buffers (4.5%); 0 WAL file(s) added, 48 removed, 0 recycled; write=101.619 s, sync=0.432 s, total=102.406 s; sync files=272, longest=0.148 s, average=0.001 s; distance=3897981 kB, estimate=3897981 kB
	2020-09-23 04:24:02.475
checkpoint complete: wrote 579010 buffers (4.6%); 0 WAL file(s) added, 48 removed, 0 recycled; write=103.964 s, sync=0.799 s, total=105.157 s; sync files=280, longest=0.194 s, average=0.002 s; distance=4041618 kB, estimate=4041618 kB
	2020-09-23 04:25:47.633
checkpoint complete: wrote 595010 buffers (4.7%); 0 WAL file(s) added, 52 removed, 0 recycled; write=108.080 s, sync=0.785 s, total=109.265 s; sync files=268, longest=0.157 s, average=0.002 s; distance=4121686 kB, estimate=4121686 kB
	2020-09-23 04:27:36.900
checkpoint complete: wrote 614878 buffers (4.9%); 0 WAL file(s) added, 51 removed, 0 recycled; write=111.459 s, sync=0.725 s, total=112.609 s; sync files=290, longest=0.152 s, average=0.002 s; distance=4276085 kB, estimate=4276085 kB
	2020-09-23 04:29:29.510
checkpoint complete: wrote 631424 buffers (5.0%); 0 WAL file(s) added, 55 removed, 0 recycled; write=113.838 s, sync=0.562 s, total=114.834 s; sync files=250, longest=0.156 s, average=0.002 s; distance=4404573 kB, estimate=4404573 kB
	2020-09-23 04:31:24.345
checkpoint complete: wrote 660754 buffers (5.3%); 0 WAL file(s) added, 54 removed, 0 recycled; write=116.597 s, sync=0.493 s, total=117.501 s; sync files=348, longest=0.137 s, average=0.001 s; distance=4592097 kB, estimate=4592097 kB
	2020-09-23 04:33:21.847
checkpoint complete: wrote 667591 buffers (5.3%); 0 WAL file(s) added, 59 removed, 0 recycled; write=123.305 s, sync=0.684 s, total=124.356 s; sync files=267, longest=0.214 s, average=0.002 s; distance=4658769 kB, estimate=4658769 kB
	2020-09-23 04:35:26.204
checkpoint complete: wrote 698686 buffers (5.6%); 0 WAL file(s) added, 57 removed, 0 recycled; write=124.273 s, sync=0.490 s, total=125.295 s; sync files=328, longest=0.131 s, average=0.001 s; distance=4875460 kB, estimate=4875460 kB
	2020-09-23 04:37:31.500
checkpoint complete: wrote 713123 buffers (5.7%); 0 WAL file(s) added, 59 removed, 0 recycled; write=125.435 s, sync=0.434 s, total=126.438 s; sync files=222, longest=0.092 s, average=0.001 s; distance=4994466 kB, estimate=4994466 kB
	2020-09-23 04:39:37.940
checkpoint complete: wrote 733523 buffers (5.8%); 0 WAL file(s) added, 65 removed, 0 recycled; write=135.692 s, sync=0.600 s, total=136.880 s; sync files=302, longest=0.154 s, average=0.001 s; distance=5104651 kB, estimate=5104651 kB
	2020-09-23 04:41:54.821
checkpoint complete: wrote 764430 buffers (6.1%); 0 WAL file(s) added, 60 removed, 0 recycled; write=134.568 s, sync=0.507 s, total=135.629 s; sync files=269, longest=0.134 s, average=0.001 s; distance=5326627 kB, estimate=5326627 kB
	2020-09-23 04:44:10.451
checkpoint complete: wrote 781289 buffers (6.2%); 0 WAL file(s) added, 70 removed, 0 recycled; write=149.158 s, sync=0.599 s, total=150.202 s; sync files=242, longest=0.176 s, average=0.002 s; distance=5441411 kB, estimate=5441411 kB
	2020-09-23 04:46:40.654
checkpoint complete: wrote 827285 buffers (6.6%); 0 WAL file(s) added, 71 removed, 0 recycled; write=149.880 s, sync=0.518 s, total=150.902 s; sync files=345, longest=0.125 s, average=0.001 s; distance=5726855 kB, estimate=5726855 kB
	2020-09-23 04:49:11.557
checkpoint complete: wrote 828570 buffers (6.6%); 0 WAL file(s) added, 67 removed, 0 recycled; write=149.845 s, sync=0.541 s, total=150.754 s; sync files=230, longest=0.183 s, average=0.002 s; distance=5763169 kB, estimate=5763169 kB
	2020-09-23 04:51:42.313
checkpoint complete: wrote 862726 buffers (6.9%); 0 WAL file(s) added, 73 removed, 0 recycled; write=154.824 s, sync=0.735 s, total=156.183 s; sync files=413, longest=0.130 s, average=0.001 s; distance=5995189 kB, estimate=5995189 kB
	2020-09-23 04:54:18.497
checkpoint complete: wrote 881530 buffers (7.0%); 0 WAL file(s) added, 75 removed, 0 recycled; write=158.884 s, sync=0.631 s, total=160.005 s; sync files=319, longest=0.199 s, average=0.001 s; distance=6142932 kB, estimate=6142932 kB
	2020-09-23 04:56:58.504
checkpoint complete: wrote 902975 buffers (7.2%); 0 WAL file(s) added, 74 removed, 0 recycled; write=163.007 s, sync=0.985 s, total=164.541 s; sync files=251, longest=0.432 s, average=0.003 s; distance=6297079 kB, estimate=6297079 kB
	2020-09-23 04:59:43.047
checkpoint complete: wrote 933434 buffers (7.4%); 0 WAL file(s) added, 82 removed, 0 recycled; write=172.206 s, sync=0.932 s, total=173.658 s; sync files=330, longest=0.326 s, average=0.002 s; distance=6525318 kB, estimate=6525318 kB
	2020-09-23 05:02:36.705
checkpoint complete: wrote 976508 buffers (7.8%); 0 WAL file(s) added, 84 removed, 0 recycled; write=180.547 s, sync=0.677 s, total=182.110 s; sync files=426, longest=0.145 s, average=0.001 s; distance=6777622 kB, estimate=6777622 kB
	2020-09-23 05:05:38.817
checkpoint complete: wrote 1000907 buffers (8.0%); 0 WAL file(s) added, 83 removed, 0 recycled; write=184.149 s, sync=0.889 s, total=185.772 s; sync files=369, longest=0.127 s, average=0.002 s; distance=6964558 kB, estimate=6964558 kB
	2020-09-23 05:08:44.590
checkpoint complete: wrote 1035732 buffers (8.2%); 0 WAL file(s) added, 87 removed, 0 recycled; write=183.870 s, sync=0.452 s, total=185.044 s; sync files=261, longest=0.065 s, average=0.001 s; distance=7194936 kB, estimate=7194936 kB
	2020-09-23 05:11:49.636
checkpoint complete: wrote 1045965 buffers (8.3%); 0 WAL file(s) added, 87 removed, 0 recycled; write=191.660 s, sync=0.470 s, total=192.697 s; sync files=294, longest=0.141 s, average=0.001 s; distance=7270727 kB, estimate=7270727 kB
	2020-09-23 05:15:02.334
checkpoint complete: wrote 1082124 buffers (8.6%); 0 WAL file(s) added, 92 removed, 0 recycled; write=200.842 s, sync=0.580 s, total=202.213 s; sync files=324, longest=0.146 s, average=0.001 s; distance=7569814 kB, estimate=7569814 kB
	2020-09-23 05:18:24.548
checkpoint complete: wrote 1131966 buffers (9.0%); 0 WAL file(s) added, 98 removed, 0 recycled; write=208.839 s, sync=0.697 s, total=210.161 s; sync files=461, longest=0.185 s, average=0.001 s; distance=7882286 kB, estimate=7882286 kB
2020-09-23 05:21:54.710
checkpoint complete: wrote 1158558 buffers (9.2%); 0 WAL file(s) added, 96 removed, 0 recycled; write=211.192 s, sync=0.533 s, total=212.385 s; sync files=277, longest=0.109 s, average=0.001 s; distance=8040977 kB, estimate=8040977 kB
	2020-09-23 05:25:27.097
checkpoint complete: wrote 1185376 buffers (9.4%); 0 WAL file(s) added, 99 removed, 0 recycled; write=216.885 s, sync=0.408 s, total=218.036 s; sync files=388, longest=0.127 s, average=0.001 s; distance=8215892 kB, estimate=8215892 kB
	2020-09-23 05:29:05.134
checkpoint complete: wrote 1200198 buffers (9.5%); 0 WAL file(s) added, 97 removed, 0 recycled; write=208.808 s, sync=0.613 s, total=210.318 s; sync files=286, longest=0.187 s, average=0.002 s; distance=8175757 kB, estimate=8211878 kB
	2020-09-23 05:32:35.453
checkpoint complete: wrote 1214953 buffers (9.7%); 0 WAL file(s) added, 101 removed, 0 recycled; write=224.536 s, sync=0.530 s, total=225.776 s; sync files=390, longest=0.132 s, average=0.001 s; distance=8068065 kB, estimate=8197497 kB
	2020-09-23 05:36:21.231
checkpoint complete: wrote 1268153 buffers (10.1%); 0 WAL file(s) added, 98 removed, 0 recycled; write=218.545 s, sync=0.369 s, total=219.560 s; sync files=356, longest=0.076 s, average=0.001 s; distance=8235931 kB, estimate=8235931 kB
	2020-09-23 05:40:00.792
	
-----------------reboot-----------------
	
checkpoint complete: wrote 1538583 buffers (12.2%); 0 WAL file(s) added, 12828 removed, 0 recycled; write=89.681 s, sync=0.290 s, total=151.618 s; sync files=422, longest=0.049 s, average=0.000 s; distance=13940819 kB, estimate=13940819 kB
	2020-09-23 05:48:37.800
checkpoint complete: wrote 21728 buffers (0.2%); 0 WAL file(s) added, 306 removed, 51 recycled; write=269.336 s, sync=0.175 s, total=271.911 s; sync files=407, longest=0.051 s, average=0.000 s; distance=104053 kB, estimate=104053 kB
	2020-09-23 05:58:10.548
checkpoint complete: wrote 26130 buffers (0.2%); 0 WAL file(s) added, 181 removed, 7 recycled; write=269.472 s, sync=0.107 s, total=270.979 s; sync files=317, longest=0.034 s, average=0.000 s; distance=95187 kB, estimate=103166 kB
	2020-09-23 06:03:09.557
checkpoint complete: wrote 29285 buffers (0.2%); 0 WAL file(s) added, 180 removed, 6 recycled; write=269.388 s, sync=0.135 s, total=270.563 s; sync files=421, longest=0.055 s, average=0.000 s; distance=113793 kB, estimate=113793 kB
	2020-09-23 06:08:09.146
checkpoint complete: wrote 14733 buffers (0.1%); 0 WAL file(s) added, 77 removed, 7 recycled; write=269.822 s, sync=0.164 s, total=270.423 s; sync files=341, longest=0.046 s, average=0.000 s; distance=117321 kB, estimate=117321 kB
	2020-09-23 06:13:08.599

[Владимир]

Melkij,
Частота чекпоинтов

[Владимир]

Melkij, =(

[Melkij]

Что-то чекпойнты всё-таки удаляют. Значит кто-то их держит, но медленно выгружает. Из-за того что при аварийном рестарте их выгрузку не ждём - поведение похоже на temporary replication slot.