Приветствую, друзья. Я столкнулся с проблемой оптимизации одного из своих проектов, поэтому решил попросить совета у вас. Сначала вкратце опишу ситуацию:
Есть сервис позволяющий людям подавать заявки. Каждая заявка рассматривается (одобряется) автоматически, операция одобрения требует тяжелых выборок из БД (с изолированными транзакциями). Сейчас операция одобрения заявки происходит сразу по нажатию кнопки подачи заявки, делая тяжелую выборку каждый раз когда кто-то подает заявку.
Операция подачи заявки доступна через API, поэтому сервер бывает очень нагружен, когда работает софт. Сразу замечу, что если заявка одобрена, то она помещается в таблицу активных заявок, а софту выдается ее ID в этой таблице, через который и происходит дальнейший доступ к заявке. Если заявка не одобрена, то выдается причина по которой это произошло.
Немного подумав, я нашел идеальное (как мне казалось) решение - сделать пул заявок (отдельную MEMORY таблицу) и обрабатывать ее раз в несколько секунд. Это позволит сократить количество тяжеловесных выборок из БД.
На проверку, решение оказалось не таким уж и идеальным. Возникает ряд проблем: после подачи заявки нужно будет вернуть идентификатор по которому клиент сможет узнать статус заявки. Очевидное решение - ID заявки в пуле (MEMORY таблице). Но:
- как при таком подходе сохранить этот ID и для таблицы успешных заявок? Если присваивать успешным заявкам их временный ID, то в таблице успешных заявок будут просто огромные гэпсы, а это явно не хорошо.
- как оповестить клиента о причинах отказа в одобрении заявки, если после ее рассмотрения запись из временной таблицы будет удалена? Создавать таблицу для не одобренных заявок (или хранить их в таблице одобренных)? Это как-то не красиво, учитывая что не одобренных в сотни раз больше.
Буду рад услышать любые ваши мысли по изложенной проблеме. Спасибо.
А нельзя этот пул перенести в обычную таблицу, но работать с ней как с пулом? Пришла заявка - просто заносится в таблицу заявок, через некоторое время обработчик пула начинает работать с последними необработанными заявками занося результат их распределения в эту же таблицу. Результаты и id никуда не пропадают, соответственно вы можете в любой момент их использовать для оповещений.
Структуры пула и таблицы успешных заявок отличаются. К тому же, вынесения пула в отдельную MEMORY таблицу, как мне казалось, очень не хилая оптимизация.
Да, но она добавляет дополнительную работу. Кроме того, таблица в памяти не переполнится? По поводу вынесения таблицы в MEMORY, вы ее вынесли в нее пул для чего? Ведь у вас основная работа как я понял выполняется в соседних таблицах?
Уверен, памяти хватит.
Да, основная работа выполняется в соседних таблицах. Но при такой архитектуре пул берет на себя все обращения, потому
логично вынести его в MEMORY.
Самое главное, что бы время внесения/извлечения записи из пула было меньше времени обработки записи. Если с обычной таблицей это время много меньше времени работы с соседними таблицами, то мне кажется, можно смело использовать такой способ.
Это, конечно, да. Приростом производительности от MEMORY таблицы можно и пожертвовать. Но вот как я уже сказал, структуры у пула и таблицы заявок разные.
Также, чтобы не блокировать все дело тяжелыми транзакциями, можно предусмотреть дополнительное состояние таска "оценивается". То есть исполнитель одним атомарным UPDATE SET status='оценивается' захватывает таск, и все, больше ничего не блокируется, дальше, спокойно себе проверяются все условия по таску (тут можно и кеши подрубить чтобы нагрузку снять еще), если результат оценки положительный, выставляем статус "на обработке" и отрабатываем, если нет, возвращаем таск в свободное плавание и захватываем/оцениваем следующий свободный.
@Tyranron тоже думал над дополнительными полями место блокировок, но они понадобятся не только в таблице исполнителей, но и в остальных (например, что бы исполнитель или заказчик не смог сменить цену во время обработки запроса).
По поводу NULL'ов, это как-то не эстетично. К тому же лишний байт на каждое поле.
@Cyapa вопрос невозможности смены цены во время обработки запроса, имхо, нужно решать на уровне приложения, а не БД. Поле со статусом то есть, что мешает атомарно проверять этот статус при попытке сменить цену? Подобным образом, уверен, можно решить и другие подобные ситуации, немного подумав. Тут уж извините, без подробностей не скажу точно.
На вкус и цвет... =) Если не эстетично - нужно переделывать схему грамотнее тогда (sql way): бьем таблицу на несколько со связями 1 к 1, соответственно те поля что есть не у всех записей - выносим в эти таблицы.
Разве количество записей имеет отношение к вопросу? Количество новых запросов постоянно растет. Сейчас может поступать до 100 заявок в секунду.
Тяжелая выборка требует транзакции максимального уровня изоляции (для того что бы двум заявкам не достался один и тот же исполнитель). При рассмотрении каждой заявки затрагиваются все основные таблицы сервиса: просматривается таблица предложений исполнителей, таблицы ставок, таблица пользователей. Все это фильтруется.
Соль еще в том, что нельзя, скажем выкинуть из выборки тех исполнителей, которые не подходят этой заявке по стоимости, потому как нужно знать есть ли вообще исполнители предоставляющие такие услуги. Если такие исполнители есть, то причина отказа будет "Недостаточная ставка", а если таких исполнителей нет, то причина отказа будет "В данный момент такие услуги не предоставляются". И таких моментов очень много, что знатно утяжеляет рассмотрение заявок.
MEMORY хранилище в MySQL не работает нормально и никогда не работало нормально. Никаких преимуществ перед правильно настроенным InnoDB оно не дает: во-первых, оно не быстрее, во-вторых, в нем возможны только hash индексы и, как следствие, невозможны range scans. Еще там, кажется, построчных блокировок нет - но тут надо уточнять, я точно не помню.
Что именно Вы пытаетесь оптимизировать? Какой именно запрос? Вы пытались настраивать параметры InnoDB? Какие именно параметры и как настраивали?
Простой
Простой
