Как ускорить выполнения SQL запросов?

Question

[Иван]

Доброго времени суток.
Для начала небольшая предистория:
Имеется задача сниферить траффик, и в реальном времени принимать решение о пакете (разрешен данный протокол в сети или нет). Решение принимается на основе записей в БД (MySQL), запись вида ip источника, ip назначения, протокол, порт назначения.
Используем библиотеку на C (nDPI), которая сниферит интерфейс, парсит пакет и говорит какой это протокол, на Python забираем данные с консоли, парсим регуляркой, и кладем на обработку в очередь.
Далее в другом процессе (multithreading) стучимся в базу и ищем запись соответсвующую атрибутам пакета, дальнейшие действия уже не так важны.
Самое узкое место в обработке это mysql, перерыл пол интернета, но так и не смог найти способа ускорить выполнение запроса, меньше 1 мс никак не выходит, это очень много для наших задачь, конечно python тоже узкое место, потом перепишем на C++.
Пробовал заюзать redis но больше времени уходит на накладные расходы, к слову БД довольно не большая не более 1000 записей.
Использовать локальный кэш тоже не очень хороший вариант, т.к. в БД могут писать их других мест, а данные нужны актуальные.

Прошу помощи как можно ускорить запрос, может другая БД или еще как то?

Answer 1

[Melkij]

Использовать локальный кэш тоже не очень хороший вариант, т.к. в БД могут писать их других мест, а данные нужны актуальные.

Использовать локальный кеш. Обновляются данные явно не каждые 1мс, за которые вы боретесь.
Обновление - вплоть до из триггера пинать UDF, которая сообщит вашему приложению, что данные в БД обновились.

Comments

[Иван]

Да конечно не каждую 1 мс, спасибо за посказку погуглю

Answer 2

[Александр N++]

Пример запроса хотелось'б видеть, может есть что оптимизировать?
Индексы созданы?

Прибывали обратится напрямую к хранилищу через интерфейс memcached?
mysqld_plugin_memcached + отключенные транзакции.

Comments

[Иван]

"SELECT rules.rule_id " \
"FROM data_flow_rules rules " \
"JOIN hosts src_host ON rules.src_host_id = src_host.id " \
"JOIN hosts trg_host ON rules.trg_host_id = trg_host.id " \
"JOIN protocols protocol ON rules.protocol_id = protocol.id " \
"WHERE src_host.ip_v4_address = '{src_ip_v4}' " \
"AND trg_host.ip_v4_address = '{dst_ip_v4}' " \
"AND protocol.name = '{protocol}' " \
"AND rules.target_port = {target_port} " \
"LIMIT 1"
В общем виде вот так. индексы есть, memcached не пробовал я так понимаю это аналог redis, так что на накладных расходах тоже можно потерять

Answer 3

[unwrecker]

Таблицы в памяти держать пробовали (тип memory)?

Но скорее всего придётся отказаться от mysql в пользу какого-нибудь быстрого и нереляционного key-value хранилища, например memcache.

Comments

[Иван]

Тут проблема что базу юзают еще другие приложения и веб морда, так что nosql не совсем подходит

[unwrecker]

Другие приложения переписать нельзя? Ну тогда только кешировать и остаётся.

[Иван]

Нет другие довольно проблематично

Answer 4

[mayorovp]

Правильно ли я понимаю, что база данных для вас - просто хранилище настроек?..

Загружайте все себе в память, и кладите в хеш-таблицу. Если особенно нужна скорость - можно использовать идеальный хеш (т.е. подобрать параметры хеш-функции таким образом, чтобы в таблице не было коллизий). После этого пропадет еще и необходимость очереди - ну или в очередь будут попадать уже отфильтрованные пакеты.

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

Comments

[Иван]

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

[mayorovp]

SpinenkoIA: ну, в триггере может быть вызов UDF - а UDF может делать что угодно.

[Иван]

А понял, да уже предлагали буду пробовать, спасибо

Answer 5

[Вадим Шандринов]

Можно попробовать так: стоит mysql-мастер с которым работают пользователи, и есть слейв, у которого таблички с типом MEMORY и ваш анализатор трафика юзает слейв.
Еще хинт - можно предгенерять табличку, там у вас джойнов много - напишите плоскую таблицу, в которую по триггеру на инсерте/апдейте будет складываться все данные. Что бы селект получался простым, типа
"SELECT rules.rule_id " \
"FROM data_flow_rules_DENORMALIZED rules " \
"WHERE rules.ip_v4_address = '{src_ip_v4}' " \
"AND rules.ip_v4_address = '{dst_ip_v4}' " \
"AND rules.name = '{protocol}' " \
"AND rules.target_port = {target_port} " \
"LIMIT 1"
+ индекс на все поля, у вас простое равенство - поможет. не забывайте о селективности индекса (первой в индексе должно идти самое селективное поле)
Кстати, если в индекс последним включить поле rules.rule_id то обращения к табличке вообще происходить не будет, только к индексу :)
Еще откройте для себя EXPLAIN https://dev.mysql.com/doc/refman/5.5/en/explain.html