IP 주소-국가 매핑 쿼리 최적화 방법 (쿼리 시간 90% 절감 후기)

들어가는 글

각 사용자가 접속했을 때 수집한 IP 주소를 토대로 이를 국가나 도시 등 Geography 정보로 매핑하는 작업은 데이터 분석 환경에서 중요한 작업 중 하나입니다. 특히, 글로벌 서비스를 운영 중이라면 이는 필수적인 작업입니다. PostgreSQL에서는 IP 주소에 대한 다양한 연산자들을 제공하여 IP 주소 유형의 칼럼을 효율적으로 관리할 수 있도록 제공하고 있습니다. 하지만 이러한 연산자들은 종종 높은 연산량을 요구하기 때문에 대규모 데이터를 처리할 때 성능 문제가 발생하기 쉽습니다.

본 아티클에서는 PostgreSQL을 기준으로 IP 주소 연산을 최적화하는 방법에 대해 다룰 것입니다. 최적화를 통해 기존 실행 시간을 약 90% 감소시킨 경험을 자세히 들려드리도록 하겠습니다.

IP 주소의 구조

Source

IP 주소는 위 그림과 같이, Dot(.)을 기준으로 총 4개의 영역으로 구분됩니다. 각 영역은 0~255까지의 정수로 이루어져 있는데, 이는 8개의 Bit로 이루어진 1 Bytes를 10진법으로 변환한 것입니다. 따라서 이 표기방법으로 표현 가능한 IP 주소의 모든 경우의 수는 약 43억개입니다.

(2 ** 8) * (2 ** 8) * (2 ** 8) * (2 ** 8) # Result: 4,294,967,296

CIDR이란 Classless Inter-domain Routing을 준말로서, 각 IP 주소를 네트워크 별로 분류하기 위한 방법들 중 하나입니다. 즉, 각 IP 주소의 범위를 지정하여 네트워크 별로 분류하는 기준을 표기할 수 있는 것입니다. 아래 그림과 같이, Slash(/) 이후의 숫자인 Subnet Mask를 통해 몇 번째 Bit 단위까지 동일해야 하는지 기준을 표시하여 각 IP 주소가 해당 CIDR 네트워크에 포함되는지 확인할 수 있는 방법입니다.

Source

PostgreSQL의 IP 주소 연산자

PostgreSQL에는 이러한 IP 주소들을 비교할 수 있는 연산자들을 제공하고 있습니다.

Source

특히, <<= 연산자가 눈에 띄는데요. 이를 통해, 다음과 같이 특정 사용자의 접속 IP 주소가 어떤 CIDR 네트워크에 포함되는지 쿼리를 통해 바로 확인할 수 있을 것입니다.

10.10.1.44 <<= 10.10.1.32/27 -- TRUE
10.10.1.90 <<= 10.10.1.32/27 -- FALSE

따라서, 만약 우리가 각 CIDR 별로 Geography 정보가 매핑된 테이블을 미리 가지고 있다면 각 사용자의 접속 IP 주소를 통해 사용자의 Geography 정보를 쉽게 적재할 수 있겠죠.

필자 작성

작업 환경과 목표

준비된 테이블

src_cidrs_countries 테이블

• cidr, country 칼럼으로 이루어져 있음

src_sessions 테이블

• session_id, user_id, session_ip 칼럼으로 이루어져 있음

작업 목표

src_sessions 테이블의 session_ip (접속한 IP 주소) 칼럼을 src_cidrs_countries 테이블의 cidr 칼럼에 매핑하여 country 칼럼이 함께 생성된 fct_sessions 테이블을 생성하려고 합니다.

기존 접근 방법

기존 접근 방법 Flow

필자 작성

STEP 1) src_cidrs_countries 테이블에 Index를 생성합니다.

src_sessions 테이블과 JOIN시 cidr 칼럼을 빈번하게 스캔할 예정이므로, 이 칼럼을 Index로 생성합니다.

CREATE INDEX idx_cidr ON src_cidrs_countries (cidr)
;

STEP 2) src_cidrs_countries 테이블과 src_sessions 테이블을 JOIN한 fct_sessions 테이블을 생성합니다.

⭐ <<= 연산자를 사용합니다.

CREATE TABLE fct_sessions AS
    SELECT
        S.session_id,
        S.user_id,
	C.country
    FROM
	src_sessions S
    LEFT JOIN
        src_cidrs_countries C
	ON S.session_ip::INET <<= C.cidr
;

신규 접근 방법

신규 접근 방법 Flow

필자 작성

STEP 1) src_cidrs_countries 테이블을 가공하여 dim_ips_countries 테이블을 생성합니다.

cidr 칼럼 값의 IP 주소 범위를 start_ip 및 end_ip 칼럼으로 미리 Parse하는 작업입니다.

• start_ip 칼럼은 CIDR에서 Subnet Mask를 제외한 순수 IP 주소를 BIGINT 타입으로 Casting한 것입니다. 이를 통해, CIDR 네트워크에 포함된 가장 이른 순서의 IP 주소를 확인할 수 있습니다.
• end_ip 칼럼은 CIDR 네트워크의 브로드캐스트 주소를 계산하여, Subnet Mask를 제외한 순수 IP 주소를 BIGINT 타입으로 Casting한 것입니다. 이를 통해, CIDR 네트워크에 포함된 가장 늦은 순서의 IP 주소를 확인할 수 있습니다.

