Khắc phục sự cố độ trễ sao chép - Cơ sở dữ liệu Azure cho MySQL - Máy chủ linh hoạt (2023)

ÁP DỤNG CHO: Cơ sở dữ liệu Azure cho MySQL - Máy chủ đơnCơ sở dữ liệu Azure cho MySQL - Máy chủ linh hoạt

Cácđọc bản saotính năng cho phép bạn sao chép dữ liệu từ Cơ sở dữ liệu Azure dành cho máy chủ MySQL sang máy chủ bản sao chỉ đọc. Bạn có thể mở rộng quy mô khối lượng công việc bằng cách định tuyến các truy vấn đọc và báo cáo từ ứng dụng đến các máy chủ bản sao. Thiết lập này làm giảm áp lực cho máy chủ nguồn. Nó cũng cải thiện hiệu suất tổng thể và độ trễ của ứng dụng khi mở rộng quy mô.

Các bản sao được cập nhật không đồng bộ bằng cách sử dụng công nghệ sao chép dựa trên vị trí tệp nhật ký nhị phân gốc (binlog) của công cụ MySQL. Để biết thêm thông tin, xemTổng quan về cấu hình sao chép dựa trên vị trí của tệp binlog MySQL.

Độ trễ sao chép trên các bản sao chỉ có quyền đọc phụ thuộc vào một số yếu tố. Những yếu tố này bao gồm nhưng không giới hạn ở:

• Độ trễ mạng.
• Khối lượng giao dịch trên máy chủ nguồn.
• Tính bậc của máy chủ nguồn và máy chủ bản sao chỉ có quyền đọc thứ cấp.
• Truy vấn chạy trên máy chủ nguồn và máy chủ thứ cấp.

Trong bài viết này, bạn sẽ tìm hiểu cách khắc phục sự cố độ trễ sao chép trong Cơ sở dữ liệu Azure cho MySQL. Bạn cũng sẽ hiểu một số nguyên nhân phổ biến làm tăng độ trễ sao chép trên các máy chủ sao chép.

khái niệm sao chép

Khi bật nhật ký nhị phân, máy chủ nguồn sẽ ghi các giao dịch đã cam kết vào nhật ký nhị phân. Nhật ký nhị phân được sử dụng để sao chép. Tính năng này được bật theo mặc định cho tất cả các máy chủ mới được cung cấp hỗ trợ dung lượng lưu trữ lên tới 16 TB. Trên các máy chủ bản sao, hai luồng chạy trên mỗi máy chủ bản sao. Một chủ đề làChủ đề IO, và khác làchủ đề SQL:

• Chuỗi IO kết nối với máy chủ nguồn và yêu cầu nhật ký nhị phân được cập nhật. Chủ đề này nhận được các bản cập nhật nhật ký nhị phân. Những cập nhật đó được lưu trên một máy chủ bản sao, trong nhật ký cục bộ được gọi lànhật ký chuyển tiếp.
• Chuỗi SQL đọc nhật ký chuyển tiếp và sau đó áp dụng các thay đổi dữ liệu trên các máy chủ bản sao.

Theo dõi độ trễ sao chép

Cơ sở dữ liệu Azure cho MySQL cung cấp số liệu về độ trễ sao chép tính bằng giây trongMàn hình Azure. Số liệu này chỉ khả dụng trên các máy chủ bản sao chỉ có quyền đọc. Nó được tính theo số liệu seconds_behind_master có sẵn trong MySQL.

Để hiểu nguyên nhân của độ trễ sao chép tăng lên, hãy kết nối với máy chủ sao chép bằng cách sử dụngBàn làm việc MySQLhoặcVỏ đám mây Azure. Sau đó chạy lệnh sau.

mysql --host=myreplicademoserver.mysql.database.azure.com --user=myadmin@mydemoserver -p

Đây là trải nghiệm trông như thế nào trong thiết bị đầu cuối Cloud Shell:

Đang yêu cầu Cloud Shell.Succeeded.Kết nối thiết bị đầu cuối...Chào mừng bạn đến với Azure Cloud ShellType "az" để sử dụng "trợ giúp" Azure CLIType để tìm hiểu về Cloud Shelluser@Azure:~$mysql -h myreplicademoserver.mysql.database.azure.com - u myadmin@mydemoserver -pNhập mật khẩu: Chào mừng bạn đến với màn hình MySQL. Các lệnh kết thúc bằng ; hoặc \g.Id kết nối MySQL của bạn là 64796Phiên bản máy chủ: 5.6.42.0 Phân phối nguồnCopyright (c) 2000, 2020, Oracle và/hoặc các chi nhánh của nó. Bảo lưu mọi quyền.Oracle là thương hiệu đã đăng ký của Tập đoàn Oracle và/hoặc các chi nhánh của nó. Các tên khác có thể là thương hiệu của chủ sở hữu tương ứng. Nhập 'trợ giúp;' hoặc '\h' để được trợ giúp. Nhập '\c' để xóa câu lệnh nhập hiện tại.mysql>

