Opening

Chả là tuần vừa rồi, trong 1 buổi Estimate đầu sprint, mình có 1 task là điều tra nguyên nhân và xử lí 1 câu lệnh bị chậm. Tuy mới onboard công ty mới chưa được 2 tuần, nhưng thấy có cơ hội tập làm “lang băm” nên mình thử “bắt bệnh” xem sao :D. Phần dưới mình sẽ trình bày những step mình đã thử để điều tra và những kiến thức mình thu được. Let’s go.

Hồ sơ bệnh án

Dưới đây là những báo cáo sơ bộ về hiện trạng và triệu chứng của bệnh nhân:

Bối cảnh: Tính năng tìm kiếm video và xuất csv cho người dùng dựa trên 1 số điều kiện như Contact, Order, Product,….
Triệu chứng: Người dùng kêu gào vì tìm kiếm và xuất csv bị chậm, đặc biệt là xuất csv
- Download file csv vơi 700 dòng có thể mất 8 phút
- Download file 1300 dòng có thể tốn gần 13 phút.
Một số thông tin hữu ích về bệnh nhân là sử dụng SQL Server để lưu trữ với ngôn ngữ C# .NET và GraphQL để trao đổi dữ liệu API.

Quá trình bắt bệnh và chẩn đoán

Kiểm tra API

Đầu tiên mình kiểm tra API call với tính năng search và export csv trên giao diện staging.
Với tính năng search thì ko có gì đặc biệt. Còn khi kiểm tra api với tính năng xuất csv, mình thấy có điều bất thường là nó gọi rất nhiều API GetVideos với paging là 20 items. Giả sử cần xuất csv với 1000 dòng, như vậy sẽ tốn 1000 / 20 = 50 lần gọi api. Mỗi lần gọi api tốn khoảng 1s, vậy export file 1000 phần tử sẽ mất khoảng ~1 phút (dù đây mới là trên staging).
Mình hỏi team rằng tại sao khi export lại thực thi với từng 20 item thì là do nếu để nhiều hơn 20 item có lúc nó bị lỗi.

Kiểm tra SQL Server

Mình thử test lại tính năng trên giao diện rồi kiểm tra top 10 câu lệnh được thực hiện gần đây. Mình biết rõ câu query có liên quan tới 1 bảng nên không khó để query ra những câu lệnh đó. Mình thực hiện bằng script:

  SELECT TOP 10
      deqs.last_execution_time AS [Last Execution Time],
      dest.TEXT AS [Query Text]
  FROM sys.dm_exec_query_stats AS deqs
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
  WHERE dest.TEXT LIKE '%ProductionItem%'
  ORDER BY deqs.last_execution_time DESC;

Sau khi có được các câu lệnh rồi, mình kiểm tra từng câu một thì nhận thấy một vài câu có thiếu index. Tuy nhiên đây không phải vấn đề chính, dữ liệu khá nhỏ nên index không ảnh hưởng đến hiệu năng. Mỗi câu lệnh chỉ chạy hết dưới 100ms.
Tiếp đến mình cũng thử bật tính năng Query Store trên SQL Server. Query Store là tính năng giúp theo dõi, thu thập thông tin về hiệu năng câu truy vấn như wait, cpu ram sử dụng,… Mình kiểm tra các wait event thì cũng không thấy vấn đề gì cả, các câu lệnh khô

Kiểm tra app

Khi kiểm tra app bằng cách chạy api, mình thấy có 1 điều rất thú vị trên log
Có 1 câu truy vấn rất dài, gồm rất nhiều join. Điều này có thể gây giảm hiệu năng vì khi sinh chiến lược thực thi thì db có thể dê bị nhầm lẫn và không chọn được chiến lược tối ưu.
Query IN trong 1 list: đây có thể gây ra vấn đề tiềm ẩn trong tương lai, vì db sẽ phải loop qua danh sách list IN trong query để tìm kiếm. Nếu danh sách dài khoảng 100 đến 1000 phần tử thì phải xem xét tối ưu. Do ở đây dữ liệu bé nên chưa thấy được vấn đề hiệu năng.
n + 1 query: n + 1 query chính là vấn đề. n + 1 query là khi truy vấn 1 danh sách các bảng cha, với mỗi id cha lại truy vấn thêm n câu query bảng con, điều này gây giảm performance và tốn tài nguyên. Nguyên nhân do n + 1 query thường sử dụng ORM không đúng cách (ở đây là EF core). Như ở ví dụ trên, db phải thực hiện n câu truy vấn với từng TemplateParentId để lấy ChildTemplate.

