Điều chỉnh biến hệ thống MySQL cho hiệu suất cao

Đối với hầu hết các nhà phát triển ứng dụng, cơ sở dữ liệu là một bàn thờ của các vị thần quỷ tốt nhất không được chấp nhận. Nhưng nó không cần phải như vậy!


Những thứ khác như nhau, mức độ thoải mái của nhà phát triển với cơ sở dữ liệu cơ bản xác định mức thâm niên của họ. Cơ sở dữ liệu ít và kinh nghiệm mã hóa ít = nhà phát triển cơ sở; cơ sở dữ liệu ít và kinh nghiệm mã hóa tốt = nhà phát triển trung cấp; cơ sở dữ liệu tốt và kinh nghiệm mã hóa tốt = nhà phát triển cao cấp.

Đó là một thực tế phũ phàng mà thậm chí các nhà phát triển với 6-8 năm đấu tranh để giải thích sự phức tạp của trình tối ưu hóa truy vấn và thích nhìn lên trời khi được hỏi về điều chỉnh cơ sở dữ liệu.

Tại sao?

Đáng ngạc nhiên, lý do là sự lười biếng (mặc dù trong một số phần là).

Vấn đề là cơ sở dữ liệu là một lực lượng của riêng họ để tranh đấu. Ngay cả theo truyền thống, khi chỉ có các loại cơ sở dữ liệu quan hệ để đối phó, việc thành thạo chúng là một phép lạ và con đường sự nghiệp của riêng nó; những ngày này, chúng ta có rất nhiều loại cơ sở dữ liệu đến nỗi nó không thể mong đợi một linh hồn phàm trần duy nhất làm chủ được mọi thứ.

Điều đó nói rằng, có một cơ hội tốt mà bạn vẫn hài lòng với cơ sở dữ liệu quan hệ hoặc là một phần của nhóm có sản phẩm chạy trên cơ sở dữ liệu quan hệ một cách thỏa đáng trong một thời gian dài. Và trong chín trường hợp trên mười, bạn Were trên MySQL (hoặc MariaDB). Đối với những trường hợp này, lặn sâu hơn một chút dưới mui xe mang lại lợi ích to lớn trong việc tăng hiệu suất ứng dụng và đáng để học hỏi.

Tò mò? Hãy để Lặn lặn trong!

Không tò mò? Vâng, lặn trong dù sao, bởi vì sự nghiệp của bạn phụ thuộc vào nó! ��

Tối ưu hóa bộ đệm truy vấn MySQL

Hầu như tất cả các tối ưu hóa trong lĩnh vực máy tính đều đi vào bộ nhớ đệm. Ở một đầu, CPU duy trì một số mức bộ nhớ cache để tăng tốc tính toán và mặt khác, các ứng dụng web sử dụng mạnh mẽ các giải pháp bộ đệm như Redis để máy chủ kết quả được tính toán trước cho người dùng thay vì truy cập cơ sở dữ liệu mỗi lần.

Nhưng này, ngay cả cơ sở dữ liệu MySQL nghèo cũng có bộ đệm truy vấn riêng! Nghĩa là, mỗi khi bạn truy vấn thứ gì đó và dữ liệu vẫn còn cũ, MySQL sẽ cung cấp các kết quả được lưu trong bộ nhớ cache này thay vì chạy lại truy vấn, làm cho ứng dụng nhanh hơn một cách lố bịch.

Bạn có thể kiểm tra xem bạn có bộ đệm truy vấn khả dụng không (ghi chú, khả dụng, không được bật) trong cơ sở dữ liệu của bạn bằng cách chạy truy vấn này trong bảng điều khiển cơ sở dữ liệu:

MariaDB [(không có)]> HIỂN THỊ BIỂU TƯỢNG THÍCH ‘have_query_cache’;
+——————+——-+
| Biến_ame | Giá trị |
+——————+——-+
| have_query_cache | CÓ |
+——————+——-+

Vì vậy, bạn có thể thấy rằng tôi đang chạy MariaDB và tôi có sẵn bộ nhớ đệm truy vấn để bật. Nó rất khó để bạn có thể tắt nó nếu bạn sử dụng cài đặt MySQL tiêu chuẩn.

Bây giờ, hãy để xem nếu tôi có bộ đệm truy vấn thực sự được bật:

MariaDB [(không có)]> HIỂN THỊ BIỂU TƯỢNG THÍCH ‘query_cache_type’;
+——————+——-+
| Biến_ame | Giá trị |
+——————+——-+
| truy vấn_cache_type | TRÊN |
+——————+——-+

Vâng tôi đồng ý. Nhưng trong trường hợp bạn don lồng, bạn có thể bật nó lên bằng cách nói:

MariaDB [(không có)]> THIẾT LẬP truy vấn TOÀN CẦU_cache_type = ON;

