Как снизить нагрузку на БД при большом количестве запросов?

Question

[waterlilies]

Небольшая ремарка: есть скрипт, который порождает определенное количество дочерних процессов (также скрипты) для фоновой работы с базой. В целом, операций на запись больше чем операций на чтение. На данный момент mysql полностью нагружает CPU, запросы падают в очередь. Помимо оптимизации запросов и сокращения их количества, что еще было бы уместно? Т.к. скрипты работают в фоне в независимости от того, запрашивает пользователь веб-страницы или нет.

Comments

[AlikDex]

а оперативной памяти мускулу то хватает? Проверьте конфиг.

Answer 1

[Андрей Суржиков]

Возможно поменять my.cnf на large.cnf если оперативная память позволит.
В 90% проектов это действие решает проблемы на долго..

Comments

[waterlilies]

Благодарю, обращу внимание.

Answer 2

[Антон Натаров]

Вы используете транзакциями в своих запросах ? Если у вас есть логи или ревизии сносите их к чертям. Скрипты или сам JS - это клиентский язык, он работает у клиента в браузере, а не на серваке. Если вся проблема в скриптах, то можно же запускать их по запросу, а не держать включенными постоянно.

Comments

[waterlilies]

скрипты в моем случае - php. они выполняются на том же сервере, где крутится база. проблема не в скриптах, потому как htop явно показывает, кто виновник нагрузки. Они должны работать в фоне, т.к. "по запросу" - это то, что запускает дочерние процессы. Операция немного дольше, чем "вытащил из базы - отдал пользователю".

[Антон Натаров]

waterlilies: тогда нужно смотреть в конфиг mysql. В целом если вам нужно оставить все как есть и не заморачиваться. Совет от Андрея Суржикова, решит многие проблемы. Но есть пару рекомендаций по поиску и отлову особо долгих запросов. Будете знать запрос, сможете его поправить.

1. деление на инстансы советую раскомментировать #innodb_buffer_pool_instances=4

2. Отшибайте зависшие запросы
wait_timeout = 15
interactive_timeout = 15
net_write_timeout = 15
net_read_timeout = 15
Возможно вы на этом этапе увидите ошибку

3. Мониторьте запросы самые долгие по выполнению, возможно в них спрятана цикличность которая и выжирает память

4. Так ловите медленные запросы
long_query_time = 2
log_slow_queries = /var/log/mysql/mysql-slow.log

Answer 3

[Иван]

К выше написанному - кластеризация.

Answer 4

[Everything_is_not_so_bad]

Не факт что проблема именно из-за СУБД. Ведь задача СУБД - обслуживать запросы приложений.
Вообще, нужно более детально произвести мониторинг процессов в СУБД, чтобы точно сказать где узкое место. Можно встроенными средствами, а также набором инструментов Percona.
Советую проверить также скриптовую программу на наличие неоптимизированных запросов вставок в БД.
Если идёт вставка большого количества записей, то можно оптимизировать одной вставкой, обернув в транзакцию.

Answer 5

[LAV45]

1) Переезжайте на PostgreSql
2) Используйте batch insert, у меня по 100 000 записей пишет и не запиниется
3) Удаляйте B-Tree индекс он при каждой вставке производит переиндексацию всего дерева, в PostgreSql с этим немного лудше там переиндексация выполняется отложенно.
У Mysql тормаза начинаются при 2.000.000 записей в таблице у PostgreSql после 5.000.000.
4) Если перейдете на PostgreSql можно поднять вторую базу в режме master-slave
В master писать, со slave читать. И синхронизировать из в асинхронном режиме. Таким образом slave будет немного отставать от master (примерно на 2 сек в зависимости от нагрузки) но slave будет просто летать.

#PostgreSQLRussia митап в компании Avito.ru
https://www.youtube.com/watch?v=2LDAcGZRAEM