VietNetwork.Vn

MySQL và MariaDB là hệ quản trị cơ sở dữ liệu quan hệ (RDMS) được sử dụng rộng rãi nhất khi nói đến lưu trữ trang web và các hệ thống CMS như Joomla, WordPress, Drupal và Typo 3.

Trong bài viết này, mình sẽ giải thích cách tăng tốc và mình ưu hóa máy chủ cơ sở dữ liệu MySQL và MariaDB.

1. Lưu trữ dữ liệu MySQL trong các phân vùng riêng biệt.

Khi nói đến điểm mình ưu hóa và đảm bảo, việc lưu trữ dữ liệu cơ sở dữ liệu trong một phân vùng riêng biệt luôn là ý tưởng tốt nhất. Các ổ đĩa dành riêng cho phân vùng riêng biệt lưu trữ nhanh như SSD, NVMe. Ngay cả khi hệ thống của bạn bị lỗi, bạn sẽ có cơ sở dữ liệu của mình an toàn. Vì phân vùng riêng biệt được tạo thành từ phân vùng nhanh, hiệu suất sẽ nhanh hơn.

2. Đặt số lượng kết nối MySQL tối đa.

MySQL / MariaDB sử dụng một tùy chọn max_connections chỉ định số lượng kết nối đồng thời được phép trong máy chủ. Quá nhiều kết nối dẫn đến tiêu thụ nhiều bộ nhớ cũng như tải CPU cao.

Đối với các trang web nhỏ, kết nối có thể được chỉ định thành 100-200 và các kết nối lớn hơn có thể cần 500-800 và hơn thế nữa. Tùy chọn max_connections có thể được thay đổi động bằng cách sử dụng truy vấn SQL. Trong ví dụ này, mình đã đặt giá trị thành 200.

$ MySQL -u root -p
MySQL> set global max_connections = 200;

3. Bật Nhật ký truy vấn chậm MySQL.

Việc ghi nhật ký các truy vấn mất nhiều thời gian để thực thi làm cho việc khắc phục sự cố cơ sở dữ liệu trở nên dễ dàng hơn. Nhật ký truy vấn chậm có thể được kích hoạt bằng cách thêm các dòng sau vào tập tin cấu hình MySQL / MariaDB.

slow-query-log=1
slow-query-log-file= /var/lib/mysql/mysql-slow-query.log
long-query-time=1

• Nơi tùy chọn đầu tiên là bật nhật ký truy vấn chậm.
• Tùy chọn thứ hai xác định thư mục tập tin nhật ký.
• Tùy chọn thứ ba xác định thời gian để hoàn thành một truy vấn MySQL.

Khởi động lại dịch vụ MySQL / MariaDB và theo dõi nhật ký.

$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
4. Đặt gói tối đa mà MySQL cho phép.

Dữ liệu được chia thành các gói trong MySQL. Max_allowed_packet xác định kích thước tối đa của gói có thể được gửi.

Việc thiết lập max_allowed_packet quá thấp có thể khiến truy vấn quá chậm. Nên đặt giá trị gói tin bằng kích thước của gói tin lớn nhất.

5. Thiết lập dung lượng bảng tạm thời.

Tmp_table_size là không gian được sử dụng cho bảng tích hợp trong bộ nhớ. Nếu kích thước của bảng vượt quá giới hạn được chỉ định, nó sẽ được chuyển đổi thành bảng MyISAM trên đĩa.

Trong MySQL / MariaDB, bạn có thể thêm các biến sau vào tập tin cấu hình để thiết lập kích thước bảng Tạm thời. Bạn nên đặt giá trị này là 64M trên mỗi GB của RAM trên máy chủ.

[mysqld]

tmp_table_size=64M
Khởi động lại dịch vụ MySQL.

$ systemctl restart mysql
$ systemctl restart mariadb
6. Thiết lập dung lượng bảng bộ nhớ tối đa.

Max_heap_table_size là biến được sử dụng trong MySQL để cấu hình dung lượng bảng bộ nhớ tối đa.

Kích thước của dung lượng bảng bộ nhớ tối đa phải bằng với dung lượng bảng tạm thời để tránh ghi đĩa. Bạn nên đặt giá trị này trên máy chủ thành 64M trên mỗi GB của RAM. Thêm dòng sau vào tập tin cấu hình MySQL và khởi động lại dịch vụ.

[mysqld]

max_heap_table_size=64M
Để áp dụng các thay đổi, hãy khởi động lại máy chủ cơ sở dữ liệu.

$ systemctl restart mysql
$ systemctl restart mariadb
7. Tắt tra cứu ngược DNS cho MySQL.

Khi nhận được kết nối mới, MySQL / MariaDB sẽ thực hiện tra cứu DNS để phân giải địa chỉ IP của người dùng. Điều này có thể gây ra sự chậm trễ khi cấu hình DNS không hợp lệ hoặc có sự cố với máy chủ DNS.

Để tắt tra cứu DNS, hãy thêm dòng sau vào tập tin cấu hình MySQL và khởi động lại dịch vụ MySQL.

[mysqld]

skip-name-resolve
Khởi động lại dịch vụ MySQL.

$ systemctl restart mysql
$ systemctl restart mariadb
8. Tránh sử dụng Swappiness trong MySQL.

