by Darshit Thakkar, Chuho Chang, Pathik Shah, and Wei Zheng | on 17 NOV 2023 | in Amazon Athena, Analytics, Intermediate (200) | Permalink |  Comments |  Share

Amazon Athena là dịch vụ phân tích tương tác, phi máy chủ được xây dựng trên các khung nguồn mở, hỗ trợ các định dạng tệp bảng mở. Athena cung cấp một cách đơn giản, linh hoạt để phân tích hàng petabyte dữ liệu nơi nó sống. Bạn có thể phân tích dữ liệu hoặc xây dựng ứng dụng từ  hồ dữ liệu Amazon Simple Storage Service (Amazon S3) và 30 nguồn dữ liệu, bao gồm nguồn dữ liệu tại chỗ hoặc các hệ thống đám mây khác sử dụng SQL hoặc Python. Athena được xây dựng trên các công cụ Trino và Presto mã nguồn mở và các khung Apache Spark, không cần nỗ lực cung cấp hoặc cấu hình.

Bắt đầu từ hôm nay, công cụ Athena SQL sử dụng trình tối ưu hóa dựa trên chi phí (CBO), một tính năng mới sử dụng thống kê bảng và cột được lưu trữ trong  Danh mục dữ liệu AWS Glue như một phần của siêu dữ liệu của bảng. Bằng cách sử dụng các số liệu thống kê này, CBO cải thiện kế hoạch chạy truy vấn và tăng hiệu suất của các truy vấn chạy trong Athena. Một số tối ưu hóa cụ thể mà CBO có thể sử dụng bao gồm sắp xếp lại tham gia và đẩy tổng hợp xuống dựa trên số liệu thống kê có sẵn cho mỗi bảng và cột.

Điểm chuẩn TPC-DS Các điểm chuẩn này thể hiện sức mạnh của trình tối ưu hóa dựa trên chi phí — các truy vấn chạy nhanh hơn tới 2 lần khi bật CBO so với việc chạy cùng một truy vấn TPC-DS mà không có CBO.

So sánh hiệu suất và chi phí trên điểm chuẩn TPC-DS

Chúng tôi đã sử dụng TPC-DS 3 TB tiêu chuẩn ngành để đại diện cho các trường hợp sử dụng khác nhau của khách hàng. Đây là đại diện cho khối lượng công việc với kích thước chuẩn gấp 10 lần kích thước điểm chuẩn đã nêu. Điều này có nghĩa là tập dữ liệu điểm chuẩn 3 TB thể hiện chính xác khối lượng công việc của khách hàng trên bộ dữ liệu 30–50 TB.

Trong thử nghiệm của chúng tôi, tập dữ liệu được lưu trữ trong Amazon S3 ở định dạng Parquet không nén và Danh mục dữ liệu AWS Glue được sử dụng để lưu trữ siêu dữ liệu cho cơ sở dữ liệu và bảng. Các bảng dữ kiện được phân vùng trên cột ngày được sử dụng cho các hoạt động nối và mỗi bảng dữ kiện bao gồm 2.000 phân vùng. Để giúp minh họa hiệu suất của CBO, chúng tôi so sánh hành vi của các truy vấn khác nhau và làm nổi bật sự khác biệt về hiệu suất giữa chạy với CBO được bật so với bị vô hiệu hóa.

Biểu đồ sau minh họa thời gian chạy của các truy vấn trên công cụ có và không có CBO.

Biểu đồ sau đây trình bày 10 truy vấn hàng đầu từ điểm chuẩn TPC-DS với sự cải thiện hiệu suất lớn nhất.

Hãy thảo luận về một số kỹ thuật tối ưu hóa dựa trên chi phí đã góp phần cải thiện hiệu suất truy vấn.

Sắp xếp lại tham gia dựa trên chi phí

