Индексация идёт уже 2 недели, в чем у меня ошибка?

Question

[beduin01]

Заливаю дамп PostgreSQL в PG14.
Размер дампа 60GB.
В дампе 25 таблиц.
В каждой таблице от 1 до 7 индексов.
В одном из мест используется составной индекс.
Несколько индексов хэши -- остальные btree

Максимальный размер одной из таблиц около 100 млн записей.

Проблема в том, что индексация идёт уже 14 дней и судя по pg_stat_progress_create_index будет идти еще дней 15.

Вроде все конфиги которые могут повлиять на скорость индексации я подкрутил (перед запуском заливки).

Сервер Xeon 6 ядер. 16GB RAM.

Диск HDD. Данные загрузились за 5 часов.
Индексы создаются синхронно.
Процессор почему-то загружен только на 25%.
ОС Windows.

Конфиг: pastie.org/p/585DJwfj6rstQwukSQjhtT

Comments

[Василий Банников]

Сервер Xeon 6 ядер. 16GB RAM.

SSD?

[Roman K]

Индексы создаются синхронно или асинхронно? Если асинхронно, какая нагрузка на БД?

[rPman]

посмотреть на нагрузку во что упирается сервер нужно
мне нравится консольная утилита atop, сразу покажет, цветом подкрасит что именно по максимуму нагружено, почти наверняка диск медленный (хотя месяц на 100млн записей это перебор, все же должно работать быстрее)

[ComodoHacker]

А за какое время загрузились данные?

[beduin01]

ComodoHacker, Roman Kitaev, Василий Банников, добавил ответы в стартовый пост.

[Roman K]

beduin01,

Процессор почему-то загружен только на 25%.

Вот поэтому:

Диск HDD. Данные загрузились за 5 часов.

CPU незачем работать, пока он ждёт диск. PG — это куча страниц по 8кб, раскиданных по диску. Для HDD это — рандомное чтение/рандомная запись. Это в тысячи раз дольше, чем для SSD. А если ещё и TOAST есть и попадает в индекс — ещё медленней.

[ComodoHacker]

Roman Kitaev, Вы ошибаетесь. Построение индекса по загруженным данным это не рандомное чтение. Тут явно что-то не так.

[Roman K]

ComodoHacker, то есть, индекс на диске — гарантировано последовательная структура и при построении индекса между добавлениями btree нод и считываниями новых данных из таблицы (которая вообще неизвестно где находится) позиционирования головки не происходит?

[DevMan]

Roman Kitaev, ComodoHacker, rPman, это потому что сервер не на D. или, крайняк, Dart.
бедуин, переписывай на wasm.

сорян за офтоп, не могу удержаться.

[ComodoHacker]

Roman Kitaev, Не знаю, что вы подразумеваете под "последовательной структурой", но алгоритм построения индекса оптимизирован именно под HDD, чтобы доступ к диску был максимально последовательным. Сначала сканируется таблица, затем выборка сортируется, затем пишется в индекс как последовательность листовых узлов, и по ходу дела создаются промежуточные узлы. Это совсем не тот алгоритм, что используется при обычных вставках или модификациях отдельных строк в таблице.

Answer 1

[rPman]

значит узкое место почти наверняка диск.

Пальцем в небо, файловая система на которой таблеспейсы лежат какая? случайно не cow (btrfs/zfs/xfs)? с ними отвратительно работают базы данных, так как частые записи в файл генерируют сильную фрагментацию. В этом случае перед тяжелой обработкой хотя бы дефрагментируй файлы базы и отключи cow фичу на таблеспейсах

