Điều gì gây ra độ trễ sao chép MySQL?

Ở đây, trong nhóm hỗ trợ percona mysql, chúng tôi thường gặp các sự cố khi khách hàng phàn nàn về sự chậm trễ sao chép – và nhiều lần vấn đề kết thúc liên quan đến độ trễ nô lệ sao chép mysql. tất nhiên điều này không có gì mới đối với người dùng mysql và chúng tôi đã có một vài bài đăng ở đây trên blog hiệu suất mysql về chủ đề này trong nhiều năm (hai bài đăng đặc biệt phổ biến trước đây là. “ lý do cho độ trễ sao chép mysql ” và “ quản lý độ trễ nô lệ với sao chép mysql ”, cả hai đều của percona ceo peter zaitsev)

Tuy nhiên, trong bài viết hôm nay, tôi sẽ chia sẻ một số cách mới để xác định sự chậm trễ trong quá trình sao chép – bao gồm các nguyên nhân có thể gây ra tình trạng nô lệ bị tụt hậu – và cách khắc phục sự cố này

cách xác định độ trễ sao chép

bản sao mysql hoạt động với hai luồng, io_thread & sql_thread. io_thread kết nối với máy chủ, đọc các sự kiện nhật ký nhị phân từ máy chủ khi chúng đến và chỉ sao chép chúng vào tệp nhật ký cục bộ có tên là relaylog. mặt khác, sql_thread đọc các sự kiện từ nhật ký chuyển tiếp được lưu trữ cục bộ trên nô lệ sao chép (tệp được ghi bởi luồng io) và sau đó áp dụng chúng nhanh nhất có thể. bất cứ khi nào quá trình sao chép bị trì hoãn, điều quan trọng trước tiên là phải khám phá xem nó đang bị trì hoãn trên nô lệ io_thread hay nô lệ sql_thread

thông thường, luồng i/o sẽ không gây ra độ trễ sao chép lớn vì nó chỉ đọc nhật ký nhị phân từ chủ. tuy nhiên, nó phụ thuộc vào kết nối mạng, độ trễ của mạng… giữa các máy chủ nhanh như thế nào. chuỗi i/o nô lệ có thể bị chậm do sử dụng băng thông cao. thông thường, khi nô lệ io_thread có thể đọc nhật ký nhị phân đủ nhanh, nó sẽ sao chép và chất đống các nhật ký chuyển tiếp trên nô lệ - đó là một dấu hiệu cho thấy nô lệ io_thread không phải là thủ phạm của độ trễ nô lệ

mặt khác, khi sql_thread nô lệ là nguồn gốc của sự chậm trễ sao chép thì có thể là do các truy vấn đến từ luồng sao chép mất quá nhiều thời gian để thực thi trên nô lệ. điều này đôi khi là do phần cứng khác nhau giữa chủ/tớ, các chỉ mục lược đồ khác nhau, khối lượng công việc. hơn nữa, khối lượng công việc oltp nô lệ đôi khi gây ra sự chậm trễ sao chép do khóa. chẳng hạn, nếu một lần đọc dài đối với bảng myisam chặn luồng sql hoặc bất kỳ giao dịch nào đối với bảng innodb sẽ tạo khóa ix và chặn ddl trong luồng sql. đồng thời, hãy tính đến việc nô lệ đó là một luồng trước mysql 5. 6, đó sẽ là một lý do khác cho sự chậm trễ trên nô lệ sql_thread

hãy để tôi chỉ cho bạn qua ví dụ về trạng thái chính/trạng thái nô lệ để xác định một trong hai nô lệ đang bị trễ trên nô lệ io_thread hoặc nô lệ sql_thread

mysql-master> show master status;
+------------------+--------------+------------------+------------------------------------------------------------------+
| file | position  | binlog_do_db | binlog_ignore_db | executed_gtid_set                                                |
+------------------+--------------+------------------+------------------------------------------------------------------+
| mysql-bin.018196 | 15818564     |                  | bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:1-2331947                   |
+------------------+--------------+------------------+------------------------------------------------------------------+
mysql-slave> show slave status\g
*************************** 1. row ***************************
slave_io_state: queueing master event to the relay log
master_host: master.example.com
master_user: repl
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.018192
read_master_log_pos: 10050480
relay_log_file: mysql-relay-bin.001796
relay_log_pos: 157090
relay_master_log_file: mysql-bin.018192
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
replicate_ignore_db:
replicate_do_table:
replicate_ignore_table:
replicate_wild_do_table:
replicate_wild_ignore_table:
last_errno: 0
last_error:
skip_counter: 0
exec_master_log_pos: 5395871
relay_log_space: 10056139
until_condition: none
until_log_file:
until_log_pos: 0
master_ssl_allowed: no
master_ssl_ca_file:
master_ssl_ca_path:
master_ssl_cert:
master_ssl_cipher:
master_ssl_key:
seconds_behind_master: 230775
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 2
master_uuid: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166
master_info_file: /var/lib/mysql/i1/data/master.info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: reading event from the relay log
master_retry_count: 86400
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166
executed_gtid_set: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166,
ea75c885-c2c5-11e3-b8ee-5cf3fcfc9640:1-1370
auto_position: 1

