AWS REDSHIFT VS MONGODB

Vấn đề về thời gian query vào DB có thể nói luôn là nỗi ám ảnh với những ai làm backends. Vừa rồi thì mình cũng bị dính vào vấn đề này khi mà thời gian hiển thị một trang là ác mộng (sohai). Điều đó đã dẫn đến việc mình phải ngâm cứu để chuyển loại DB cho dữ liệu, từ no-sql sang sql. Sau đây sẽ là bài toán mà mình gặp phải.

Vấn đề

Mình có bảng như dưới đây:

Bảng là độc lập duy nhất, không cần liên kết hay cần phải join với bảng nào cả. Điều đó có nghĩa là những gì sql mạnh như join bảng là không hề có. Trong thực tế thì trường url và trường title có thể là những string rất là dài, nhất là trường title. Điều này làm cho mặc dù chí có 8.5 triệu bản ghi nhưng dữ liệu lên đến .... Gb.

Ở màn hình thì đối với 1 client_id và một khoảng thời gian xác định mình có 4 mục sau đây cần phải đưa ra:

• Một bảng như dưới đây đươc phân trang bằng Kaminari và max row có thể hiện thị là 500. User có thể tùy chọn sort ASC hay DESV theo column và default sẽ là pv_total. Các mục avr ví dụ pv_avr = pv_total / visit_total của url trong khoảng thời gian đó.

• Đưa ra tổng số pv_total, pv_avr, start, leave_avr, dwell_time_avr cửa tòan bộ client_id trong khoảng thời gian đó

• Đưa ra 10 url có pv_total cao nhất để hiện thị graph

• Để phục vụ cho Kaminari phân trang thì cần đếm xem có tổng cộng bao nhiêu url có trong bảng trong khoảng thời gian đấy.

Với từng yêu cầu như ở trên thì cụ thể các bước đưa ra dữ liệu như sau:

1. Đưa ra bảng:

    - WHERE client_id = "c16012" AND date BETWEEN date1 AND date2
    - GROUP BY url và tính tổng pv, start, leave, dwell_time
    - Tính pv_avr, leave_avr, dwell_time_avr
    - SORT theo trường được yêu cầu và gán LIMIT 500

1. Tính tổng:

    - WHERE client_id = "c16012" AND date BETWEEN date1 AND date2
    - Tính tổng pv, start, leave, dwell_time
    - Tính pv_avr, leave_avr, dwell_time_avr

1. Lấy Data cho graph:

    - WHERE client_id = "c16012" AND date BETWEEN date1 AND date2
    - Tỉnh tổng pv

1. Lấy số lượng url:

    - WHERE client_id = "c16012" AND date BETWEEN date1 AND date2
    - GROUP BY url
    - Count số lượng

Với dữ liệu mình có thì sau câu lệnh where đầu tiên sẽ có 8.5 triệu row kết quả, và sau câu lệnh group url thì số lượng là 1.6 triệu row. Dữ liệu lớn như vây giúp mình tình được performance của Redshift và MongoDB.

MongoDB

Đầu tiên là với MongoDB. Để có thể query được cho từng yêu câu trên thì mình sẽ sử dụng Aggregation Pipeline. Aggregation Pipeline sẽ hỗ trợ bạn sử dụng liên tiếp các query mà kết quả của query trước sẽ là đầu vào của query sau. Ngoài ra để lấy được excute time thì mình sẽ chạy lệnh sau

db.setProfilingLevel(2); và sau đó mỗi lần chạy xong 1 câu query thì mình sẽ chạy db.system.profile.find().limit(1).sort( { ts : -1 } ).pretty(), tìm dến giá trị millis để lấy excute time của query ngay trước đó.

Mình sẽ viết query nặng nhất là query để đưa ra dữ liệu cho bảng