Trong cùng một thiết bị đầu cuối Cloud Shell, hãy chạy lệnh sau:

mysql> HIỂN THỊ TÌNH TRẠNG NÔ LỆ;

Đây là một đầu ra điển hình:

Đầu ra chứa nhiều thông tin. Thông thường, bạn chỉ cần tập trung vào các hàng mà bảng sau đây mô tả.

Nếu Slave_IO_Running làĐúngvà Slave_SQL_Running làĐúng, thì bản sao sẽ chạy tốt.

Tiếp theo, hãy kiểm tra Last_IO_Errno, Last_IO_Error, Last_SQL_Errno và Last_SQL_Error. Các trường này hiển thị số lỗi và thông báo lỗi của lỗi gần đây nhất khiến luồng SQL dừng lại. Một số lỗi của0và một thông báo trống có nghĩa là không có lỗi. Điều tra bất kỳ giá trị lỗi khác không nào bằng cách kiểm tra mã lỗi trongTham khảo thông báo lỗi máy chủ MySQL.

Các tình huống phổ biến đối với độ trễ sao chép cao

Các phần sau giải quyết các tình huống trong đó độ trễ sao chép cao là phổ biến.

Độ trễ mạng hoặc mức tiêu thụ CPU cao trên máy chủ nguồn

Nếu bạn thấy các giá trị sau, thì độ trễ sao chép có thể do độ trễ mạng cao hoặc mức tiêu thụ CPU cao trên máy chủ nguồn.

Slave_IO_State: Chờ master gửi eventMaster_Log_File: chuỗi tệp nhị phân lớn hơn Relay_Master_Log_File, ví dụ: mysql-bin.00020Relay_Master_Log_File: chuỗi tệp nhỏ hơn Master_Log_File, vd mysql-bin.00010

Trong trường hợp này, chuỗi IO đang chạy và đang chờ trên máy chủ nguồn. Máy chủ nguồn đã ghi vào tệp nhật ký nhị phân số 20. Bản sao chỉ nhận được tối đa tệp số 10. Các yếu tố chính dẫn đến độ trễ sao chép cao trong trường hợp này là tốc độ mạng hoặc mức sử dụng CPU cao trên máy chủ nguồn.

Trong Azure, độ trễ mạng trong một khu vực thường có thể được đo bằng mili giây. Trên khắp các khu vực, độ trễ dao động từ mili giây đến giây.

Trong hầu hết các trường hợp, độ trễ kết nối giữa luồng IO và máy chủ nguồn là do mức sử dụng CPU cao trên máy chủ nguồn. Các chủ đề IO được xử lý chậm. Bạn có thể phát hiện sự cố này bằng cách sử dụng Azure Monitor để kiểm tra việc sử dụng CPU và số lượng kết nối đồng thời trên máy chủ nguồn.

Nếu bạn không thấy mức sử dụng CPU cao trên máy chủ nguồn, thì vấn đề có thể là do độ trễ của mạng. Nếu độ trễ mạng đột ngột cao bất thường, hãy kiểm traTrang trạng thái Azurecho các sự cố đã biết hoặc ngừng hoạt động.

Các đợt giao dịch lớn trên máy chủ nguồn

Nếu bạn thấy các giá trị sau, thì một đợt giao dịch lớn trên máy chủ nguồn có khả năng gây ra độ trễ sao chép.

Slave_IO_State: Đang đợi luồng SQL phụ giải phóng đủ không gian nhật ký chuyển tiếpMaster_Log_File: chuỗi tệp nhị phân lớn hơn Relay_Master_Log_File, ví dụ: mysql-bin.00020Relay_Master_Log_File: chuỗi tệp nhỏ hơn Master_Log_File, ví dụ: mysql-bin.00010

Đầu ra cho thấy bản sao có thể truy xuất nhật ký nhị phân phía sau máy chủ nguồn. Nhưng luồng IO bản sao chỉ ra rằng không gian nhật ký chuyển tiếp đã đầy.

