Tham gia Đăng nhập
Điểm:1
Igor Mytyuk avatar Server

Sự cố máy chủ do sử dụng CPU MySQL

lá cờ gy
Igor Mytyuk

tiền đề:

Tôi không phải là DBA và tôi không sử dụng máy chủ, nhưng tôi là người duy nhất trong công ty có thể sử dụng một chút thứ đó.

Chúng tôi có một VPS Windows với các thông số kỹ thuật sau:

CPU: Intel Xeon E5-2630 v4 2.20GHz
RAM: 60GB
VẬY: Trung tâm dữ liệu Windows Server 2016
Ổ cứng: SSD 2TB

Ở đây tôi đã lưu trữ các ứng dụng web truy cập DB được lưu trữ trên cùng một máy chủ, các ứng dụng web được sử dụng bởi hơn - 1000 người dùng yêu cầu dữ liệu từ DB thông qua API ứng dụng web, phiên bản MySQL là: 8.0.20 (Máy chủ cộng đồng MySQL - GPL)

Và đây là my.ini

# Các giá trị điều chỉnh mặc định khác
# Tệp cấu hình phiên bản máy chủ MySQL
# --------------------------------------------- ---------------------
# Được tạo bởi Trình hướng dẫn cấu hình phiên bản máy chủ MySQL
# 
# Hướng dẫn cài đặt
# --------------------------------------------- ---------------------
# 
# Trên Linux, bạn có thể sao chép tệp này vào /etc/my.cnf để đặt các tùy chọn chung,
# mysql-data-dir/my.cnf để đặt các tùy chọn dành riêng cho máy chủ
# (@localstatedir@ cho cài đặt này) hoặc để
# ~/.my.cnf để đặt các tùy chọn dành riêng cho người dùng.
# 
# Trên Windows, bạn nên giữ tệp này trong thư mục cài đặt 
# máy chủ của bạn (ví dụ: C:\Program Files\MySQL\MySQL Server X.Y). Đến
# đảm bảo máy chủ đọc tệp cấu hình, sử dụng tùy chọn khởi động 
# "--defaults-tệp". 
# 
# Để chạy máy chủ từ dòng lệnh, hãy thực hiện điều này trong một 
# vỏ dòng lệnh, ví dụ:
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
# 
# Để cài đặt máy chủ dưới dạng dịch vụ Windows theo cách thủ công, hãy thực hiện thao tác này trong 
# vỏ dòng lệnh, ví dụ:
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
# 
# Và sau đó thực hiện điều này trong trình bao dòng lệnh để khởi động máy chủ, ví dụ:
# khởi động mạng MySQLXY
# 
# Hướng dẫn sửa file này
# --------------------------------------------- ---------------------
# 
# Trong tệp này, bạn có thể sử dụng tất cả các tùy chọn dài mà chương trình hỗ trợ.
# Nếu bạn muốn biết các tùy chọn mà chương trình hỗ trợ, hãy khởi động chương trình
# với tùy chọn "--help".
# 
# Thông tin chi tiết hơn về các tùy chọn riêng lẻ cũng có thể được
# tìm thấy trong sách hướng dẫn.
# 
# Để được tư vấn về cách thay đổi cài đặt, vui lòng xem
# https://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
# 
# PHẦN KHÁCH HÀNG
# --------------------------------------------- ---------------------
# 
# Các tùy chọn sau sẽ được đọc bởi các ứng dụng máy khách MySQL.
# Lưu ý rằng chỉ các ứng dụng khách do MySQL vận chuyển mới được đảm bảo
# để đọc phần này. Nếu bạn muốn chương trình máy khách MySQL của riêng bạn
# tôn vinh những giá trị này, bạn cần chỉ định nó như một tùy chọn trong quá trình
# Khởi tạo thư viện máy khách MySQL.
# 
[khách hàng]

#ống=

# ổ cắm=MYSQL

cổng = 3306

[mysql]
không có tiếng bíp =

# bộ ký tự mặc định=

# PHẦN MÁY CHỦ
# --------------------------------------------- ---------------------
# 
# Các tùy chọn sau sẽ được đọc bởi Máy chủ MySQL. Đảm bảo rằng
# bạn đã cài đặt máy chủ đúng cách (xem bên trên) nên nó đọc cái này 
# tệp.=
# 
# server_type=2
[mysqld]

# Ba tùy chọn tiếp theo loại trừ lẫn nhau đối với SERVER_PORT bên dưới.
# bỏ qua mạng =
# enable-name-pipe=
# bộ nhớ dùng chung =

# shared-memory-base-name=MYSQL

# Đường ống mà Máy chủ MySQL sẽ sử dụng
# ổ cắm=MYSQL