Tham gia sắp xếp lại, một kỹ thuật tối ưu hóa được sử dụng bởi các trình tối ưu hóa SQL dựa trên chi phí, phân tích các chuỗi nối khác nhau để chọn thứ tự giảm thiểu thời gian chạy truy vấn bằng cách giảm dữ liệu trung gian được xử lý ở mỗi bước, giảm yêu cầu bộ nhớ và CPU.

Hãy nói về truy vấn 82 của tập dữ liệu TPC-DS 3TB. Truy vấn thực hiện nối trong trên bốn bảng: item, inventory, date_dim và store_sales.  Bảng store_sales có 8,6 tỷ hàng và được phân vùng theo ngày. Bảng inventory có 1 tỷ hàng và cũng được phân vùng theo ngày.  Bảng item chứa 360.000 hàng và  bảng date_dim chứa 73.000 hàng.

Query 82

select  i_item_id ,i_item_desc ,i_current_price from item, inventory, date_dim, store_sales where i_current_price between 30 and 30+30 and inv_item_sk = i_item_sk and d_date_sk=inv_date_sk and cast(d_date as date) between cast(‘2002-05-30’ as date) and (cast(‘2002-05-30′ as date) +  interval ’60’ day) and i_manufact_id in (437,129,727,663) and inv_quantity_on_hand between 100 and 500 and ss_item_sk = i_item_sk group by i_item_id,i_item_desc,i_current_price order by i_item_id limit 100

Không có CBO

Không sử dụng CBO, công cụ sẽ xác định thứ tự nối dựa trên chuỗi các bảng được xác định trong truy vấn đầu vào với heuristics nội bộ. Mệnh đề FROM của truy vấn đầu vào là “from item, inventory, date_dim, store_sales” (tất cả các phép nối bên trong). Sau khi trải qua các phương pháp phỏng đoán nội bộ, Athena đã chọn thứ tự nối là ((item ⋈ (inventory ⋈ date_dim)) ⋈ store_sales). Mặc dù store_sales là bảng dữ kiện lớn nhất, nó được định nghĩa cuối cùng trong mệnh đề FROM và do đó được nối cuối cùng. Kế hoạch này không thể giảm kích thước nối trung gian càng sớm càng tốt, dẫn đến tăng thời gian chạy truy vấn. Sơ đồ sau đây cho thấy thứ tự nối không có CBO và số hàng chảy qua các giai đoạn khác nhau.

Với CBO

Khi sử dụng CBO, trình tối ưu hóa xác định thứ tự tham gia tốt nhất bằng cách sử dụng nhiều dữ liệu khác nhau, bao gồm số liệu thống kê cũng như ước tính kích thước tham gia, bên xây dựng và loại nối. Trong trường hợp này, thứ tự tham gia đã chọn của Athena là ((store_sales ⋈ mục) ⋈ (inventory ⋈ date_dim)). Bảng dữ kiện lớn nhất, store_sales, không bị xáo trộn, trước tiên được nối với  bảng thứ nguyên mục. Bảng được phân vùng khác, inventory trước tiên cũng được nối tại chỗ với  bảng thứ nguyên date_dim. Tham gia với bảng thứ nguyên hoạt động như một bộ lọc trên bảng dữ kiện, giúp giảm đáng kể kích thước dữ liệu đầu vào của phép nối tiếp theo. Lưu ý rằng bảng nằm ở phía nào cho phép nối có ý nghĩa quan trọng trong Athena, bởi vì đó là bảng bên phải sẽ được tích hợp vào bộ nhớ cho hoạt động nối. Do đó, chúng tôi luôn muốn giữ bàn lớn hơn ở bên trái và bàn nhỏ hơn ở bên phải. CBO đã chọn một kế hoạch mà phía bên trái là 8,6 tỷ trước đây, và bây giờ là 13,6 triệu.

Với CBO, thời gian chạy truy vấn được cải thiện 25% (từ 15 giây xuống còn 11 giây) bằng cách chọn thứ tự nối tối ưu.

Tiếp theo, hãy thảo luận về một kỹ thuật CBO khác.

Đẩy xuống tổng hợp dựa trên chi phí

