Kiến trúc microservice, giống như mọi thứ trên thế giới này, đều có ưu và nhược điểm. Một số quy trình trở nên dễ dàng hơn với nó, một số quy trình khác khó khăn hơn. Và vì tốc độ thay đổi và khả năng mở rộng tốt hơn, bạn cần phải hy sinh. Một trong số đó là sự phức tạp của phân tích. Nếu trong một khối nguyên khối, tất cả các phân tích hoạt động có thể được rút gọn thành các truy vấn SQL thành một bản sao phân tích, thì trong kiến ​​trúc đa dịch vụ, mỗi dịch vụ có cơ sở dữ liệu riêng và có vẻ như một truy vấn là không đủ (hoặc có thể sẽ như vậy?). Đối với những người quan tâm đến cách chúng tôi giải quyết vấn đề phân tích hoạt động trong công ty của mình và cách chúng tôi học cách chung sống với giải pháp này - xin chào mừng.

Tên tôi là Pavel Sivash, tại DomClick, tôi làm việc trong một nhóm chịu trách nhiệm duy trì kho dữ liệu phân tích. Thông thường, các hoạt động của chúng tôi có thể được quy cho kỹ thuật dữ liệu, nhưng trên thực tế, phạm vi nhiệm vụ rộng hơn nhiều. Có tiêu chuẩn kỹ thuật dữ liệu ETL / ELT, hỗ trợ và điều chỉnh các công cụ phân tích dữ liệu và phát triển các công cụ của riêng họ. Cụ thể, đối với báo cáo hoạt động, chúng tôi quyết định “giả vờ” rằng chúng tôi có một cơ sở dữ liệu nguyên khối và cung cấp cho các nhà phân tích một cơ sở dữ liệu chứa tất cả dữ liệu họ cần.

Nói chung, chúng tôi đã xem xét các lựa chọn khác nhau. Có thể xây dựng một kho lưu trữ chính thức - chúng tôi thậm chí đã thử, nhưng thành thật mà nói, chúng tôi không thể kết bạn với những thay đổi logic khá thường xuyên với quy trình xây dựng kho lưu trữ và thực hiện các thay đổi đối với kho lưu trữ khá chậm ( nếu ai đó đã thành công, hãy viết trong phần bình luận như thế nào). Bạn có thể nói với các nhà phân tích: “Các bạn, hãy học python và chuyển sang dòng phân tích,” nhưng đây là một yêu cầu tuyển dụng bổ sung và có vẻ như điều này nên tránh nếu có thể. Chúng tôi quyết định thử sử dụng công nghệ FDW (Foreign Data Wrapper): trên thực tế, đây là một dblink tiêu chuẩn, theo tiêu chuẩn SQL, nhưng với giao diện thuận tiện hơn nhiều. Trên cơ sở của nó, chúng tôi đã đưa ra một quyết định, cuối cùng đã bén rễ, chúng tôi đã giải quyết nó. Chi tiết của nó là chủ đề của một bài viết riêng, và có thể nhiều hơn một bài, vì tôi muốn nói về rất nhiều: từ đồng bộ hóa lược đồ cơ sở dữ liệu đến kiểm soát truy cập và cá nhân hóa dữ liệu cá nhân. Cũng cần lưu ý rằng giải pháp này không phải là giải pháp thay thế cho kho lưu trữ và cơ sở dữ liệu phân tích thực, nó chỉ giải quyết một vấn đề cụ thể.

Ở cấp cao nhất, nó trông như thế này:

Có một cơ sở dữ liệu PostgreSQL nơi người dùng có thể lưu trữ dữ liệu công việc của họ và quan trọng nhất là các bản sao phân tích của tất cả các dịch vụ được kết nối với cơ sở dữ liệu này thông qua FDW. Điều này giúp bạn có thể viết một truy vấn tới một số cơ sở dữ liệu và không quan trọng đó là gì: PostgreSQL, MySQL, MongoDB hoặc thứ gì đó khác (tệp, API, nếu đột nhiên không có trình bao bọc phù hợp, bạn có thể tự viết). Vâng, tất cả mọi thứ dường như là tuyệt vời! Chia tay?

