Коллеги, подскажите, какая лучше всего файловая система для хранения данных

постгреса ? база большая (400Гб), очень много таблиц (более 4к), и тормозит судя по strace на pread64 , т.е. на случайном чтении. Тормозит как select, так и vacuum, cluster и тд. Сейчас ext4 без какого-либо тюнинга. Что лучше выбрать для хранения данных таблиц на hdd (wal лежит на nvme/ext4): btrfs, xfs, jfs, zfs, nils ?

спасибо, кэп, это и так понятно. Но у заказчика может не быть бюджета на hot plug nvme .

Вот и оставьте ext4 и не меняйте

выберите ту фс, которую лучше всего знает ваш знакомы сисадмин

с учетом того, что это 2 диска в зеркало и 400гб это не окончательный объем , плюс их надо будет hotplug делать и это все *2 для резервирования самого сервера в 500$ точно это не выльется.

Что-то мне подсказывает, что проблема тут далеко не в файловой системе и даже не в отсутствии индексов. При среднем размере таблицы в 100Мбайт все это добро должно даже при cross join двух таких таблиц должно спокойно влезать в оперативку при нормальных настройках в postgresql.conf

оптимизировать запросы уже некуда: есть индекс btree по числу, по которому и достаются данные. sort, join - нет

ну вот тыкая perf-ом и strace получаю что тормозит именно чтение с диска, так как постгрес читает 8к за раз одним pread64.

Какие параметры сервера и какие настройки shared_buffers и work_mem стоят?

тут еще может такая штука быть как исторические данные лежат в одной куче с горячими. Так то мало вводных) Менять фс по моему как то странно

X5680 *2, 16Gb RAM (ее хватает, во время работы остается free) , shared_buffers = 4096MB , work_mem = 128MB .

А ещё бы хорошо посмотреть \d+ таблицы, откуда читаете, и explain analyze самого запроса. А то мало ли

ну так вот дело в том, что все данные "теплые": оно частями достается постоянно, частично обновляется

База, которая постоянные random iops гоняет, на hdd. Wal, который by design sequential iops гоняет, на nmve. Правильной дорогой идёте.

nvme там не только для wal, но и для части таблиц которые мелкие, но с очень большим количеством запросов. с ними как раз проблем нет.

нет, конечно. select * from ... where idx in (....), обновления тоже пачками по 100-200 записей. софт уже поправлен, что бы не было обновлений одной таблицы "параллельно" из нескольких потоков

А вот это уже читали? https://habr.com/ru/company/ozontech/blog/564520/

https://habr.com/ru/post/169513/

да, cluster не особо помогает.

ну вот тут как раз ожидаемые числа

На самом деле если на разные диски — то вероятно на пользу, но по деньгам опять дорожэ...

Мелкие таблицы, из которых постоянно — будут сидеть в памяти, нет смысла их на nvme класть.

ставьте XFS https://www.programmersought.com/article/84541519658/

зато еще и надежнее.

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

там 13 год, 9.2.1 версия pg ... :c

Да тут даже обычные sata/sas ssd вместо имеющихся механических дисков будут на совершенно другом уровне. И это те самые 400-500$ фактически

Это вопрос не к pg, а к ядру

этот вариант рассматривается, но вопрос в том, насколько быстро софт "запилит" диск, что его придется менять.

Не сказал бы.

Берете какой-нибудь intel с mlc ячейками. Забываете лет на 5-7

ну лучше чем 1 диск. для надежности 2й сервер с БД есть :)

Хужэ, конечно. Вероятность отказа повышается примерно вдвое (поскольку постгрес падает при отказе любого тэйблспейса).

Мне кажется, Вы не до конца поняли посыл этой статьи... и Вам правильно же советуют https://t.me/pgsql/317882 . Т.е. если добиться того, чтобы все "горячие" данные были в shared buffers, то эффект будет гораздо больше, чем от "плясок вокруг дисков". Как говорится, "Cache is King". ©

