Логирование трафика в БД — как нормализовать?

Question

[DrFreez]

Есть структура:
connect_ip (id, src_ip, dst_ip) ~6 млн. записей
connect_time (id, ip_id, time_start, time_end) ~30 млн. записей

В существующую структуру надо добавить protocol, src_port, dst_port

Как лучше поступить? Сделать ещё одну таблицу connect_port или запихать порты в connect_ip? Куда запихать протокол? MySQL

Comments

[Олег]

Если бы такая информация собиралась в отдельной таблице сразу (вместе с connect_ip и connect_time), то приблизительно сколько бы в ней было записей?

[DrFreez]

@0LLEGator 30млн, столько же сколько connect_time. Вместе с protocol, src_port, dst_port должно вырасти до ~60 млн, как минимум

[Роман Макаров]

По каким полям планируется поиск? В таблице connect_ip хранится информация об каждом отдельном ip и они не дублируются?

[DrFreez]

@vollossy по всем. В connect_ip хранятся связка src_ip + dst_ip, не дублируются

Answer 1

[Александр]

На сколько я знаю, по правилам нормализации, выносить в отдельную таблицу.

Answer 2

[Олег]

На мой взгляд лучше добавить поля к connect_time, правда придётся разрешить в них значения null.
Не стоит множить сущности без необходимости, а количество полей на скорость не влияют. Тем более что вся инфа в одной таблице и дополнительно джойнить, более того, скорее всего left join-ом, не придётся.

Answer 3

[Роман Макаров]

Я бы посоветовал, как и @0LLEGator добавить в существующую таблицу(просто потому, что это логичнее выглядит, не так страшен джоин, как его почему-то малюют :-) ). Только вот вместо NULL-значений бы установил дефолтными из области недопустимых(0, -100 или что-то в этом роде), т.к. NULL- значения не удастся выбрать при помощи индекса, что может замедлить некоторые запросы. Хотя, конечно, зависит от задачи.

Comments

[DrFreez]

@vollossy @0LLEGator у меня палка о двух концах. Если бы позволяло место я бы вообще денормализовал до первой формы, но по моим подсчётам все эти данные будут занимать 750+ Гб, что мне совершенно не подходит. Меня скорее даже больше интересует насколько сильно упадет скорость выборки, если я сделаю ещё одну сущность connect_port (id, src_port, dst_port) и на сколько сильно увеличится объем БД, если я добавлю это в уже существующую таблицу.

[Роман Макаров]

@DrFreez Насчет скорости -- зависит от того, по каким полям выборки чаще всего будут делаться, где-то быстрее, где-то медленнее, так с наскока сказать сложно. Насчет объема -- опять же смотря какие типы выберете для полей. Я так думаю -- для протокола, можно взять unsigned tinyint(1 байт), для портов unsigned smallint(2байта), итого +5 байт на запись. И текущая таблица у Вас вырастет на ~ 150MB. Хотя, может я и ошибаюсь. В отдельную таблицу выносить особого смысла не вижу, честно говоря.