# Cổng TCP/IP mà Máy chủ MySQL sẽ lắng nghe
cổng = 3306

# Đường dẫn đến thư mục cài đặt. Tất cả các đường dẫn thường được giải quyết liên quan đến điều này.
# basedir="C:/Program Files/MySQL/MySQL Server 8.0/"

# Đường dẫn đến thư mục gốc của cơ sở dữ liệu
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Dữ liệu

# Bộ ký tự mặc định sẽ được sử dụng khi một lược đồ hoặc bảng mới được
# đã tạo và không có bộ ký tự nào được xác định
# ký tự-bộ-máy chủ=

# Plugin xác thực mặc định được sử dụng khi kết nối với máy chủ
default_authentication_plugin=mysql_native_password

# Công cụ lưu trữ mặc định sẽ được sử dụng khi tạo bảng mới khi
default-storage-engine=INNODB

# Đặt chế độ SQL thành nghiêm ngặt
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

# Ghi nhật ký chung và chậm.
log-output=TỆP

nhật ký chung = 0

general_log_file="VMI384596.log"

truy vấn chậm-log=1

slow_query_log_file="VMI384596-slow.log"

long_query_time=10

# Lỗi đăng nhập.
log-error="VMI384596.err"

# ***** Bản sao nhóm liên quan *****
# Chỉ định tên cơ sở để sử dụng cho tệp nhật ký nhị phân. Với ghi nhật ký nhị phân
# đã bật, máy chủ ghi lại tất cả các câu lệnh thay đổi dữ liệu thành nhị phân
# nhật ký, được sử dụng để sao lưu và sao chép.
log-bin="VMI384596-bin"

# ***** Bản sao nhóm liên quan *****
# Đặt định dạng ghi nhật ký nhị phân và có thể là bất kỳ một trong STATEMENT, ROW,
# hoặc HỖN HỢP. ROW được đề xuất cho Sao chép nhóm.
# binlog_format=

# ***** Bản sao nhóm liên quan *****
# Yêu cầu chủ viết tổng kiểm tra cho từng sự kiện trong nhật ký nhị phân.
# binlog_checksum hỗ trợ các giá trị KHÔNG (đã tắt) và CRC32.
# Mặc định là CRC32. Khi bị tắt (giá trị KHÔNG), máy chủ sẽ xác minh
# rằng nó chỉ ghi các sự kiện hoàn chỉnh vào nhật ký nhị phân bằng cách ghi
# và kiểm tra độ dài sự kiện (chứ không phải tổng kiểm tra) cho từng sự kiện.
# NONE phải được sử dụng với Sao chép nhóm.
# binlog_checksum=

# ***** Bản sao nhóm liên quan *****
# Tên cơ sở cho nhật ký chuyển tiếp. Máy chủ tạo tệp nhật ký chuyển tiếp trong
# chuỗi bằng cách thêm hậu tố số vào tên cơ sở. Nếu bạn chỉ định điều này
# tùy chọn, giá trị được chỉ định cũng được sử dụng làm tên cơ sở cho nhật ký chuyển tiếp
# tệp chỉ mục. Nhật ký chuyển tiếp tăng tốc độ bằng cách sử dụng cân bằng tải giữa các đĩa.
# relay_log=

# ***** Bản sao nhóm liên quan *****
# Chỉ định ID máy chủ. Đối với các máy chủ được sử dụng trong cấu trúc liên kết sao chép,
# bạn phải chỉ định một ID máy chủ duy nhất cho mỗi máy chủ sao chép, trong
# phạm vi từ 1 đến 2^32 - 1. �Unique� có nghĩa là mỗi ID phải khác nhau
# từ mọi ID khác đang được sử dụng bởi bất kỳ chủ hoặc nô lệ sao chép nào khác.
id máy chủ = 1

# ***** Bản sao nhóm liên quan *****
# Tên máy chủ hoặc địa chỉ IP của nô lệ sẽ được báo cáo cho chủ
# trong quá trình đăng ký nô lệ. Giá trị này xuất hiện trong đầu ra của SHOW SLAVE HOSTS
# trên máy chủ chính. Không đặt giá trị này nếu bạn không muốn nô lệ
# tự đăng ký với chủ.
# report_host=0.0

# ***** Bản sao nhóm liên quan *****
# Số cổng TCP/IP để kết nối với nô lệ, sẽ được báo cáo cho chủ trong quá trình
# đăng ký nô lệ. Chỉ đặt tùy chọn này nếu nô lệ đang nghe trên một cổng không mặc định hoặc nếu
# bạn có một đường hầm đặc biệt từ chủ hoặc các máy khách khác đến nô lệ.
report_port=3306

