Database bottlenecks

March 8, 2021

Với vai trò là Developer ắt hẳn ai cũng đã từng gặp phải vấn đề về Performance Optimization. Và một trong những vấn đề làm cho performance hệ thống kém, giật lag đó là Database. Do vậy hôm nay mình viết bài để cùng thảo luận với mọi người chủ đề “Database Bottleneck“.

Những vấn đề thường gây ra Database Bottleneck như là CPU, Memory, Disk, Network, Caching… Do chủ đề khá rộng nên mình chỉ giới thiệu 2 vấn đề thường gặp nhất nhé.

Đầu tiên là High CPU

Đây là một trong những vấn đề dễ nhìn thấy nhất bằng việc “Monitoring”. Nguyên nhân thường gặp dẫn đến tình trạng này là query thiếu index.

Một số việc làm cho high CPU:

Câu query của bạn sử dụng quá nhiều bản ghi
Câu query của bạn lấy ra những bản ghi với nhiều dữ liệu có thể không cần sử dụng đến, ví dụ: sử dụng SELECT *
Join quá nhiều bảng dữ liệu trong một query

Thứ hai đó là Memory

Một số việc Memory trong database làm là:

Cache index
Chứa các bản ghi kết quả của từng query
Buffer cho các connections tới DB

Một số case thường gặp Bottleneck về Memory

Lạm dụng việc đánh index
Không turning config cho Memory
Query sort không hiệu quả, filter còn quá nhiều bản ghi dẫn đến Memory không chứa hết số bản ghi cần xử lý bởi vậy đẩy xuống DISK.

Tổng kết

Để giải quyết những vấn đề trên mình nghĩ nên có mindset như sau:

Đánh index hiệu quả và tiết kiệm
- Có 1 vài tips để đánh index hiệu quả như sau:
  - Không tính toán với cột được đánh index sau mệnh đề WHERE vì SQL sẽ không nhận ra Index của trường ,ví dụ: ‘SELECT * FROM table_A WHERE field_count + 1 = 2’. Lúc này index của trường field_count sẽ không được sử dụng.
  - Chọn các trường có dữ liệu cần sắp xếp hay tìm kiếm để đánh index
  - Có thể index theo multi columns, ví dụ bạn muốn SELECT theo email và role, thì bạn có thể đánh index như sau: ‘CREATE INDEX index_name on user(email, role)’
  - Index là một phương pháp đầu tiên nên nghĩ đến khi muốn tối ưu tốc độ truy vấn. Tuy vậy các bạn cũng nên xem xét mỗi lần đánh index. Nó tuy hay nhưng lại không tốt mỗi khi chúng ta insert or update.
Tính toán được DB sẽ sử dụng bao nhiêu % RAM cho cache, bao nhiêu % RAM cho index…
- Bạn có thể search với keyword ‘How Mysql use memory’ để tìm hiểu thêm nhé
- Các bạn có thể tham khảo tại web này: ‘https://www.mysqlcalculator.com/‘
- Giả dụ database system của chúng ta cần 10 connections. Mỗi connection khoảng ~2-3MB RAM. Vậy số RAM cần là ~20->30MB.
- Những config còn lại các bạn có thể setup cache size bao nhiêu,….. Sau đó thì có thể xác định được hệ thống của chúng ta cần tổng cộng bao nhiêu Memory.
Thiết kế database chọn data type phù hợp để tiết kiệm bộ nhớ, IO, và index hiệu quả hơn
- Ví dụ để lưu trữ field age. Chúng ta không nên sử dụng BigInt để lưu age
- Lưu dữ liệu ngày tháng thì chọn DATETIME thay vì lưu dạng string
- Trong mysql có 2 kiểu số là số nguyên và số thực. Giả dụ INT có khoảng từ [ -2147483648, 2147483647 ]. Nếu hệ thống chúng ta không cần sử dụng số ÂM, thì có thể sử dụng kiểu UNSIGNED. Như vậy giá trị cho phép của INT sẽ là [0, 4294967295]. Vậy thì khoảng giá trị chúng ta được sử dụng sẽ nhiều hơn.
- String: Có thể sử dụng varchar, char, text, blob. Các bạn nên cân nhắc sử dụng type string nào cho đủ dùng khi thiết kế database.
- DateTime: Mysql có nhiều kiểu dữ liệu date time, Mà có 2 kiểu chúng ta thường sử dụng là DATETIME và TIMESTAMP. TIMESTAMP sử dụng 4 bytes còn DATETIME sử dụng 8 bytes.
Nên tối ưu câu query, query những gì mình cần để tiết kiệm Memory, tránh dùng SELECT ALL
Turning Config: Khi setup database xong chúng ta có thể setup một vài config cho nó, ví dụ:
- innodb_buffer_pool_size: đây là nơi cache data và index. Con số này càng nhiều càng tốt, tránh việc khi cache không đủ nó sẽ lưu xuống disk, và việc query từ disk sẽ làm chậm hệ thống. Bạn có thể setup con số này dựa trên số RAM mà bạn có. Mình suggest sử dụng khoảng 70% RAM. ví dụ bạn có 8GB RAM, thì setup 5->6GB RAM cho giá trị này.
- max_connections: Bạn có thể setup max connection để kiểm soát số lượng connection được phép sử dụng DB của bạn.
Giảm thiểu join, hạn chế query nặng
Giảm số lượng read query bằng cache