điều này rõ ràng gợi ý rằng nô lệ io_thread bị trễ và rõ ràng là do nô lệ sql_thread cũng bị trễ và nó dẫn đến sự chậm trễ sao chép. như bạn có thể thấy tệp nhật ký chính là mysql-bin. 018196 (tham số tệp từ trạng thái chính) và nô lệ io_thread nằm trên mysql-bin. 018192 ( master_log_file từ trạng thái nô lệ) cho biết nô lệ io_thread đang đọc từ tệp đó, trong khi ở chế độ chính, nó ghi trên mysql-bin. 018196 , vì vậy nô lệ io_thread đứng sau 4 binlog. trong khi đó, sql_thread nô lệ đang đọc từ cùng một tệp tôi. e. mysql-bin. 01819 2 (relay_master_log_file từ trạng thái nô lệ) điều này cho biết rằng sql_thread nô lệ đang áp dụng các sự kiện đủ nhanh, nhưng nó cũng bị trễ, điều này có thể được quan sát từ sự khác biệt giữa read_master_log_pos & exec_master_log_pos từ đầu ra trạng thái nô lệ hiển thị

bạn có thể tính toán độ trễ sql_thread của nô lệ từ read_master_log_pos – exec_master_log_pos nói chung miễn là đầu ra tham số master_log_file từ trạng thái nô lệ hiển thị và tham số relay_master_log_file từ đầu ra trạng thái nô lệ hiển thị là như nhau. điều này sẽ cung cấp cho bạn ý tưởng sơ bộ về việc sql_thread nô lệ đang áp dụng các sự kiện nhanh như thế nào. như tôi đã đề cập ở trên, nô lệ io_thread bị trễ như trong ví dụ này thì tất nhiên nô lệ sql_thread cũng ở phía sau. bạn có thể đọc mô tả chi tiết về các trường đầu ra trạng thái nô lệ hiển thị tại đây

đồng thời, tham số seconds_behind_master hiển thị độ trễ lớn tính bằng giây. tuy nhiên, điều này có thể gây hiểu lầm vì nó chỉ đo lường sự khác biệt giữa các dấu thời gian của nhật ký chuyển tiếp được thực hiện gần đây nhất, so với mục nhật ký chuyển tiếp được io_thread tải xuống gần đây nhất. nếu có nhiều binlog hơn trên master, thì Slave sẽ không tính chúng vào phép tính seconds_behind_master. bạn có thể đo độ trễ nô lệ chính xác hơn bằng cách sử dụng pt-beatbeat từ bộ công cụ percona. vì vậy, chúng tôi đã học cách kiểm tra độ trễ sao chép - đó là nô lệ io_thread hoặc nô lệ sql_thread. bây giờ, hãy để tôi cung cấp một số mẹo và đề xuất về nguyên nhân chính xác gây ra sự chậm trễ này

mẹo và đề xuất nguyên nhân gây ra sự chậm trễ sao chép và các cách khắc phục có thể

thông thường, nô lệ io_thread đứng sau do mạng chậm giữa chủ/tớ. hầu hết thời gian, việc bật sẽ giúp giảm thiểu độ trễ của io_thread nô lệ. một đề xuất khác là vô hiệu hóa đăng nhập nhị phân trên nô lệ vì nó cũng chuyên sâu về io trừ khi bạn yêu cầu nó để khôi phục thời điểm

để giảm thiểu độ trễ sql_thread của nô lệ, hãy tập trung vào tối ưu hóa truy vấn. khuyến nghị của tôi là bật tùy chọn cấu hình để các truy vấn được thực hiện bởi nô lệ mất nhiều thời gian hơn sẽ được ghi vào nhật ký chậm. để thu thập thêm thông tin về hiệu suất truy vấn, tôi cũng khuyên bạn nên đặt tùy chọn cấu hình thành “đầy đủ”

bằng cách này, chúng ta có thể biết liệu có các truy vấn được thực hiện bởi nô lệ sql_thread mất nhiều thời gian để hoàn thành hay không. bạn có thể theo dõi bài viết trước của tôi về cách bật nhật ký truy vấn chậm trong khoảng thời gian cụ thể với các tùy chọn được đề cập tại đây. và xin nhắc lại, biến log_slow_slave_statements lần đầu tiên được giới thiệu trong máy chủ percona 5. 1 hiện là một phần của vanilla mysql từ phiên bản 5. 6. 11 trong phiên bản ngược dòng của máy chủ mysql log_slow_slave_statements đã được giới thiệu dưới dạng tùy chọn dòng lệnh. chi tiết có thể được tìm thấy trong khi log_slow_verbosity là tính năng cụ thể của máy chủ percona

