빅쿼리 Query Plan을 이용한 쿼리 실행 분석

 

빅쿼리 Query Plan을 이용한 쿼리 실행 분석

최유석

개요

일반적으로 대부분의 데이터베이스의 SQL (MS-SQL, MySQL, Oracle, 등)에서 제공하는 Query Explain Plan(쿼리 실행 계획)기능은 사용자가 쿼리를 실행하기전에 쿼리를 분석할 수 있도록 쿼리가 실행되는 각각의 과정에 대해서 예상정보를 제공한다. 이 정보를 통해 해당 되는 쿼리에 대해서 성능에 대한 향상, 실행 오류 방지 등 쿼리를 실행하기 이전에 최적화 할 수 있게 도와준다. 빅쿼리에서도 이와 유사한 기능으로 Query Plan(쿼리 계획)을 제공한다. 이글에서는 빅쿼리의 Query Plan을 통해 쿼리 실행을 분석하는 방법에 대해서 알아보고자 한다.

 

빅쿼리와 다른 SQL엔진의 차이점

앞에서 언급한 것처럼 다른 SQL엔진은 쿼리를 실행하기 전에 해당되는 쿼리에 대한 예상 실행정보를 각각의 SQL에서 제공하는 Explain(각각 제공하는 이름은 다르지만 일반적인 의미)문을 적용해서 쿼리를 실행하고 분석해서 쿼리 튜닝을 하게 된다. 하지만 빅쿼리는 다른 SQL엔진과 다른 방식으로 실행이 완료된 쿼리에 대해서 쿼리 실행의 각 단계에 대한 정보를 메타데이터 형태로 제공한다. 또한 일반적인 Explain문이 빅쿼리에 따로 없기 때문에 직접 실행할 수 없으며, 실행이 완료된 쿼리에 대해서 Query Plan정보를 자동적으로 제공해준다.

 

빅쿼리의 부담 없는 쿼리 실행

빅쿼리는 구글 클라우드 플랫폼의 관리형 서비스로 구글에서 전체적인 시스템에 대한 관리를 하기 때문에 사용자 입장에서는 빅쿼리에 데이터를 저장하고 분석하는 등, 빅쿼리가 제공하는 서비스를 사용하기만 하면 된다. 따라서 일반적인 데이터베이스처럼 잘못된 쿼리로 인한 오류로 인해 데이터베이스에 문제를 일으키는 경우에 대해서 걱정 할 필요가 없다. 단지 쿼리가 실패할 뿐이며, 바로 다시 쿼리를 수정해서 실행할 수 있다. 또한 쿼리를 실행하기전에 쿼리에 대한 Validation (유효성 검사)기능을 제공하기 때문에 잘못된 쿼리의 경우 대부분 여기에서 걸러진다. 추가적으로Validation 을 통해 쿼리오류를 확인하지 못했다고 하더라도 쿼리가 잘못되거나 기타 다른 문제로 쿼리가 실패해서 오류가 발생되는 경우, 실패한 쿼리에 대해서는 비용이 부과되지 않기 때문에 부담이 적다.

 

빅쿼리에서 쿼리 실행 분석에 대한 접근

빅쿼리의 아키텍처를 보면 일반적인 데이터베이스에서 제공하는 Key, Index가 없으며, 기본적으로 쿼리요청의 대상이 되는 각각의 열에 대해서 Full Scan이다. 따라서 일반적으로 고려하는 Key, Index 기반의 쿼리 튜닝으로 방향이 아닌, 빅쿼리의 Query Plan에서 제공하는 메타데이터를 토대로 쿼리 실행단계에서 소요된 작업대기, 읽기, 쓰기, CPU연산 등에 대한 상대적인 시간 비율을 기준으로 실행 된 쿼리에 대한 분석 및 최적화 방향을 고려해야 한다.

 

*사실 빅쿼리의 쿼리 최적화는 쿼리실행이 열에 대한 풀스캔이고, 거기에 대한 비용이 발생하는 구조기 때문에 쿼리 비용자체를 감소시키기는 어렵다. 주요한 방향으로 생각해야 할 부분은 쿼리 실행 성능의 향상이다.

 

빅쿼리의 Query Plan 인터페이스

빅쿼리 웹 콘솔(Web UI)