Tổng hợp đẩy xuống là một kỹ thuật tối ưu hóa được sử dụng bởi các trình tối ưu hóa truy vấn để cải thiện hiệu suất. Nó liên quan đến việc đẩy các hoạt động tổng hợp như SUM, COUNT và AVG vào giai đoạn sớm hơn trong kế hoạch truy vấn, trong khi vẫn duy trì ngữ nghĩa truy vấn tương tự. Điều này làm giảm lượng dữ liệu được truyền giữa các giai đoạn. Bằng cách giảm thiểu xử lý dữ liệu, đẩy xuống tổng hợp làm giảm mức sử dụng bộ nhớ, chi phí I / O và lưu lượng mạng.

Tuy nhiên, đẩy tổng hợp xuống không phải lúc nào cũng có lợi. Nó phụ thuộc vào phân phối dữ liệu. Ví dụ: nhóm trên một cột có nhiều hàng nhưng ít giá trị riêng biệt (như giới tính) trước khi nối hoạt động tốt hơn. Nhóm đầu tiên có nghĩa là tổng hợp một số lượng lớn hồ sơ thành ít hồ sơ hơn (ví dụ chỉ nam, nữ). Nhóm sau khi tham gia có nghĩa là một số lượng lớn hồ sơ phải tham gia tham gia trước khi được tổng hợp. Mặt khác, nhóm trên một cột hồng y cao được thực hiện tốt hơn sau khi nối. Làm điều đó trước rủi ro tổng hợp không cần thiết vì mỗi giá trị có khả năng là duy nhất và bước đó sẽ không dẫn đến việc giảm lượng dữ liệu được truyền giữa các giai đoạn trung gian sớm hơn.

Do đó, có nên đẩy tổng hợp xuống hay không nên là một quyết định dựa trên chi phí. Hãy lấy ví dụ về truy vấn 2 chạy trên tập dữ liệu 3TB TPC-DS, cho thấy giá trị của việc đẩy xuống tổng hợp phụ thuộc vào phân phối dữ liệu như thế nào.  Bảng web_sales có 2,1 tỷ hàng và  bảng catalog_sales có 4,23 tỷ hàng. Cả hai bảng đều được phân vùng trên cột ngày.

Query 2

with wscs as (select sold_date_sk     ,sales_price   from (select ws_sold_date_sk sold_date_sk           ,ws_ext_sales_price sales_price     from web_sales     union all     select cs_sold_date_sk sold_date_sk           ,cs_ext_sales_price sales_price     from catalog_sales)), wswscs as (select d_week_seq,     sum(case when (d_day_name=’Sunday’) then sales_price else null end) sun_sales,     sum(case when (d_day_name=’Monday’) then sales_price else null end) mon_sales,     sum(case when (d_day_name=’Tuesday’) then sales_price else  null end) tue_sales,     sum(case when (d_day_name=’Wednesday’) then sales_price else null end) wed_sales,     sum(case when (d_day_name=’Thursday’) then sales_price else null end) thu_sales,     sum(case when (d_day_name=’Friday’) then sales_price else null end) fri_sales,     sum(case when (d_day_name=’Saturday’) then sales_price else null end) sat_sales from wscs ,date_dim where d_date_sk = sold_date_sk group by d_week_seq) select d_week_seq1   ,round(sun_sales1/sun_sales2,2)   ,round(mon_sales1/mon_sales2,2)   ,round(tue_sales1/tue_sales2,2)   ,round(wed_sales1/wed_sales2,2)   ,round(thu_sales1/thu_sales2,2)   ,round(fri_sales1/fri_sales2,2)   ,round(sat_sales1/sat_sales2,2) from (select wswscs.d_week_seq d_week_seq1     ,sun_sales sun_sales1     ,mon_sales mon_sales1     ,tue_sales tue_sales1     ,wed_sales wed_sales1     ,thu_sales thu_sales1     ,fri_sales fri_sales1     ,sat_sales sat_sales1   from wswscs,date_dim   where date_dim.d_week_seq = wswscs.d_week_seq and     d_year = 2001) y, (select wswscs.d_week_seq d_week_seq2     ,sun_sales sun_sales2     ,mon_sales mon_sales2     ,tue_sales tue_sales2     ,wed_sales wed_sales2     ,thu_sales thu_sales2     ,fri_sales fri_sales2     ,sat_sales sat_sales2   from wswscs   ,date_dim   where date_dim.d_week_seq = wswscs.d_week_seq and     d_year = 2001+1) z where d_week_seq1=d_week_seq2-53 order by d_week_seq1