db.totalize_pages.aggregate([
 {$match: {
   client_id: "c16012", date: {$gte: ISODate("2016-02-12T00:00:00Z")}
 }},
 {$group: {
   _id: {page: "$url", title: "$title"},
   pv: {$sum: "$pv"},
   cv: {$sum: "$cv"},
   dwell_time_total: {$sum: "$dwell_time"},
   bounce: {$sum: "$bounce"},
   visit: {$sum: "$visit"},
   start: {$sum: "$start"},
   leave: {$sum: "$leave"}
  }},

  {$project: {
    pv: 1,
    cv: 1,
    visit: 1,
    start: 1,
    dwell_time_total_rate: {$cond: [{$eq: ["$visit",0]},
      0, {$divide: ["$dwell_time_total", "$visit"]}]},
    bounce_rate: {$cond: [{$eq: ["$start",0]},
      0, {$divide: ["$bounce", "$start"]}]},
    pv_avr: {$cond: [{$eq: ["$visit",0]},
      0, {$divide: ["$pv", "$visit"]}]},
    leave_rate: {$cond: [{$eq: ["$pv",0]},
      "$leave", {$divide: ["$leave", "$pv"]}]},
    cv_rate: {$cond: [{$eq: ["$pv",0]},
      "$cv", {$divide: ["$cv", "$pv"]}]}
  {$limit: 500}
], {allowDiskUse: true})

Do dữ liệu quá lớn nên mình setting thêm allowDiskUse: true cho phép MongoDB ghi dư liệu tạm thời vào file. Và kết quả của câu lệnh này sẽ đưa ra sau **122.116 s ** (sohai)

Tương tự như vậy kết quả cho 3 câu query còn lại sẽ là:

• Tính tổng: **73.010 s **
• Graph: 92.181 s
• Số lượng url: 70.668 s

Tổng thời gian cho 4 query là 357.975s (Gần 6 phút =)) Vấn đề ở đây có lẽ là lượng data phải xử lý quá lớn, cộng với việc phải group url rồi mới nhân chia cộng trừ đã khiến cho từng câu query có thời gian xử lý ác mộng (haiz).

AWS Redshift

AWS Redshift là service của amazon giúp bạn quản lý, lưu trữ, xử lý lượng dữ liệu lên đến Petabyte. Cụ thể về cấu trúc, cách kết nối vói DB mình xin giới thiệu ở những bài tiếp theo.

Quay trở lại với vấn đề của chúng ta. Thì sau đây sẽ là câu SQL của mình để test khả năng của Redshift

SELECT url, title, total_pv, cast(total_pv as float) / total_visit as pv_avr ,
  cast(total_dwell_time_total as float) / total_visit as dwell_time_total_avr ,
  total_start, cast(total_leave_count as float) / total_visit as leave_count_avr ,
  cast(total_bounce as float) / total_visit as bounce_avr
FROM (
  SELECT url , title, sum(visit) as total_visit , sum(pv) as total_pv ,
    sum(dwell_time_total) as total_dwell_time_total , sum(leave_count) as
    total_leave_count , sum(bounce) as total_bounce , sum(start) as total_start
  FROM test_test
  WHERE date BETWEEN '2016-02-12' AND '2016-03-25' AND client_id = 'c16012900556'
  GROUP BY url, title
  HAVING sum(visit) > 0
)
ORDER BY total_pv DESC
LIMIT 500 OFFSET 0

iming để xem runtime và thời gian thực hiện là 10.035 s (iine)

Với 3 câu lệnh còn lại thời gian thực hiện là:

• Tính tổng: **2.021 s **
• Graph: 4.534 s
• Số lượng url: 5.071 s

Những con số cực kì ấn tượng tuy nhiên tổng thời gian cho 4 câu query vẫn là hơn 22s. Kết quả này vẫn chưa phải tốt nhất. Và chúng ta có thể giảm hơn nữa.

Temporary Table

OK quay trở lại với vấn đề trên, chúng ta có thể thấy là cả 4 câu query gần như có 1 điểm chung đó là ở 2 bước đầu :

  - WHERE client_id = "c16012" AND date BETWEEN date1 AND date2
  - GROUP BY url và tính tổng pv, start, leave, dwell_time

Tất nhiên là với query đếm sô lượng url và query cho graph thì không cần phải tính tổng pv, start, leave, dwell_time. Tuy nhiên nếu chúng ta tạo một temporary table là kết quả của 2 bước bên trên, và sau đó 4 câu query trên sẽ query vào temporary table, thì có lẽ tốc độ chung cho cả 4 query sẽ được cải thiện. Mình sẽ lại test lần lượt với 2 cấu trúc DB.

1. MongoDB

Để sử dụng temporary table cho mongo thì mình sẽ dùng $$ut. Câu lệnh để tạo temporary table sẽ là

  db.totalize_pages.aggregate([
   {$match: {
     client_id: "c16012", date: {$gte: ISODate("2016-02-12T00:00:00Z")}
   }},
   {$group: {
     _id: {page: "$url", title: "$title"},
     pv: {$sum: "$pv"},
     cv: {$sum: "$cv"},
     dwell_time_total: {$sum: "$dwell_time"},
     bounce: {$sum: "$bounce"},
     visit: {$sum: "$visit"},
     start: {$sum: "$start"},
     leave: {$sum: "$leave"}
    }},
    {$out: "temporary_table"}
    ], {allowDiskUse: true})

Và thời gian để tạo ra temporary table là ** 169.873s ** (yaoming)

Sau đó chạy 4 câu lệnh query, mình có được kết quả:

• Data cho table: 9.519 s
• Tỉnh tổng: **2.124 s **
• Graph: 3.370 s
• Số lượng url: ≈ 0s Gần như ngay lập tức kết quả trả về

Tổng thời gian cho tất cả query là 184.256 s hơn 3 phút. Thời gian xử lý giảm 1 nửa nhưng vẫn là quá tệ khi hiển thị trang. Tại sao việc ghi ra temporary table lại lón đến như vậy, vì dữ liệu còn lại sau 2 câu query trên vẫn là rất lớn với 1.6 triệu bản ghi

2. Redshift

Dưới đây sẽ là query để tạo temporary table

  CREATE TEMP TABLE temporary_page AS
    SELECT url, title, sum(visit) as total_visit , sum(pv) as total_pv ,
      sum(dwell_time_total) as total_dwell_time_total ,
      sum(leave_count) as total_leave_count , sum(bounce) as total_bounce , sum(start) as total_start
    FROM test_test
    WHERE date BETWEEN '2016-02-12' AND '2016-03-25' AND client_id = 'c16012900556'
    GROUP BY url, title
    HAVING sum(visit) > 0

Thời gian tạo temporary table là 9.926 s Và chạy 4 cấu query còn lại là:

• Data cho table: 238.045 ms
• Tỉnh tổng: 88.777 ms
• Graph: 126.840 ms
• Số lượng url: 15.525 ms

Tổng thời gian thực hiện là hơn 10s. Thời gian xử lý giảm đi hơn 1 nửa. 10s cho 8,5 triệu bản ghi là khá ấn tượng.

Kết luận

Như vậy rõ ràng là tốc độ query của Redshift là nhanh hơn rất nhiều so với Mongodb. Ngoài ra còn 1 điều cực hay là 10s chưa phải là giá trị nhỏ nhất có thể đạt được. Trong bài viết tiếp theo thì mình sẽ giải thích tại sao Redshift lai nhanh như vậy, đồng thời đưa ra cách thức để tiếp tục giảm thời gian query.

Vì cũng mới chỉ dùng Mongo một thời gian, cũng như mới học lại SQL nên có thể các câu lệnh của mình còn chưa tối ưu, cũng như có thể Mongo có setting nào đó giúp tăng tốc độ. Toàn bộ câu lệnh query mình sẽ để trong link dưới đây:

https://github.com/linuxhjkaru/redshift_mongo

Rất mong chờ comment của các bạn. Xin cảm ơn và hẹn gặp lại.