нету там горячих данных - запросы к этим данным рандонмые: читается случайная часть данных с точки зрения софта, генератор случайности - юзер, который то это смотрит, то то.

Так, на самом деле, в правильно спроектированных OLTP базах почти никогда не бывает — что-то из выделенного к этой не относится? Или она чем-то ещё особенная?

какие данные запросит юзер предугадать невозможно. так как там по смыслу этой таблицы все данные уже "горячие" и так. Те, что архив лежат в другой таблице.

Значит, опять-таки — тут либо просто не хватает RAM, либо какая-то часть того, что читается с диска — просто лишняя (к примеру, малополезные индексы плохи не только тем, что замедляют CRUD, но и тем, что занимают "чужую" память / искусственно "раздувают" горячие данные). Т.е. на Вашем месте я бы смотрел в этих направлениях.

Ну да, я видел (и Вас даже процитировал ;) ) — мне просто показалось, что он не понял, что и почему ему советуют.

я уже пробовал выключать часть индексов: постгрес переходит на "grep" по данным, что не очень хорошо, так как поднимаются все данные с диска. в таблице 2 индекса, оба активно используются при запросах. в одном из индексов есть include поле: оно нужно для того, что бы как раз не поднимать с диска саму запись ради u32 числа. сама запись в БД тоже максимально компактная, насколько это возможно. Все упирается в iops диска, скорее всего.

Нет, Вы не понимаете, в чём суть, к сожалению. :( Пример с индексом был просто примером для понимания — очевидно, неудачным. > Все упирается в iops диска, скорее всего. Опять-таки, при правильном проектировании (и, соответственно, достаточном количестве RAM) база на чтении будет "летать" хоть на дискетах, грубо говоря. ;) Т.е. диски практически не имеют значения. > в одном из индексов есть include поле: оно нужно для того, что бы как раз не поднимать с диска саму запись ради u32 числа. Вот даже на этом примере — если другие запросы почему-то поднимают с диска те же сами записи — это "раздутые" горячие данные. Т.е. include тут — это минус, а не плюс. Т.е. общий посыл в том, что при нехватке RAM для "горячих" данных все прочие меры по сравнению с устранением этой нехватки практически бесполезны, понимаете? Проще всего добавить [быстрой] RAM, ясное дело. ;)

> Т.е. include тут — это минус, а не плюс с учетом того, что у меня в этих таблицах нет insert вообше (он был в начале, когда данные создавались), то для обновления данных мне надо как раз получить это поле u32 с индекса. так как hot update нет, то мне зачем читать всю запись ради одного поля, если я ее гарантировано буду перезаписывать ?

> я к тому что посмотреть в системной таблице вьюхе нельзя его работу? Для этого "из коробки" есть расширение: https://www.postgresql.org/docs/current/pgbuffercache.html > так то я понимаю, что чаще читается - то в буфере лежит. Грубо говоря, да (есть всякие специальные стратегии для работы с shared buffers, но они, в основном, для защиты от всяких "глупостей", вроде того, как если бы "случайный" seq.scan огромной таблицы "вымывал" всё, что было в RAM). > если я правильно знаю, то деления на разные буферы в постгрис как в оракле нет? Я не знаю, как там в Oracle. Но shared_buffers используется не только для кеширования данных БД, конечно.

> то для обновления данных мне надо как раз получить это поле u32 с индекса. Зачем?! > то мне зачем читать всю запись ради одного поля, если я ее гарантировано буду перезаписывать ? А всё равно PostgreSQL её прочитает. С HOT или без него — update всегда читает записи целиком, насколько я помню. Т.е. этот индекс — почти наверняка пример того, о чём я писал.

поставил xfs, на ней действительно лучше стало. Еще попробовал jfs - ну то же самое, что и с ext4. На xfs при том же iops (ну быстрее не может оно) объем прокачиваемых данных больше.

Чего за древняя статья такая- «EXT4 can only support up to 4TB (more need to patch)»?

Ну да, мы тоже пробовали у себя на ней 👍