빅쿼리 웹 콘솔(Web UI)에서는 쿼리를 실행하고 나서 다음과 같이 Explanation 버튼을 클릭하면 쿼리 실행에 대한 각각의 스테이지로 정보가 간략하게 나타나고 각각의 스테이지에 있는 세모모양의 아이콘을 클릭하면 각각의 스테이지 별로 쿼리 실행의 세부정보가 나타난다.

 

 

API

빅쿼리에서 API형태로 제공하는 Query Plan 정보는 실행이 완료된 쿼리 결과에 대해서 자동으로 해당 쿼리 Job의 리소스로 포함되어 7일간 보관된다. 이 기간 안에 Job.get() 메소드를 호출하게 되면 응답 결과에 포함 된 JSON 배열 형식의 queryPlan 속성에서 상세한 쿼리 실행 정보를 얻을 수 있다.

 

빅쿼리 Query plan의 메타데이터

STAGE 메타데이터

API속성	Web UI	설명
Id	Stage x	스테이지의 고유한 정수 ID (해당 Query Plan 범위 한정)
recordsRead	Input	스테이지에서 읽은 행의 수
recordsWritten	Output	스테이지가 작성한 행의 수

 

상대적인 시간비율에 따른 메타데이터

API속성	Web UI	설명
waitRatioAvg		작업 대기에 소요된 평균시간
waitRatioMax		작업 대기에 소요된 최대시간
readRatioAvg		입력데이터의 읽기에 소요된 평균시간
readRatioMax		입력데이터의 읽기에 소요된 최대시간
computeRatioAvg		CPU연산에 소요된 평균시간
computeRatioMax		CPU연산에 소요된 최대시간
writeRatioAvg		출력데이터의 쓰기에 소요된 평균시간
writeRatioMax		출력데이터의 쓰기에 소요된최대시간

*빅쿼리 웹 콘솔에서는 AVG, MAX는 따로 표시되지 않는다. 또한, 정확한 수치를 확인하기 위해서는 API를 이용한 접근이 필요하다.

 

간단한 쿼리를 이용한 기본적인 메타데이터 정보 확인하기

먼저 다음의 쿼리를 빅쿼리 웹 콘솔에서 실행하고, Query Plan에 대한 메타데이터 정보를 확인해보자.

SELECT COUNT(*)
FROM [publicdata:samples.shakespeare]
WHERE corpus = 'hamlet'

쿼리를 실행하고 Explanation 탭을 클릭하면 다음과 같이 Query Plan에 대한 정보를 확인 할 수 있다.

먼저 좌측의 Stage 뒤의 1, 2가 실행 된 해당 쿼리에 대한 고유한 ID이며,우측 Input, Output 아래의 수치가 해당 쿼리에 대한 각각의 스테이지에서 읽거나 기록한 행의 수이다. 중간의 Stage timing 아래에 나타나는 부분이 해당 쿼리가 실행되는 각각의 스테이지에서 [스케줄에 대한 작업대기, 읽기, CPU연산, 쓰기]에 의해서 소요된 시간 비율들을 나타내며, 위의 쿼리의 경우 실행 데이터의 크기가 작기 때문에 각각의 상대적인 시간비율들의 평균값과 최대값이 같게 나타난다.

 

이번에는 다음과 같이 시간비율의 편차를 보기 위해 쿼리의 대상이 되는 데이터의 크기가 크고 쿼리 연산이 복잡한 경우를 살펴보자.

SELECT language, SUM(views) as views

FROM [bigquery-samples:wikipedia_benchmark.Wiki1B]

WHERE regexp_match(title,"G.*o.*o.*g")

GROUP by language

ORDER by views DESC

위의 결과에서 보듯이 스테이지1에서 데이터의 읽기와 CPU연산에 소요된 시간 비율의 평균과 최대치의 차이가 발생하는 것을 확인할 수 있다.

 

*사용자가 실행하는 쿼리와 대상이 되는 데이터의 크기, 분포 등에 따라서 위와 같이 쿼리 실행 단계의 각각의 상대적인 시간비율들의 평균, 최대 값의 차이가 발생하게 된다.

 

STEP(스테이지의 세부 단계)의 메타데이터 정보

Query Plan에서 제공하는 각각의 스테이지 내부에서의 확인 가능한 세부적인 메타데이터 정보는 다음과 같다.