Nếu mọi thứ kết thúc nhanh chóng và đơn giản như vậy, thì có lẽ bài báo đã không tồn tại.

Điều quan trọng là phải rõ ràng về cách postgres xử lý các yêu cầu đến các máy chủ từ xa. Điều này có vẻ hợp lý, nhưng thường mọi người không chú ý đến nó: postgres chia truy vấn thành các phần được thực hiện độc lập trên các máy chủ từ xa, thu thập dữ liệu này và tự thực hiện các phép tính cuối cùng, vì vậy tốc độ thực hiện truy vấn sẽ phụ thuộc rất nhiều vào cách thức thực hiện nó được viết. Cũng cần lưu ý: khi dữ liệu đến từ một máy chủ từ xa, chúng không còn chỉ mục nữa, không có gì giúp ích cho bộ lập lịch, do đó, chỉ có chính chúng tôi mới có thể trợ giúp và đề xuất. Và đó là những gì tôi muốn nói chi tiết hơn.

Một yêu cầu đơn giản và một kế hoạch với nó

Để biết cách Postgres truy vấn một bảng 6 triệu hàng trên một máy chủ từ xa, hãy xem xét một kế hoạch đơn giản.

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

Sử dụng câu lệnh VERBOSE cho phép bạn xem truy vấn sẽ được gửi đến máy chủ từ xa và kết quả mà chúng tôi sẽ nhận được để xử lý thêm (chuỗi RemoteSQL).

Hãy đi xa hơn một chút và thêm một số bộ lọc vào truy vấn của chúng ta: từng bộ lọc boolean trường, từng mục một dấu thời gian mỗi khoảng thời gian và từng cái một 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

Đây chính là thời điểm mà bạn cần chú ý khi viết truy vấn. Các bộ lọc không được chuyển đến máy chủ từ xa, điều đó có nghĩa là để thực thi nó, postgres kéo tất cả 6 triệu hàng để lọc cục bộ sau đó (dòng Bộ lọc) và thực hiện tổng hợp. Chìa khóa thành công là viết một truy vấn để các bộ lọc được truyền đến máy từ xa và chúng tôi chỉ nhận và tổng hợp các hàng cần thiết.

Đó là một số booleanshit

Với các trường boolean, mọi thứ đều đơn giản. Trong truy vấn ban đầu, vấn đề là do toán tử is. Nếu chúng ta thay thế nó bằng =, thì ta được kết quả như sau:

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

Như bạn có thể thấy, bộ lọc đã bay đến máy chủ từ xa và thời gian thực hiện đã giảm từ 27 xuống 19 giây.

Cần lưu ý rằng người vận hành is khác với nhà điều hành = cái có thể hoạt động với giá trị Null. Nó có nghĩa là là không đúng sự thật trong bộ lọc sẽ để lại các giá trị Sai và Null, trong khi != Đúng sẽ chỉ để lại các giá trị Sai. Vì vậy, khi thay toán tử không phải là bạn nên chuyển hai điều kiện cho bộ lọc bằng toán tử OR, ví dụ: Ở ĐÂU (col != True) HOẶC (col là null).

Với boolean đã tìm ra, tiếp tục. Trong thời gian chờ đợi, hãy trả bộ lọc theo giá trị boolean về dạng ban đầu để xem xét độc lập tác động của các thay đổi khác.

dấu thời gian? hz