CREATE TABLE dim_ips_countries AS
    SELECT
	cidr,
        ('x' || 
	LPAD(TO_HEX((SPLIT_PART(HOST(cidr), '.', 1)::INTEGER)), 2, '0') ||
	LPAD(TO_HEX((SPLIT_PART(HOST(cidr), '.', 2)::INTEGER)), 2, '0') ||
	LPAD(TO_HEX((SPLIT_PART(HOST(cidr), '.', 3)::INTEGER)), 2, '0') ||
	LPAD(TO_HEX((SPLIT_PART(HOST(cidr), '.', 4)::INTEGER)), 2, '0')
        )::BIT(32)::BIGINT AS start_ip,
        ('x' ||
	LPAD(TO_HEX((SPLIT_PART(HOST(BROADCAST(cidr)), '.', 1)::INTEGER)), 2, '0') ||
        LPAD(TO_HEX((SPLIT_PART(HOST(BROADCAST(cidr)), '.', 2)::INTEGER)), 2, '0') ||
        LPAD(TO_HEX((SPLIT_PART(HOST(BROADCAST(cidr)), '.', 3)::INTEGER)), 2, '0') ||
        LPAD(TO_HEX((SPLIT_PART(HOST(BROADCAST(cidr)), '.', 4)::INTEGER)), 2, '0')
        )::BIT(32)::BIGINT AS end_ip,				
	country
    FROM
	src_cidrs_countries
;

STEP 2) dim_ips_countries 테이블에 Index를 생성합니다.

src_sessions 테이블과 JOIN시 start_ip, end_ip 칼럼을 빈번하게 스캔할 예정이므로, 이 두 개 칼럼을 Index로 생성합니다.

CREATE INDEX idx_ip_range ON dim_ips_countries (start_ip, end_ip)
;

STEP 3) dim_ips_countries 테이블과 src_sessions 테이블을 JOIN한 fct_sessions 테이블을 생성합니다.

⭐ BETWEEN 연산자를 사용합니다.

• session_ip 칼럼도 마찬가지로, IP 주소에서 Subnet Mask를 제외한 순서 IP 주소를 BIGINT 타입으로 Casting해야 합니다. 이를 통해 start_ip 및 end_ip와 올바르게 비교할 수 있을 것입니다.

CREATE TABLE fct_sessions AS
    SELECT
	S.session_id,
	S.user_id,
	C.country
    FROM (
	SELECT
  	    session_id,
	    user_id,
            ('x' ||
	    LPAD(TO_HEX((SPLIT_PART(HOST(session_ip::INET), '.', 1)::INTEGER)), 2, '0') ||
            LPAD(TO_HEX((SPLIT_PART(HOST(session_ip::INET), '.', 2)::INTEGER)), 2, '0') ||
            LPAD(TO_HEX((SPLIT_PART(HOST(session_ip::INET), '.', 3)::INTEGER)), 2, '0') ||
            LPAD(TO_HEX((SPLIT_PART(HOST(session_ip::INET), '.', 4)::INTEGER)), 2, '0')                
            )::BIT(32)::BIGINT AS session_ip
        FROM
            src_sessions
    ) S
    LEFT JOIN
        src_cidrs_countries C
	ON S.session_ip BETWEEN C.start_ip AND C.end_ip
;

결론

쿼리 실행 시간이 대폭 감소했습니다.

fct_sessions 테이블 생성 기준으로, 기존 접근 방법의 쿼리 실행 시간이 100시간이었다면, 신규 접근 방법의 경우 10시간 정도로 대폭 감소했습니다. (90% 감소)

JOIN은 Nested Loop입니다.

아래 그림과 같이, SQL의 JOIN은 중첩 반복문을 통해 조건을 만족하는 경우를 탐색하는 과정이라고 생각할 수 있습니다. 따라서 SQL의 쿼리 최적화를 위해 가장 면밀하게 검토해야 할 부분 중 하나입니다.

필자 작성

JOIN을 효율적으로 진행하기 위해 기억해야 할 점

TIP 1) JOIN의 조건 칼럼은 최대한 가벼운 타입을 가져야 합니다.

• 기존 접근 방법에서는 cidr 칼럼이 CIDR 타입이었으나, 신규 접근 방법에서는 이를 BIGINT로 Parse하여 타입을 경량화시켰습니다.

TIP 2) JOIN의 조건문은 최대한 가벼운 연산자로 구성되어야 합니다.

• 기존 접근 방법에서는 >>=라는 다소 무거운 연산자를 사용했으나, 신규 접근 방법에서는 이를 BETWEEN 연산자를 사용하여 부담을 줄였습니다.

REFERENCES

• Wikipedia (IPv4 address structure and writing systems)
• Wikipedia (Classless Inter-Domain Routing)
• PostgreSQL Docs — Network Address Functions and Operators

Follow Us

D’CENT Wallet
WEPIN Wallet
Career