Kernel Linux di chuyển một phần bộ nhớ sang một phân vùng đặc biệt của đĩa được gọi là không gian "swap" khi hệ thống sử dụng hết bộ nhớ vật lý.

Trong điều kiện này, hệ thống ghi thông tin vào đĩa thay vì giải phóng một số bộ nhớ. Vì bộ nhớ hệ thống nhanh hơn bộ nhớ đĩa, bạn nên tắt tính năng swappiness. Swappiness có thể bị vô hiệu hóa bằng cách sử dụng lệnh sau.

$ sysctl -w vm.swappiness=0

9. Tăng kích thước InnoDB buffer pool.

MySQL / MariaDB có một công cụ InnoDB có một vùng đệm để lưu vào bộ nhớ cache và lập chỉ mục dữ liệu trong bộ nhớ. Vùng đệm giúp các truy vấn MySQL / MariaDB được thực thi tương đối nhanh hơn. Việc chọn kích thước thích hợp của vùng đệm InnoDB yêu cầu một số kiến thức về bộ nhớ hệ thống. Ý tưởng tốt nhất là đặt giá trị của kích thước vùng đệm InnoDB thành 80% RAM.

Ví dụ.

• Bộ nhớ hệ thống = 4GB
• Kích thước Buffer Pool = 3.2GB

Thêm dòng sau vào tập tin cấu hình MySQL và khởi động lại dịch vụ.

[mysqld]

Innodb_buffer_pool_size 3.2G
Khởi động lại cơ sở dữ liệu MySQL.

$ systemctl restart mysql
$ systemctl restart mariadb
10. Xử lý kích thước bộ nhớ cache của Truy vấn (Query cache size)

Tùy chọn Query cache size trong MySQL / MariaDB được sử dụng để lưu vào bộ đệm tất cả các truy vấn liên tục lặp lại với cùng một dữ liệu. Bạn nên đặt giá trị thành 64MB và tăng theo thời gian đối với các trang web nhỏ. Không nên tăng giá trị của kích thước bộ nhớ cache truy vấn lên GB vì nó có thể làm giảm hiệu suất cơ sở dữ liệu.

Thêm dòng sau vào tập tin my.cnf.

[mysqld]

query_cache_size=64M
11. Kiểm tra các kết nối không hoạt động.

Tài nguyên được sử dụng bởi các kết nối không hoạt động nên nó cần được kết thúc hoặc làm mới nếu có thể.

Các kết nối này vẫn ở trạng thái "ngủ" và có thể tồn tại trong một thời gian dài. Kiểm tra các kết nối chạy không tải bằng lệnh sau.

$ mysqladmin processlist -u root -p | grep "Sleep"
Truy vấn sẽ liệt kê các tiến trình đang ở trạng thái ngủ. Nói chung trong PHP, sự kiện có thể xảy ra khi sử dụng mysql_pconnect. Thao tác này sẽ mở kết nối MySQL, thực thi các truy vấn, xóa xác thực và để kết nối mở.

Sử dụng tùy chọn wait_timeout, các kết nối không hoạt động có thể bị gián đoạn. Giá trị mặc định cho wait_timeout là 28800 giây nhưng chúng taccó thể được giảm xuống một khoảng thời gian thiểu như 60 giây.

Thêm dòng sau vào tập tin my.cnf.

[mysqld]

wait_timeout=60
12. Tối ưu hóa và sửa chữa cơ sở dữ liệu MySQL.

Nếu máy chủ bị tắt đột ngột thì có khả năng các bảng trong MySQL / MariaDB có thể gặp sự cố. Có những lý do có thể khác dẫn đến sự cố bảng cơ sở dữ liệu như truy cập cơ sở dữ liệu trong khi quá trình sao chép đang chạy, hệ thống tập tin bị sập đột ngột. Trong tình huống này, mình có một công cụ đặc biệt gọi là "mysqlcheck" để kiểm tra, sửa chữa và tối ưu hóa tất cả các bảng trong cơ sở dữ liệu.

Sử dụng lệnh sau để thực hiện các hoạt động sửa chữa và tối ưu hóa.

Đối với tất cả các cơ sở dữ liệu:

$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
Đối với cơ sở dữ liệu cụ thể:

$ mysqlcheck -u root -p --auto-repair --check --optimize dbname
Thay thế dbname bằng tên cơ sở dữ liệu của bạn

13. Kiểm tra hiệu suất MySQL / MariaDB bằng các công cụ kiểm tra.

Cách tốt nhất là kiểm tra hiệu suất cơ sở dữ liệu MySQL / MariaDB thường xuyên. Điều này sẽ giúp bạn dễ dàng nhận được báo cáo hiệu suất và điểm cải tiến.

Có rất nhiều công cụ có sẵn trong đó mysqltuner là công cụ tốt nhất.

Chạy lệnh sau để tải xuống công cụ.

$ wget https://github.com/major/MySQLTuner-perl/tarball/master
Giải nén tập tin.

$ tar xvzf master
Chuyển đến thư mục dự án và thực hiện đoạn mã sau.

$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl

Trong bài viết này, chúng ta đã học cách tối ưu hóa MySQL / MariaDB bằng các kỹ thuật khác nhau. Nếu bạn có kinh nghiệm nào hay thì hãy để lại bình luận bên dưới để chia sẻ với mọi người.