Как защитить БД с критичными данными от произвола медленных запросов?

Question

[AntonioK]

Ситуация: есть боевой сервер, на нем — вебсервер и MySQL. С мусклем взаимодействуют, во-первых, PHP-скрипты выполняющиеся под апачем на этом сервере, а во-вторых — удаленные пользователи через TCP. Работают они с одной и той же базой. Однако, работоспособность связки «локальный апач плюс мускль» критична, а «удаленные юзеры плюс мускль» — нет.


«Удаленный» юзер запускает корявый запрос — например, REGEXP селект по неиндексированному столбцу на 20 млн строк. При этом в течение 3-5 минут тормозят все остальные запросы к этой базе, которые при обычных условиях летают. В итоге критичная веб-часть перестает отвечать с приемлемой скоростью. Как сделать так, чтобы удаленные юзеры могли посылать говнозапросы без вреда функционированию локальных подключений к БД? «Локалка» и «удаленщики» коннектятся к БД разными юзерами. Разнести базу на две — вариант не устраивает. Запас производительности на сервере есть (8 ядер, 24 гига памяти).

Comments

[AntonioK]

Проблема частично решена существенным увеличением max_connections (по умолчанию 151) и back-log (по умолчанию 50) в my.cnf — это позволило полнее задействовать запас мощности сервера, заметно увеличив начальный порог тормозов.

Answer 1

[mgyk]

Вынести чтение для удаленных юзверей на slave сервер (может размещаться на той же железяке, только использовать отдельный hdd), а запись на мастер. Разделить чтение и запись можно с помощью mysql proxy forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting.

Comments

[pwlnw]

в этом тупом скрипте кодировки не сохраняются. то есть set names не будет использован при втором подключении.

Answer 2

[GrossHo]

можно настроить репликацию (в задачнике не указывается, будут ли удаленные клиенты менять БД): master-у отдать работу с локальным сервером, а удаленным клиентам и процессу съема архива для бэкапа — со slave. Так например вопрос с бэкапом данных с таблицами сверх 1млн. записей лучше всего решать именно через обращение к серверу slave.

Comments

[GrossHo]

(первый комментарий лучше расскрывает вопрос)

Answer 3

[slang]

Написать демон, который с нужной периодичностью проверяет текущие мускульные квери по удалённому юзеру, и киляет их, если они затянулись дольше чем…

Comments

[homm]

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

[slang]

В условии сказано что «работоспособность связки удаленные юзеры плюс мускль — не критична»

Answer 4

[multik]

Разнести базу на две — вариант не устраивает.? а почему обязательно разнести на две — как уже говорили выделить отдельный слейв или построение системы мастера-слейвы не входит в ваши планы? ведь таким образом не только быстродействие повышается но ещё и надёжность. Да конечно от Вас потребуется умение всё это спроектировать и решить задачи синхронизации — но результат может превзойти ваши ожидания.

Answer 5

[Влад Животнев]

использовать Sphinx

Comments

[AntonioK]

при чём он тут?

[Влад Животнев]

медленные запросы будут быстрее отрабатывать.

Answer 6

[Владимир]

Думаю, из железа могут помочь высокооборотистые scsi/sas винты, а из софтовой оптимизации — sort_cache ,random_cache и т.п.

Answer 7

[Zorkus]

Мне, как Ынтерпрайзнику, вообще кажется чем-то диким давать юзерам, которые не является девелоперами / support инженерами, и не знают хорошо SQL и базы данных «вообще» прямой SQL-level доступ к большой нагруженной БД с критичными данными (кстати, что значит доступ по TCP? Вы имели в виду — возможность запускать вручную запросы из mysql клиентов, типа SQLYog?)

Вам так необходимо, чтобы Ваши юзеры могли запускать собственноручно криво написанные запросы? Они не могут обходиться набором заголовленных репорт-запросов, или чем-то таким? Они не могут посылать запросы, которые хотят исполнить, специальному грамотному чуваку, который будет их ревьювить и запускать? А как вы предотвращаете нечаянные удаления данных и прочее? Юзеры имеют read-only привилегии на все объекты БД?

Если же Вам прямо так критично давать юзерам такой доступ… Тогда, как тут сказали тут, делать slave-сервер с репликацией, либо настраивать user resource quotas по поводу использования CPU, IO-bandwidth, памяти каждым пользователем (под которыми ваши юзеры коннектятся у БД). Сам в mysql не силен, потому не знаю, как у него с квотами ресурсов.