Giới thiệu

Trước khi chúng tôi bắt đầu, bạn đã hiểu được các yếu tố ảnh hưởng đến hiệu suất của MySQL, để bạn có thể học cách điều chỉnh và tối ưu hóa máy chủ MySQL theo đúng cách và nhận được kết quả đúng. Các yếu tố chính bao gồm:


  • Khối lượng dữ liệu được lấy
  • Tài nguyên có sẵn, tức là CPU, Bộ nhớ (RAM)
  • Tải chạy bởi máy chủ MySQL>

Nguyên nhân của hiệu suất máy chủ MySQL kém bao gồm:

  • Thiết kế cơ sở dữ liệu kém
  • Nút thắt phần cứng
  • Mã hóa kém
  • Chỉ số kém
  • Cấu hình không hiệu quả

Lưu ý đặc biệt: như bạn có thể thấy, một vài trong số các yếu tố này có thể được giải quyết bằng cách nâng cấp lên một dịch vụ lưu trữ web tốt hơn. HostAdvice mang đến cho bạn các nhà cung cấp dịch vụ lưu trữ MySQL tốt nhất dựa trên đánh giá của chuyên gia và người dùng.

Cài đặt mysqltuner và tun-primer

Cài đặt mysqltuner

Mysqltuner là một kịch bản Perl điều chỉnh hiệu suất cao của MySQL cung cấp một ảnh chụp nhanh về tình trạng sức khỏe của máy chủ MySQL và đưa ra các khuyến nghị cụ thể để cải thiện, tăng hiệu suất, ổn định và hiệu quả.

Chạy các lệnh dưới đây để cài đặt mysqltuner

$ cd / tmp
$ sudo wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl –quiet –no-check-cert –output-document = / usr / local / bin / mysqltuner
$ sudo chmod u + x / usr / local / bin / mysqltuner

Cài đặt điều chỉnh mồi

Tune Primer là tập lệnh shell lấy thông tin từ bên trong máy chủ MySQL và đưa ra khuyến nghị về việc điều chỉnh các biến máy chủ.

Chạy các lệnh dưới đây để cài đặt điều chỉnh mồi:

$ sudo apt cài đặt bc -y
$ sudo wget http://www.day32.com/MySQL/tuning-primer.sh –output-document = / usr / local / bin / tun-primer
$ sudo chmod u + x / usr / local / bin / tun-primer

Điều chỉnh và tối ưu hóa máy chủ mysql

Việc điều chỉnh máy chủ MySQL được thực hiện trên tệp cấu hình MySQL.

/etc/mysql/mysql.conf.d/mysqld.cnf

Chạy mysqltuner.

$ sudo mysqltuner
$ sudo mysqltuner
>> MySQLTuner 1.7.9 – Thiếu tá Hayden
>> Báo cáo lỗi, yêu cầu tính năng và tải xuống tại http://mysqltuner.com/
>> Chạy với ‘–help’ để có thêm tùy chọn và lọc đầu ra

[-] Bỏ qua phiên bản kiểm tra tập lệnh MySQLTuner
[OK] Đăng nhập bằng thông tin đăng nhập từ tài khoản bảo trì debian.
[OK] Hiện đang chạy phiên bản MySQL được hỗ trợ 5.7.22-0ubfox18.04.1
[OK] Hoạt động trên kiến ​​trúc 64 bit