Không có CBO

Athena đầu tiên tham gia kết quả của liên minh tất cả các hoạt động trên  bảng web_sales và  bảng catalog_sales với một bảng khác. Chỉ sau đó, nó mới thực hiện tổng hợp trên các kết quả đã nối. Trong ví dụ này, lượng dữ liệu cần được nối là rất lớn, dẫn đến thời gian chạy truy vấn lâu hơn.

Với CBO

Athena sử dụng một trong những giá trị thống kê, số lượng giá trị riêng biệt, để đánh giá ý nghĩa chi phí của việc đẩy tổng hợp xuống so với không làm như vậy. Khi một cột có nhiều hàng nhưng ít giá trị riêng biệt, CBO có nhiều khả năng đẩy tập hợp xuống. Điều này đã thu nhỏ các hàng đủ điều kiện từ web_sales và catalog_sales bảng xuống còn lần lượt là 2.590 và 3.590 hàng. Các bản ghi tổng hợp này sau đó được hợp nhất và được sử dụng để tham gia với các bảng. So với kế hoạch không có CBO, kỷ lục tham gia từ hai bảng lớn giảm từ 6,33 tỷ hàng (2,1 tỷ + 4,23 tỷ) xuống chỉ còn 6.180 hàng (2.590 + 3.590). Điều này làm giảm đáng kể thời gian chạy truy vấn.

Với CBO, thời gian chạy truy vấn được cải thiện 50% (từ 37 giây xuống còn 18 giây). Tóm lại, CBO đã giúp Athena chọn một kế hoạch đẩy xuống tổng hợp tối ưu, cắt giảm một nửa thời gian truy vấn so với việc không sử dụng tối ưu hóa dựa trên chi phí.

Kết thúc

Trong bài đăng này, chúng tôi đã thảo luận về cách Athena sử dụng trình tối ưu hóa dựa trên chi phí (CBO) trong công cụ v3 của mình để sử dụng thống kê bảng để tạo kế hoạch chạy truy vấn hiệu quả hơn. Thử nghiệm trên điểm chuẩn TPC-DS cho thấy sự cải thiện 11% về hiệu suất truy vấn tổng thể khi sử dụng CBO so với không có nó.

Hai tối ưu hóa chính được CBO sử dụng là sắp xếp lại tham gia và đẩy xuống tổng hợp. Tham gia sắp xếp lại làm giảm dữ liệu trung gian bằng cách chọn thứ tự thông minh để tham gia các bảng dựa trên số liệu thống kê. Đẩy xuống tổng hợp làm giảm dữ liệu trung gian bằng cách đẩy các tập hợp sớm hơn trong kế hoạch khi có lợi.

Tóm lại, trình tối ưu hóa dựa trên chi phí mới của Athena tăng tốc đáng kể các truy vấn bằng cách chọn các kế hoạch chạy vượt trội. CBO tối ưu hóa dựa trên số liệu thống kê bảng được lưu trữ trong Danh mục dữ liệu AWS Glue. Tối ưu hóa tự động này cải thiện năng suất cho người dùng Athena thông qua hiệu suất truy vấn đáp ứng nhanh hơn. Để tận dụng các kỹ thuật tối ưu hóa của CBO, hãy tham khảo làm việc với thống kê cột để tạo số liệu thống kê trên các bảng và cột trong Danh mục dữ liệu AWS Glue.

Tham khảo

Speed up queries with the cost-based optimizer in Amazon Athena | AWS Big Data Blog