# ***** Bản sao nhóm liên quan *****
# Tùy chọn này chỉ định liệu số nhận dạng giao dịch toàn cầu (GTID) có
# được sử dụng để xác định các giao dịch. BẬT phải được sử dụng với Sao chép nhóm.
# gtid_mode=

# ***** Bản sao nhóm liên quan *****
# Khi được bật, máy chủ sẽ thực thi tính nhất quán của GTID bằng cách cho phép thực thi
# câu lệnh duy nhất có thể được ghi lại an toàn bằng GTID. Bạn phải đặt cái này
# thành BẬT trước khi bật sao chép dựa trên GTID.
# thi hành_gtid_consistency=

# ***** Bản sao nhóm liên quan *****
# Liệu các bản cập nhật mà máy chủ phụ nhận được từ máy chủ chính có phải là
# đăng nhập vào nhật ký nhị phân của chính nô lệ. Ghi nhật ký nhị phân phải được bật trên
# nô lệ để biến này có tác dụng. BẬT phải được sử dụng với
# Sao chép nhóm.
# log_slave_updates=

# ***** Bản sao nhóm liên quan *****
# Xác định xem máy chủ nô lệ có ghi lại trạng thái chủ và thông tin kết nối hay không
# vào bảng InnoDB trong cơ sở dữ liệu mysql hoặc vào tệp trong thư mục dữ liệu.
# Cài đặt BẢNG được yêu cầu khi nhiều kênh sao chép được định cấu hình.
# master_info_repository=

# ***** Bản sao nhóm liên quan *****
# Xác định xem máy chủ nô lệ có ghi vị trí của nó trong nhật ký chuyển tiếp vào InnoDB hay không
# bảng trong cơ sở dữ liệu mysql hoặc vào một tệp trong thư mục dữ liệu. Cài đặt BẢNG là
# bắt buộc khi nhiều kênh sao chép được cấu hình.
# relay_log_info_repository=

# ***** Bản sao nhóm liên quan *****
# Xác định thuật toán được sử dụng để băm ghi trích xuất trong một giao dịch. nếu bạn
# đang sử dụng Sao chép nhóm, biến này phải được đặt thành XHASH64 vì quá trình
# trích xuất ghi từ một giao dịch là cần thiết để phát hiện xung đột trên tất cả
# thành viên nhóm.
# giao dịch_write_set_extract=

# LƯU Ý: Sửa đổi giá trị này sau khi khởi tạo Máy chủ sẽ không có hiệu lực.
Lower_case_table_names=1

# Bảo mật tệp riêng tư.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

# Số phiên đồng thời tối đa mà máy chủ MySQL sẽ
# cho phép. Một trong những kết nối này sẽ được dành riêng cho người dùng có
# SIÊU đặc quyền cho phép quản trị viên đăng nhập ngay cả khi
# đã đạt đến giới hạn kết nối.
max_connections = 2000

# Số lượng bảng mở cho tất cả các chủ đề. Tăng giá trị này
# tăng số lượng bộ mô tả tệp mà mysqld yêu cầu.
# Do đó, bạn phải đảm bảo đặt số lượng tệp đang mở
# cho phép ít nhất 4096 trong biến "open-files-limit" trong
# phần [mysqld_safe]
table_open_cache=2000

# Kích thước tối đa cho các bảng tạm thời bên trong (trong bộ nhớ). Nếu một cái bàn
# phát triển lớn hơn giá trị này, nó sẽ tự động được chuyển đổi vào đĩa
# dựa trên bảng Giới hạn này dành cho một bảng duy nhất. Có thể có nhiều
# của họ.
tmp_table_size = 4G

# Có bao nhiêu chủ đề chúng ta nên giữ trong bộ đệm để sử dụng lại. Khi một khách hàng
# ngắt kết nối, các luồng của khách hàng sẽ được đưa vào bộ đệm nếu không có
# nhiều hơn các chuỗi thread_cache_size trước đó. Điều này làm giảm đáng kể
# số lượng tạo chủ đề cần thiết nếu bạn có nhiều chủ đề mới
# kết nối. (Thông thường điều này không mang lại hiệu suất đáng chú ý
# cải thiện nếu bạn triển khai chuỗi tốt.)
thread_cache_size=10

# *** MyISAM Tùy chọn cụ thể
# Kích thước tối đa của tệp tạm thời mà MySQL được phép sử dụng trong khi
# tạo lại chỉ mục (trong quá trình REPAIR, ALTER TABLE hoặc LOAD DATA INFILE.
# Nếu kích thước tệp lớn hơn mức này, chỉ mục sẽ được tạo
# thông qua bộ đệm chính (chậm hơn).
myisam_max_sort_file_size=10G

