Как правильно тюнить конфиги БД, распределять ресурсы?

Question

[Chvalov]

Немного запутался с конфигами, а точнее с распределением ресурсов.
Есть сервер с 32Гб ОЗУ, 4 ядра по 3.2ГГц, 2Тб HDD в рейде
Установленно: PHP-FPM, MySQL, Postgresql, Nginx
читаю о конфигах на ruhighload

Начнем к примеру с Postgresql:
shared_buffers:

следует установить в 15..25% от всей доступной оперативной памяти

effective_cache_size:

следует устанавливать в 50%...75% всей доступной оперативной памяти

Далее MySQL:
innodb_buffer_pool_size

стоит устанавливать в 70%...80% всей доступной памяти

Как правильно распределять ресурсы в конфигах, чтобы все работало нормально и никого не обидеть теснотой ?
Ведь если по такой инструкции выделять ОЗУ Postgresql и MySQL не уверен что они будут нормально работать при одинаковых нагрузках.

Здесь по инструкции только под Postresql выделяется все 100% ОЗУ, такой конфиг подойдет разве что если на сервере будет только постгрес крутится и не более.

Есть ли метод более разумно распределять ресурсы на сервере, мониторинги нагрузок от которых в последствии делать выводы и выделять ресурсы тем процессам которые более используемые ?

Comments

[Сергей Горностаев]

Во-первых, зачем вам две СУБД? Во-вторых, вы же читаете инструкции с ruhighload, а не ruhomeserver, а первое что делается при проектировании системы под мало-мальские нагрузки - это выносят СУБД на выделенный сервер. Наконец, нет ни одной инструкции в которой написан рабочий рецепт на все случаи жизни. Тюнинг производительности серверов - это сложный процесс, требующий глубоких знаний о работе железа, операционной системы и самого тюнингуемого сервера.

Answer 1

[Melkij]

Здесь по инструкции только под Postresql выделяется все 100% ОЗУ, такой конфиг подойдет разве что если на сервере будет только постгрес крутится и не более.

Именно так. Потому что вы читаете про highload. Под СУБД выделяется отдельный физический хост, плюс ещё парочка таких же под реплики. И уж явно не с механическим io. highload СУБД на HDD не бывает уже очень давно.

Проставьте оба конфига из расчёта 16гб памяти. Всё равно упрётесь в диски. Да и в CPU тоже упрётесь.
Если вы поставите shared_buffers в 25% ram, и innodb_buffer_pool_size в 80% - то у вас запустится банально только одна из баз. Ну или буфер перманентно в свопе будет, что в общем-то ещё хуже.
Кстати, надеюсь, вы по крайней мере проверяете смысл изменяемых настроек. Чтобы не было интересных сюрпризов вроде сделали всю базу на myisam, а крутите innodb_buffer_pool_size

Answer 2

[Doc44]

Профилировать сначала.

Answer 3

[Макс]

начните с понимания сколько данных и в каких БД у Вас. А то Вы начнете отдавать память Постгресу, храня основные данные в Мускуле.
у мускуля отдельная песня - движки. Вы пишите про innodb - а в нем ли храните?

Короче, первый шаг это сбор статистики.

Comments

[Chvalov]

Макс храню в InnoDB, чем собрать статистику ?

[Макс]

для mysql есть mysqltuner . -он Вам напишет сколько в каком движке. Заодно и немало интересного про использование кэша, read\write rate напишет, кое-какие советы по оптимизации конфига выдаст.
только не кидайтесь сразу в слепую выполнять эти рекомендации
в постгресе - тут придется погуглить. объем базы вроде как через \l можно посмотреть.

тема оптимизации БД очень нескучная тема.
я бы очень посоветовал после понимания объемов данных посмотреть насколько Вы много пишите или читаете ( ожидаемо на запись кэш особо не влияет), потом оценить cache hit rate - это насколько хорошо чтение попадает в кэш. Если данных мало, кэш большой а хитрэйт низкий - точно нелишним будет порыться в запросах, бываю ТАКИЕ перлы... у меня был пример, когда из таблицы в 17М строк каждые пару секунд читали все. Или другой пример, когда из таблички в 25-30М строк вытаскивали строчек 500-1000, но по полю без индекса.

[Chvalov]

Макс, mysqltuner не работает с MySQL 8 уже проверял (

[Макс]

тогда sql-ными скриптами.

[Chvalov]

Макс, существует ли мониторинг нагрузки, чтобы посмотреть по нагрузке на mysql и postgresql и распределить ресурсы по приоритету нагрузки ?

[Макс]

автоматически - мне таковые не известны. Мониторинг нагрузки да хоть тот-же Заббикс.

[Chvalov]

Макс, размер БД также стоит учесть при конфиге ?
К примеру сумарный размер БД на Postgresql - 12Гб, MySQL - 260Гб
Больше трафика приходит на Postresql, есть ли формула по которой можно все просчитать ?

[Макс]

Chvalov, формула врядли, скорее уж здравый смысл.
вот смотрите, в постгре данных всего 12Гигов. значит вот прямо сейчас отдавать под кэш в него более этих 12 гигов особого смысла нет, постгре они не помогут. т.е. уже память пополам делить смысла нет. Однако он, по Вашим словам, основной источник данных.
В мускуле данных изрядно. на секундочку в 20 раз больше. однако всего памяти 32, БД мускуля хоть упрись в память не загрузишь.
значит будем искать компромис.
Я бы начал с того, что отдал бы 12 гигов под shared_buffer Postgres-а, effective_cache_size - еще 4Г. из оставшихся 16 12г под innodb_buffer_pool, гиг по мелочи между остальными буферами мускуля.
дальше подождал бы день-два и посмотрел на кеш-хит-рэйт