Thật thú vị, biến này cũng chấp nhận một giá trị thứ ba biểu thị cho nhu cầu trên mạng, có nghĩa là MySQL sẽ chỉ lưu trữ những truy vấn mà chúng tôi nói với nó, nhưng chúng tôi đã thắng được ở đây.

Với điều này, bạn có bộ nhớ đệm truy vấn trên và đã thực hiện bước đầu tiên để thiết lập MySQL mạnh mẽ hơn! Tôi nói bước đầu tiên bởi vì trong khi bật nó là một cải tiến lớn, chúng tôi cần điều chỉnh bộ đệm ẩn truy vấn cho phù hợp với thiết lập của chúng tôi. Vì vậy, hãy để học hỏi để làm điều đó.

Biến quan tâm khác ở đây là query_cache_size, có chức năng tự giải thích:

MariaDB [(không có)]> HIỂN THỊ BIỂU TƯỢNG THÍCH ‘query_cache_size’;
+——————+———-+
| Biến_ame | Giá trị |
+——————+———-+
| truy vấn_cache_size | 16777216 |
+——————+———-+

Vì vậy, tôi có bộ đệm truy vấn có kích thước khoảng 16 MB. Lưu ý rằng ngay cả khi bộ nhớ đệm truy vấn được bật, nhưng kích thước này bằng 0, bộ nhớ đệm có hiệu quả tắt. Rằng tại sao chỉ kiểm tra một biến là đủ. Bây giờ, bạn nên đặt kích thước bộ đệm truy vấn, nhưng nó nên là bao nhiêu? Trước hết, xin lưu ý rằng tính năng bộ đệm ẩn truy vấn sẽ cần 4 KB để lưu trữ siêu dữ liệu của nó, vì vậy bất cứ điều gì bạn chọn phải ở trên mức đó.

Giả sử bạn đặt kích thước bộ đệm truy vấn là 500 KB:

MariaDB [(không có)]> THIẾT LẬP truy vấn TOÀN CẦU_cache_size = 500000;

Là làm điều này đủ nhiều? Chà, không, bởi vì công cụ truy vấn thực sự sẽ hoạt động như thế nào phụ thuộc vào một vài điều nữa:

  • Trước hết, biến query_cache_size phải đủ lớn để giữ kết quả truy vấn của bạn. Nếu nó quá nhỏ, sẽ không có gì được lưu trữ.
  • Thứ hai, nếu query_cache_size được đặt thành một số quá cao, sẽ có hai loại vấn đề: 1) Công cụ sẽ phải thực hiện thêm công việc lưu trữ và định vị kết quả truy vấn trong vùng bộ nhớ lớn này. 2) Nếu hầu hết các truy vấn dẫn đến kích thước nhỏ hơn nhiều, bộ đệm sẽ bị phân mảnh và lợi ích của việc sử dụng bộ đệm sẽ bị mất.

Làm thế nào để bạn biết bộ nhớ cache đang bị phân mảnh? Kiểm tra tổng số khối trong bộ đệm như thế này:

MariaDB [(không có)]> hiển thị trạng thái như ‘Qcache_total_blocks’;
+———————+——-+
| Biến_ame | Giá trị |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Nếu số lượng rất cao, bộ đệm bị phân mảnh và cần được xóa.

Vì vậy, để tránh những vấn đề này, hãy đảm bảo rằng kích thước của query_cache_size được chọn một cách khôn ngoan. Nếu bạn cảm thấy thất vọng vì tôi đã không để lại cho bạn một con số cụ thể ở đây, tôi sẽ sợ rằng cách thức của mọi thứ một khi bạn chuyển qua phát triển và bước vào kỹ thuật. Bạn phải xem xét ứng dụng mà bạn đang chạy và xem kích thước truy vấn cho các kết quả truy vấn quan trọng là gì và sau đó đặt số này. Và thậm chí sau đó bạn có thể sẽ phạm sai lầm. ��

Luồng, luồng chủ đề, chờ đợi và thời gian chờ

Đây có lẽ là phần thú vị nhất về cách thức hoạt động của MySQL và làm cho nó đúng nghĩa là làm cho ứng dụng của bạn nhanh hơn nhiều lần!

Luồng

MySQL là một máy chủ đa luồng. Điều đó có nghĩa là, mỗi khi có một kết nối mới đến máy chủ MySQL, nó sẽ mở một luồng mới với dữ liệu kết nối và chuyển một tay cầm cho nó đến máy khách (chỉ trong trường hợp bạn không biết mình đang xử lý một luồng nào, hãy xem điều này). Sau đó, khách hàng sẽ gửi tất cả các truy vấn qua chuỗi này và nhận kết quả. Điều này dẫn chúng ta đến một câu hỏi tự nhiên: MySQL có thể quay được bao nhiêu luồng? Câu trả lời nằm ở phần tiếp theo.