# Kích thước của bộ đệm được phân bổ khi sắp xếp các chỉ mục MyISAM
# trong REPAIR TABLE hoặc khi tạo chỉ mục với CREATE INDEX
# hoặc THAY ĐỔI BẢNG.
myisam_sort_buffer_size=256K

# Kích thước của Bộ đệm khóa, được sử dụng để lưu trữ các khối chỉ mục cho các bảng MyISAM.
# Không đặt nó lớn hơn 30% bộ nhớ khả dụng của bạn, vì một số bộ nhớ
# cũng được hệ điều hành yêu cầu lưu trữ các hàng. Ngay cả khi bạn không sử dụng
# Các bảng MyISAM, bạn vẫn nên đặt thành 8-64M vì nó cũng sẽ như vậy
# được sử dụng cho các bảng đĩa tạm thời bên trong.
key_buffer_size = 64M

# Kích thước của bộ đệm được sử dụng để thực hiện quét toàn bộ bảng MyISAM.
# Được phân bổ cho mỗi luồng, nếu cần quét toàn bộ.
read_buffer_size=64K

read_rnd_buffer_size=256K

# *** Tùy chọn cụ thể của INNODB ***
# innodb_data_home_dir=

# Sử dụng tùy chọn này nếu bạn có máy chủ MySQL hỗ trợ InnoDB
# nhưng bạn không định sử dụng nó. Điều này sẽ tiết kiệm bộ nhớ và không gian đĩa
# và tăng tốc một số thứ.
# bỏ qua-innodb=

# Nếu được đặt thành 1, InnoDB sẽ xóa (fsync) nhật ký giao dịch vào
# đĩa tại mỗi lần xác nhận, cung cấp hành vi ACID đầy đủ. Nếu bạn là
# sẵn sàng thỏa hiệp sự an toàn này và bạn đang chạy nhỏ
# giao dịch, bạn có thể đặt giá trị này thành 0 hoặc 2 để giảm I/O đĩa xuống
# nhật ký. Giá trị 0 có nghĩa là nhật ký chỉ được ghi vào tệp nhật ký và
# tệp nhật ký được xóa vào đĩa khoảng một lần mỗi giây. Giá trị 2
# có nghĩa là nhật ký được ghi vào tệp nhật ký ở mỗi lần xác nhận, nhưng nhật ký
# tệp chỉ được xóa vào đĩa khoảng một lần mỗi giây.
innodb_flush_log_at_trx_commit = 1

# Kích thước của bộ đệm mà InnoDB sử dụng để đệm dữ liệu nhật ký. Càng sớm càng
# đã đầy, InnoDB sẽ phải xóa nó vào đĩa. Khi nó đỏ bừng
# dù sao một lần mỗi giây, sẽ không có ý nghĩa gì nếu nó rất lớn
# (ngay cả với các giao dịch dài).


# InnoDB, không giống như MyISAM, sử dụng vùng đệm để lưu trữ cả chỉ mục và
# hàng dữ liệu. Bạn đặt cái này càng lớn thì càng cần ít đĩa I/O hơn
# truy cập dữ liệu trong bảng. Trên một máy chủ cơ sở dữ liệu chuyên dụng, bạn có thể thiết lập
# tham số lên tới 80% kích thước bộ nhớ vật lý của máy. Đừng đặt nó
Tuy nhiên, # quá lớn vì sự cạnh tranh của bộ nhớ vật lý có thể
# gây phân trang trong hệ điều hành. Lưu ý rằng trên các hệ thống 32 bit, bạn
# có thể bị giới hạn ở 2-3,5G bộ nhớ cấp người dùng cho mỗi quy trình, vì vậy đừng
# đặt nó quá cao.
innodb_buffer_pool_size=38G

# Kích thước của mỗi tệp nhật ký trong một nhóm nhật ký. Bạn nên đặt kích thước kết hợp
# tệp nhật ký vào khoảng 25% -100% kích thước nhóm bộ đệm của bạn để tránh
# hoạt động xóa nhóm bộ đệm không cần thiết khi ghi đè lên tệp nhật ký. Tuy nhiên,
# lưu ý rằng kích thước tệp nhật ký lớn hơn sẽ tăng thời gian cần thiết cho
# Quá trình phục hồi.
innodb_log_file_size=48M

# Số luồng được phép bên trong nhân InnoDB. giá trị tối ưu
# phụ thuộc nhiều vào ứng dụng, phần cứng cũng như HĐH
# thuộc tính bộ lập lịch. Giá trị quá cao có thể dẫn đến hỏng luồng.
innodb_thread_concurrency=21

