자습서: 탄력적 클러스터를 사용하여 실시간 분석 대시보드 디자인

이 자습서에서는 Azure Database for PostgreSQL 탄력적 클러스터에서 탄력적 클러스터를 사용하여 실시간 대시보드를 디자인하고 쿼리를 병렬화하는 방법을 알아봅니다.

• 필수 조건
• psql 유틸리티를 사용하여 스키마 만들기
• 노드 간 테이블 분할
• 샘플 데이터 생성
• 롤업 수행
• 원시 및 집계 데이터 쿼리
• 데이터 만료

필수 조건

다음 방법 중 하나로 탄력적 클러스터를 만듭니다.

• 포털을 사용하여 탄력적 클러스터 만들기
• Bicep을 사용하여 탄력적 클러스터 만들기
• ARM 템플릿을 사용하여 탄력적 클러스터 만들기

psql 유틸리티를 사용하여 스키마 만들기

psql을 사용하여 탄력적 클러스터에 연결되면 탄력적 클러스터를 구성할 수 있습니다. 이 자습서에서는 웹 분석의 트래픽 데이터를 수집한 다음, 데이터를 롤업하여 해당 데이터를 기반으로 실시간 대시보드를 제공하는 방법을 설명합니다.

원시 웹 트래픽 데이터를 모두 사용하는 테이블을 만들어 보겠습니다. psql 터미널에서 다음 명령을 실행합니다.

CREATE TABLE http_request (
  site_id INT,
  ingest_time TIMESTAMPTZ DEFAULT now(),

  url TEXT,
  request_country TEXT,
  ip_address TEXT,

  status_code INT,
  response_time_msec INT
);

또한 분 단위 집계를 보관할 테이블과 마지막 롤업의 위치를 유지하는 테이블을 만들겠습니다. psql에서도 다음 명령을 실행합니다.

CREATE TABLE http_request_1min (
  site_id INT,
  ingest_time TIMESTAMPTZ, -- which minute this row represents

  error_count INT,
  success_count INT,
  request_count INT,
  average_response_time_msec INT,
  CHECK (request_count = error_count + success_count),
  CHECK (ingest_time = date_trunc('minute', ingest_time))
);

CREATE INDEX http_request_1min_idx ON http_request_1min (site_id, ingest_time);

CREATE TABLE latest_rollup (
  minute timestamptz PRIMARY KEY,

  CHECK (minute = date_trunc('minute', minute))
);

이제 이 psql 명령을 사용하면 테이블 목록에서 새로 만든 테이블을 볼 수 있습니다.

\dt

노드 간 테이블 분할

탄력적 클러스터 배포는 사용자가 지정한 열의 값에 따라 다른 노드에 테이블 행을 저장합니다. 이 "배포 열"은 노드 간에 데이터를 분할하는 방법을 표시합니다.

배포 열(분할 키)을 site_id로 설정해 보겠습니다. psql에서 다음 함수를 실행합니다.

SELECT create_distributed_table('http_request',      'site_id');
SELECT create_distributed_table('http_request_1min', 'site_id');

비고

Azure Database for PostgreSQL의 탄력적 클러스터 성능 기능을 활용하려면 테이블을 분배하거나 스키마 기반 샤딩을 사용해야 합니다. 테이블 또는 스키마를 배포할 때까지 클러스터 노드는 해당 데이터와 관련된 분산 쿼리를 실행하지 않습니다.

샘플 데이터 생성

이제 클러스터가 데이터를 수집할 준비가 완료되었습니다. psql 연결에서 로컬로 다음 명령을 실행하여 지속적으로 데이터를 삽입할 수 있습니다.

DO $$
  BEGIN LOOP
    INSERT INTO http_request (
      site_id, ingest_time, url, request_country,
      ip_address, status_code, response_time_msec
    ) VALUES (
      trunc(random()*32), clock_timestamp(),
      concat('http://example.com/', md5(random()::text)),
      ('{China,India,USA,Indonesia}'::text[])[ceil(random()*4)],
      concat(
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2)
      )::inet,
      ('{200,404}'::int[])[ceil(random()*2)],
      5+trunc(random()*150)
    );
    COMMIT;
    PERFORM pg_sleep(random() * 0.25);
  END LOOP;
END $$;

이 쿼리는 초당 약 8개의 행을 삽입합니다. 행은 배포 열 site_id에 따라 다른 작업자 노드에 저장됩니다.

비고

데이터 생성 쿼리가 계속 실행되도록 그대로 두고, 이 자습서의 나머지 명령에 대한 두 번째 psql 연결을 엽니다.

쿼리