Kiểm tra code

Như vậy nguyên nhân trực tiếp đó là do vấn đề n + 1 query khi sử dụng với ORM. Tuy nhiên việc tìm kiếm nguyên nhân thực sự tại sao lại sinh ra n + 1 query tương đối khó.
Mình tìm được đoạn code trả về api là IQueryable, bên trong câu query có một đoạn Select ToList(). IQueryable là câu query bằng linq và sẽ được Linq gen ra câu lệnh sql thực để truy vấn db. Ban đầu mình nghĩ do có một đoạn ToList() trong IQueryable nên nó được thực thi trước gây ra n + 1. Tuy nhiên nhiên mình check docs của Linq và ef core thì không có chỗ nào nhắc đến ToList() trong IQueryable sinh ra n + 1 query.
Đang bí không biết mò tiếp như thế nào thì chợt nhớ đến GraphQL. Câu lệnh IQueryable trả về cho GraphQL thực thi, vậy nên có thể có manh mối khi mình tìm tìm hiểu về cách GraphQL hoạt động :D.

GraphQL

GraphQL được tạo ra nhằm đơn giản hóa việc trao đổi dữ liệu giữa BE và FE, thay vì phải gọi nhiều API hay 1 cục dữ liệu khổng lồ thì FE chỉ việc lựa chọn những trường cần thiết. Vậy GraphQL có những thành vần gì và nó thực thi như thế nào?

GraphQL query

GraphQL query là câu truy vấn mà FE gửi lên, nó có cấu trúc khá đơn giản và dễ hiểu. Ví dụ với một câu graphql query kèm dữ liệu trả về sẽ như sau:

Schema

Trong graphql server có 2 thành phần chính là Schema và Resolver function.
Schema: Là data model được BE định nghĩa, dùng để truy vấn và trả dữ liệu. Nó định nghĩa những trường dữ liệu mà có thể dùng để select và query. Ví dụ 1 schema Graphql đơn giản với 3 thành phần Author, Post, và Query sẽ như sau:

Resolver function

Resolver function chính là các hàm để lấy dữ liệu. Nó sẽ định nghĩa các cục dữ liệu được lấy từ đâu, có thể từ database, từ mem, hay kể cả từ api khác. Cục data lớn trả về có thể tách thành nhiều cục nhỏ hơn, mỗi cục nhỏ được định nghĩa cách lấy thông qua từng resolver function. Ví dụ:
Chính sự ẩn đi công đoạn lấy các nguồn dữ liệu giúp cho việc giao tiếp giữa BE và FE dễ dàng và thống nhất hơn,

Kết luận chẩn đoán

Sau khi tìm hiểu về graphql mình đã biết rằng các thành phần data có thể được lấy từ các nguồn khác nhau thông qua các resolver function, vậy thì chỉ cần đi tìm các resolver function là có thể được.
Đúng như dự đoán, mình đã tìm được đoạn code bị dính n + 1 query :D
Vậy là chỉ cần xử đoạn code này, loại bỏ for loop query và thay bằng 1 đoạn query cả Parent và Child là xong.

Bốc thuốc

Tổng kết lại mình cần xử lí 2 vấn đề: 1 câu join query to và n + 1 query.
Đối với câu query to, Entity Framework cho phép ta chỉ định 1 câu linq query có thể được tách thành nhiều câu sql. Sử dụng tính năng này bằng cách thêm AsSplitQuery() đằng sau lệnh Include(). Ví dụ:
```
  using (var context = new BloggingContext())
  {
      var blogs = await context.Blogs
          .Include(blog => blog.Posts)
          .AsSplitQuery()
          .ToListAsync();
  }
```
Với vấn đề n + 1 query: bỏ đi for loop, thay vào đó có thể viết câu lệnh join để lấy dữ liệu hoặc lấy tất cả dữ liệu lên và join trên mem.

Kết quả

Dưới đây là kết quả mình kiểm nghiệm trên staging với khoảng 306 items
Trước khi tối ưu:
- Get 20 items ~ 700m
- Get 100 items ~ 3s
- Get 1000 items (thực tế là chỉ có 306) ~ 15s
Sau khi tối ưu:
- Get 20 items ~ 700ms
- Get 100 items ~ 1s
- Get 1000 items ~ 2s