# Kích thước gia tăng (tính bằng MB) để mở rộng kích thước của tệp vùng bảng hệ thống InnoDB tự động mở rộng khi tệp đầy.
innodb_autoextend_increment=64

# Số vùng mà nhóm bộ đệm InnoDB được chia thành.
# Đối với các hệ thống có vùng đệm trong phạm vi nhiều gigabyte, việc chia vùng đệm thành các phiên bản riêng biệt có thể cải thiện tính đồng thời,
# bằng cách giảm tranh chấp khi các chủ đề khác nhau đọc và ghi vào các trang được lưu trong bộ nhớ cache.
innodb_buffer_pool_instances=8

# Xác định số luồng có thể vào InnoDB đồng thời.
innodb_concurrency_tickets=5000

# Chỉ định khoảng thời gian tính bằng mili giây (ms) mà một khối được chèn vào danh sách con cũ phải ở đó sau lần truy cập đầu tiên trước đó
# nó có thể được chuyển sang danh sách con mới.
innodb_old_blocks_time=1000

# Nó chỉ định số lượng tệp .ibd tối đa mà MySQL có thể tiếp tục mở cùng một lúc. Giá trị tối thiểu là 10.
innodb_open_files=300

# Khi bật biến này, InnoDB sẽ cập nhật số liệu thống kê trong các câu lệnh siêu dữ liệu.
innodb_stats_on_metadata=0

# Khi bật innodb_file_per_table (mặc định trong 5.6.6 trở lên), InnoDB lưu trữ dữ liệu và chỉ mục cho từng bảng mới tạo
# trong một tệp .ibd riêng biệt, thay vì trong vùng bảng hệ thống.
innodb_file_per_table=1

# Sử dụng danh sách các giá trị sau: 0 cho crc32, 1 chostrict_crc32, 2 cho innodb, 3 chostrict_innodb, 4 cho none, 5 chostrict_none.
innodb_checksum_algorithm = không

bỏ qua-innodb-doublewrite=

# Số lượng yêu cầu kết nối chưa xử lý mà MySQL có thể có.
# Tùy chọn này hữu ích khi chuỗi MySQL chính nhận được nhiều yêu cầu kết nối trong một thời gian rất ngắn.
# Sau đó, sẽ mất một khoảng thời gian (mặc dù rất ít) để luồng chính kiểm tra kết nối và bắt đầu một luồng mới.
# Giá trị back_log cho biết có bao nhiêu yêu cầu có thể được xếp chồng lên nhau trong thời gian ngắn này trước MySQL trong giây lát
# ngừng trả lời các yêu cầu mới.
# Bạn chỉ cần tăng mức này nếu bạn mong đợi một số lượng lớn kết nối trong một khoảng thời gian ngắn.
back_log=80

# Nếu giá trị này được đặt thành giá trị khác không, tất cả các bảng sẽ được đóng sau mỗi giây flush_time để giải phóng tài nguyên và
# đồng bộ hóa dữ liệu chưa được xóa vào đĩa.
# Tùy chọn này chỉ được sử dụng tốt nhất trên các hệ thống có tài nguyên tối thiểu.
flush_time=0

# Kích thước tối thiểu của bộ đệm được sử dụng để quét chỉ mục đơn giản, quét chỉ mục phạm vi và liên kết không sử dụng
# chỉ mục và do đó thực hiện quét toàn bộ bảng.
tham gia_buffer_size=256K

# Kích thước tối đa của một gói hoặc bất kỳ chuỗi trung gian hoặc chuỗi nào được tạo hoặc bất kỳ tham số nào được gửi bởi
# mysql_stmt_send_long_data() Hàm API C.
max_allowed_packet=4M

# Nếu nhiều hơn mức này, nhiều yêu cầu kết nối liên tiếp từ máy chủ bị gián đoạn mà không có kết nối thành công,
# máy chủ chặn máy chủ đó thực hiện các kết nối tiếp theo.
max_connect_errors=100

# Thay đổi số lượng bộ mô tả tệp có sẵn cho mysqld.
# Bạn nên thử tăng giá trị của tùy chọn này nếu mysqld báo lỗi "Quá nhiều tệp đang mở".
open_files_limit=4161

# Nếu bạn thấy nhiều sort_merge_passes mỗi giây trong đầu ra SHOW GLOBAL STATUS, bạn có thể xem xét tăng
# giá trị sort_buffer_size để tăng tốc các thao tác ORDER BY hoặc GROUP BY không thể cải thiện bằng tối ưu hóa truy vấn
# hoặc lập chỉ mục được cải thiện.
sort_buffer_size = 256K