Azure Database for PostgreSQL 탄력적 클러스터를 사용하면 여러 노드가 빠른 속도로 쿼리를 병렬로 처리할 수 있습니다. 예를 들어 데이터베이스는 작업자 노드에서 SUM 및 COUNT 같은 집계를 계산하고, 그 결과를 최종 대답에 결합합니다.

다음은 몇 가지 통계와 함께 분당 웹 요청 수를 집계하는 쿼리입니다. psql에서 이 쿼리를 실행하고 결과를 관찰해보세요.

SELECT
  site_id,
  date_trunc('minute', ingest_time) AS minute,
  COUNT(1) AS request_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) AS success_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) AS error_count,
  SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
FROM http_request
WHERE date_trunc('minute', ingest_time) > now() - '5 minutes'::interval
GROUP BY site_id, minute
ORDER BY minute ASC;

데이터 롤업

위의 쿼리는 초기 단계에서는 제대로 작동하지만, 데이터가 커지면 성능이 저하됩니다. 분산 처리를 사용하더라도 이 데이터를 반복적으로 다시 계산하는 것보다는 미리 컴퓨팅하는 것이 더 빠릅니다.

원시 데이터를 주기적으로 집계 테이블에 롤링하여 대시보드 속도를 높게 유지할 수 있습니다. 집계 기간으로 실험할 수 있습니다. 여기에서는 분당 집계 테이블을 사용했지만 데이터를 5분, 15분, 60분으로 분할할 수도 있습니다.

이러한 롤업을 보다 쉽게 실행하기 위해 plpgsql 함수에 삽입할 것입니다. psql에서 다음 명령을 실행하여 rollup_http_request 함수를 만듭니다.

-- initialize to a time long ago
INSERT INTO latest_rollup VALUES ('10-10-1901');

-- function to do the rollup
CREATE OR REPLACE FUNCTION rollup_http_request() RETURNS void AS $$
DECLARE
  curr_rollup_time timestamptz := date_trunc('minute', now());
  last_rollup_time timestamptz := minute from latest_rollup;
BEGIN
  INSERT INTO http_request_1min (
    site_id, ingest_time, request_count,
    success_count, error_count, average_response_time_msec
  ) SELECT
    site_id,
    date_trunc('minute', ingest_time),
    COUNT(1) AS request_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) AS success_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) AS error_count,
    SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
  FROM http_request
  -- roll up only data new since last_rollup_time
  WHERE date_trunc('minute', ingest_time) <@
          tstzrange(last_rollup_time, curr_rollup_time, '(]')
  GROUP BY 1, 2;

  -- update the value in latest_rollup so that next time we run the
  -- rollup it will operate on data newer than curr_rollup_time
  UPDATE latest_rollup SET minute = curr_rollup_time;
END;
$$ LANGUAGE plpgsql;

함수가 준비되면 다음과 같이 함수를 실행하여 데이터를 롤업합니다.

SELECT rollup_http_request();

데이터가 미리 집계된 형태로 되면 롤업 테이블을 쿼리하여 이전과 동일한 보고서를 얻을 수 있습니다. 다음 쿼리를 실행합니다.

SELECT site_id, ingest_time AS minute, request_count,
       success_count, error_count, average_response_time_msec
FROM http_request_1min
WHERE ingest_time > date_trunc('minute', now()) - '5 minutes'::interval;

오래된 데이터 만료

롤업으로 쿼리 속도가 좀 더 빨라지겠지만, 불필요한 스토리지 비용이 발생하지 않도록 오래된 데이터를 만료해야 합니다. 각 세분성에서 데이터를 유지할 기간을 결정하고, 표준 쿼리를 사용하여 만료된 데이터를 삭제합니다. 다음 예제에서는 원시 데이터를 하루 동안 유지하고, 분 단위 집계를 한 달 동안 유지하기로 결정했습니다.

DELETE FROM http_request WHERE ingest_time < now() - interval '1 day';
DELETE FROM http_request_1min WHERE ingest_time < now() - interval '1 month';

프로덕션 환경에서는 이러한 쿼리를 함수에 래핑하고 cron 작업에서 1분마다 호출할 수 있습니다.

다음 단계

이 자습서에서는 탄력적 클러스터를 만드는 방법을 알아보았습니다. psql로 연결하고, 스키마를 생성한 후 데이터를 분산했습니다. 데이터를 원시 형식으로 쿼리하고, 해당 데이터를 주기적으로 집계하고, 집계된 테이블을 쿼리하고, 오래된 데이터를 만료하는 방법을 알아보았습니다.

탄력적 클러스터에 대한 자세한 정보