[PostgreSQL, Программирование, SQL, Администрирование баз данных] Опыт хранения IP-адресов в PostgreSQL
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Описание проблемыНе раз наша команда в Каруне сталкивались с задачей, связанной с хранением и использованием IP-адресов в базе данных. Предположим, что есть типичная задача: необходимо парсить огромное количество диапазонов адресов (~300k) с известного ресурса, а далее определять страну по IP-адресу клиента. Кажется, ничего особенного. Это довольно просто решается любым ниже описанным способом при малых нагрузках. Но если у нас тысячи пользователей, или наш сервис является прокси перед всеми остальными? В этом случае не хочется быть бутылочным горлышком и приходится бороться за каждую долю секунды.Немного про адресациюСуществует 2 типа адресации в сетиINET (Классовая адресация IP-сетей) — архитектура сетевой адресации, которая использовалась в Интернете в период с 1981 по 1993 годы. Была вытеснена бесклассовой адресацией ввиду плохой гибкости и неэкономичного использования адресного пространства.CIDR (Classless Inter-Domain Routing, Бесклассовая адресация) — современный метод IP-адресации, при которой количество адресов в сети определяется маской подсети. Диапазон адресов записывается в виде address/y, где y — число бит маски подсети. Например, /28 означает, что 28 разряда IP-адреса отводятся под номер сети, а остальные 4 разряда полного адреса — под адреса хостов этой сети, адрес этой сети и широковещательный адрес сети.Например, запись 192.168.5.0/24 означает диапазон адресов от 192.168.5.1 до 192.168.5.254, а также 192.168.5.0 — адрес сети и 192.168.5.255 — широковещательный адрес сети.Типы inet и cidr по умолчаниюPostgreSQL предоставляет 2 типа по умолчанию для хранения IP-адресов и диапазонов: inet и cidr. Существует путаница между официальными названиями классовой и бесклассовой адресации и типами inet/cidr.Тип inet содержит адрес узла, а также может содержать подсеть. Вводимое значение должно иметь формат address/y. Если компонент y отсутствует, то маска сети считается равной 32 (для IPv4), так что это значение будет представлять один узел.Тип cidr содержит определение сети IPv4 (или IPv6). Вводимое значение также имеет формат address/y. Но если y компонент отсутствует, то сеть вычисляется по старой классовой схеме нумерации сетей (INET).Существенным отличием этих двух типов является в том, что inet принимает значения с ненулевыми битами справа от маски сети, а cidr нет. Если у вас сетевая маска /8, то тип cidr требует, чтобы все 24 крайних правых бита были равны нулю, inet не имеет этого требования. Например, 255.0.0.2/8 будет ошибочным для cidr т.к. справа от маски 255.0.0.0 имеются ненулевые значения (цифра 2 в последнем разряде адреса). 255.128.128.7/24, 255.255.255.255/31 — тоже ошибочны, а вот для типа inet являются валидными.А может уже померим что-нибудь?Выполним несколько предварительных настроек на локальной машине (MacBook 16, 2019 2,6 GHz 6-Core Intel Core i7). Создадим таблицу и добавим индекс для поля с IP-адресом:
CREATE INDEX ON ip_ranges USING GIST (ip_range inet_ops);
Попробуем выполнить большое количество запросов (1.000.000) определения вхождения в диапазон IP-адреса клиента с помощью цикла:
DO
$$
DECLARE
i RECORD;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM country_id FROM ip_ranges WHERE ip_range >>= ‘{random_ip}’;
end loop;
END;
$$
;
и посчитаем среднее время определения адреса.inetcidr749 мкс891 мксВолшебный ip4rСуществует способ и более быстродействующий — использование расширения ip4r, позволяющее значительно сократить время определения страны пользователя.Расширение гарантирует, что умеет в индексы лучше, чем встроенные типы PostgreSQL. И указывает на низкую производительность дефолтных типов даже в новых версиях СУБД. Кроме того, говорит о перегруженности дефолтных типов.После тех же самых проверок получили значительное снижение времени определения страны пользователя до 38 мкс.Серебряная пуля (или нет?)Если вдруг вы используете nginx, то для него есть geo модуль, позволяющий определять по IP-адресу нужный параметр. Создадим сервис через docker-compose.yml:
version: '3.7'
services:
web:
image: nginx:latest
volumes:
- ./nginx.conf:/etc/nginx/nginx.conf
- ./GeoIP.dat:/var/geo/GeoIP.dat
- ./geo.conf:/var/geo/geo.conf
ports:
- "8080:80"
environment:
- NGINX_PORT=80
Конфиг nginx:
http {
...
geo $geo {
default NONE;
include /var/geo/geo.conf;
}
geoip_country /var/geo/GeoIP.dat;
...
server {
...
location / {
...
add_header Geo-By-File $geo;
add_header Geo-By-Binary $geoip_country_code;
}
}
}
Мы можем получать гео клиента, через переменную $geo, предварительно сгенерировав файл geo.conf типа:
128.0.0.0/1 US;
...
Или скачать бинарный файл GeoIP.dat и использовать его без генерации, получая гео через переменные ($geoip_country_code).Измерить скорость определения в данном случае не получилось, но на боевом сервере проблем с быстродействием этого способа не возникало. Несмотря на простоту и удобство данного способа, не всегда возможно его использование (требование частого обновления и проблемы с этим, политики безопасности компании, и т.д.).ВыводыСтоит отметить, что измерения производились в клиенте PostgreSQL — и свели к минимуму оверхед языка программирования. Если вы боретесь за милисекунды, то стоит учесть и этот факт.Конечно, все представленные типы позволяют делать не только операцию поиска вхождения в диапазон (она была выбрана как наиболее популярная в нашей команде). А также операции пересечения диапазонов, сравнения и т.д. Буду благодарен, если кто-то напишет об опыте использования других операций и их поведении для разных типов.В случае если вам не нужно хорошее быстродействие, мало клиентов, вы не боретесь за доли секунды, то вам подойдут типы по умолчанию inet или cidr, различие между которыми находятся в рамках статистической погрешности. Расширение ip4r позволит сократить время в ~20 раз.
===========
Источник:
habr.com
===========
Похожие новости:
- [Программирование, Разработка игр, Игры и игровые приставки, Интервью] Подкаст «Хочу в геймдев» #4 — текстовая версия
- [Я пиарюсь] sqlite-gui: еще один редактор SQLite для Windows
- [Open source, Программирование микроконтроллеров, DIY или Сделай сам, Электроника для начинающих] я подарю тебе маленькую вселенную
- [Программирование, ERP-системы, Управление проектами, Карьера в IT-индустрии, Читальный зал] Надували, надуваем и будем надувать. Пузыри программистов
- [Ненормальное программирование, Программирование, ООП, Функциональное программирование, Kotlin] Мультивселенная и задачи о переправе
- [Программирование, C++, Qt] Про uuid-ы, первичные ключи и базы данных
- [Программирование, Совершенный код, Проектирование и рефакторинг, Управление разработкой, Исследования и прогнозы в IT] Про комментарии к коду (перевод)
- [Open source, Администрирование баз данных, Облачные сервисы] ClickHouse от Яндекса вошла в топ-50 самых популярных в мире СУБД
- [Администрирование баз данных] Ключевые аспекты при выборе базы данных для вашего приложения
- [Open source, PostgreSQL, NoSQL, Администрирование баз данных] А нужен ли Redis или хватит PostgreSQL (перевод)
Теги для поиска: #_postgresql, #_programmirovanie (Программирование), #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_postgresql, #_ip4r, #_inet, #_cidr, #_blog_kompanii_karuna (
Блог компании Karuna
), #_postgresql, #_programmirovanie (
Программирование
), #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 09:30
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
Описание проблемыНе раз наша команда в Каруне сталкивались с задачей, связанной с хранением и использованием IP-адресов в базе данных. Предположим, что есть типичная задача: необходимо парсить огромное количество диапазонов адресов (~300k) с известного ресурса, а далее определять страну по IP-адресу клиента. Кажется, ничего особенного. Это довольно просто решается любым ниже описанным способом при малых нагрузках. Но если у нас тысячи пользователей, или наш сервис является прокси перед всеми остальными? В этом случае не хочется быть бутылочным горлышком и приходится бороться за каждую долю секунды.Немного про адресациюСуществует 2 типа адресации в сетиINET (Классовая адресация IP-сетей) — архитектура сетевой адресации, которая использовалась в Интернете в период с 1981 по 1993 годы. Была вытеснена бесклассовой адресацией ввиду плохой гибкости и неэкономичного использования адресного пространства.CIDR (Classless Inter-Domain Routing, Бесклассовая адресация) — современный метод IP-адресации, при которой количество адресов в сети определяется маской подсети. Диапазон адресов записывается в виде address/y, где y — число бит маски подсети. Например, /28 означает, что 28 разряда IP-адреса отводятся под номер сети, а остальные 4 разряда полного адреса — под адреса хостов этой сети, адрес этой сети и широковещательный адрес сети.Например, запись 192.168.5.0/24 означает диапазон адресов от 192.168.5.1 до 192.168.5.254, а также 192.168.5.0 — адрес сети и 192.168.5.255 — широковещательный адрес сети.Типы inet и cidr по умолчаниюPostgreSQL предоставляет 2 типа по умолчанию для хранения IP-адресов и диапазонов: inet и cidr. Существует путаница между официальными названиями классовой и бесклассовой адресации и типами inet/cidr.Тип inet содержит адрес узла, а также может содержать подсеть. Вводимое значение должно иметь формат address/y. Если компонент y отсутствует, то маска сети считается равной 32 (для IPv4), так что это значение будет представлять один узел.Тип cidr содержит определение сети IPv4 (или IPv6). Вводимое значение также имеет формат address/y. Но если y компонент отсутствует, то сеть вычисляется по старой классовой схеме нумерации сетей (INET).Существенным отличием этих двух типов является в том, что inet принимает значения с ненулевыми битами справа от маски сети, а cidr нет. Если у вас сетевая маска /8, то тип cidr требует, чтобы все 24 крайних правых бита были равны нулю, inet не имеет этого требования. Например, 255.0.0.2/8 будет ошибочным для cidr т.к. справа от маски 255.0.0.0 имеются ненулевые значения (цифра 2 в последнем разряде адреса). 255.128.128.7/24, 255.255.255.255/31 — тоже ошибочны, а вот для типа inet являются валидными.А может уже померим что-нибудь?Выполним несколько предварительных настроек на локальной машине (MacBook 16, 2019 2,6 GHz 6-Core Intel Core i7). Создадим таблицу и добавим индекс для поля с IP-адресом: CREATE INDEX ON ip_ranges USING GIST (ip_range inet_ops);
DO
$$ DECLARE i RECORD; BEGIN FOR i IN 1..1000000 LOOP PERFORM country_id FROM ip_ranges WHERE ip_range >>= ‘{random_ip}’; end loop; END; $$ ; version: '3.7'
services: web: image: nginx:latest volumes: - ./nginx.conf:/etc/nginx/nginx.conf - ./GeoIP.dat:/var/geo/GeoIP.dat - ./geo.conf:/var/geo/geo.conf ports: - "8080:80" environment: - NGINX_PORT=80 http {
... geo $geo { default NONE; include /var/geo/geo.conf; } geoip_country /var/geo/GeoIP.dat; ... server { ... location / { ... add_header Geo-By-File $geo; add_header Geo-By-Binary $geoip_country_code; } } } 128.0.0.0/1 US;
... =========== Источник: habr.com =========== Похожие новости:
Блог компании Karuna ), #_postgresql, #_programmirovanie ( Программирование ), #_sql, #_administrirovanie_baz_dannyh ( Администрирование баз данных ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 09:30
Часовой пояс: UTC + 5