# Số lượng định nghĩa bảng (từ tệp .frm) 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 lớn để tăng tốc độ mở bảng.
# Bộ đệm định nghĩa bảng chiếm ít không gian hơn và không sử dụng bộ mô tả tệp, không giống như bộ đệm bảng thông thường.
# Giá trị tối thiểu và mặc định đều là 400.
table_definition_cache=1400

# Chỉ định kích thước tối đa của sự kiện nhật ký nhị phân dựa trên hàng, tính bằng byte.
# Hàng được nhóm thành các sự kiện nhỏ hơn kích thước này nếu có thể. Giá trị phải là bội số của 256.
binlog_row_event_max_size=8K

# Nếu giá trị của biến này lớn hơn 0, thì nô lệ sao chép sẽ đồng bộ hóa tệp master.info của nó với đĩa.
# (dùng fdatasync()) sau mỗi sự kiện sync_master_info.
sync_master_info=10000

# Nếu giá trị của biến này lớn hơn 0, máy chủ MySQL sẽ đồng bộ hóa nhật ký chuyển tiếp của nó với đĩa.
# (dùng fdatasync()) sau mỗi lần sync_relay_log ghi vào nhật ký chuyển tiếp.
sync_relay_log=10000

# Nếu giá trị của biến này lớn hơn 0, thì nô lệ sao chép sẽ đồng bộ hóa tệp relay-log.info của nó với đĩa.
# (sử dụng fdatasync()) sau mỗi giao dịch sync_relay_log_info.
sync_relay_log_info=10000

# Tải plugin mysql khi bắt đầu."plugin_x ; plugin_y".
# plugin_load=

# Cổng TCP/IP mà Giao thức MySQL Server X sẽ lắng nghe.
# loose_mysqlx_port=33060

# Kích thước của Bộ đệm khóa, được sử dụng để lưu trữ các khối chỉ mục cho các bảng MyISAM.
# Không đặt nó lớn hơn 30% bộ nhớ khả dụng của bạn, vì một số bộ nhớ
# cũng được hệ điều hành yêu cầu lưu trữ các hàng. Ngay cả khi bạn không sử dụng
# Các bảng MyISAM, bạn vẫn nên đặt thành 8-64M vì nó cũng sẽ như vậy
# được sử dụng cho các bảng đĩa tạm thời bên trong.

Làm cách nào để cải thiện hiệu suất MySQL của tôi và ngăn chặn sự cố máy chủ bằng cách hạn chế mức sử dụng CPU?

711
0 + 0
Điểm:1
avatar Server
lá cờ ua
Rick James

Không có lời giải thích rõ ràng trong các BIẾN và TÌNH TRẠNG.

Phân tích TÌNH TRẠNG TOÀN CẦU và BIẾN:

Quan sát:

  • Phiên bản: 8.0.20
  • 60 GB RAM
  • Thời gian hoạt động = 04:49:11; một số giá trị TÌNH TRẠNG TOÀN CẦU có thể chưa có ý nghĩa.
  • Bạn đang chạy trên Windows.
  • 4,89 Truy vấn/giây : 3,43 Câu hỏi/giây

Các vấn đề quan trọng hơn:

Hầu như không có gì đang xảy ra. Tôi gặp khó khăn khi tưởng tượng rằng MySQL đã gây ra sự cố.

Thấp hơn max_connections đến 500. (Không có hơn 23 kết nối đồng thời kể từ khi khởi động.)

tmp_table_size = 500M -- nó hiện đang ở mức cao nguy hiểm đối với dung lượng RAM bạn có.

innodb_doublewrite = BẬT

Chi tiết và các quan sát khác:

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1.024 * 4 = 4.096 -- Lượng công việc cho trình dọn dẹp trang mỗi giây. -- "InnoDB: page_cleaner: mất 1000ms vòng lặp dự kiến..." có thể sửa được bằng cách hạ thấp lru_scan_depth: Cân nhắc 1000 / innodb_page_cleaners (hiện là 4). Cũng kiểm tra trao đổi.

( innodb_lru_scan_depth ) = 1.024 -- "InnoDB: page_cleaner: mất 1000ms vòng lặp dự định..." có thể được khắc phục bằng cách hạ thấp lru_scan_depth

( Innodb_buffer_pool_pages_free * 16384/innodb_buffer_pool_size) = 2.478.311 * 16384/38912M = 99,5% - vùng đệm miễn phí -- buffer_pool_size lớn hơn bộ làm việc; có thể giảm nó

