이 세상의 모든 것과 마찬가지로 마이크로서비스 아키텍처에는 장단점이 있습니다. 일부 프로세스는 더 쉬워지고 다른 프로세스는 더 어려워집니다. 그리고 변화의 속도와 더 나은 확장성을 위해서는 희생이 필요합니다. 그 중 하나는 분석의 복잡성이 증가하고 있다는 것입니다. 모놀리스에서 모든 운영 분석을 분석 복제본에 대한 SQL 쿼리로 축소할 수 있다면 멀티서비스 아키텍처에서는 각 서비스가 자체 데이터베이스를 가지며 하나의 쿼리를 수행할 수 없는 것처럼 보입니다(또는 수행할 수 있을까요?). 우리 회사에서 운영 분석 문제를 어떻게 해결했는지, 그리고 이 솔루션을 어떻게 활용하는지에 관심이 있는 분들을 환영합니다.

제 이름은 Pavel Sivash입니다. 저는 DomClick에서 분석 데이터 웨어하우스 유지 관리를 담당하는 팀에서 일하고 있습니다. 일반적으로 우리의 활동은 데이터 엔지니어링으로 분류될 수 있지만 실제로는 업무 범위가 훨씬 더 넓습니다. 데이터 엔지니어링, 데이터 분석을 위한 도구 지원 및 적용과 자체 도구 개발을 위한 ETL/ELT 표준이 있습니다. 특히, 운영 보고를 위해 우리는 하나의 단일체가 있는 것처럼 "가장" 분석가에게 필요한 모든 데이터가 포함된 하나의 데이터베이스를 제공하기로 결정했습니다.

일반적으로 우리는 다양한 옵션을 고려했습니다. 본격적인 저장소를 구축하는 것이 가능했습니다. 우리도 시도했지만 솔직히 말해서 저장소를 구축하고 변경하는 다소 느린 프로세스와 로직의 상당히 빈번한 변경을 결합할 수 없었습니다. , 방법을 댓글에 적어주세요). 분석가들에게 “얘들아, 파이썬 좀 배워서 분석용 레플리카로 가거라”라고 말할 수는 있었지만, 이는 채용을 위한 추가 요구 사항이고, 가능하면 피해야 할 것 같았다. 우리는 FDW(Foreign Data Wrapper) 기술을 사용하기로 결정했습니다. 기본적으로 이는 SQL 표준에 속하지만 훨씬 더 편리한 인터페이스를 갖춘 표준 dblink입니다. 이를 바탕으로 우리는 솔루션을 만들었고, 결국 성공을 거두었고, 이를 정착시켰습니다. 세부 사항은 데이터베이스 스키마 동기화부터 개인 데이터의 액세스 제어 및 개인화 해제에 이르기까지 많은 것에 대해 이야기하고 싶기 때문에 별도의 기사의 주제이거나 하나 이상일 수도 있습니다. 또한 이 솔루션은 실제 분석 데이터베이스 및 리포지토리를 대체하는 것이 아니며 특정 문제만 해결한다는 점도 유보해야 합니다.

최상위 수준에서는 다음과 같습니다.

사용자가 작업 데이터를 저장할 수 있는 PostgreSQL 데이터베이스가 있으며, 가장 중요한 것은 모든 서비스의 분석 복제본이 FDW를 통해 이 데이터베이스에 연결된다는 것입니다. 이를 통해 여러 데이터베이스에 대한 쿼리를 작성할 수 있으며 그것이 무엇인지는 중요하지 않습니다: PostgreSQL, MySQL, MongoDB 또는 다른 것(파일, API, 갑자기 적합한 래퍼가 없으면 직접 작성할 수 있습니다). 글쎄, 모든 것이 좋아 보인다! 우리 헤어지는 건가요?

모든 것이 너무 빠르고 간단하게 끝났다면 아마도 기사가 없을 것입니다.

Postgres가 원격 서버에 대한 요청을 처리하는 방법을 명확히 아는 것이 중요합니다. 이는 논리적인 것처럼 보이지만 종종 사람들은 이에 주의를 기울이지 않습니다. Postgres는 요청을 원격 서버에서 독립적으로 실행되는 부분으로 나누고 이 데이터를 수집하고 자체적으로 최종 계산을 수행하므로 쿼리 실행 속도는 다음 사항에 따라 크게 달라집니다. 그것이 어떻게 쓰여졌는가. 또한 주목해야 할 점은 데이터가 원격 서버에서 도착하면 더 이상 인덱스가 없으며 스케줄러를 도울 수 있는 것이 없기 때문에 우리 자신만이 그를 돕고 조언할 수 있다는 것입니다. 이것이 바로 제가 더 자세히 이야기하고 싶은 것입니다.

간단한 쿼리와 그에 따른 계획

Postgres가 원격 서버에서 6만 행 테이블을 쿼리하는 방법을 보여주기 위해 간단한 계획을 살펴보겠습니다.

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

VERBOSE 문을 사용하면 원격 서버로 전송될 쿼리와 추가 처리를 위해 수신할 결과(RemoteSQL 라인)를 볼 수 있습니다.

좀 더 나아가 요청에 여러 필터를 추가해 보겠습니다. 부울 필드, 발생별로 하나씩 따라서 오른쪽 하단에 간격으로 그리고 하나씩 JSONB.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