——– Đăng nhập tập tin khuyến nghị ——————————————————————
[-] Tệp nhật ký: /var/log/mysql/error.log(18K)
[OK] Tệp nhật ký /var/log/mysql/error.log tồn tại
[OK] Tệp nhật ký /var/log/mysql/error.log có thể đọc được.
[OK] Tệp nhật ký /var/log/mysql/error.log không trống
[OK] Tệp nhật ký /var/log/mysql/error.log nhỏ hơn 32 Mb
[!!] /var/log/mysql/error.log chứa 14 cảnh báo.
[!!] /var/log/mysql/error.log chứa 3 lỗi.
[-] 3 start (s) được phát hiện trong /var/log/mysql/error.log
[-] 1) 2018-06-06T17: 55: 47.928847Z 0 [Lưu ý] / usr / sbin / mysqld: sẵn sàng cho các kết nối.
[-] 2) 2018-06-06T17: 55: 44.798684Z 0 [Lưu ý] mysqld: sẵn sàng cho các kết nối.
[-] 3) 2018-06-06T17: 55: 41.931105Z 0 [Lưu ý] mysqld: sẵn sàng cho các kết nối.
[-] 2 lần tắt máy được phát hiện trong /var/log/mysql/error.log
[-] 1) 2018-06-06T17: 55: 46.410548Z 0 [Lưu ý] mysqld: Tắt máy hoàn tất
[-] 2) 2018-06-06T17: 55: 43,758366Z 0 [Lưu ý] mysqld: Tắt máy hoàn tất

——– Thống kê công cụ lưu trữ —————————————————————–
[-] Trạng thái: + ARCHIVE + BLACKHOLE + CSV -FEDERATED + InnoDB + MEMOR + MRG_MYISAM + MyISAM + PERFORMANCE_SCHema
[-] Dữ liệu trong các bảng InnoDB: 16K (Bảng: 1)
[OK] Tổng số bảng phân mảnh: 0

——– Khuyến nghị bảo mật ——————————————————————
[OK] Không có tài khoản ẩn danh cho bất kỳ người dùng cơ sở dữ liệu nào
[OK] Tất cả người dùng cơ sở dữ liệu được gán mật khẩu
[-] Lỗi # 80860 MySQL 5.7: Tránh kiểm tra mật khẩu khi kích hoạt validate_password

——– Khuyến nghị bảo mật CVE ————————————————————–
[-] Bỏ qua do tùy chọn –cvefile không xác định

——– Chỉ số hiệu suất ———————————————————————–
[-] Lên đến: 9m 8 giây (110 q [0.201 qps], 38 Conn, TX: 187K, RX: 10K)
[-] Đọc / Viết: 98% / 2%
[-] Ghi nhật ký nhị phân bị vô hiệu hóa
[-] Bộ nhớ vật lý: 3.6G
[-] Bộ nhớ MySQL tối đa: 352,4M
[-] Bộ nhớ tiến trình khác: 175,4M
[-] Tổng số bộ đệm: 192.0M toàn cầu + 1.1M trên mỗi luồng (151 luồng tối đa)
[-] P_S Sử dụng bộ nhớ tối đa: 72B
[-] Sử dụng bộ nhớ Galera GCache Max: 0B
[OK] Mức sử dụng bộ nhớ tối đa đạt được: 194,1M (5,26% RAM đã cài đặt)
[OK] Sử dụng bộ nhớ tối đa có thể: 352,4M (9,54% RAM đã cài đặt)
[OK] Nhìn chung việc sử dụng bộ nhớ có thể với quy trình khác tương thích với bộ nhớ khả dụng
[OK] Truy vấn chậm: 0% (0/110)
[OK] Sử dụng cao nhất các kết nối khả dụng: 1% (2/151)
[OK] Kết nối bị hủy: 0,00% (0/38)
[!!] phân giải tên đang hoạt động: độ phân giải tên ngược được tạo cho mỗi kết nối mới và có thể làm giảm hiệu suất
[!!] Bộ đệm truy vấn có thể bị tắt theo mặc định do tranh chấp mutex.
[!!] Hiệu quả bộ đệm truy vấn: 0,0% (0 bộ nhớ cache / 49 lựa chọn)
[OK] Truy vấn mận bộ nhớ cache mỗi ngày: 0
[OK] Sắp xếp yêu cầu các bảng tạm thời: 0% (0 loại tạm thời / 2 loại)
[OK] Không tham gia mà không có chỉ mục
[OK] Các bảng tạm thời được tạo trên đĩa: 4% (36 trên đĩa / tổng số 846)
[OK] Tốc độ nhấn bộ đệm của luồng: 94% (2 kết nối được tạo / 38)
[OK] Tốc độ nhấn bộ đệm bảng: 95% (591 mở / 620 mở)
[OK] Mở giới hạn tệp được sử dụng: 1% (50 / 5K)
[OK] Khóa bảng có được ngay lập tức: 100% (158 ngay lập tức / 158 khóa)