( innodb_io_ capacity ) = 200 -- Khi tuôn ra, hãy sử dụng nhiều IOP này. -- Số lần đọc có thể chậm chạp hoặc đột ngột.

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 2.478.311/2490368 = 99,5% -- Pct của buffer_pool hiện không được sử dụng -- innodb_buffer_pool_size (hiện tại là 40802189312) có lớn hơn mức cần thiết không?

( innodb_io_ capacity_max / innodb_io_ capacity ) = 2.000/200 = 10 -- Công suất: max/đồng bằng -- Đề xuất 2. Giá trị tối đa phải tương đương với IOP mà hệ thống con I/O của bạn có thể xử lý. (Nếu loại ổ đĩa không xác định thì 2000/200 có thể là một cặp hợp lý.)

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 196.214.784/38912M = 0,48% -- Phần trăm vùng đệm được sử dụng bởi dữ liệu -- Một phần trăm nhỏ có thể chỉ ra rằng buffer_pool lớn không cần thiết.

( innodb_doublewrite ) = innodb_doublewrite = TẮT -- Thêm I/O, nhưng thêm an toàn khi va chạm. -- TẮT là được đối với FusionIO, Galera, Bản sao, ZFS.

( Innodb_os_log_write / (Thời gian hoạt động / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 3.944.448 / (17351 / 3600) / 2 / 48M = 0,00813 -- Tỉ lệ -- (xem phút)

( Thời gian hoạt động / 60 * innodb_log_file_size / Innodb_os_log_write ) = 17.351/60 * 48M / 3944448 = 3.690 -- Số phút giữa các lần quay nhật ký InnoDB Bắt đầu với 5.6.8, điều này có thể được thay đổi linh hoạt; hãy chắc chắn cũng thay đổi my.cnf. -- (Khuyến nghị 60 phút giữa các lần quay là tùy ý.) Điều chỉnh innodb_log_file_size (hiện tại là 50331648). (Không thể thay đổi trong AWS.)

( innodb_flush_method ) = innodb_flush_method = không có bộ đệm -- Cách InnoDB nên yêu cầu hệ điều hành viết các khối. Đề xuất O_DIRECT hoặc O_ALL_DIRECT (Percona) để tránh đệm đôi. (Ít nhất là cho Unix.) Xem chrischandler để biết trước về O_ALL_DIRECT

( innodb_io_ capacity ) = 200 - I/O ops mỗi giây có khả năng trên đĩa. 100 cho ổ đĩa chậm; 200 đối với ổ quay; 1000-2000 cho SSD; nhân với hệ số RAID.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = BẬT -- Thông thường nên BẬT. -- Có những trường hợp TẮT tốt hơn. Xem thêm innodb_adaptive_hash_index_parts (hiện là 8) (sau 5.7.9) và innodb_adaptive_hash_index_partitions (MariaDB và Percona). BẬT có liên quan đến các sự cố hiếm gặp (lỗi 73890). 10.5.0 đã quyết định TẮT mặc định.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = TẮT -- Có đăng nhập tất cả các Bế tắc hay không. -- Nếu bạn đang mắc kẹt với Bế tắc, hãy bật tính năng này lên. Thận trọng: Nếu bạn có nhiều bế tắc, điều này có thể ghi rất nhiều vào đĩa.

( max_connections ) = 2.000 -- Số lượng kết nối (luồng) tối đa. Tác động phân bổ khác nhau. -- Nếu max_connections (hiện là 2000) quá cao và nhiều cài đặt bộ nhớ khác nhau cao, bạn có thể hết RAM.

( số lượng lớn_insert_buffer_size ) = 8/61440M = 0,01% -- Bộ đệm cho INSERT nhiều hàng và LOAD DATA -- Quá lớn có thể ảnh hưởng đến dung lượng RAM. Quá nhỏ có thể cản trở các hoạt động như vậy.

( tmp_table_size ) = 4096M -- Giới hạn về kích thước của KỈ NIỆM các bảng tạm thời được sử dụng để hỗ trợ CHỌN -- Giảm tmp_table_size (bây giờ là 4294967296) để tránh hết RAM. Có lẽ không quá 64M.

( Select_full_join / Com_select ) = 15.198 / 31082 = 48,9% -- % trong số các lựa chọn là liên kết không có chỉ mục -- Thêm (các) chỉ mục phù hợp vào các bảng được sử dụng trong THAM GIA.

( Com_admin_commands / Truy vấn ) = 25.348 / 84808 = 29,9% -- Phần trăm truy vấn là lệnh "quản trị viên". -- Có chuyện gì vậy?

( long_query_time ) = 10 -- Ngưỡng (Giây) để xác định truy vấn "chậm". -- Đề nghị 2

( log_slow_slave_statements ) = log_slow_slave_statements = TẮT -- (5.6.11, 5.7.1) Theo mặc định, các câu lệnh sao chép sẽ không hiển thị trong nhật ký chậm; điều này khiến chúng lộ ra. -- Có thể hữu ích trong quá trình ghi chậm để xem các bản ghi có thể cản trở việc đọc Bản sao.

( back_log ) = 80 -- (Được tự động hóa kể từ 5.6.6; dựa trên max_connections) -- Nâng lên mức tối thiểu(150, max_connections (now 2000)) có thể hữu ích khi thực hiện nhiều kết nối.

( Max_used_connections / max_connections ) = 23/2000 = 1,1% -- Đỉnh % kết nối -- Vì một số yếu tố bộ nhớ có thể mở rộng dựa trên max_connections (hiện tại là 2000), nên không nên đặt cài đặt đó quá cao.

( Com_change_db/Kết nối) = 25.413/311 = 81,7 -- Công tắc cơ sở dữ liệu trên mỗi kết nối -- (phụ) Cân nhắc sử dụng cú pháp "db.table"

( Aborted_connects / Kết nối ) = 227/311 = 73,0% -- Có lẽ một hacker đang cố gắng đột nhập? (Cố gắng kết nối)

Nhỏ bất thường:

10 * read_buffer_size = 0,6 MB
Com_insert = 4,4 /HR
Handler_read_next = 16 /giây
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 0,47%
Innodb_dblwr_pages_write = 0
Innodb_rows_updated = 0,62 /HR
back_log / max_connections = 4,0%
innodb_doublewrite_files = 0
innodb_doublewrite_pages = 0

Lớn bất thường:

Com_create_db = 0,21 / nhân sự
Com_create_table = 92 /HR
Com_show_charsets = 1,7 /HR
Com_show_plugins = 0,41 /HR
Com_show_storage_engines = 0,41 /HR
Innodb_buffer_pool_pages_free = 2,48e+6
Innodb_system_rows_deleted = 0,1 /giây
Innodb_system_rows_inserted = 0,1 /giây
Innodb_system_rows_updated = 0,32 /giây
Ssl_accepts = 304
Ssl_default_timeout = 7.200
Ssl_finished_accepts = 304
Ssl_session_cache_hits = 290
Ssl_session_cache_timeouts = 5
Ssl_verify_depth = 4,29e+9
Ssl_verify_mode = 5
gtid_executed_compression_period = 0,058 /giây
innodb_thread_concurrency = 21
max_error_count = 1,024
max_length_for_sort_data = 4,096
trình tối ưu hóa_trace_offset = --1
performance_schema_max_cond_classes = 100
performance_schema_max_mutex_classes = 300
performance_schema_max_rwlock_classes = 60
performance_schema_max_stage_classes = 175
performance_schema_max_statement_classes = 218
performance_schema_max_thread_classes = 100

Chuỗi bất thường:

event_scheduler = BẬT
ft_boolean_syntax = + -><()~*:\"\"&
have_query_cache = KHÔNG
innodb_fast_shutdown = 1
innodb_temp_tablespaces_dir = .\#innodb_temp\
Lower_case_file_system = BẬT
Lower_case_table_names = 1
mysqlx_compression_algorithms = DEFLATE_STREAM,LZ4_MESSAGE,ZSTD_STREAM
Optimizer_trace = đã bật=tắt,one_line=tắt
trình tối ưu hóa_trace_features = tham lam_search=bật, phạm vi_optimizer=bật, dynamic_range=bật, lặp đi lặp lại_subselect=bật
protocol_compression_algorithms = zlib,zstd,không nén
Slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
0 + 0
lá cờ VietNam
Phan Văn Trường
Câu hỏi này là trong các ngôn ngữ khác:

Đăng câu trả lời

Hầu hết mọi người không hiểu rằng việc đặt nhiều câu hỏi sẽ mở ra cơ hội học hỏi và cải thiện mối quan hệ giữa các cá nhân. Ví dụ, trong các nghiên cứu của Alison, mặc dù mọi người có thể nhớ chính xác có bao nhiêu câu hỏi đã được đặt ra trong các cuộc trò chuyện của họ, nhưng họ không trực giác nhận ra mối liên hệ giữa câu hỏi và sự yêu thích. Qua bốn nghiên cứu, trong đó những người tham gia tự tham gia vào các cuộc trò chuyện hoặc đọc bản ghi lại các cuộc trò chuyện của người khác, mọi người có xu hướng không nhận ra rằng việc đặt câu hỏi sẽ ảnh hưởng—hoặc đã ảnh hưởng—mức độ thân thiện giữa những người đối thoại.