Tốc độ mạng không gây ra sự chậm trễ. Bản sao đang cố gắng bắt kịp. Nhưng kích thước nhật ký nhị phân cập nhật vượt quá giới hạn trên của không gian nhật ký chuyển tiếp.

Để khắc phục sự cố này, hãy bậtnhật ký truy vấn chậmtrên máy chủ nguồn. Sử dụng nhật ký truy vấn chậm để xác định các giao dịch chạy dài trên máy chủ nguồn. Sau đó điều chỉnh các truy vấn đã xác định để giảm độ trễ trên máy chủ.

Độ trễ sao chép thuộc loại này thường do tải dữ liệu trên máy chủ nguồn gây ra. Khi các máy chủ nguồn tải dữ liệu hàng tuần hoặc hàng tháng, rất tiếc là không thể tránh khỏi độ trễ sao chép. Các máy chủ bản sao cuối cùng cũng bắt kịp sau khi quá trình tải dữ liệu trên máy chủ nguồn kết thúc.

Sự chậm chạp trên máy chủ bản sao

Nếu bạn quan sát các giá trị sau, thì sự cố có thể xảy ra trên máy chủ bản sao.

Slave_IO_State: Đang đợi master gửi eventMaster_Log_File: Chuỗi tệp nhật ký nhị phân bằng Relay_Master_Log_File, ví dụ: mysql-bin.000191Read_Master_Log_Pos: Vị trí của máy chủ chính được ghi vào tệp trên lớn hơn Relay_Log_Pos, ví dụ: 103978138Relay_Master_Log_File: mysql-bin.000191Slave_IO_Running: YesSlave_SQL_Running: YesExec_Master_Log_Pos: Vị trí của nô lệ đọc từ tệp nhật ký nhị phân chính nhỏ hơn Read_Master_Log_Pos, ví dụ: 13468882Seconds_Behind_Master: Có độ trễ và giá trị ở đây lớn hơn 0

Trong trường hợp này, đầu ra cho thấy cả chuỗi IO và chuỗi SQL đang chạy tốt. Bản sao đọc cùng một tệp nhật ký nhị phân mà máy chủ nguồn ghi. Tuy nhiên, một số độ trễ trên máy chủ bản sao phản ánh cùng một giao dịch từ máy chủ nguồn.

Các phần sau đây mô tả các nguyên nhân phổ biến của loại độ trễ này.

Không có khóa chính hoặc khóa duy nhất trên bảng

Cơ sở dữ liệu Azure cho MySQL sử dụng bản sao dựa trên hàng. Máy chủ nguồn ghi các sự kiện vào nhật ký nhị phân, ghi lại các thay đổi trong các hàng của bảng riêng lẻ. Sau đó, chuỗi SQL sao chép những thay đổi đó sang các hàng của bảng tương ứng trên máy chủ bản sao. Khi một bảng thiếu khóa chính hoặc khóa duy nhất, chuỗi SQL sẽ quét tất cả các hàng trong bảng đích để áp dụng các thay đổi. Quá trình quét này có thể gây ra độ trễ sao chép.

Trong MySQL, khóa chính là một chỉ mục được liên kết để đảm bảo hiệu suất truy vấn nhanh vì nó không thể bao gồm các giá trị NULL. Nếu bạn sử dụng công cụ lưu trữ InnoDB, dữ liệu bảng được sắp xếp vật lý để thực hiện tra cứu và sắp xếp cực nhanh dựa trên khóa chính.

Chúng tôi khuyên bạn nên thêm khóa chính trên các bảng trong máy chủ nguồn trước khi tạo máy chủ bản sao. Thêm khóa chính trên máy chủ nguồn rồi tạo lại bản sao chỉ có quyền đọc để giúp cải thiện độ trễ sao chép.

Sử dụng truy vấn sau để tìm ra bảng nào thiếu khóa chính trên máy chủ nguồn:

chọn tab.table_schema làm database_name, tab.table_name từ tab information_schema.tables bên trái tham gia information_schema.table_constraints tco trên tab.table_schema = tco.table_schema và tab.table_name = tco.table_name và tco.constraint_type = 'KEY CHÍNH' trong đó tco.constraint_type là null và tab.table_schema không có trong('mysql', 'information_schema', 'performance_schema', 'sys') và tab.table_type = 'BASE TABLE' theo thứ tự tab.table_schema, tab.table_name;

Các truy vấn chạy dài trên máy chủ bản sao