STEP(세부 속성)	설명
READ	입력 테이블 또는 중간 결과에서 하나 이상의 열을 읽는다.
WRITE	출력 테이블 또는 중간 결과에 대해 하나 이상의 열을 기록한다.
COMPUTE	대부분의 식에 대한 모든 계산 및 내장된 함수의 호출한다
FILTER	WHERE, OMIT IF, HAVING절을 구현하는 연산자이다.
SORT	정렬 연산을 나타내며 열의 키와 정렬방향을 포함한다.
AGGREGATE	집계연산(예:GROUP BY)을 나타내며, 여러 단계로 분할된다.
LIMIT	LIMIT절을 구현하는 연산자이다.
JOIN	JOIN연산을 나타내며, JOIN의 종류와 JOIN에 사용되는 열의 정보를 포함한다.
ANALYTIC_FUNCTION	분석 기능(CF. window functions)을 호출한다.
USER_DEFINED_FUNCTION	사용자 정의 함수를 호출한다

 

간단한 쿼리를 이용한 상세한 메타데이터 정보 확인하기

앞서 메타데이터 정보를 확인하기 위해 처음 실행 했던 쿼리를 이용해서 Query Plan의 각각의 세부적인 단계(STEP)에 대한 정보를 빅쿼리 웹 콘솔(Web UI)과 API를 통해서 각각 확인해보자.

 

빅쿼리 웹 콘솔(Web UI) 에서 STEP정보 확인하기

앞서 간단하게 메타데이터 정보를 확인하기 위해 처음 실행 했던 쿼리의 Explanation을 통해서 쿼리 실행의 각각의 단계(STEP)에 대한 정보를 확인해보자. 실행 된 쿼리의 Explanation에서 각각의 스테이지 좌측에 나타나는 삼각형모양의 아이콘을 클릭하면 각각의 스테이지에 대한 세부적인 메타데이터 정보를 확인 할 수 있다.

먼저 스테이지 1, 2에서 READ, AGGREGATE, WRITE스탭을 포함하고 있는 것을 볼 수 있다.

 

스테이지1에서는 READ스탭에서 [publicdata:samples.shakespeare] 테이블의 대상이 되는 "corpus" 열을 읽어서 값이 "hamlet"인 행을 찾는다. 그 다음 AGGREGATE스탭에서 앞서 읽은 행을 카운트하고 WRITE에서 __stage1_output 이라는 식별자에 기록을 한다.

 

스테이지2에서는 스테이지1에서 __stage1_output에 기록한 정보를 받아서 READ스탭에서 스테이지1의 작업의 카운트한 정보를 읽고 AGGREGATE스탭에서 카운트 된 정보에 대한 합산을 하고 WRITE스탭에서 합산한 결과를 출력한다.

 

API를 이용해서 STEP정보 확인하기

구글에서 제공하는 APIs Explorer을 사용해서 빅쿼리의 Query Plan정보를 간단하게 API를 호출하고 결과를 확인해보도록 하자.

 

Job ID 확인

앞서 언급한 것처럼, 빅쿼리 Query Plan정보는 실행된 쿼리에 대한 결과만 확인 할 수 있으며, Query Plan정보를 얻기 위해서는 실행된 쿼리에 대한 정보 중 하나인 Job ID가 필요하다. 빅쿼리 웹 콘솔(Web UI)에서 좌측상단 Query History클릭하고 앞서 실행한 쿼리정보에 대한Job ID의 값을 확인한다.(복사하거나 창을 열어둔다.)

*빅쿼리 웹 콘솔의 Query History에서 Job ID 는 Project ID:Job ID 형식으로 되어있으니, job.get() 메소드를 호출할 때는 각각 나눠서 입력해야 한다. 

Google APIs Explorer 페이지 접속

다음으로 APIs Explorer에서 빅쿼리 Job.get()메소드 테스트 페이지인 다음의 주소로 접속한다.

https://developers.google.com/apis-explorer/#p/bigquery/v2/bigquery.jobs.get

 

권한 부여

빅쿼리의 Job.get() 메소드를 사용하기 위해서는 빅쿼리에 대한 읽기 또는 전체 관리권한이 필요하다. 따라서 해당되는 권한을 임시로 부여하기 위해 우측의 off로 되어있는 버튼을 클릭하고 해당되는 권한범위를 체크하고(필요한 범위는 기본값으로 체크되어 있다.) Authorize를 클릭해서 실행 권한을 부여한다.

Job.get()메소드를 사용하기 위한 권한 범위URI는 위의 화면에서 체크된 항목과 같다.