——– Lược đồ hiệu suất ————————————————————————
[-] Bộ nhớ được sử dụng bởi P_S: 72B
[-] Lược đồ Sys được cài đặt.

——– Số liệu ThreadPool ————————————————————————
[-] Chỉ số ThreadPool bị vô hiệu hóa.

——– Số liệu MyISAM —————————————————————————-
[!!] Bộ đệm chính được sử dụng: 18,3% (sử dụng 3M / bộ đệm 16M)
[OK] Kích thước bộ đệm chính / tổng chỉ số MyISAM: 16.0M / 43.0K
[!!] Đọc Tỷ lệ nhấn bộ đệm chính: 94,1% (118 bộ nhớ cache / 7 lần đọc)

——– Số liệu của InnoDB —————————————————————————-
[-] InnoDB được bật.
[-] Đồng thời chủ đề InnoDB: 0
[OK] Tệp InnoDB trên mỗi bảng được kích hoạt
[OK] Kích thước dữ liệu / vùng đệm của InnoDB: 128.0M / 16.0K
[!!] Kích thước tệp nhật ký Ratio InnoDB / Kích thước nhóm bộ đệm InnoDB (75%): 48.0M * 2 / 128.0M phải bằng 25%
[OK] Các trường hợp nhóm bộ đệm InnoDB: 1
[-] Số lượng bộ đệm Pool InnoDB Chunk: 1 cho 1 Instance Pool Buffer (s)
[OK] Innodb_buffer_pool_size được căn chỉnh với Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Đọc hiệu quả bộ đệm: 88,39% (1933 lượt truy cập / tổng số 2187)
[!!] Hiệu suất Nhật ký ghi của InnoDB: 0% (tổng 5 lần truy cập / 0)
[OK] Nhật ký InnoDB chờ: 0,00% (0 chờ / 5 ghi)

——– Số liệu của AriaDB —————————————————————————-
[-] AriaDB bị vô hiệu hóa.

——– Số liệu TokuDB —————————————————————————-
[-] TokuDB bị vô hiệu hóa.

——– Số liệu XtraDB —————————————————————————-
[-] XtraDB bị vô hiệu hóa.

——– Số liệu của RocksDB —————————————————————————
[-] RocksDB bị vô hiệu hóa.

——– Số liệu nhện —————————————————————————-
[-] Nhện bị vô hiệu hóa.

——– Kết nối số liệu —————————————————————————
[-] Kết nối bị tắt.

——– Số liệu Galera —————————————————————————-
[-] Galera bị vô hiệu hóa.

——– Số liệu nhân rộng ———————————————————————–
[-] Sao chép đồng bộ Galera: KHÔNG
[-] Không có nô lệ sao chép cho máy chủ này.
[-] Định dạng Binlog: ROW
[-] Hỗ trợ XA được bật: BẬT
[-] Bản sao nhân bản bán đồng bộ: Không được kích hoạt
[-] Slave nhân rộng bán đồng bộ: Không được kích hoạt
[-] Đây là một máy chủ độc lập