쿼리 작성 시 주의해야 할 점이 바로 여기에 있습니다. 필터는 원격 서버로 전송되지 않았습니다. 즉, 이를 실행하기 위해 Postgres는 6만 개의 행을 모두 가져와서 로컬로 필터링하고(필터 행) 집계를 수행합니다. 성공의 열쇠는 필터가 원격 시스템으로 전송되도록 쿼리를 작성하고 필요한 행만 수신하여 집계하는 것입니다.

그거 좀 말도 안 되는 소리야

부울 필드를 사용하면 모든 것이 간단해집니다. 원래 요청에서는 문제가 운영자로 인해 발생했습니다. is. 으로 교체하면 =, 그러면 다음과 같은 결과를 얻습니다.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

보시다시피 필터가 원격 서버로 날아가서 실행 시간이 27초에서 19초로 단축되었습니다.

주목할 점은 운영자가 is 운영자랑 다름 = Null 값으로 작업할 수 있기 때문입니다. 그것은 다음을 의미합니다 사실이 아니다 필터에 False 및 Null 값을 남겨 두는 반면 != 참 False 값만 남깁니다. 따라서 오퍼레이터를 교체할 때 하지 않습니다 OR 연산자가 있는 두 가지 조건이 필터에 전달되어야 합니다. 예를 들어, WHERE (col != True) OR (col은 null입니다).

우리는 불리언을 다루었습니다. 계속 진행하겠습니다. 지금은 다른 변경 사항의 효과를 독립적으로 고려하기 위해 부울 필터를 원래 형식으로 되돌려 보겠습니다.

타임스탬프tz? hz

일반적으로 원격 서버와 관련된 요청을 올바르게 작성하는 방법을 실험한 다음 왜 이런 일이 발생하는지에 대한 설명을 찾아야 하는 경우가 많습니다. 이에 대한 정보는 인터넷에서 거의 찾을 수 없습니다. 따라서 실험에서 고정 날짜 필터가 쾅 소리와 함께 원격 서버로 날아가는 것을 발견했지만 now() 또는 CURRENT_DATE와 같이 날짜를 동적으로 설정하려는 경우에는 이런 일이 발생하지 않습니다. 이 예에서는 Create_at 열에 과거 정확히 1개월(BETWEEN CURRENT_DATE - INTERVAL '7개월' AND CURRENT_DATE - INTERVAL '6개월')의 데이터가 포함되도록 필터를 추가했습니다. 이 경우 우리는 무엇을 했나요?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

서브 쿼리에 날짜를 미리 계산해서 미리 만들어진 변수를 필터에 전달하라고 플래너에게 지시했습니다. 그리고 이 힌트는 우리에게 훌륭한 결과를 주었고 요청은 거의 6배 빨라졌습니다!

여기서도 주의하는 것이 중요합니다. 하위 쿼리의 데이터 유형은 필터링하는 필드의 데이터 유형과 동일해야 합니다. 그렇지 않으면 플래너는 유형이 다르기 때문에 먼저 모든 항목을 가져와야 한다고 결정합니다. 데이터를 수집하고 로컬로 필터링합니다.

날짜 필터를 원래 값으로 되돌려 보겠습니다.

프레디 vs. Jsonb

일반적으로 부울 필드와 날짜는 이미 쿼리 속도를 충분히 높였지만 데이터 유형이 하나 더 남아 있었습니다. 솔직히 말해서 필터링과의 싸움은 아직 끝나지 않았지만 여기서도 성공했습니다. 그래서 이것이 우리가 필터를 통과하는 방법입니다. JSONB 필드를 원격 서버에 보냅니다.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

필터링 연산자 대신 하나의 연산자가 있어야 합니다. JSONB 다른. 원래의 7초가 아닌 29초입니다. 지금까지 이것은 필터를 통해 필터를 전송하는 데 성공한 유일한 옵션입니다. JSONB 그러나 여기서는 한 가지 제한 사항을 고려하는 것이 중요합니다. 즉, 데이터베이스 버전 9.6을 사용하고 있지만 12월 말까지 마지막 테스트를 완료하고 버전 10로 이동할 계획입니다. 업데이트한 후에는 이것이 어떤 영향을 미쳤는지 쓸 것입니다. json_path, 새로운 CTE 동작, 푸시다운(버전 XNUMX부터 존재함) 등 많은 희망이 있는 변경 사항이 있기 때문입니다. 빨리 해보고 싶네요.

그를 마무리

각 변경 사항이 요청 속도에 개별적으로 어떤 영향을 미치는지 테스트했습니다. 이제 세 개의 필터가 모두 올바르게 작성되면 어떤 일이 발생하는지 살펴보겠습니다.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

예, 요청이 더 복잡해 보입니다. 이는 강제 요금이지만 실행 속도는 2초로 10배 이상 빠릅니다! 그리고 우리는 상대적으로 작은 데이터 세트에 대한 간단한 쿼리에 대해 이야기하고 있습니다. 실제 요청에 따라 최대 수백 배까지 증가를 받았습니다.

요약하자면: PostgreSQL을 FDW와 함께 사용하는 경우 항상 모든 필터가 원격 서버로 전송되는지 확인하세요. 그러면 만족스러울 것입니다... 적어도 다른 서버의 테이블을 조인할 때까지는 말이죠. 그러나 그것은 다른 기사에 대한 이야기입니다.

관심을 가져주셔서 감사합니다! 댓글로 여러분의 경험에 대한 질문, 의견, 이야기를 듣고 싶습니다.

출처 : habr.com