неплохим тюнингом может оказаться (на выбор):
* разместить базу в ram диске (буквально, залить на сервер в облаке, обработать данные, залить назад, работая напрямую с таблеспейсами, но версия софта должна совпадать до последней цифры)
* разместить базу целиком на ssd (даже если это будет потребительский и дешевый)
* добавить в систему ssd кеш для hdd с помощью например bcache (включенный на запись), правда для линейной обработки базы это может дать мало пользы, но вообще это неплохой способ на порядок поднять производительность за дешево (в одном месте я использовал фичу virtualbox со снапшотами в файл, есть и у kvm, когда последующие записи шли не на исходный образ а на другой диск, и он ssd)
* разместить таблеспейс для индексов (а может и каждую таблицу отдельно) на другом физическом устройстве (hdd, ssd или даже в ram), требования к размеру тут обычно низкие, ключевое слово - исключить последовательные чтения/записи на одно устройство.
* разместить журнал (например ext4) на ssd диск (хватит пары гигабайт) или по хардкору даже отключить его (очень опасно, можно получить кашу из данных при сбое питания, но как временное решение пока идет долгая операция, при наличии всех бакапов, оправдано) - наименьшая оптимизация, но при частых мелких записях это заметно

Comments

[beduin01]

А какие FS лучше всего для БД подходят?

[Василий Банников]

beduin01, на ext4 вполне нормально должно работать.
Главное чтобы не cow

[beduin01]

Василий Банников, получается в противовес cow идут журналируемые файловые системы?

[beduin01]

Я правильно понимаю, что WAL это фактически тот же самый COW?

[galaxy]

beduin01,

Я правильно понимаю, что WAL это фактически тот же самый COW?

WAL - журнал, COW - copy-on-write (так что - нет, неправильно)

получается в противовес cow идут журналируемые файловые системы?

независимые вещи. ext4 - журналируемая без COW, zfs - нежурналируемый COW, btrfs - есть и то, и то.

[galaxy]

beduin01, ну и в дополнение к ответу: из моего опыта, например, zfs вполне нормально работает с БД, но до тех пор пока достаточно свободного места на диске. Чем ближе к концу, тем хуже, на 90+% она может тупо висеть

[beduin01]

galaxy, а WAL разве не устроен по принципу CoW? Вроде бы у них назначение похожее очень

[galaxy]

beduin01, назначение не похожее, вообще говоря, просто с помощью COW можно, в принципе, пытаться решать задачу надежной записи на диск и восстановления после сбоя. А так COW - это, по сути, копирование вместо перезаписи. Этот принцип используется много где и много для чего (например, при вызове fork() в *nix должна создаваться копия адресного пространства для нового процесса - конечно, сейчас никто не делает копию, просто при записи в свою память новым процессом создается новая страница-копия).
COW позволяет не только увеличивать надежность ФС, но и делать моментальные снепшоты - очень полезная вешь в некоторых ситациях.

По поводу устройства: ничего общего. Журнал - это журнал: последовательная область ФС, куда пишутся данные или метаданные до записи в целевые области ФС. COW при записи на диск прото пишет в новый блок ФС, не трогая старый

[beduin01]

galaxy, но можно ли выключить журнал или сделать его уровень минимальным в надежде, что контроль целостности обеспечит CoW?

[galaxy]

beduin01, не знаю про все ФС. В ext4, например, можно указать, писать ли в журнал данные или только метаданные
https://www.kernel.org/doc/Documentation/filesyste...

[rPman]

galaxy, zfs отвратительно работает с hdd, особенно потребительскими (очень много визуально лишних операций даже на чтение, из-за чего hdd очень много дергает головкой, полагаю серверные hdd с быстрыми шпинделями и большими кешами проблему скрывают) плюс хочет оперативную память для онлайн дедупликации ()с другой стороны ради этой фичи можно и по страдать), у btrfs чуть по лучше но все равно есть лишние записи (объем в количестве записей и в мегабайтах в несколько раз выше чем софт реально отправляет)

у всех cow файловых систем есть возможность на каталог и файлы (пока они нулевой длины) выставить флаг 'chattr -c', отключающий cow фичу. Это оправдано так как очень часто низкая цена на снапшоты оправдывает эти сложности (особенно когда альтернатива только lvm и там скорость раздела под снапшотом в разы медленнее)