Как оптимизировать производительность большой базы в postgresql?

Question

[tsegorah]

Есть база данных в postgresql под rhel для очень специфичного ПО (специфичность заключается в том, что, например, связи между таблицами могут хранится не в БД, а в прикладном ПО).
Объемы базы в пределах до 100-200 гигабайт.
Есть довольно шустрое железо, это около 200+ гигов оперативы, доступных и четыре Xeon E5-4640.

95% запросов к базе будут на выборку данных, приблизительно равномерно по частоте размазаны тривиальные селекты и join нескольких таблиц со сложными условиями. Данные приблизительно равнометно запрашиваются из разных таблиц.
5% запросов будут на запись небольших объемов данных.

Вопрос в том, как лучше оптимизировать все под такую задачу, вариант просто положить в БД данные и минимальный тюнинг СУБД не дают нужной производительности.

Сразу на ум приходит несколько вариантов.

Первый вариант это ограничиться просто вдумчивым тюнингом СУБД.
К счастью, на офф сайте документации достаточно (например, тут wiki.postgresql.org/wiki/Performance_Optimization).
Но тут остаются вопросы по тому насколько СУБД сможет использовать все ресурсы, и не будет ли проблем с долгим "прогревом".

Второй вариант обусловлен тем, что вся база поместится в оперативную память. Можно примонтировать оперативку и настроить соответствующим образом табличное пространство.
Но тогда возникает вопрос стабильности и согласованности данных, ведь память то энергозависимая. Как вариант, можно предусмотреть репликацию с такой же БД на харде,
или единую точку входа для прикладного ПО, которая будет отправлять все запросы в обе базы.

Третий вариант обусловлен тем, что большая часть запросов на чтение. Разделить аппаратные ресурсы на несколько реплик БД и соответствующим образом организовать запросы на чтение/запись.

Партицирование просьба не предлагать, это и так отдельный кусок базы, которые далее нецелесообразно отделять.

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

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

Comments

[Вячеслав Успенский]

первое на что стоит смотреть после тюнинга - prepared query

Answer 1

[hell]

Собственно, вам нужно поднять значение shared_buffers до величины, чтобы в них влезала ваша максимальная выборка ( общий объем всех таблиц самого большого джойна), установить work_mem так, чтобы все сортировки оказывались в оперативной памяти (смотрится explain analyzeом), а потом (или, скорее - в процессе) заставить вашу машину со всем этим работать, донастроив ядро.

Comments

[tsegorah]

Ковырял эти данные, но про подсказку по конкретным значениям спасибо. Протестируем в таком варианте.

Answer 2

[hell]

Теоретически (да и практически, чаще всего), PostgreSQL умный. То есть, весьма велика вероятность, что вам хватит "вдумчивого тюнинга". Правда, тюнинговать придется не только БД, но и операционку (в случае Linux лопатится примерно четверть доступных sysctl).
Монитровать оперативку скорее всего просто не нужно - как я уже писал, PostgreSQL умный и обычно справляется сам (разумеется с правильными настройками).
Делить аппаратные ресурсы в вашем случае тоже смысла особого не имеет - PostgreSQL очень хорошо параллелится.
Возможно, вам еще стоит посмотреть в сторону pgbouncer - в режиме transaction и с постоянными соединениями он держит СУБД в "разогретом" состоянии, а это, насколько я понимаю, в данном случае и будет являться потенциальным бутылочным горлышком.

Comments

[tsegorah]

За pgbouncer спасибо. Попробуем настроить и сравнить производительность.

[Алексей Сундуков]

@tsegorah pgbouncer ставить нужно обязательно, установка соединения в PostgreSQL достаточно дорогая операция (разница может быть в десятки раз).

[tsegorah]

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

Answer 3

[Руслан Касымов]

Из вопроса не совсем ясна структура данных. Т.е. кретична ли высокая согласованность данных? Требуют ли данные транзакции?
Если устраивает подход "согласованность в конечном счете", то вероятно есть смысл смотреть в сторону NoSQL и там использовать MapReduce

Comments

[tsegorah]

Прикладное ПО работает только в postgresql. Схема далеко не соответствует трабованиям по нормализации и тд, но она фиксированная. Ну и да, вопрос согласованности данных важен.