구글 빅쿼리 완벽 가이드 5

• 6장 생략

7장 성능 및 비용 최적화(1)

• 빅쿼리의 성능 최적화는 쿼리의 실행 시간이나 비용 또는 이 둘 모두를 줄이기 위해 진행한다.

성능 최적화의 기본 원칙

• 성능 최적화는 개발의 마지막 단계에서만 그리고 일반적인 쿼리가 너무 오래 걸릴 때만 수행하는게 좋다.
• 성능을 개선하기 위해 테이블 레이아웃과 쿼리를 난독화하는 것보다는 유연한 테이블 스키마와 우아하고 읽기 쉽고 유지 가능한 쿼리를 작성하는 것이 훨씬 낫다.
• 그러나 쿼리가 너무 자주 수행되어 작은 개선도 의미가 있다면 성능 향상을 해도 된다.

비용 통제하기

• 쿼리 비용은 가격 정책에 따라 달라진다.
• 빅쿼리의 가격정책은 두가지가 존재한다.
  1. 주문형(on-demand) 가격 정책 : 고용주가 직원의 쿼리로 처리된 데이터의 양에 따라 구글에게 비용을 지급한다.
  2. 정액제(flat-rate) 가격 정책 : 비즈니스에 일정 수의 슬록을 제공하며, 추가 비용을 들이지 않고도 원하는 수의 쿼리를 실행할 수 있다.
• 주문형 가격 정책을 사용하면 빅쿼리에서 쿼리를 실행하기 전에 비용 견적을 받을 수 있다.
• 비용을 통제하려고 할 때, 집중적으로 확인할 쿼리 목록을 만들면 도움이 된다. 그러려면 프로젝트와 관련된 INFORMATION_SCHEMA를 쿼리해 비용이 가장 많이 드는 쿼리를 찾으면 된다.

SELECT
  job_id
  , query
  , user_email
  , total_bytes_processed
  , total_slot_ms
FROM `some-project`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE EXTRACT(YEAR FROM creation_time) = 2019
ORDER BY total_bytes_processed DESC
LIMIT 5

• 이 쿼리는 total_bytes_processed 컬럼으로부터 프로젝트에서 실행한 쿼리 중 2019년에 가장 비용이 많이 들었던 5개의 쿼리를 출력한다.

측정과 문제 해결

• 쿼리의 성능을 최적화하려면, 다음 사항을 모두 확인해 초점을 맞출 사항을 파악하는 것이 중요하다.
  1. 스토리지에서 읽은 데이터 양과 데이터의 구성 방법
  2. 쿼리에 필요한 실행 단계의 수와 각 단계의 병렬 실행 가능 여부
  3. 각 단계에서 처리되는 데이터 양과 각 단계의 연산 집약도

쿼리 속도 높이기

• 쿼리 속도를 측정하고 잠재적인 문제를 식별하려면 다음 절차를 따라야 한다.
  1. 빅쿼리 워크로드 테스터로 전체 작업 부하 시간을 측정한다.
  2. 워크로드가 예상하지 못한 조작을 수행하지 않는지 확인하기 위해 로그를 검사한다.
  3. 워크로드는 구성하는 쿼리의 쿼리 계쇡 정보를 검토해 병목 현상이나 불필요한 단계를 식별한다.
• 문제가 있음을 확인하고 워크플로우에 명백한 오류가 없다고 판단했다면 이제는 워크로드에서 중요한 부분의 속도를 높이는 방법을 고려할 차례이다. 여기서는 쿼리 성능을 높일 수 있는 다음 방법을 알아본다.
  1. I/O 최소화
  2. 이전 쿼리 결과 캐싱
  3. 효율적으로 조인하기
  4. 워커의 과도한 작업 피하기
  5. 대략적인 집계 기능 사용

1. I/O 최소화

• 세 컬럼의 합계를 계산하는 쿼리는 컬럼 열의 합계를 계산하는 쿼리보다 느리지만, 그 성능의 차이는 뎃셈을 한 번 더 해서가 아니라 더 많은 데이터를 읽었기 때문이다.

SELECT의 대상을 명확히 하기

• 빅쿼리는 컬럼 기반 파일 형식을 사용하므로 SELECT 절로 읽는 컬럼의 수가 적을수록 읽어야 할 데이터의 양이 줄어든다.
• 특히 SELECT *는 테이블의 모든 행의 모든 컬럼을 읽으므로 상당히 느리고 요금이 비싸다.
• 서브쿼리에서 SELECT *를 사용해서 외부 쿼리의 필드 몇 개만 참조하는 경우는 예외다.
• 빅쿼리 옵티마이저는 충분히 똑똑해서 정확히 필요한 컬럼만 읽는다.

-- 두번째 쿼리는 첫번째 쿼리에 비해 3배 느리고 7배 비싸다.
SELECT
  bike_id
  , duration
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
ORDER BY duration DESC
LIMIT 1

SELECT
  *
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
ORDER BY duration DESC
LIMIT 1

• 클러스터링된 테이블에서 읽지 않는 한, LIMIT 절을 적용한다고 해서 데이터를 읽을 때 발생하는 비용이 절약되는 것은 아니다. 클러스터링된 테이블을 읽으면 읽어야 하는 바이트 수가 줄어들어 예측성은 떨어지지만 비용은 절감된다.
• 테이블의 거의 모든 컬럼이 필요하다면 SELECT * EXCEPT를 사용해서 필요하지 않는 칼럼은 읽지 않도록 한다.

데이터 읽는 양 줄이기

• 쿼리를 최적화할 때는 읽고 있는 데이터를 줄일 수 있는지가 중요하다.