——– khuyến nghị —————————————————————————
Khuyến nghị chung:
Kiểm soát (các) dòng cảnh báo vào tập tin /var/log/mysql/error.log
Kiểm soát (các) dòng lỗi vào tệp /var/log/mysql/error.log
MySQL đã được bắt đầu trong vòng 24 giờ qua – các khuyến nghị có thể không chính xác
Chỉ định cấu hình tài khoản của bạn bằng ip hoặc mạng con, sau đó cập nhật cấu hình của bạn với Skip-name-notify = 1
Trước khi thay đổi innodb_log_file_size và / hoặc innodb_log_files_in_group hãy đọc điều này: http://bit.ly/2wgkDvS
Các biến để điều chỉnh:
truy vấn_cache_size (= 0)
truy vấn_cache_type (= 0)
truy vấn_cache_limit (> 1M hoặc sử dụng các tập kết quả nhỏ hơn)
innodb_log_file_size phải là (= 16M) nếu có thể, do đó, tổng kích thước tệp nhật ký của InnoDB bằng 25% kích thước nhóm bộ đệm.

Tập trung vào phần cuối của đầu ra trong phần Khuyến nghị khuyến nghị và kiểm tra các biến cần tăng.

Tạo một bản sao lưu của tệp cấu hình mysql và chỉnh sửa tệp cấu hình thực tế.

$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Điều chỉnh biến số tập tin theo các khuyến nghị. Bạn có thể nhận xét các giá trị biến mặc định và tăng giá trị biến lên gấp đôi giá trị mặc định. Trên thực tế, có thể mất vài ngày để đưa ra các giá trị tốt nhất cho máy chủ của bạn.

Sau khi thực hiện các thay đổi, khởi động lại máy chủ mysql.

$ sudo systemctl khởi động lại mysql

Lưu ý đặc biệt: Nếu không khởi động lại được, hãy quay lại và nhận xét các giá trị mới và bắt đầu điều chỉnh từng cái một và xem điều chỉnh biến nào gây ra lỗi máy chủ

Nếu máy chủ tải lại thành công, hãy chạy lại mysqltuner và xem liệu có thêm khuyến nghị nào liên quan đến việc điều chỉnh giá trị biến không.

Làm quá trình tương tự để điều chỉnh mồi.

Chạy mồi điều chỉnh

$ sudo tun-primer
$ sudo tun-primer

— MYSQL HIỆU QUẢ HOẠT ĐỘNG —
– Bởi: Matthew Montgomery –

Phiên bản MySQL 5.7.22-0ubfox18.04.1 x86_64

Thời gian hoạt động = 0 ngày 0 giờ 1 phút 49 giây
Trung bình qps = 0
Tổng số câu hỏi = 15
Chủ đề được kết nối = 1

Cảnh báo: Máy chủ đã không chạy trong ít nhất 48 giờ.
Có thể không an toàn khi sử dụng các khuyến nghị này

Để tìm hiểu thêm thông tin về cách mỗi trong số này
biến thời gian thực hiện hiệu ứng truy cập:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
Truy cập http://www.mysql.com/products/enterprise/advisors.html
để biết thông tin về Dịch vụ tư vấn và giám sát doanh nghiệp của MySQL

SỐ LƯỢNG
Nhật ký truy vấn chậm KHÔNG được bật.
Long_query_time hiện tại = 10,000000 giây.
Bạn có 0 trên 36 mất nhiều hơn 10,000000 giây. hoàn thành
Long_query_time của bạn có vẻ ổn

Nhật ký cập nhật BINary
Nhật ký cập nhật nhị phân KHÔNG được bật.
Bạn sẽ không thể phục hồi kịp thời
Xem http://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

CÔNG NHÂN THREADS
Chủ đề hiện tại_cache_size = 8
Chủ đề hiện tại_cached = 0
Chủ đề hiện tại_per_sec = 0
Chủ đề lịch sử_per_sec = 0
Thread_cache_size của bạn là tốt