một lý do khác cho sự chậm trễ trên nô lệ sql_thread nếu bạn sử dụng định dạng binlog dựa trên hàng là nếu bất kỳ bảng cơ sở dữ liệu nào của bạn thiếu khóa chính hoặc khóa duy nhất thì nó sẽ quét tất cả các hàng của bảng để tìm dml trên nô lệ và gây ra sự chậm trễ sao chép, vì vậy hãy đảm bảo tất cả . kiểm tra báo cáo lỗi này để biết chi tiết http. // lỗi. mysql. com/lỗi. php?id=53375 bạn có thể sử dụng truy vấn dưới đây trên nô lệ để xác định bảng cơ sở dữ liệu nào thiếu khóa chính hoặc khóa duy nhất

mysql> select t.table_schema,t.table_name,engine
from information_schema.tables t inner join information_schema .columns c
on t.table_schema=c.table_schema and t.table_name=c.table_name
group by t.table_schema,t.table_name
having sum(if(column_key in ('pri','uni'), 1,0)) =0;

một cải tiến được thực hiện cho trường hợp này trong mysql 5. 6, trong đó hàm băm trong bộ nhớ được sử dụng để giải cứu

lưu ý rằng seconds_behind_master không được cập nhật trong khi chúng tôi đọc sự kiện rbr lớn, vì vậy, "tụt hậu" có thể liên quan đến điều đó - chúng tôi chưa hoàn thành việc đọc sự kiện. ví dụ: các giao dịch lớn sao chép theo hàng có thể gây ra sự chậm trễ ở phía nô lệ e. g. nếu bạn có bảng 10 triệu hàng và bạn thực hiện “xóa khỏi bảng trong đó id < 5000000″ 5m hàng sẽ được gửi đến nô lệ, mỗi hàng riêng biệt sẽ rất chậm. vì vậy, nếu thỉnh thoảng bạn phải xóa các hàng cũ nhất khỏi bảng lớn bằng cách sử dụng phân vùng có thể là giải pháp thay thế tốt cho việc này đối với một số loại khối lượng công việc mà thay vào đó, sử dụng xóa, sử dụng phân vùng cũ có thể tốt và chỉ có câu lệnh được sao chép vì nó sẽ là hoạt động ddl

để giải thích rõ hơn, giả sử bạn có phân vùng 1 chứa các hàng id từ 1 đến 1000000 , phân vùng 2 - id từ 1000001 đến 2000000, v.v. thay vì xóa thông qua câu lệnh "xóa khỏi bảng trong đó id <= 1000000;" . để thay đổi hướng dẫn kiểm tra hoạt động phân vùng – hãy xem cả bài đăng tuyệt vời này từ đồng nghiệp của tôi, Roman, giải thích các cơ sở có thể gây ra sự chậm trễ sao chép tại đây

pt-stalk là một trong những công cụ tốt nhất từ ​​​​bộ công cụ percona thu thập dữ liệu chẩn đoán khi xảy ra sự cố. bạn có thể thiết lập pt-stalk như sau để bất cứ khi nào có độ trễ nô lệ, nó có thể ghi lại thông tin chẩn đoán mà sau này chúng tôi có thể phân tích để kiểm tra xem chính xác nguyên nhân gây ra độ trễ

đây là cách bạn có thể thiết lập pt-stalk để nó thu thập dữ liệu chẩn đoán khi có độ trễ của nô lệ

------- pt-plug.sh contents
#!/bin/bash
trg_plugin() {
mysqladmin $ext_argv ping &> /dev/null
mysqld_alive=$?
if [[ $mysqld_alive == 0 ]]
then
seconds_behind_master=$(mysql $ext_argv -e "show slave status" --vertical | grep seconds_behind_master | awk '{print $2}')
echo $seconds_behind_master
else
echo 1
fi
}
# uncomment below to test that trg_plugin function works as expected
#trg_plugin
-------
-- that's the pt-plug.sh file you would need to create and then use it as below with pt-stalk:
$ /usr/bin/pt-stalk --function=/root/pt-plug.sh --variable=seconds_behind_master --threshold=300 --cycles=60 [email protected] --log=/root/pt-stalk.log --pid=/root/pt-stalk.pid --daemonize

bạn có thể điều chỉnh ngưỡng, hiện tại là 300 giây, kết hợp ngưỡng đó với –cycles, điều đó có nghĩa là nếu giá trị seconds_behind_master >= 300 trong 60 giây trở lên thì pt-stalk sẽ bắt đầu thu thập dữ liệu. thêm tùy chọn sẽ thông báo qua email khi pt-stalk thu thập dữ liệu. bạn có thể điều chỉnh ngưỡng pt-stalk phù hợp để đó là cách nó kích hoạt thu thập dữ liệu chẩn đoán trong sự cố

phần kết luận

một nô lệ tụt hậu là một vấn đề phức tạp nhưng là một vấn đề phổ biến trong sao chép mysql. Tôi đã cố gắng đề cập đến hầu hết các khía cạnh của sự chậm trễ sao chép trong bài đăng này. vui lòng chia sẻ trong phần nhận xét nếu bạn biết bất kỳ lý do nào khác gây ra sự chậm trễ sao chép

Các nguyên nhân phổ biến của độ trễ sao chép là gì?

Làm cách nào để cải thiện hiệu suất sao chép MySQL?

MySQL sao chép bị trì hoãn là gì?

Làm cách nào để sao chép nhanh hơn?