Hồ bơi

Không có chương trình nào trong một hệ thống máy tính có thể mở nhiều chủ đề như nó muốn. Lý do là gấp đôi: 1) Chủ đề tốn bộ nhớ (RAM) và hệ điều hành vừa giành được GẠCH cho phép bạn đi điên cuồng và ăn hết tất cả. 2) Tự mình quản lý một triệu luồng là một nhiệm vụ lớn và nếu máy chủ MySQL có thể tạo ra nhiều luồng đó, nó sẽ chết khi cố gắng xử lý chi phí.

Để tránh những vấn đề này, MySQL đi kèm với một nhóm luồng – một số luồng cố định là một phần của nhóm lúc bắt đầu. Các yêu cầu kết nối mới khiến MySQL nhận một trong các luồng này và trả về dữ liệu kết nối và nếu tất cả các luồng được sử dụng hết, các kết nối mới sẽ tự nhiên bị từ chối. Hãy để xem các nhóm chủ đề lớn như thế nào:

ariaDB [(không có)]> hiển thị các biến như ‘thread_pool_size’;
+——————+——-+
| Biến_ame | Giá trị |
+——————+——-+
| chủ đề_pool_size | 4 |
+——————+——-+

Vì vậy, máy của tôi cho phép tối đa bốn kết nối cùng một lúc. Thật thú vị khi lưu ý rằng số 4 xuất phát từ việc tôi có bộ xử lý bốn lõi, điều đó có nghĩa là máy tính của tôi chỉ có thể chạy 4 tác vụ song song cùng một lúc (Tôi nói về các tác vụ thực sự song song ở đây, chứ không phải đồng thời). Lý tưởng nhất, đây là giới hạn sẽ thúc đẩy giá trị của thread_pool_size, nhưng trên các máy tăng cường hơn, nó có lợi cho một điểm. Nếu bạn muốn không làm cho tất cả các kết nối mới phải chờ và có thể đạt được một số hiệu suất (một lần nữa, đây là một lĩnh vực mà bạn có thể đánh giá tốt nhất dựa trên hiệu suất của ứng dụng trên ứng dụng của bạn khi tải), việc đẩy nó lên tới 8 có thể là một ý tưởng hay.

Tuy nhiên, đặt nó ngoài 16 là một ý tưởng tồi tệ trừ khi bạn có một máy 32 lõi, vì hiệu suất giảm đáng kể. Lỗ thỏ của các nhóm luồng trong MySQL đi sâu, nhưng nếu bạn quan tâm, ở đây một cuộc thảo luận chi tiết hơn.

Chờ đợi và hết giờ

Khi một luồng đã được tạo và đính kèm vào máy khách, nó sẽ lãng phí tài nguyên nếu máy khách không gửi truy vấn nào trong vài giây (hoặc phút) tiếp theo. Kết quả là, MySQL chấm dứt kết nối sau một thời gian không hoạt động. Điều này được kiểm soát bởi biến Wait_timeout:

MariaDB [(không có)]> hiển thị các biến như ‘chờ%’;
+—————+——-+
| Biến_ame | Giá trị |
+—————+——-+
| chờ đợi 28800 |
+—————+——-+

Giá trị kết quả được tính bằng giây. Vì vậy, có, mặc định MySQL được đặt để chờ hơn 8 giờ trước khi cắt đứt dây! Điều này có thể tốt nếu bạn có các truy vấn dài và thực sự muốn chờ đợi chúng (nhưng ngay cả khi đó, tám giờ là vô lý!) Nhưng rất tệ trong hầu hết các trường hợp. Khi một truy vấn được chạy, giá trị này được đặt thành 0 (nghĩa là mãi mãi), nhưng nói chung, giá trị này phải được đặt thành giá trị rất thấp (ví dụ: 5 giây hoặc thậm chí ít hơn) để giải phóng kết nối cho các quy trình khác.

Điều chỉnh các bảng tạm thời

Hãy bắt đầu với các bảng tạm thời trong MySQL là gì.

Giả sử chúng ta có một MySQL có cấu trúc như thế này: BẢNG MỘT ĐOÀN (BẢNG B INNER THAM GIA C). Đó là, chúng tôi quan tâm đến việc tham gia các bảng B và C, và sau đó thực hiện kết hợp kết quả với bảng A. Bây giờ, trước tiên, MySQL sẽ tiến hành tham gia các bảng B và C, nhưng trước khi có thể thực hiện kết hợp, nó cần để lưu trữ dữ liệu này ở đâu đó. Đây là nơi các bảng tạm thời xuất hiện – MySQL sử dụng chúng để lưu trữ dữ liệu ở các giai đoạn trung gian trong các truy vấn phức tạp tạm thời và khi truy vấn kết thúc, bảng tạm thời này sẽ bị loại bỏ.

