Kỹ thuật tối ưu hóa máy chủ MySQL cho trang web có nhiều lượt truy cập
Để có thể giảm tải cho máy chủ MySQL mà không cần chỉnh sửa cài đặt của mã nguồn DLE nói riêng và các mã nguồn khác nói chung thì có vài phương pháp. Kỹ thuật ở đây có thể áp dụng một vài kỹ thuật mà cá nhân đã đúc kết và tham khảo từ nhiều nguồn.
Lưu ý: Bài viết này chỉ dành cho các admin đang chạy web sử dụng VPS hoặc máy chủ riêng chứ không dành cho các admin sử dụng shared hosting nhé!
- Bộ nhớ đệm truy vấn
Chúng ta sẽ nói về các khả năng của MySQL như kết quả truy vấn bộ nhớ đệm. Bộ nhớ đệm truy vấn cho phép bạn tăng hiệu suất của các ứng dụng web mà không thực hiện bất kỳ thay đổi thiết kế nào đối với chính ứng dụng đó. Bản chất công việc của nó là nó ghi nhớ kết quả của các truy vấn trong RAM và khi truy vấn được lặp lại, nó không thực hiện truy vấn mà chỉ trả về ngay kết quả từ bộ đệm. Theo mặc định, bộ nhớ đệm trong MySQL bị tắt nên không phải ai cũng có thể kích hoạt nó mà chỉ những người dùng có khả năng thực hiện truy vấn thay mặt cho người dùng root MySQL hoặc có quyền truy cập vào tập tin cấu hình máy chủ MySQL. Nhưng thế là đủ rồi, bởi vì... Bộ nhớ đệm MySQL chỉ hiệu quả đối với các dự án có nhiều lượt truy cập và họ thường có các gói VPS nơi có quyền truy cập vào cài đặt hoặc máy chủ của riêng họ. Nếu dự án của bạn có lưu lượng truy cập thấp thì về nguyên tắc bạn không cần điều này, bởi vì... bạn sẽ không đạt được nhiều hiệu quả đáng chú ý.
MySQL có cơ chế bộ nhớ đệm truy vấn tích hợp không được bật theo mặc định. Để biết trạng thái bộ nhớ đệm, hãy chạy yêu cầu sau trong phpMyAdmin hoặc thông qua ssh (sau khi đã login vào MySQL bằng lệnh mysql -u root -p xong):
show variables like 'query_cache%';Kết quả bạn sẽ được một bảng có nội dung gần giống như sau:
+------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+trong đó query_cache_size hiển thị kích thước RAM được phân bổ cho bộ đệm truy vấn. Để bật bộ đệm truy vấn và phân bổ 16 megabyte bộ nhớ, bạn cần chạy truy vấn sau:
SET @@global.query_cache_size=16777216;Truy vấn phải được thực thi với tài khoản root của MySQL (truy vấn này sẽ tác động tạm thời lên dịch vụ MySQL đang chạy và sẽ trở lại ban đầu nếu khởi động lại dịch vụ). Nếu bạn muốn đặt cài đặt này vĩnh viễn, bạn mở tập tin my.cnf và vào tiểu mục [mysqld] thêm dòng mã sau vào bên dưới: (16M là 16 megabytes hoặc 1G là 1 gigabytes)
query_cache_size=16Mvà khởi động lại dịch vụ MySQL. Nếu bạn có đủ RAM dự trữ, bạn có thể tăng kích thước bộ đệm. Nhưng thật khó để đánh giá điều này ngay sau khi bật bộ đệm, nhu cầu tăng bộ nhớ đệm có thể được đánh giá khoảng một ngày sau khi bật bộ đệm, nhưng tôi sẽ nói về điều này bên dưới.
Để kiểm tra trạng thái bộ đệm, bạn cần chạy truy vấn sau:SHOW GLOBAL STATUS LIKE 'Qcache%';Kết quả sẽ được một bảng có nội dung gần giống như sau:
+-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | Qcache_free_blocks | 14 | | Qcache_free_memory | 1673479600 | | Qcache_hits | 2679 | | Qcache_inserts | 2223 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1004 | | Qcache_queries_in_cache | 1079 | | Qcache_total_blocks | 2230 | +-------------------------+------------+trong đó
Qcache_free_memory là kích thước của bộ nhớ đệm trống
Qcache_hits là số lượng yêu cầu được gửi từ bộ đệm
Qcache_lowmem_prunes là dung lượng bộ nhớ được giải phóng do thiếu bộ nhớ đệm, lý tưởng nhất là bằng 0, nếu không phải như vậy thì điều đó có nghĩa là bạn không có đủ bộ nhớ đệm và bạn cần tăng nó.
Chú ý, để đạt hiệu quả của bộ nhớ đệm MySQL trong DLE, bạn phải bật tùy chọn trong cài đặt tập lệnh trong phần tối ưu hóa:Bộ nhớ Cache đếm lượt xem bài viết (Cache the news views counter)
Nếu 'Tắt', thì bộ đếm lượt xem tin tức sẽ được cập nhật với mỗi lượt xem. Nếu 'Bật', số lượt xem sẽ được lưu trong một bảng khác và sẽ được cập nhật sau mỗi 2 giờ. Bật tùy chọn này để tiết kiệm thời gian CPU xử lý đối với các trang web lớn hơn.Nếu không, các bài viết sẽ được cập nhật liên tục mỗi khi có người xem và bộ nhớ đệm sẽ không còn hiệu quả.
- Tùy chỉnh tham số wait_timeout và interactive_timeout
Đôi khi có thể do ứng dụng web mà cụ thể là DLE thực thi tác vụ có kết nối cơ sở dữ liệu nhưng chờ thực hiện tác vụ khác làm tốn thời gian sau đó kết thúc tiến trình thực thi mà không ngắt kết nối vào cơ sở dữ liệu hoặc không thể ngắt kết nối thì sẽ có nhiều kết nối ở trạng tháng "Sleeping idle connections". Việc này sẽ là tăng tải của hệ thống và hao tốn tài nguyên không đáng.
Để hiển thị các kết nối đang ở trạng thái nghỉ, ta thực hiện lệnh:show full processlist;Cách giải quyết như sau:
– Giảm thời gian truy vấn chạy để lấy kết quả bằng cách tối ưu query, tối ưu dữ liệu trả về,..(vấn đề này phải chỉnh sửa mã nguồn DLE)
– Chỉnh tham số "wait_timeout" và "interactive_timeout" quy định thời gian dừng kết nối đang chế độ rãnh rồi của dịch vụ MySQLThông tin biến cấu hình "wait_timeout"
– Loại: integer
– Giá trị mặc định: 28800 (8 giờ)
– Giá trị nhỏ nhất có thể quy định: 1
– Giá trị lớn nhất có thể quy định: 31536000
– Phân loại nhóm: Global, Session
– Là giá trị đại diện cho khoảng thời gian mà dịch vụ MySQL sẽ chờ đợi trước khi ngắt các kết nối đang ở chế độ rảnh rỗi (idle connections) được dùng trong phiên kết nối chế độ "non-interactive" - kết nối không có tương tác trực tiếp từ người dùng mà cụ thể là bàn phím (terminal shell).
– Khi một thread xử lý kết nối từ người dùng được khởi tạo, thì giá trị "wait_timeout" của một phiên hoạt động (session) sẽ được khởi tạo dựa trên giá trị Global "wait_timeout"Thông tin biến cấu hình "interactive_timeout"
– Loại: integer
– Giá trị mặc định: 28800 (8 giờ)
– Giá trị nhỏ nhất có thể quy định: 1
– Phân loại nhóm: Global, Session
– Khi bạn sử dụng terminal shell, dùng chương trình lệnh “mysql” và thực thi lệnh query trên terminal gõ nhập từ bàn phím. Đó sẽ là phiên kết nối “interactive”.
– Nếu query thực hiện trên shell thì sau khoảng thời gian “interactive_timeout” thì MySQL sẽ tự động ngắt kết nối.Để thiết lập hai biến này thì bạn mở tập tin my.cnf và vào tiểu mục [mysqld] thêm dòng mã sau vào bên dưới:
wait_timeout=300 interactive_timeout=300 # 300 la 5 phutKhởi động lại dịch vụ MySQL
Kiểm tra các giá trị đã gán bằng lệnh
show global variables like '%timeout%'; - Điều chỉnh tham số max_connections
phần này anh em có thể tham khảo thêm tài liệu trên mạng nhé. Anh em có kinh nghiệm hơn thì chia sẽ dưới phần bình luận luôn nghen
Đây là vài tham số tham khảo
wait_timeout = 300 interactive_timeout = 300 #300 la 5 phut max_connections = 600 key_buffer_size = 128M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 1G # Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 - Tham khảo
https://dev.mysql.com/doc/refman/5.7/en/optimization.html
https://stackoverflow.com/questions/2482234/how-do-i-find-the-mysql-my-cnf-location
https://stackabuse.com/how-to-change-connection-timeout-in-mysql/
https://www.educba.com/mariadb-max-connections/
https://ixnfo.com/en/changing-thread_cache_size-in-mysql.html
https://ixnfo.com/en/how-to-convert-mysql-tables-from-myisam-to-innodb.html
https://stackoverflow.com/questions/46591509/mysql-threads-created-quickly-growing
https://cuongquach.com/cau-hinh-wait-timeout-interactive-timeout-mysql.html
https://www.percona.com/blog/is-your-query-cache-really-disabled/
https://kb.pavietnam.vn/dieu-chinh-hieu-suat-trong-mysql.html
https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-vs-mysql/