Khối lượng công việc trên máy chủ bản sao có thể làm cho luồng SQL tụt hậu so với luồng IO. Các truy vấn chạy dài trên máy chủ bản sao là một trong những nguyên nhân phổ biến gây ra độ trễ sao chép cao. Để khắc phục sự cố này, hãy kích hoạtnhật ký truy vấn chậmtrên máy chủ bản sao.

Truy vấn chậm có thể làm tăng mức tiêu thụ tài nguyên hoặc làm chậm máy chủ để bản sao không thể theo kịp máy chủ nguồn. Trong trường hợp này, điều chỉnh các truy vấn chậm. Các truy vấn nhanh hơn ngăn ngừa tắc nghẽn chuỗi SQL và cải thiện đáng kể độ trễ sao chép.

Truy vấn DDL trên máy chủ nguồn

Trên máy chủ nguồn, lệnh ngôn ngữ định nghĩa dữ liệu (DDL) nhưTHAY ĐỔI BẢNGcó thể mất nhiều thời gian. Trong khi lệnh DDL đang chạy, hàng nghìn truy vấn khác có thể đang chạy song song trên máy chủ nguồn.

Khi DDL được sao chép, để đảm bảo tính nhất quán của cơ sở dữ liệu, công cụ MySQL sẽ chạy DDL trong một luồng sao chép duy nhất. Trong tác vụ này, tất cả các truy vấn sao chép khác bị chặn và phải đợi cho đến khi hoạt động DDL kết thúc trên máy chủ sao chép. Ngay cả các hoạt động DDL trực tuyến cũng gây ra sự chậm trễ này. Hoạt động DDL tăng độ trễ sao chép.

Nếu bạn đã bậtnhật ký truy vấn chậmtrên máy chủ nguồn, bạn có thể phát hiện vấn đề về độ trễ này bằng cách kiểm tra lệnh DDL chạy trên máy chủ nguồn. Thông qua việc loại bỏ, đổi tên và tạo chỉ mục, bạn có thể sử dụng thuật toán INPLACE cho BẢNG THAY ĐỔI. Bạn có thể cần sao chép dữ liệu bảng và xây dựng lại bảng.

Thông thường, DML đồng thời được hỗ trợ cho thuật toán INPLACE. Nhưng bạn có thể nhanh chóng sử dụng khóa siêu dữ liệu độc quyền trên bảng khi bạn chuẩn bị và chạy thao tác. Vì vậy, đối với câu lệnh CREATE INDEX, bạn có thể sử dụng các mệnh đề ALGORITHM và LOCK để tác động đến phương thức sao chép bảng và mức độ đồng thời để đọc và viết. Bạn vẫn có thể ngăn các thao tác DML bằng cách thêm chỉ mục FULLTEXT hoặc chỉ mục SPATIAL.

Ví dụ sau tạo một chỉ mục bằng cách sử dụng các mệnh đề ALGORITHM và LOCK.

THAY ĐỔI TABLE table_name THÊM INDEX index_name (cột), ALGORITHM=INPLACE, LOCK=NONE;

Thật không may, đối với câu lệnh DDL yêu cầu khóa, bạn không thể tránh được độ trễ sao chép. Để giảm các tác động tiềm ẩn, hãy thực hiện các loại hoạt động DDL này trong giờ thấp điểm, chẳng hạn như vào ban đêm.

Hạ cấp bản sao máy chủ

Trong Cơ sở dữ liệu Azure dành cho MySQL, các bản sao chỉ có quyền đọc sử dụng cấu hình máy chủ giống như máy chủ nguồn. Bạn có thể thay đổi cấu hình máy chủ bản sao sau khi nó được tạo.

Nếu máy chủ bản sao bị hạ cấp, khối lượng công việc có thể tiêu tốn nhiều tài nguyên hơn, do đó có thể dẫn đến độ trễ sao chép. Để phát hiện sự cố này, hãy sử dụng Azure Monitor để kiểm tra mức tiêu thụ CPU và bộ nhớ của máy chủ bản sao.

Trong trường hợp này, chúng tôi khuyên bạn nên giữ cấu hình của máy chủ bản sao ở các giá trị bằng hoặc lớn hơn giá trị của máy chủ nguồn. Cấu hình này cho phép bản sao theo kịp máy chủ nguồn.

Cải thiện độ trễ sao chép bằng cách điều chỉnh các tham số máy chủ nguồn

Trong Cơ sở dữ liệu Azure dành cho MySQL, theo mặc định, sao chép được tối ưu hóa để chạy với các luồng song song trên các bản sao. Khi khối lượng công việc đồng thời cao trên máy chủ nguồn khiến máy chủ bản sao bị tụt lại phía sau, bạn có thể cải thiện độ trễ sao chép bằng cách định cấu hình tham số binlog_group_commit_sync_delay trên máy chủ nguồn.