Tối đa KẾT NỐI
Max_connections hiện tại = 151
Chủ đề hiện tại_connected = 1
Lịch sử max_use_connections = 1
Số lượng kết nối được sử dụng là 0% mức tối đa được cấu hình.
Bạn đang sử dụng ít hơn 10% số max_connections được định cấu hình của mình.
Giảm max_connections có thể giúp tránh phân bổ bộ nhớ quá mức
Xem "SỬ DỤNG NHỚ" phần để đảm bảo bạn không phân bổ quá mức

Không hỗ trợ InnoDB được kích hoạt!

SỬ DỤNG NHỚ
Bộ nhớ tối đa từng được phân bổ: 177 M
Cấu hình bộ đệm tối đa cho mỗi luồng: 160 M
Cấu hình bộ đệm toàn cầu Max: 176 M
Cấu hình giới hạn bộ nhớ tối đa: 336 M
Bộ nhớ vật lý: 3,60 G
Giới hạn bộ nhớ tối đa dường như nằm trong định mức chấp nhận được

BUFFER chính
Không gian chỉ số MyISAM hiện tại = 43 K
Key_buffer_size hiện tại = 16 M
Tỷ lệ bỏ lỡ bộ đệm chính là 1: 2
Tỷ lệ không có bộ đệm chính = 81%
Key_buffer_size của bạn có vẻ ổn

CÂU CHUYỆN
Bộ đệm truy vấn được bật
Truy vấn hiện tại_cache_size = 16 M
Truy vấn hiện tại_cache_use = 16 K
Truy vấn hiện tại_cache_limit = 1 M
Bộ đệm truy vấn hiện tại Tỷ lệ lấp đầy bộ nhớ = .10%
Truy vấn hiện tại_cache_min_res_unit = 4 K
Truy vấn_cache_size của bạn dường như quá cao.
Có lẽ bạn có thể sử dụng các tài nguyên này ở nơi khác
MySQL sẽ không lưu trữ kết quả truy vấn lớn hơn kích thước query_cache_limit

HOẠT ĐỘNG NGẮN
Sắp xếp hiện tại_buffer_size = 256 K
Hiện tại read_rnd_buffer_size = 256 K
Không có hoạt động sắp xếp đã được thực hiện
Sắp xếp bộ đệm có vẻ ổn

THAM GIA
Tham gia hiện tại_buffer_size = 260,00 K
Bạn đã có 0 truy vấn trong đó tham gia không thể sử dụng chỉ mục đúng cách
Tham gia của bạn dường như đang sử dụng chỉ mục đúng cách

MỞ GIỚI HẠN
Open_files_limit = 5000 tệp hiện tại
Open_files_limit thường được đặt thành ít nhất là 2x-3x
của bảng_cache nếu bạn sử dụng MyISAM nặng.
Giá trị open_files_limit của bạn có vẻ ổn

BẢNG BẢNG
Bảng_open_cache hiện tại = 2000 bảng
Bảng_def định_cache hiện tại = 1400 bảng
Bạn có tổng cộng 119 bảng
Bạn có 249 bàn mở.
Giá trị table_cache có vẻ ổn

BẢNG TEMP
Max_heap_table_size hiện tại = 16 M
Hiện tại tmp_table_size = 16 M
Trong số 361 bảng tạm thời, 6% được tạo trên đĩa
Tạo tỷ lệ bảng tmp đĩa có vẻ tốt

BẢNG QUÉT
Hiện tại read_buffer_size = 128 K
Tỷ lệ quét bảng hiện tại = 673: 1
read_buffer_size có vẻ ổn

BẢNG KHÓA
Tỷ lệ chờ khóa hiện tại = 0: 219
Khóa bàn của bạn có vẻ ổn

Lưu ý đặc biệt: Tập trung nhiều hơn vào văn bản được tô sáng màu đỏ và màu vàng vì nó đưa ra các khuyến nghị về cách tối ưu hóa và cải thiện hiệu suất mysql. Hay nói, là một tài tài của, qua, qua, qua một tài khác, qua giữ, qua một tài khác