-- 이름을 사용해 쿼리의 I/O 오버헤드를 줄인 쿼리
-- 위의 쿼리보다 아래의 쿼리가 30% 정도 속도가 향상되는데 
-- start_station_id를 읽을 필요가 없기 때문에 제외시켰기 때문이다.
SELECT
  MIN(start_station_name) AS start_station_name
  , MIN(end_station_name) AS end_station_name
  , APPROX_QUANTILES(duration, 10)[OFFSET(5)] AS typical_duration
  , COUNT(duration) AS num_trips
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE
  start_station_id != end_station_id
GROUP BY
  start_station_id, end_station_id
ORDER BY num_trips DESC
LIMIT 10
    
SELECT
  start_station_name
  , end_station_name
  , APPROX_QUANTILES(duration, 10)[OFFSET(5)] AS typical_duration
  , COUNT(duration) AS num_trips
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE
  start_station_name != end_station_name
GROUP BY
  start_station_name, end_station_name
ORDER BY num_trips DESC
LIMIT 10

비용이 높은 연산 줄이기

• 거리의 계산은 비용이 많이 드는 작업이므로 모든 대여소 간의 거리를 미리 계산해 두면 cycle, stations 테이블과 ctcle_hire 테이블을 조인할 필요가 없다.

WITH trip_distance AS (
  SELECT
    bike_id
    , ST_Distance(ST_GeogPoint(s.longitude, s.latitude),
                  ST_GeogPoint(e.longitude, e.latitude)) AS distance
  FROM
    `bigquery-public-data`.london_bicycles.cycle_hire,
    `bigquery-public-data`.london_bicycles.cycle_stations s,
    `bigquery-public-data`.london_bicycles.cycle_stations e
  WHERE
    start_station_id = s.id
    AND end_station_id = e.id
)

SELECT
  bike_id
  , SUM(distance)/1000 AS total_distance
FROM trip_distance
GROUP BY bike_id
ORDER BY total_distance DESC
LIMIT 5;

-- with 부분만 변경한다면 속도가 30% 향상됨
WITH stations AS (
  SELECT
    s.id AS start_id
    , e.id AS end_id
    , ST_Distance(ST_GeogPoint(s.longitude, s.latitude),
                  ST_GeogPoint(e.longitude, e.latitude)) AS distance
  FROM
    `bigquery-public-data`.london_bicycles.cycle_stations s,
    `bigquery-public-data`.london_bicycles.cycle_stations e
)
, trip_distance AS (
  SELECT
    bike_id
    , distance
  FROM
    `bigquery-public-data`.london_bicycles.cycle_hire,
    `bigquery-public-data`.london_bicycles.cycle_stations s,
    `bigquery-public-data`.london_bicycles.cycle_stations e
  WHERE
    start_station_id = s.id
    AND end_station_id = e.id
)

SELECT
  bike_id
  , SUM(distance)/1000 AS total_distance
FROM trip_distance
GROUP BY bike_id
ORDER BY total_distance DESC
LIMIT 5;

구체화된 뷰를 이용한 캐싱 연산

• 빅쿼리의 구체화된 뷰(materialized view)는 정기적으로 빅쿼리 테이블에 대한 쿼리의 결과를 캐시해서 성능과 효율성을 향상시키는 미리 연산된 뷰다.
• 구체화된 뷰는 테이블이나 조인 결과의 행과 열의 일부일 수도 있고 집계 함수를 사용한 요약 정보일 수도 있다.
• 따라서 쿼리를 재실행할 때 구체화된 뷰가 존재하면 쿼리는 모든 테이블을 다시 스캔할 필요 없이 구체화된 뷰로부터 빠르게 응답할 수 있다.
• 구체화된 뷰는 성능을 크게 향상시키며 스캔해야 할 데이터의 크기도 훨씬 작으므로 비용도 절감할 수 있다.

WITH oneway AS (
  SELECT EXTRACT(date FROM start_date) AS rental_date,
  duration, start_station_name, end_station_name
  FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
  WHERE start_station_name != end_station_name
)

SELECT
  rental_date, AVG(duration) AS avg_duration,
  start_station_name, end_station_name
FROM oneway
WHERE rental_date BETWEEN '2015-01-01' AND '2015-01-07'
GROUP BY rental_date, start_station_name, end_station_name

-- 이 쿼리는 사용자가 다른 날짜를 선택할 때마다 다른 rental_date 기간을 선택하기 위해 데이터를 처리한다. 
-- 이 경우 전체 테이블을 다시 스캔해서 집계값을 계산해야 하기 때문이다.
-- 하지만 다음과 같이 모든 날짜에 대한 구체화된 뷰를 만들어둘 수 있다.

CREATE OR REPLACE MATERIALIZED VIEW ch07eu.oneway_rentals -- 이부분 수정필요
AS

WITH oneway AS (
  SELECT EXTRACT(date FROM start_date) AS rental_date,
  duration, start_station_name, end_station_name
  FROM
  ch07eu.cycle_hire
  WHERE start_station_name != end_station_name
)

SELECT
  rental_date, AVG(duration) AS avg_duration,
  start_station_name, end_station_name
FROM oneway
GROUP BY rental_date, start_station_name, end_station_name

• 빅쿼리의 구체화된 뷰는 유지비용이 들지 않고 항상 최신의 상태이며 자적으로 튜닝한다.
• 기반 테이블에서 발생하는 테이터의 증분 변경은 자동으로 구체화된 뷰에 추가된다.
• 또한 데이터를 기반 테이블의 델타 변경과 합쳐서 새로운 데이터를 실시간으로 리턴한다.

뒤에서 이어짐