Tham số binlog_group_commit_sync_delay kiểm soát số lượng micro giây mà cam kết nhật ký nhị phân chờ trước khi đồng bộ hóa tệp nhật ký nhị phân. Lợi ích của tham số này là thay vì ngay lập tức áp dụng mọi giao dịch đã cam kết, máy chủ nguồn sẽ gửi hàng loạt bản cập nhật nhật ký nhị phân. Độ trễ này làm giảm IO trên bản sao và giúp cải thiện hiệu suất.

Có thể hữu ích khi đặt tham số binlog_group_commit_sync_delay thành 1000 hoặc hơn. Sau đó theo dõi độ trễ sao chép. Đặt thông số này một cách thận trọng và chỉ sử dụng nó cho khối lượng công việc đồng thời cao.

Đối với khối lượng công việc đồng thời thấp bao gồm nhiều giao dịch đơn lẻ, cài đặt binlog_group_commit_sync_delay có thể làm tăng độ trễ. Độ trễ có thể tăng lên vì luồng IO chờ cập nhật nhật ký nhị phân hàng loạt ngay cả khi chỉ một vài giao dịch được thực hiện.

Tùy chọn khắc phục sự cố nâng cao

Nếu sử dụng lệnh hiển thị trạng thái nô lệ không cung cấp đủ thông tin để khắc phục sự cố độ trễ sao chép, hãy thử xem các tùy chọn bổ sung này để biết quy trình nào đang hoạt động hoặc đang chờ.

Xem bảng chủ đề

Cácperformance_schema.threadsbảng hiển thị trạng thái quá trình. Một quy trình có trạng thái Đang chờ khóa lock_type cho biết rằng có một khóa trên một trong các bảng, ngăn luồng sao chép cập nhật bảng.

CHỌN tên, processlist_state, processlist_time TỪ performance_schema.threads WHERE name LIKE '%slave%';

Để biết thêm thông tin, xemKỳ chủ đề chung.

Xem bảng replica_connection_status

Bảng performance_schema.replication_connection_status hiển thị trạng thái hiện tại của luồng I/O sao chép xử lý kết nối của bản sao với nguồn và trạng thái này thay đổi thường xuyên hơn. Bảng chứa các giá trị thay đổi trong quá trình kết nối.

CHỌN * TỪ performance_schema.replication_connection_status;

Xem bảng replica_applier_status_by_worker

Cácperformance_schema.replication_applier_status_by_workerbảng hiển thị trạng thái của chuỗi công nhân, Giao dịch được xem lần cuối cùng với số lỗi và thông báo gần đây nhất, giúp bạn tìm thấy giao dịch gặp sự cố và xác định nguyên nhân gốc rễ.

Bạn có thể chạy các lệnh bên dưới trong Sao chép dữ liệu vào để bỏ qua các lỗi hoặc giao dịch:

az_replication_skip_counter

hoặc

az_replication_skip_gtid_transaction

CHỌN * TỪ performance_schema.replication_applier_status_by_worker;

Xem câu lệnh SHOW RELAYLOG SỰ KIỆN

Cáchiển thị các sự kiện relaylogcâu lệnh hiển thị các sự kiện trong nhật ký chuyển tiếp của một bản sao.

· Đối với sao chép dựa trên GITD (Bản sao đọc), câu lệnh hiển thị giao dịch GTID và tệp binlog và vị trí của nó, bạn có thể sử dụng mysqlbinlog để lấy nội dung và câu lệnh đang được chạy. · Đối với sao chép vị trí binlog MySQL (được sử dụng để sao chép Dữ liệu vào), nó hiển thị các câu lệnh đang được chạy, điều này sẽ giúp biết các giao dịch trên bảng nào đang được chạy

Kiểm tra đầu ra màn hình khóa và màn hình tiêu chuẩn InnoDB

Bạn cũng có thể thử kiểm tra Đầu ra màn hình khóa và Màn hình tiêu chuẩn InnoDB để giúp giải quyết các khóa và bế tắc cũng như giảm thiểu độ trễ sao chép. Màn hình khóa giống như Màn hình tiêu chuẩn ngoại trừ việc nó bao gồm thông tin khóa bổ sung. Để xem thông tin khóa và bế tắc bổ sung này, hãy chạy lệnh show engine innodb status\G.

Bước tiếp theo

Kiểm traTổng quan về sao chép binlog của MySQL.