Điều chỉnh biến của tệp theo các khuyến nghị. Bạn có thể nhận xét các giá trị biến mặc định và điều chỉnh các giá trị biến như được đề xuất trong các đề xuất. Hay nói, là một tài tài của, qua, qua, qua một tài khác, qua giữ, qua một tài khác

Sau khi thực hiện các thay đổi, khởi động lại máy chủ MySQL.

$ sudo systemctl khởi động lại mysql

Lưu ý đặc biệt: Nếu không khởi động lại được, hãy quay lại và nhận xét các giá trị mới và bắt đầu điều chỉnh từng cái một và xem điều chỉnh biến nào gây ra lỗi máy chủ.

Nếu máy chủ khởi động lại thành công, hãy chạy lại tuneprimer và xem liệu có thêm khuyến nghị nào liên quan đến việc điều chỉnh giá trị biến không.

Phần kết luận

Điều rất quan trọng cần lưu ý là các công cụ điều chỉnh mysqltuner hoặc tun-primer không phải là giải pháp cho máy chủ MySQL hoạt động kém, mà là các tập lệnh để hướng dẫn bạn tinh chỉnh máy chủ MySQL.

Để có hiệu suất tốt nhất,

  • Thực hiện đánh giá kỹ lưỡng các truy vấn được gửi đến máy chủ và tối ưu hóa cấu trúc / thiết kế cơ sở dữ liệu và truy vấn SQL
  • Đánh giá các yếu tố ảnh hưởng đến hiệu suất của MySQL như được giải thích trong phần giới thiệu.
  • Xem xét nâng cấp phần cứng của máy chủ vật lý để có hiệu suất tốt hơn và tối ưu hóa cấu hình MySQL dựa trên thông số kỹ thuật phần cứng.
  • Nghiên cứu thêm về cách điều chỉnh cấu hình MySQL dựa trên các ứng dụng sử dụng máy chủ MySQL.

Rất khuyến khích nên thường xuyên chạy công cụ mysqltuner hoặc tunprimer trong khoảng 48 giờ sau khi khởi động lại máy chủ MySQL, để có được thống kê sử dụng có ý nghĩa.

Kiểm tra 3 dịch vụ lưu trữ MySQL hàng đầu sau:

Lưu trữ A2

Giá khởi điểm:
$ 3,92


độ tin cậy
9,3


Giá cả
9.0


Thân thiện với người dùng
9,3


Ủng hộ
9,3


Đặc trưng
9,3

Đọc đánh giá

Ghé thăm Hosting A2

FastComet

Giá khởi điểm:
$ 2,95


độ tin cậy
9,7


Giá cả
9,5


Thân thiện với người dùng
9,7


Ủng hộ
9,7


Đặc trưng
9,6

Đọc đánh giá

Ghé thăm FastComet

Chủ nhà

Giá khởi điểm:
0,99 đô la


độ tin cậy
9,3


Giá cả
9,3


Thân thiện với người dùng
9,4


Ủng hộ
9,4


Đặc trưng
9,2

Đọc đánh giá

Ghé thăm Hostinger

Bài viết hướng dẫn liên quan

  • Cách điều chỉnh và tối ưu hóa hiệu suất của MySQL 8.0 trên máy chủ CentOS 7
    chuyên gia
  • Cách sao lưu cơ sở dữ liệu MySQL của bạn trên VPS Ubuntu 18.04 hoặc máy chủ chuyên dụng
    Trung gian
  • Cách cài đặt MYSQL 8.0 và tạo cơ sở dữ liệu trên VPS Linux Ubuntu 18.04
    Trung gian
  • Cách vô hiệu hóa MySQL 5 "chế độ nghiêm ngặt" trên VPS Ubuntu 18.04 hoặc Máy chủ chuyên dụng
    Trung gian
  • Cách cài đặt PhpMyAdmin trên VPS Ubuntu 18.04 hoặc Máy chủ chuyên dụng
    Trung gian
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me