Bottleneck trong việc config MySQl (MariaDB)
Mysql
49
Bottleneck
1
Database
33
Server
6
TIL
720
White

Nguyễn Thế Huy viết ngày 08/09/2018

Trong bài viết nho nhỏ này mình xin chia sẻ một case mà mình mới gặp trong quá trình làm việc với Mysql, hy vọng có thể giúp được các bạn gặp phải trường hợp tương tự. Chúng ta sẽ cùng bắt đầu từ bài toán trước nhé.

Vấn đề

Bọn mình có một CSDL Mysql (Đúng hơn là MariaDB, nhưng những gì dưới đây cũng tương tự với Mysql), trong đó có 1 bảng "products". Bảng này có kích thước không quá lớn, cỡ khoảng nửa triệu bản ghi thôi. Website mới làm nên lượng truy cập cũng rất nhỏ. Tuy nhiên bỗng dưng một ngày website đơ ra, không thể thao tác được gì cả. Mình xem htop kiểm tra thì CPU, Memory đều full đỏ lừ. Lượng process Mysql cũng cao một cách bất thường. Mình nhanh chóng loại bỏ các yếu tố từ phần cứng và quá tải do truy cập sau khi kiểm tra. Tiếp tục xem thử show processlist, có đến gần 100 process bị hold lại không chạy được, đều có state là: Creating sort index. State này chỉ ra có hệ thống trong quá trình load bộ nhớ nhằm sắp xếp dữ liệu Order by trên câu query.

Do đó mình suy đoán rằng vì một lý do nào đó các process Mysql ngốn tài nguyên một cách bất thường, bắt đầu từ Memory (RAM).

Chẩn bệnh

Thứ đầu tiên mà mình nghĩ đến là kiểm tra config Mysql. Vì là dân không chuyên nên mình cần một tool để giúp mình tìm ra các vấn đề với CSDL, và tool đầu tiên mình nghĩ đến là MysqlTuner. Đây là một script Perl có thể giúp mình có cái nhìn tổng quan nhất về các vấn đề đối với Mysql và đưa ra các hướng giải quyết.

Kết quả sau khi mình chạy MysqlTuner:

alt text

Vậy vấn đề ở đây chính là mình config Mysql hiện tại của mình quá ngốn RAM

Lật ngược lên phía trên, phần Perfomance Metrics, thì thấy có dòng:

Maximum reached memory usage: 100G (1250% of installed RAM)

Với mỗi thread Mysql sinh ra sẽ được quyền chiếm một dung lượng bộ nhớ nhất định. Và tổng lượng bộ nhớ cho phép đang vượt quá ngưỡng bộ nhớ phần cứng của mình

Kiểm tra my.cnf:

join_buffer_size = 60M
read_buffer_size = 40M
sort_buffer_size = 60M
max_connections = 1000

Về cơ bản một thread sẽ được cấp phát một dung lượng bộ nhớ = join_buffer_size + read_buffer_size + sort_buffer_size

Con số này nhân với max_connections chính là Maximum reached memory usage

Sau một hồi tham khảo các nguồn thì các con số này trên server mình đều được config quá cao và không cần thiết, mình đã điều chỉnh về một con số phù hợp hơn:

join_buffer_size = 1M
read_buffer_size = 28M
sort_buffer_size = 1M

và hệ thống nhanh chóng trở lại ổn định.

Tóm tắt

Chúng ta thường hay gặp một số vấn đề với perfomance hệ thống, trong đó về Mysql là khá phổ biến. Trước khi scale phần cứng, ta có thể quan tâm tới các vấn đề:

  • Kiểm tra các config cài đặt để đảm bảo phù hợp nhất đối với bài toán và phần cứng đang có. MysqlTuner là một công cụ khá ổn, tuy nhiên chỉ mang tính tham khảo, vì một số suggestion của nó có thể là chưa phù hợp (Ví dụ suggest tắt query_cache trên MariaDB phiên bản 10.1 trở lên Bạn có thể tham khảo tại đây).

  • Tối ưu query, trong đó index là một phần rất quan trọng. Index sao cho phù hợp quyết định rất lớn đến hiệu suất hệ thống.

  • Sử dụng cache một cách hợp lý, giảm tải cho Mysql.

  • MysqlTuner hoặc Percona monitoring and management là các tool free rất ổn hỗ trợ chẩn bệnh cho Mysql, giúp tránh được các bottleneck của hệ thống.

  • Ngoài các thông số buffer_size kể trên, chúng ta còn quan tâm hơn về các thông số riêng biệt cho Engine (Ví dụ InnoDB hoặc MyISam). Mình sẽ viết cụ thể hơn trong một bài viết khác.

Cám ơn các bạn đã đọc bài viết của mình, hy vọng bài viết có thể giúp ích phần nào cho các bạn khi gặp các vấn đề tương tự.

Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

White

Nguyễn Thế Huy

9 bài viết.
39 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
White
30 8
Hi cả nhà, đây là bài viết đầu tiên của mình trên Kipalog nên có gì không hay mong các bạn thông cảm :D Realtime là gì ? Như chúng ta đều đã biế...
Nguyễn Thế Huy viết gần 2 năm trước
30 8
White
27 5
Xin chào mọi người :D Trong bài viết này mình sẽ trình bày một cách cơ bản để ứng dụng kỹ thuật Http Live Streaming (HLS) để play video trên web, ...
Nguyễn Thế Huy viết hơn 1 năm trước
27 5
White
21 4
Bài toán Machine Learning và những ứng dụng của nó đang ngày càng trở nên nổi bật trong những năm trở lại đây. Với sự phát triển thần tốc của cấu ...
Nguyễn Thế Huy viết 6 tháng trước
21 4
Bài viết liên quan
White
1 0
Kotlin giờ là một từ khóa làm điên đảo cả giới lập trình viên đặc biệt là lập trình viên android. Kotlin hiện nay rất phổ biến và cực kì mạnh mẽ, c...
Aragami1408 viết 10 tháng trước
1 0
White
22 0
Mysql innodb internal là một chủ đề khá sâu. Bản thân tôi cũng chưa bao quát hết. Bài viết này chỉ cung cấp một cái nhìn sơ lược. Để các bạn khôn...
manhdung viết 4 năm trước
22 0
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


White
{{userFollowed ? 'Following' : 'Follow'}}
9 bài viết.
39 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!