Bây giờ câu hỏi là: tại sao chúng ta phải bận tâm với tất cả điều này?

Đơn giản vì bảng tạm thời, chỉ là kết quả truy vấn, là dữ liệu đang được MySQL sử dụng trong tính toán và tốc độ truy cập của nó (trong số các giới hạn khác) sẽ xác định tốc độ truy vấn được thực hiện nhanh như thế nào. Chẳng hạn, lưu trữ bảng tạm thời trong RAM sẽ nhanh hơn nhiều lần so với lưu trữ trên đĩa.

Có hai biến điều khiển hành vi này:

MariaDB [(không có)]> hiển thị các biến như ‘MariaDB [(none)]> hiển thị các biến như ‘tmp_table_size’;
+—————-+———-+

| Biến_ame | Giá trị |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Biến_ame | Giá trị |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(không có)]> hiển thị các biến như ‘tmp_table_size’;
+—————-+———-+
| Biến_ame | Giá trị |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Cái đầu tiên, max_heap_table_size, cho chúng ta biết bao nhiêu RAM có thể được sử dụng bởi một bảng MySQL (ở đây, heap, ở đây đề cập đến cấu trúc dữ liệu được sử dụng trong phân bổ và quản lý RAM – đọc thêm đây), trong khi cái thứ hai, tmp_table_size, hiển thị kích thước tối đa của bảng tạm thời là gì. Trong trường hợp của tôi, cả hai đều được đặt thành 16 MB, mặc dù điểm tôi đang cố gắng làm cho nó chỉ tăng tmp_table_size sẽ không hoạt động như tổng thể, MySQL vẫn sẽ bị giới hạn bởi max_table_heap_size.

Bây giờ đến thời điểm: nếu các bảng tạm thời được tạo lớn hơn giới hạn cho phép của các biến này, MySQL sẽ bị buộc phải ghi chúng vào đĩa cứng, dẫn đến hiệu suất cực kỳ kém. Công việc của chúng tôi bây giờ rất đơn giản: làm hết sức để đoán kích thước dữ liệu chính xác nhất cho các bảng tạm thời và điều chỉnh các biến này đến giới hạn đó. Tuy nhiên, tôi muốn cảnh báo về sự vô lý: đặt giới hạn này thành 16 GB (giả sử bạn có nhiều RAM này) khi hầu hết các bảng tạm thời của bạn có kích thước dưới 24 MB là ngu ngốc – bạn chỉ đang lãng phí RAM có thể ‘ đã được sử dụng bởi các truy vấn hoặc bộ phận khác của hệ thống (ví dụ: bộ đệm).

Phần kết luận

Nó không thể bao gồm tất cả các biến hệ thống trong một bài viết, hoặc thậm chí tất cả các biến quan trọng trong một bài viết khi tài liệu MySQL tự kéo dài vài nghìn từ. Mặc dù chúng tôi đã đề cập đến một số biến phổ quát ở đây, nhưng tôi khuyến khích bạn xem xét các biến hệ thống cho công cụ mà bạn sử dụng (InnoDB hoặc là MyISAM).

Kết quả mong muốn nhất của tôi khi viết bài viết này là để bạn lấy đi ba điều:

  1. MySQL là một phần mềm điển hình hoạt động trong giới hạn do hệ điều hành đặt ra. Nó không phải là một chương trình bí ẩn nào đó mà Chúa biết và không thể chế ngự được. Ngoài ra, may mắn thay, nó không khó hiểu về cách thức mà nó thiết lập và được kiểm soát bởi các biến hệ thống của nó.
  2.  Không có cài đặt nào sẽ giúp cài đặt MySQL của bạn được phóng to. Bạn không có lựa chọn nào khác ngoài nhìn vào các hệ thống đang chạy của mình (hãy nhớ rằng, tối ưu hóa xuất hiện sau khi ứng dụng được sản xuất chứ không phải trước đó), đưa ra những dự đoán và đo lường tốt nhất và sống với thực tế rằng nó không bao giờ hoàn hảo.
  3. Điều chỉnh các biến không phải là cách duy nhất để tối ưu hóa MySQL – các truy vấn viết hiệu quả là một vấn đề lớn, nhưng nó lại là thứ gì đó mà tôi sẽ đề cập trong một bài viết khác. Nhưng vấn đề là, ngay cả khi bạn đã thực hiện một phân tích giống như thần và điều chỉnh các thông số này ở mức tốt nhất, thì nó vẫn có thể giúp bạn tạm dừng mọi thứ.

Điều gì làm biến hệ thống yêu thích của bạn để điều chỉnh? ��

THẺ

  • Cơ sở dữ liệu

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map