*한 가지만 허용해도 Job.get() 메소드를 사용할 수 있다.

 

Job.get() 메소드 실행하기

API 사용에 대한 권한범위를 허용하고 버튼이 ON되었으면 해당 프로젝트 ID와 위에서 확인한 Job ID를 입력하고 Execute버튼을 클릭해서 실행한다.

 

Job.get() 메소드 실행결과 확인

아래에 실행된 결과를 보면 Job.get()메소드에 대해서 GET으로 성공적으로 요청이 이루어졌으며, 응답된 결과로 해당 Job에 대한 모든 정보를 나타내며(해당 속성만 필드를 지정해서 실행할 수 있다.), 쿼리 실행 정보를 얻기 위한 queryPlan속성은 statistics속성의 하위 속성인 query안에 포함되어 있다.

*속성 관계: statistics > query > queryPlan 

"queryPlan": [

  {

    "name": "Stage 1",

    "id": "1",

    "waitRatioAvg": 1,

    "waitRatioMax": 1,

    "readRatioAvg": 0.8650635652173914,

    "readRatioMax": 0.8650635652173914,

    "computeRatioAvg": 0.47558847826086953,

    "computeRatioMax": 0.47558847826086953,

    "writeRatioAvg": 0.19849230434782608,

    "writeRatioMax": 0.19849230434782608,

    "recordsRead": "164656",

    "recordsWritten": "1",

    "steps": [

      {

        "kind": "READ",

        "substeps": [

          "corpus",

          "FROM publicdata:samples.shakespeare",

          "WHERE EQUAL(corpus, 'hamlet')"

            ]

        },

        {

        "kind": "AGGREGATE",

        "substeps": [

          "COUNT_STAR() AS f0_"

            ]

        },

        {

        "kind": "WRITE",

        "substeps": [

          "f0_",

          "TO __stage1_output"

                 ]

            }

        ]

    },

    {

    "name": "Stage 2",

    "id": "2",

    "waitRatioAvg": 1,

    "waitRatioMax": 1,

    "readRatioAvg": 0,

    "readRatioMax": 0,

    "computeRatioAvg": 0.05080473913043479,

    "computeRatioMax": 0.05080473913043479,

    "writeRatioAvg": 0.12621304347826087,

    "writeRatioMax": 0.12621304347826087,

    "recordsRead": "1",

    "recordsWritten": "1",

    "steps": [

      {

        "kind": "READ",

        "substeps": [

          "f0_",

          "FROM __stage1_output AS publicdata:samples.shakespeare"

            ]

        },

        {

        "kind": "AGGREGATE",

        "substeps": [

          "SUM_OF_COUNTS(f0_) AS f0_"

            ]

        },

        {

        "kind": "WRITE",

        "substeps": [

          "f0_",

          "TO __output"

                ]

            }

        ]

    }

],

앞서 언급한대로 쿼리 실행의 상대적인 시간 비율들의 정확한 수치를 포함해서, 빅쿼리 웹 콘솔(Web UI)에서 확인한 정보와 동일한 쿼리 실행 정보를 확인 할 수 있다.

 

*Job.get()메소드의 Query Plan정보를 확인할 수 있는 queryPlan속성은 기록된 정보의 출력만 가능하다.

 

Query Plan결과 해석 및 해결 방안

Query Plan의 결과에 대해서 앞서 언급한대로 작업대기, 읽기, CPU 연산, 쓰기의 상대적인 시간 비율을 토대로 해석이 이루어져야 한다. 따라서 Query Plan 정보를 통해 쿼리의 문제를 분석하고 해결하는 방법을 알아보도록 하자.

 

평균 및 최대 시간 사이에 큰 차이가 발생하는 경우

평균, 최대시간의 차이가 발생하는 이유는 데이터가 고르지 못하게 비대칭 분포된 경우이다. 이러한 비대칭 분포를 가지는 데이터들은 불균형하게 분포 되어 있기 때문에 쿼리 실행 시 오버헤드를 발생시켜 쿼리가 실행되는 속도를 느리게 만들 수 있다.

 

평균, 최대 시간의 차이가 발생하는 일반적인 원인으로 쿼리에서 JOIN, GROUP BY, PARTITION 절에서 사용할 데이터가 NULL, empty, default 값을 포함하고 있는 경우이다. 따라서 이미 데이터가 고르게 분포되어 있지 않기 때문에 쿼리를 실행 하는 과정에서 편차가 발생한다.

 

