카우치베이스 애널리틱스는 효율적인 병렬 처리를 활용하여 애드혹 쿼리를 지원하도록 설계되었습니다. 이는 한 쿼리의 결과로 인해 또 다른 쿼리에서 답을 찾아야 할 때 특히 유용합니다. (즉, 분석가에게는 항상 그렇습니다!) 다른 데이터 플랫폼과 마찬가지로, 이러한 보조 쿼리는 때때로 첫 번째 쿼리와 상당히 다른 실행 시간을 초래할 수 있습니다. 최근에 쿼리를 조금 변경한 후 성능이 눈에 띄게 저하된 고객과 함께 작업한 적이 있습니다. 문제를 분리하고 해결하기 위해 진행한 프로세스는 다음과 같습니다.
데이터
항상 그렇듯이 쿼리를 이해하기 위한 첫 번째 단계는 기초 데이터에 익숙해지는 것입니다. 이 경우 데이터는 JSON 문서로 구성되며, 각 문서는 서비스에 대한 클라이언트 요금에 대한 설명입니다. 각 세션에 대한 요금은 배열 안에 포함되어 있습니다:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
{ "customer": 1000002, "dtype": "session", "sessionCharges": [ { "qty": 1, "revCode": "B", "svc": 44284, "amt": 40, "svcDate": 1592889276 }, { "qty": 1, "revCode": "A", "svc": 28078, "amt": 24, "svcDate": 1592891076 }, { "qty": 1, "revCode": "B", "svc": 38968, "amt": 26, "svcDate": 1592893416 } ] } |
데이터 모델 설계에 대한 몇 가지 참고 사항입니다:
- 그들은 필드 이름을 설명할 수 있을 만큼 길지만 공간을 절약할 수 있을 만큼 짧게 지정하는 현명함을 발휘했습니다. 배열 내의 필드명은 '수량', 'revenueCode', '서비스', '금액', '서비스 날짜'로 쉽게 작성할 수 있었습니다. 그러나 1억 개의 인스턴스에 걸쳐 이렇게 하면 압축하기 전에 3.73GB의 공간이 추가로 필요했을 것입니다.
- 밀리초가 없는 에포크 날짜를 사용했습니다. 이러한 날짜가 ISO 8601 형식("2020-07- 24T18:17:49" 또는 밀리초 "2020-07-24T18:17:49.000")으로 저장되면 저장 공간이 두 배로 늘어납니다.
초기 쿼리
초기 쿼리는 각 수익 코드 범주 내에서 가장 높은 수익 고객/서비스 조합을 검색하도록 설계되었습니다:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select latestService, customer, revCode, service, round(totalDollar) dollars, totalQuant from ( select s.customer, c.svc as service, c.revCode, sum(c.amt) as totalDollar, sum(c.qty) as totalQuant, millis_to_str(max(c.svcDate)*1000, '1111-11-11') as latestService from sessions s unnest s.sessionCharges c group by s.customer, c.revCode, c.svc ) a where rank() over (partition by revCode order by totalDollar desc) = 1 order by revCode, totalDollar; |
쿼리 디자인에 대한 몇 가지 참고 사항입니다:
- 배열 콘텐츠는 "unnest" 동사로 액세스합니다. 이렇게 하면 값을 집계하여 평활화된 레코드로 반환할 수 있습니다.
- 최신 서비스 날짜는 millis_to_str() 함수를 통해 에포크에서 사람이 읽을 수 있는 날짜로 변환됩니다.
- 윈도우 함수 "rank() over partition"은 각 수익 코드 내에서 상위 값만 반환하는 효율적인 수단으로 외부 쿼리의 where 절에 사용됩니다.
- round() 함수는 장식용이지만, 일반적으로 페니가 중요하지 않은 보고서에서 사용됩니다.
슬로우다운
볼륨 할인 계산(서비스 누적 수량이 10개 이상일 때 20% 할인)이 추가되었을 때 문제가 발생했습니다. 이 새로운 쿼리는 실행 시간이 6배나 더 오래 걸렸습니다!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
select latestService, customer, revCode, service, round(totalDollar) dollars, round(adjDollar) dollarsAdj, totalQuant from ( select s.customer, c.svc as service, c.revCode, sum(c.amt) as totalDollar, case when sum(c.qty) < 10 then sum(c.amt) else sum(c.amt) * .8 end as adjDollar, sum(c.qty) as totalQuant, millis_to_str(max(c.svcDate)*1000, '1111-11-11') as latestService from sessions s unnest s.sessionCharges c group by s.customer, c.svc, c.revCode ) a where rank() over (partition by revCode order by totalDollar desc) = 1 order by revCode, totalDollar; |
이 원인을 어떻게 추적할 수 있나요?
설명하기
Analytics 쿼리 콘솔에서는 '계획' 버튼을 클릭하기만 하면 모든 쿼리의 실행 계획을 쉽게 검사할 수 있습니다. 아래 그림 (1)은 이 쿼리에 대한 쿼리 계획을 보여줍니다. 다이어그램의 규모가 작아도 걱정하지 마세요. 컨트롤을 사용하여 확장 및 축소할 수 있습니다. 각 개별 단계를 클릭하여 자세한 정보를 볼 수도 있습니다.
그림 1
원하는 경우 '계획 텍스트' 버튼을 클릭하여 계획을 설명하는 JSON 문서를 생성할 수도 있습니다:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
{ "operator": "distribute-result", "expressions": [ "$$157" ], "operatorId": "1.1", "physical-operator": "DISTRIBUTE_RESULT", "execution-mode": "PARTITIONED", "inputs": [ { "operator": "exchange", "operatorId": "1.2", "physical-operator": "ONE_TO_ONE_EXCHANGE", "execution-mode": "PARTITIONED", "inputs": [ { "operator": "project", "variables": [ "$$157" ], "operatorId": "1.3", "physical-operator": "STREAM_PROJECT", "execution-mode": "PARTITIONED", "inputs": [ { "operator": "assign", "variables": [ "$$157" ], ... etc. |
쿼리가 복잡해지면 중첩이 매우 깊어지고 읽기가 다소 어려워질 수 있습니다. 엔지니어들이 자주 사용하는 트릭은 쿼리 앞에 '설명 텍스트' 명령을 추가하는 것입니다:
|
1 2 3 |
explain text select ... etc.; |
여기에는 괄호 없이 계획에 대한 자세한 설명이 제공됩니다:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
distribute result [$$157] -- DISTRIBUTE_RESULT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| project ([$$157]) -- STREAM_PROJECT |PARTITIONED| assign [$$157] <- [{"latestService": millis-to-local(numeric-multiply($$168, 1000), "1111-11-11"), "customer": $$customer, "revCode": $$revCode, "service": $$svc, "dollars": round($$166), "dollarsAdj": round($$126), "totalQuant": $$167}] -- ASSIGN |PARTITIONED| exchange -- SORT_MERGE_EXCHANGE [$$revCode(ASC), $$166(ASC) ] |PARTITIONED| order (ASC, $$revCode) (ASC, $$166) -- STABLE_SORT [$$revCode(ASC), $$166(ASC)] |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| etc. |
이것이 단서를 찾는 데 사용할 것입니다.
목록 만들기
필드를 정의하는 CASE 문을 추가했을 때 속도 저하가 발생했다고 확신하므로 케이스 로직을 검색하는 것부터 시작해 보겠습니다. 33번째 줄에서 찾을 수 있습니다:
|
1 |
assign [$$125] <- [switch-case(TRUE, lt($$165, 10), $$117, $$124)] |
다음 줄의 논리를 따라가다 보면 실행 계획이 분할된 방식으로 변수를 계속 할당하고 교환하는 것을 볼 수 있습니다. 그러나 44번째 줄에는 다음과 같은 내용이 표시됩니다:
|
1 |
aggregate [$$117] <- [listify($$196)] |
쿼리 성능이나 메모리 소비가 우려되는 경우, "listify" 함수는 위험 신호가 될 수 있습니다. (한 엔지니어는 농담 삼아 "악의 함수"라고 부르기도 합니다.) 특히 배열 집계의 내부 변환에 사용되지만 실행 계획에서 최후의 수단으로 사용할 수도 있습니다. 이 예제에서는 $$117 변수가 이 리스트파이 프로세스의 결과물임을 알 수 있습니다. 위의 스위치 케이스에서 볼 수 있는 이 $$117은 쿼리의 sum(c.amt) 객체와 연관되어 있습니다. 따라서 합계 계산을 내부 스위치 케이스 밖으로 이동하여 LET의 일부로 만들어 보겠습니다:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
explain text select latestService, customer, revCode, service, round(totalDollar) dollars, round(adjDollar) dollarsAdj, totalQuant from ( select s.customer, c.svc as service, c.revCode, totalDollar, case when totalQuant < 10 then totalDollar else totalDollar * .8 end as adjDollar, totalQuant, millis_to_str(max(c.svcDate)*1000, '1111-11-11') as latestService from sessions s unnest s.sessionCharges c group by s.customer, c.svc, c.revCode let totalQuant = sum(c.qty), totalDollar = sum(c.amt) ) a where rank() over (partition by revCode order by totalDollar desc) = 1 order by revCode, totalDollar; |
이 작업을 완료하고 explain plan을 다시 살펴본 결과 길이가 절반으로 줄었고 실행 프로세스에 더 이상 listify 함수가 포함되지 않는 것을 확인할 수 있습니다. 그러면 쿼리를 실행하면 훨씬 더 나은 성능을 보여야 하며 실제로도 그렇습니다. 문제가 해결되었습니다!
마지막으로 한 가지 참고사항: 엔지니어들은 항상 쿼리 최적화 도구를 개선할 방법을 찾고 있습니다. 쿼리 중 하나를 최적화하는 과정에서 설명 텍스트에서 불량 'listify' 함수(이미 드물지만 점점 더 드물어지고 있음)를 발견한 경우, support.couchbase.com에서 지원 티켓을 열고 설명 계획을 업로드하여 엔지니어가 살펴볼 수 있도록 해 주세요. 실제로 위에서 언급한 고객과의 작업 결과로 이 특정 예제(집계 함수와 함께 사용되는 스위치 케이스)는 6.6 릴리스부터 최적화 프로그램에 의해 자동으로 재작성됩니다.
문서 및 리소스
Couchbase 문서 사이트에는 분석 언어용 N1QL 참조에 대한 링크가 포함되어 있습니다: https://docs.couchbase.com/server/current/analytics/introduction.html. 또한 여기에서 언어에 대한 튜토리얼과 Don Chamberlin(SQL 언어의 공동 발명가)의 책에 대한 링크를 찾을 수 있습니다: https://www.couchbase.com/sql-plus-plus-for-sql-users.
이 게시물을 꼼꼼하게 검토해 주신 Dmitry Lychagin과 Mike Carey에게 깊은 감사를 드립니다.