Nói chung, bạn thường phải thử nghiệm cách viết chính xác một truy vấn liên quan đến các máy chủ từ xa và chỉ sau đó tìm lời giải thích tại sao điều này lại xảy ra. Rất ít thông tin về điều này có thể được tìm thấy trên Internet. Vì vậy, trong các thử nghiệm, chúng tôi nhận thấy rằng bộ lọc ngày cố định bay đến một máy chủ từ xa với một tiếng nổ, nhưng khi chúng tôi muốn đặt ngày một cách linh hoạt, ví dụ: now() hoặc CURRENT_DATE, thì điều này không xảy ra. Trong ví dụ của chúng tôi, chúng tôi đã thêm một bộ lọc để cột created_at chứa dữ liệu cho đúng 1 tháng trước (GIỮA GIỮA CURRENT_DATE - THỜI GIAN '7 tháng' VÀ CURRENT_DATE - THỜI GIAN '6 tháng'). Chúng tôi đã làm gì trong trường hợp này?

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

Chúng tôi đã nhắc người lập kế hoạch tính ngày trước trong truy vấn phụ và chuyển biến đã được chuẩn bị sẵn cho bộ lọc. Và gợi ý này đã cho chúng tôi một kết quả tuyệt vời, truy vấn trở nên nhanh hơn gần 6 lần!

Một lần nữa, điều quan trọng là phải cẩn thận ở đây: loại dữ liệu trong truy vấn con phải giống với loại dữ liệu của trường mà chúng tôi lọc, nếu không, người lập kế hoạch sẽ quyết định rằng vì các loại khác nhau và trước tiên cần phải lấy tất cả dữ liệu và lọc nó cục bộ.

Hãy trả bộ lọc theo ngày về giá trị ban đầu của nó.

Freddy vs. jsonb

Nói chung, các trường boolean và ngày tháng đã đủ tăng tốc truy vấn của chúng tôi, nhưng còn một loại dữ liệu nữa. Thành thật mà nói, cuộc chiến với việc lọc theo nó vẫn chưa kết thúc, mặc dù cũng có những thành công ở đây. Vì vậy, đây là cách chúng tôi vượt qua bộ lọc bằng cách jsonb trường đến một máy chủ từ xa.

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

Thay vì lọc toán tử, bạn phải sử dụng sự hiện diện của một toán tử. jsonb trong một khác nhau. 7 giây thay vì 29 giây ban đầu. Cho đến nay, đây là tùy chọn thành công duy nhất để truyền bộ lọc qua jsonb đến một máy chủ từ xa, nhưng ở đây, điều quan trọng là phải tính đến một hạn chế: chúng tôi sử dụng phiên bản 9.6 của cơ sở dữ liệu, nhưng vào cuối tháng 12, chúng tôi dự định hoàn thành các thử nghiệm cuối cùng và chuyển sang phiên bản 10. Khi chúng tôi cập nhật, chúng tôi sẽ viết nó ảnh hưởng như thế nào, bởi vì có rất nhiều thay đổi mà có nhiều hy vọng: json_path, hành vi CTE mới, đẩy xuống (có từ phiên bản XNUMX). Tôi thực sự muốn thử nó sớm.

Kết thúc anh ta

Chúng tôi đã kiểm tra từng thay đổi ảnh hưởng đến tốc độ truy vấn riêng lẻ như thế nào. Bây giờ hãy xem điều gì sẽ xảy ra khi cả ba bộ lọc được viết chính xác.

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

Vâng, truy vấn có vẻ phức tạp hơn, nó là một mức giá bắt buộc, nhưng tốc độ thực hiện là 2 giây, nhanh hơn gấp 10 lần! Và chúng ta đang nói về một truy vấn đơn giản trên một tập hợp dữ liệu tương đối nhỏ. Với những yêu cầu thực tế, chúng tôi nhận được số lượng tăng lên đến vài trăm lần.

Tóm lại: nếu bạn đang sử dụng PostgreSQL với FDW, hãy luôn kiểm tra xem tất cả các bộ lọc có được gửi đến máy chủ từ xa hay không và bạn sẽ hài lòng... Ít nhất là cho đến khi bạn kết nối được giữa các bảng từ các máy chủ khác nhau. Nhưng đó là một câu chuyện cho một bài báo khác.

Cám ơn vì sự quan tâm của bạn! Tôi muốn nghe các câu hỏi, nhận xét và câu chuyện về trải nghiệm của bạn trong các nhận xét.

Nguồn: www.habr.com