트러블 슈팅방법으로 TOP COUNT문에 중간결과를 넣어서, 해당 쿼리 데이터에서 가장 일반적인 값을 분산된 데이터의 Key로써 확인한다.

* 빅쿼리는 Key가 없다

 

데이터의 비대칭을 완화하기 위해서는 필터링을 적용해서 NULL, empty, default값과 같은 쿼리에서 불 필요한 데이터를 감소시켜야 한다. 필터링을 하기위한 방법으로는 고르지 못한 값들에 대해서 쿼리를 실행하고, 나머지 값에 대해서 쿼리를 실행한다.

 

추가적인 방법으로 고르지 못한 데이터를 작은 크기로 세분화하는 방법을 고려한다. 이 경우 세분화 한 데이터 그룹들을 재결합할 때 집계 연산자 사용의 주의가 필요하다.

예) 비대칭분포를 가지는 불균형 데이터 세분화

세분화 전

SELECT ... GROUP BY a

세분화 후

SELECT ... FROM (SELECT ... (GROUP by a, b) GROUP BY a)

 

중간 스테이지에서 읽기 또는 쓰기에 대부분의 시간이 소요된 경우

이전 스테이지에서 데이터가 예상보다 많이 생성되는 경우로, 이전 스테이지에서 필터링과 JOIN연산을 사용해서 생성되는 데이터의 크기를 줄인다.

 

입력테이블의 읽기에 대부분의 시간이 소요된 경우

입력테이블 읽기가 비용이 큰 경우이다. 따라서 명확한 필터를 사용하여 불 필요한 데이터의 크기를 감소시키는 방법과 테이블에 대한 파티셔닝, 즉 작은 테이블들로 분할을 해서 대상이 되는 테이블 크기를 줄이는 방법을 통해 쿼리의 성능 향상을 기대해 볼 수 있다.

 

스케줄의 대기에 대부분의 시간이 소요된 경우

쿼리의 스케줄에 많은 작업이 포함되어 있는 경우이다. 만약 작업 시간 자체가 중요한 경우가 아니라면 기다린다. 즉시 작업이 완료 되어야하는 등의 작업 시간이 중요한 경우는 빅쿼리의 slots을 추가해서 사용하는 것을 고려해본다. 여기서 말하는 slots는 CPU연산이 이루어지는 서버계층을 의미한다.

 

출력결과를 쓰는데 대부분의 시간이 소요된 경우

처음 입력된 데이터를 읽은 결과 보다 쿼리가 실행되면서 더 많은 데이터를 내보내는 경우로 볼 수 있으며, 출력하기 전에 일부의 데이터의 필터링으로 불 필요한 데이터의 양을 줄여본다.

 

CPU 연산에 대부분의 시간이 소요된 경우

CPU의 연산에 시간이 많이 소요되는 쿼리의 경우, I/O보다 쿼리 실행과정에서 데이터의 변환 및 프로세싱에 더 많은 시간이 소요되는 경우로 볼 수 있다. 보통 사용자 정의 함수(User defined functions) 또는 JSON데이터, 정규표현식, 등을 포함하는 복잡한 쿼리에서 많이 발생하기 때문에, 필터링을 통해 복잡한 쿼리에서 사용되는 데이터의 크기를 줄여야 한다. 또한 자주 사용되는 쿼리라면, 일부 식에 대한 사전 연산등을 고려하는 것도 하나의 방법이다.

 

결론

앞서 빅쿼리의 Query Plan(쿼리 계획)을 통해 쿼리 실행을 분석하는 방법에 대해서 알아보았다. 빅쿼리가 일반적인 데이터베이스 시스템들과 다른 구조를 가지고 있고 실행된 쿼리에 대해서만 Query Plan정보를 제공하기 때문에 제한적인 부분은 있다. 실행 한 쿼리에 대해서 Query Plan의 상대적인 시간비율을 지표로 세부적인 스탭을 고려해서 데이터의 비대칭 분포를 완화시키거나, 불 필요한 데이터의 크기를 줄여서 성능을 향상 시키는 방법이 주요한 쿼리의 최적화 방향으로 보여진다.

 

참고자료

https://cloud.google.com/bigquery/query-plan-explanation

 

https://cloud.google.com/bigquery/docs/reference/v2/jobs/get

 

https://cloud.google.com/bigquery/docs/reference/v2/jobs