Hướng dẫn tối ưu hoá hiệu suất MariaDB / MySQL trên Plesk
Hiệu suất của MariaDB là điều mà rất nhiều mục đích sử dụng hiện đang quan tâm đến việc cải thiện. Kể từ khi nó nổi lên như một nhánh của MySQL, cộng đồng cơ sở dữ liệu nguồn mở đã chứng kiến sự gia tăng lớn trong việc tiếp nhận. Bắt đầu cuộc sống như một sự thay thế thả vào, MariaDB đã bắt đầu phân biệt chính nó với MySQL và đặc biệt là kể từ khi MariaDB 10.2 được phát hành.
Nhưng bỏ qua vấn đề đó, vẫn không có điểm khác biệt rõ ràng nào giữa MariaDB và MySQL, vì cả hai đều sử dụng các công cụ tương thích lẫn nhau có thể chạy tự nhiên với nhau. Điều đó có nghĩa là không có gì ngạc nhiên khi việc điều chỉnh hiệu suất của MariaDB phản ánh điều đó của MySQL. Hãy xem xét việc tăng hiệu suất của MariaDB và của các hệ thống chạy trong môi trường Linux.
MariaDB – Tối ưu hóa hệ thống và phần cứng
Bạn có thể tăng hiệu suất của MariaDB bằng cách cải thiện phần cứng của mình. Đây là thứ tự đúng để làm điều đó trong:
CẤU HÌNH RAM
Nếu bạn muốn điều chỉnh Biến hệ thống máy chủ để cung cấp cho bạn bộ đệm bảng và khóa lớn hơn, thì bộ nhớ (và rất nhiều bộ nhớ) là thứ bạn cần. Nhiều bộ nhớ hơn có nghĩa là ít bộ nhớ đệm đĩa hơn, đây là tùy chọn chậm hơn đáng kể.
Nhưng xin lưu ý rằng chỉ thêm bộ nhớ bổ sung có thể không mang lại cho bạn những cải tiến hiệu suất MariaDB ngoạn mục mà bạn mong đợi nếu bạn không đặt các biến máy chủ một cách chính xác để tận dụng lợi thế của nó.
Ngoài ra, hãy nhớ rằng việc lấp đầy các khe cắm RAM bổ sung trên bo mạch chủ sẽ nâng cao tần số bus, nghĩa là độ trễ giữa CPU và RAM sẽ cao hơn. Thay vào đó, tốt hơn là sử dụng các thanh RAM lớn nhất mà bạn có thể tìm thấy cho mỗi khe cắm.
CẤU HÌNH Ổ ĐĨA / DISK
Đĩa luôn là một nút thắt cổ chai hệ thống so với RAM vì chúng không nhanh bằng. Nhưng điều đó không có nghĩa là bạn không thể cải thiện tốc độ của chúng. Con số quan trọng nhất cần lưu ý là thời gian tìm kiếm của đĩa (cho bạn biết đầu đọc có thể di chuyển nhanh như thế nào để lấy dữ liệu), vì vậy hãy chọn các đĩa có thời gian tìm kiếm thấp nhất mà bạn có thể tìm thấy và nghĩ đến việc sử dụng các đĩa chuyên dụng cho nhật ký giao dịch và cả các tệp tạm thời.
Kết nối mạng
Song song với băng thông internet của bạn, ethernet nhanh làm giảm thời gian phản hồi yêu cầu ứng dụng khách của bạn và thời gian phản hồi sao chép của bạn để đọc nhật ký nhị phân trên các nô lệ. Thời gian phản hồi thấp hơn đặc biệt quan trọng với các cụm dựa trên Galera.
CPU
Thật khó để hình dung ra một tình huống mà việc sở hữu một bộ xử lý nhanh hơn sẽ không phải là một điều tốt, vì việc xử lý các phép tính nhanh hơn có nghĩa là nhiều dữ liệu được gửi đến máy khách sớm hơn. Nhưng trong khi tốc độ bộ xử lý là rất quan trọng thì tốc độ bus, kích thước bộ đệm và số lượng lõi cũng vậy.
Đặt bộ lập lịch I/O đĩa của bạn cho hiệu suất MariaDB
Bộ lập lịch I/O tối ưu hóa các yêu cầu truy cập đĩa bằng cách nhóm các yêu cầu I/O trong các khu vực tương tự trên đĩa. Điều này tăng tốc mọi thứ vì ổ đĩa chỉ phải đi đến một vị trí để tìm thấy những gì nó đang tìm kiếm, do đó, sẽ có ít thao tác trên đĩa hơn và thời gian phản hồi giảm đáng kể. Các công cụ được đề xuất để quản lý hiệu suất I/O của MariaDB là noop và deadline .
noop cho phép bạn kiểm tra xem các quyết định lập lịch trình I/O phức tạp của các bộ lập lịch trình khác có phải là nguyên nhân gây ra hồi quy trong I/O hay không. Trong một số trường hợp, nó có thể hữu ích cho các thiết bị tự lập lịch trình I/O, như bộ lưu trữ thông minh hoặc các thiết bị không dựa vào chuyển động cơ học, như SSD. Thông thường, bộ lập lịch I/O DEADLINE là lựa chọn phù hợp hơn cho các thiết bị này, nhưng do chi phí hoạt động ít hơn nên NOOP có thể nhận ra hiệu suất tốt hơn của MariaDB với các khối lượng công việc cụ thể.
DEADLINE là bộ lập lịch I/O được định hướng theo độ trễ. Mỗi yêu cầu I/O có thời hạn được chỉ định và những yêu cầu này thường được lưu trữ theo số ngành trong hàng đợi (đọc và ghi). Thuật toán DEADLINE duy trì hai hàng đợi bổ sung (đọc và ghi) trong đó các yêu cầu cũng được sắp xếp theo thời hạn. Miễn là không có yêu cầu nào hết thời gian, hàng đợi “sector” sẽ được sử dụng. Nếu thời gian chờ xảy ra, thì các yêu cầu từ hàng đợi “hạn chót” sẽ được phục vụ cho đến khi các yêu cầu hết hạn được thực hiện. Thuật toán thường ưu tiên đọc nhiều hơn ghi.
Đối với các thiết bị PCIe (ổ đĩa SSD NVMe), chúng có hàng đợi bên trong dài và dịch vụ nhanh nên chúng không nhận được bất kỳ lợi ích nào từ bộ lập lịch I/O. Chúng tôi khuyên bạn không nên có bất kỳ tham số cấu hình chế độ lập lịch rõ ràng nào.
Bạn có thể kiểm tra cài đặt lịch biểu của mình bằng:
<br data-mce-bogus="1">
cat /sys/block/${DEVICE}/queue/scheduler
Nó giống với đầu ra ví dụ này:
cat /sys/block/sda/queue/scheduler [noop] deadline cfq
Để biến nó thành vĩnh viễn, bạn cần chỉnh sửa tệp cấu hình /etc/default/grub. Tìm biến GRUB_CMDLINE_LINUX thêm “thang máy” như sau:
GRUB_CMDLINE_LINUX=”elevator=noop”
Tăng giới hạn tệp mở – Increase Open Files Limit
Để có hiệu suất máy chủ MariaDB tối ưu, bạn cần giữ tổng số kết nối máy khách, tệp cơ sở dữ liệu và tệp nhật ký dưới giới hạn bộ mô tả tệp tối đa của hệ điều hành (ulimit -n). Các hệ thống Linux giới hạn số lượng bộ mô tả tệp mà bất kỳ quy trình đơn lẻ nào có thể mở là 1.024. Trên các máy chủ cơ sở dữ liệu đang hoạt động (và đặc biệt là các máy chủ sản xuất), thật dễ dàng để đạt đến giới hạn hệ thống mặc định.
Để tạo thêm khoảng trống, hãy chỉnh sửa /etc/security/limits.conf và thêm hoặc chỉ định điều này:
mysql soft nofile 65535
mysql hard nofile 65535
Bạn sẽ cần khởi động lại hệ thống, sau đó xác nhận bằng cách chạy như sau:
$ ulimit -Sn
65535
$ ulimit -Hn
65535
Ngoài ra, bạn có thể muốn thiết lập điều này bằng cách sử dụng mysqld_safe nếu bạn đang bắt đầu quá trình mysqld thông qua mysqld_safe,
[mysqld_safe]open_files_limit=4294967295
hoặc nếu bạn đang sử dụng systemd,
sudo tee /etc/systemd/system/mariadb.service.d/limitnofile.conf <<EOF
[Service]
LimitNOFILE=infinity
EOF
sudo systemctl daemon-reload
Tối ưu hóa hệ thống tệp cho MariaDB
Ext4 và XFS là các hệ thống tệp được sử dụng phổ biến nhất trong môi trường Linux chạy MariaDB. Một số thiết lập cũng có sẵn để triển khai kiến trúc bằng cách sử dụng ZFS và BRTFS (như được tham chiếu trong tài liệu của MariaDB).
Ngoài ra, phần lớn các thiết lập cơ sở dữ liệu không cần ghi lại thời gian truy cập tệp, vì vậy nếu bạn muốn tắt tính năng này khi gắn ổ đĩa vào hệ thống, hãy chỉnh sửa tệp /etc/fstab. Ví dụ, trên một ổ có tên /dev/md2, nó sẽ trông như thế này:
/dev/md2 / ext4 defaults,noatime 0 0
Set max_allowed_packet
MariaDB xử lý các gói theo cách tương tự như MySQL. Nó chia dữ liệu thành các gói, có nghĩa là máy khách cần biết giá trị max_allowed_packet, giá trị xác định kích thước tối đa của gói có thể được gửi. Máy chủ lưu trữ nội dung trong bộ đệm và kích thước tối đa của nó sẽ tương ứng với giá trị đó. Vượt quá giới hạn đó và ổ cắm sẽ đóng lại, hạn chế hiệu suất của MariaDB.
Nếu nó được đặt quá thấp thì truy vấn sẽ được kích hoạt, điều đó có nghĩa là kết nối máy khách sẽ bị dừng và đóng. Đó là lý do tại sao bạn có thể thấy các lỗi như ER_NET_PACKET_TOO_LARGE hoặc thấy rằng kết nối của bạn với máy chủ MySQL đã bị gián đoạn trong quá trình truy vấn. Vì vậy, một thiết lập lý tưởng là gì? Chúng tôi khuyên bạn nên bắt đầu với 512MiB. Nếu ứng dụng chỉ có nhu cầu thấp, hãy sử dụng giá trị mặc định (là 16MiB kể từ MariaDB 10.2.4) và chỉ thay đổi giá trị đó (thông qua phiên) khi nhu cầu dữ liệu của bạn có thể tăng cao hơn, chẳng hạn như với khối lượng công việc đòi hỏi khắt khe hơn khi các gói lớn cần được xử lý. Lưu ý rằng nếu giá trị max_allowed_packet quá nhỏ trên nô lệ, điều này cũng có thể khiến nô lệ cắt luồng I/O.
Sử dụng Threadpool
Trong một số trường hợp, kiểu điều chỉnh này có thể không phải lúc nào cũng phù hợp để có hiệu suất MariaDB tốt nhất. Nhóm luồng hoạt động tốt nhất khi các truy vấn khá ngắn và tải bị ràng buộc bởi CPU (khối lượng công việc OLTP). Nếu nó không bị ràng buộc bởi CPU, bạn vẫn có thể cần đặt giới hạn về số lượng luồng để tiết kiệm bộ nhớ cho bộ đệm bộ nhớ cơ sở dữ liệu.
Threadpool lý tưởng phù hợp với các tình huống mà bạn đang tìm cách giảm chuyển đổi ngữ cảnh trên hệ thống của mình và duy trì ít luồng hơn so với các máy khách. Nhưng con số này cũng không nên quá thấp, vì chúng tôi không muốn giới hạn việc sử dụng các CPU có sẵn. Do đó, lý tưởng để tăng hiệu suất MariaDB là có một luồng hoạt động cho mỗi CPU.
Đặt thread_pool_max_threads, thread_pool_min_threads cho số luồng tối đa và tối thiểu là điều bạn không thể thực hiện trong MySQL, điều này là duy nhất đối với MariaDB.
Biến thread_handling đặt cách máy chủ xử lý các luồng cho kết nối máy khách. Cũng như các luồng cho kết nối máy khách, nó cũng áp dụng cho một số luồng máy chủ nội bộ, bao gồm các luồng phụ của Galera.
Điều chỉnh bộ đệm bảng của bạn + max_connections
Nếu bạn thấy rằng đôi khi bạn nhìn thấy các sự kiện trong danh sách quy trình liên quan đến trạng thái Mở bảng và Đóng bảng, thì điều này có thể có nghĩa là bộ đệm ẩn bảng của bạn cần được tăng lên. Bạn cũng có thể theo dõi điều này thông qua lời nhắc máy khách mysql bằng cách chạy SHOW GLOBAL STATUS LIKE ‘Open%table%’; và giám sát các biến trạng thái.
Đối với max_connections, nếu ứng dụng của bạn cần nhiều kết nối đồng thời, hãy đặt giá trị này thành 500 để bắt đầu.
Đối với table_open_cache, hãy tính đến tổng số bảng của bạn cộng với các bảng bổ sung để tính các bảng tạm thời cũng có thể cần được lưu vào bộ đệm. Vì vậy, nếu bạn có 100 bảng, bạn nên chỉ định 300.
Đặt biến table_open_cache_instances của bạn thành 8. Điều này có thể làm giảm sự tranh chấp giữa các phiên và do đó cải thiện khả năng mở rộng. Bạn có thể phân vùng bộ đệm của các bảng đang mở, chia chúng thành nhiều phiên bản bộ đệm nhỏ hơn bằng cách sử dụng table_open_cache / table_open_cache_instances làm hướng dẫn của bạn.
Đối với InnoDB, table_definition_cache đặt giới hạn mềm cho tổng số phiên bản bảng đang mở trong bộ đệm của từ điển dữ liệu InnoDB. Giá trị mà bạn đặt sẽ xác định số lượng định nghĩa bảng có thể được lưu trữ trong bộ đệm ẩn định nghĩa. Nếu bạn sử dụng nhiều bảng, bạn có thể tạo bộ nhớ đệm định nghĩa bảng có kích thước lớn để mở bảng nhanh hơn. Bộ đệm định nghĩa bảng sử dụng ít không gian hơn và không sử dụng bộ mô tả tệp, trái ngược với bộ đệm bảng thông thường. Giá trị thấp nhất có thể là 400. Giá trị mặc định được lấy từ công thức bên dưới và được giới hạn ở 2000:
MIN(400 + table_open_cache / 2, 2000)
Nếu số lượng bảng đang mở lớn hơn giá trị trong table_definition_cache, thì cơ chế LRU sẽ bắt đầu đánh dấu bất kỳ trường hợp nào của bảng cần được xóa và cuối cùng loại bỏ chúng khỏi bộ đệm từ điển dữ liệu. Việc có giới hạn bộ nhớ cache như thế này giúp giảm thiểu các trường hợp khi các bảng ngốn bộ nhớ không được sử dụng nhiều sẽ chiếm quá nhiều dung lượng một cách không cần thiết và đó là chìa khóa để cải thiện hiệu suất của Maria DB. Có thể có nhiều phiên bản bảng với siêu dữ liệu được lưu trong bộ đệm ẩn hơn mức cho phép theo giới hạn được đặt trong table_definition_cache, vì các phiên bản bảng cha và con có mối quan hệ khóa ngoại không được đưa vào danh sách LRU và do đó sẽ không chịu trách nhiệm xóa khỏi bộ nhớ .Ngược lại với table_open_cache, table_definition_cache không sử dụng bộ mô tả tệp và nhỏ hơn rất nhiều.
Xử lý bộ đệm truy vấn
Chúng tôi nghĩ rằng việc tắt bộ đệm truy vấn để cải thiện hiệu suất của MariaDB là tùy chọn ưu tiên. Bạn cần đảm bảo rằng query_cache_type=OFF và query_cache_size=0 để bộ đệm truy vấn bị tắt hoàn toàn. Ngược lại với MySQL, MariaDB vẫn hỗ trợ bộ đệm truy vấn và không có kế hoạch sớm rút hỗ trợ cho nó. Có những người nghĩ rằng việc sử dụng bộ đệm truy vấn mang lại cho họ lợi ích về hiệu suất, nhưng như bài đăng này từ Percona đã chứng minh, bộ đệm truy vấn được bật sẽ làm tăng chi phí hoạt động và giảm hiệu suất của máy chủ.
Nếu bạn muốn sử dụng bộ đệm truy vấn, hãy đảm bảo rằng bạn giám sát nó bằng cách chạy SHOW GLOBAL STATUS LIKE ‘Qcache%’;. Qcache_inserts báo cáo về số lượng truy vấn đã được thêm vào bộ đệm truy vấn, Qcache_hits cho biết có bao nhiêu truy vấn đã sử dụng bộ nhớ cache và Qcache_lowmem_prunes chứa số lượng truy vấn đã bị loại bỏ do không đủ bộ nhớ. Theo thời gian, việc sử dụng bộ đệm truy vấn có thể khiến nó bị phân mảnh. Tỷ lệ Qcache_free_blocks trên Qcache_total_blocks cao có thể dẫn đến tình trạng phân mảnh tăng lên. Để chống phân mảnh nó, hãy chạy FLUSH QUERY CACHE. Điều này sẽ chống phân mảnh bộ nhớ đệm truy vấn mà không làm mất bất kỳ truy vấn nào và cải thiện hiệu suất của MariaDB.
Luôn để mắt đến máy chủ của bạn
Giám sát các nút MariaDB của bạn là rất quan trọng để duy trì hiệu suất tối ưu của MariaDB. Các công cụ giám sát phổ biến (chẳng hạn như Nagios, Zabbix hoặc PMM) dành cho những người muốn sử dụng các công cụ nguồn mở và miễn phí đều được ưa chuộng, nhưng đối với các công cụ cấp doanh nghiệp, chúng tôi muốn hướng bạn đến ClusterControl. Nó không chỉ cung cấp khả năng giám sát mà còn cung cấp cho bạn các lời khuyên, cảnh báo và cảnh báo về hiệu suất khi nhờ đó bạn có thể cải thiện hiệu suất MariaDB của mình.
Tổng kết
Việc cải thiện hiệu suất của MariaDB yêu cầu cách tiếp cận giống như cách bạn thực hiện với MySQL, ngoại trừ một số điểm khác biệt liên quan đến các phiên bản khác nhau. MariaDB đã đi theo con đường riêng của mình và khi làm như vậy, nó đã tự khẳng định mình là một lựa chọn đáng tin cậy trong cộng đồng, vì vậy việc điều chỉnh và tối ưu hóa để có hiệu suất MariaDB tốt hơn là điều mà ngày càng có nhiều người quan tâm.