Chuyển tới nội dung chính

Xử lý sự cố MySQL

Trang Xử lý sự cố MySQL cung cấp hướng dẫn nhanh giúp bạn xác định và khắc phục các vấn đề thường gặp khi sử dụng dịch vụ MySQL trên FPT Cloud DBaaS. Phần này tập trung vào các vấn đề thường xuyên gặp như lỗi kết nối, lỗi cấu hình, suy giảm hiệu năng, lỗi sao lưu/khôi phục và các tình huống đặc thù của MySQL. Mỗi chủ đề bao gồm mô tả rõ ràng về triệu chứng, nguyên nhân gốc rễ và cách xử lý được khuyến nghị, giúp bạn có thể tự xử lý sự cố ban đầu hoặc phối hợp hiệu quả với đội ngũ hỗ trợ kỹ thuật. Mục tiêu của tài liệu này là giúp bạn vận hành MySQL một cách ổn định, an toàn và hiệu quả, đồng thời giảm thiểu gián đoạn dịch vụ khi xảy ra sự cố.

Các nhóm lỗi:​

  1. Backup Error: "Please run OPTIMIZE TABLE on all listed tables…"
  2. Server Crash: MySQL crash khi sử dụng composite index trên cột JSON
  3. Metadata Lock Storm

1. Backup Error: "Please run OPTIMIZE TABLE on all listed tables…"​

1.1. Triệu chứng​

Trong quá trình sao lưu MySQL trên FPT Database Engine, tác vụ sao lưu có thể thất bại và gửi thông báo lỗi qua email tương tự như thông điệp ở trên: cluster_id : abcxyz11 cluster_name : clustername vdc_name : ABCXYZ_VCD org_name : ABCXYZ-ORG start_time : 10/23/2055 00:30:02 backup_type : diff backup_size : NoneG backup_log : ERROR: Please run OPTIMIZE TABLE on all listed tables to fix this issue. Tables found: db/transactions... backup_state : failed created_at : 10/23/2055 00:31:01 Vấn đề này gây ra bởi một lỗi đã biết trong Percona XtraBackup , công cụ sao lưu được FCI sử dụng cho MySQL.

1.2. Nguyên nhân gốc rễ​

Bắt đầu từ MySQL 8.0.29 , InnoDB hỗ trợ INSTANT ADD/DROP COLUMN , cho phép thêm hoặc xóa cột mà không cần rebuild lại bảng. Đặc điểm của lệnh INSTANT:

  • Không copy toàn bộ bảng.
  • Không rebuild bảng.
  • Chỉ ghi thêm metadata vào InnoDB dictionary (thể hiện qua TOTAL_ROW_VERSIONS > 0).

Tuy nhiên, XtraBackup không tương thích hoàn toàn với các bảng có TOTAL_ROW_VERSIONS > 0. Kết quả là XtraBackup không thể xử lý các bảng đã sử dụng INSTANT ADD/DROP COLUMN, khiến tiến trình sao lưu dừng lại và trả về lỗi yêu cầu chạy OPTIMIZE TABLE.

1.3. Ảnh hưởng​

Lỗi này dẫn đến các tác động sau:

  • Giảm hiệu năng truy vấn: Các thao tác chậm hơn do dữ liệu không được sắp xếp tối ưu.
  • Tăng tải hệ thống: Tiêu tốn tài nguyên hệ thống và bộ nhớ.
  • Chèn hoặc cập nhật dữ liệu chậm: Các thao tác INSERT và UPDATE có thể mất nhiều thời gian hơn.
  • Khó khăn trong bảo trì và sao lưu: Các bảng bị phân mảnh có thể làm chậm tiến trình sao lưu hoặc khôi phục.

1.4. Giải pháp và Khuyến nghị​

Rebuild lại bảng bị ảnh hưởng để loại bỏ metadata INSTANT bằng lệnh sau:

CopyOPTIMIZE TABLE db.transactions;

Sau khi thực hiện:

  • Bảng được rebuild hoàn toàn.
  • Metadata cột INSTANT bị loại bỏ.
  • TOTAL_ROW_VERSIONS trở về 0.
  • Các tác vụ sao lưu hoạt động bình thường trở lại.

Lưu ý :

  • OPTIMIZE TABLE rebuild lại toàn bộ bảng và có thể gây ra WRITE lock.
  • Với các bảng lớn, quá trình rebuild có thể mất nhiều thời gian. Hãy thực hiện thao tác này vào giờ thấp điểm.
  • Đảm bảo dung lượng lưu trữ tạm đủ trước khi thực hiện.

2. Server Crash: MySQL crash khi sử dụng composite index trên cột JSON​

2.1. Triệu chứng​

Khi sử dụng composite index (đa cột) có chứa cột JSON, các node MySQL có thể bị crash bất ngờ: ` 22:20:45 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunc-tioning hardware.
Thread pointer: 0x407b5f0a3000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 407b6c43de60 thread_stack 0x40000
/rdsdbbin/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x30) [0x1f7bb30]
/rdsdbbin/mysql/bin/mysqld(print_fatal_signal(int)+0x27c) [0xfaa93c]
/rdsdbbin/mysql/bin/mysqld(handle_fatal_signal+0xc4) [0xfaaa64]
linux-vdso.so.1(__kernel_rt_sigreturn+0) [0x400018f2a668]
/rdsdbbin/mysql/bin/mysqld(row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned char const*, un-signed long)+0x164) [0x2225a64]
/rdsdbbin/mysql/bin/mysqld(row_sel_store_mysql_rec(unsigned char*, row_prebuilt_t*, unsigned char const*, dtuple_t const*, bool, dict_index_t const*, dict_index_t const*, unsigned long const*, bool, lob::undo_vers_t*, mem_block_info_t*&)+0x19c) [0x2228e3c]
/rdsdbbin/mysql/bin/mysqld(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long)+0x1a90) [0x222e410]
/rdsdbbin/mysql/bin/mysqld(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function)+0x378) [0x20de778]
/rdsdbbin/mysql/bin/mysqld(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)+0x3f8) [0x10aef98]
/rdsdbbin/mysql/bin/mysqld(handler::read_range_first(key_range const*, key_range const*, bool, bool)+0x60) [0x10afa20]
/rdsdbbin/mysql/bin/mysqld(ha_innobase::read_range_first(key_range const*, key_range const*, bool, bool)+0x1c) [0x20a955c]
/rdsdbbin/mysql/bin/mysqld(handler::multi_range_read_next(char**)+0x118) [0x10b02d8]
/rdsdbbin/mysql/bin/mysqld(handler::ha_multi_range_read_next(char**)+0x34) [0x10ac174]
/rdsdbbin/mysql/bin/mysqld(IndexRangeScanIterator::Read()+0x3c) [0x12b951c]
/rdsdbbin/mysql/bin/mysqld(IndexMergeIterator::Init()+0x130) [0x12b88f0]
/rdsdbbin/mysql/bin/mysqld(filesort(THD*, Filesort*, RowIterator*, unsigned long, unsigned long long, Filesort_info*, Sort_result*, unsigned long long*)+0x178) [0x1093e18]
/rdsdbbin/mysql/bin/mysqld(SortingIterator::DoSort()+0x54) [0x1200794]
/rdsdbbin/mysql/bin/mysqld(SortingIterator::Init()+0x1c) [0x120081c]
/rdsdbbin/mysql/bin/mysqld(LimitOffsetIterator::Init()+0x20) [0x135dd40]
/rdsdbbin/mysql/bin/mysqld(Query_expression::ExecuteIteratorQuery(THD*)+0x200) [0xf18160]
/rdsdbbin/mysql/bin/mysqld(Query_expression::execute(THD*)+0x30) [0xf185b0]
/rdsdbbin/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x158) [0xea7458]
/rdsdbbin/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0xf2c) [0xe5504c]
/rdsdbbin/mysql/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x340) [0xe580c0]
/rdsdbbin/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1560) [0xe59b40]
/rdsdbbin/mysql/bin/mysqld(do_command(THD*)+0x1e0) [0xe5ae40]
/rdsdbbin/mysql/bin/mysqld() [0xf9cc88]
/rdsdbbin/mysql/bin/mysqld() [0x2518490]
/lib64/libpthread.so.0(+0x722c) [0x400018fe622c]
/lib64/libc.so.6(+0xd46dc) [0x4000191da6dc]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (407ad76b1830): SELECT fort_knox_funds_flows.* FROM fort_knox_funds_flows WHERE (25830440 MEMBER OF(fort_knox_funds_flows.money_movements ->> "$[].to_user_id") OR 25830440 MEMBER OF(fort_knox_funds_flows.money_movements ->> "$[].from_user_id")) ORDER BY fort_knox_funds_flows.created_at DESC LIMIT 20
Connection ID (thread ID): 624306
Status: NOT_KILLED
cluster_name : clustername
` Đây là lỗi MySQL liên quan đến việc sử dụng associative index trên cột JSON, gây ra sự cố crash node MySQL. Bạn có thể xem thêm chi tiết về lỗi này tại đường dẫn: https://bugs.mysql.com/bug.php?id=109542.

2.2. Nguyên nhân gốc rễ​

Từ MySQL 8.0.2x trở đi, các bảng có định nghĩa INDEX sử dụng các field trong cột JSON có thể gặp sự cố crash như đã mô tả. Nguyên nhân chính của lỗi này là MySQL gặp vấn đề khi cố gắng tạo và duy trì index trên các cột JSON trong composite index. Cụ thể:

  • Index không tương thích với cột JSON: MySQL không thể xử lý đúng các đối tượng JSON trong composite index, dẫn đến lỗi bộ nhớ hoặc xử lý bất đồng bộ.
  • Vấn đề với việc lưu trữ và truy xuất dữ liệu JSON: MySQL không tối ưu trong việc lưu trữ và truy xuất dữ liệu JSON trong composite index, dẫn đến crash.
  • Tương tác với các tính năng lưu trữ đặc biệt (như Full Disk Encryption) có thể làm tăng mức độ nghiêm trọng của lỗi.

2.3. Ảnh hưởng​

Khi lỗi xảy ra, MySQL có thể crash hoặc khởi động lại bất ngờ, và trong một số trường hợp, cơ sở dữ liệu có thể không thể phục hồi sau crash. Điều này sẽ ảnh hưởng nghiêm trọng đến tính khả dụng và độ tin cậy của dịch vụ, đặc biệt trong môi trường production.

2.5. Giải pháp và Khuyến nghị​

  • Sử dụng single index thay vì composite index khi làm việc với cột JSON.
  • Tránh tạo index trực tiếp trên cột JSON, hoặc nếu cần thiết, sử dụng generated column để index các giá trị cụ thể trong JSON.
  • Cập nhật MySQL lên các phiên bản mới hơn (ví dụ: 8.0.42), vì lỗi này đã được xác nhận và sửa trong một số phiên bản MySQL sau khi được báo cáo.

3. Metadata Lock Storm​

3.1. Triệu chứng​

Khi sử dụng MySQL theo mô hình HA, tình huống sau có thể xảy ra: cluster cơ sở dữ liệu MySQL hoạt động bình thường (master node đọc/ghi không có vấn đề), nhưng trên hai slave node, độ trễ dữ liệu có thể tăng đáng kể (lên đến khoảng 2 giờ). Đồng thời, trên các slave node, nhiều thread xuất hiện ở trạng thái "Waiting for table metadata lock", như sau: 1073 admin 10.225.65.36:25680 fpt Query 178 Waiting for table metadata lock SELECT COUNT(1) AScntFROMuser_notifications UserNotificationEntity WHERE ( ((UserNotificat
1075 admin 10.225.65.36:25694 fpt Query 178 Waiting for table metadata lock SELECT COUNT(1) AS cnt FROM user_notifications UserNotificationEntity WHERE ( ((UserNotificat 1077 admin 10.225.65.36:34558 fpt Query 176 Waiting for table metadata lock SELECT COUNT(1) AS cntFROMuser_notifications UserNotificationEntity WHERE ( ((UserNotificat
1078 admin 10.225.65.36:34570 fpt Query 174 Waiting for table metadata lock SELECT COUNT(1) AS cnt FROM user_notifications UserNotificationEntity WHERE ( ((UserNotificat 1083 agent 10.225.65.46:36868 NULL Query 167 Waiting for table metadata lock SELECT table_schema, table_name, column_name, auto_increment,\n CAST(pow(2, case data_type\n 1116 admin 10.225.65.36:49646 fpt Query 125 Waiting for table metadata lock SELECT COUNT(1) AS cntFROMuser_notifications UserNotificationEntity WHERE ( ((UserNotificat
1118 admin 10.225.65.36:49652 fpt Query 124 Waiting for table metadata lock SELECT COUNT(1) AS cnt FROM user_notifications UserNotificationEntity WHERE ( ((UserNotificat Lỗi này xảy ra sau khi bạn thực thi lệnh DDL trên bảng user_notifications, khiến cơ sở dữ liệu (2 slave node) rơi vào trạng thái Metadata Lock Storm.

3.2. Nguyên nhân gốc rễ​

MySQL sử dụng Metadata Lock (MDL) để bảo vệ thông tin cấu trúc của các bảng (bao gồm cấp schema và cấp table) khi thực thi các lệnh Data Definition Language (DDL) và Data Manipulation Language (DML). Trong môi trường MySQL InnoDB Cluster, các giao dịch DDL được replicate (như ALTER TABLE, CREATE INDEX, DROP TABLE, v.v.) được thực thi tuần tự trên các slave node bởi applier thread. Tuy nhiên, vì một lý do nào đó, chẳng hạn như một truy vấn dài hoặc DML chưa commit, session của người dùng có thể đang giữ MDL lock. Điều này có thể dẫn đến việc applier thread phải chờ lock quá lâu. Khi applier thread không thể lấy được lock kịp thời, các giao dịch khác trong hàng đợi GR (Global Replication Queue) trở nên tắc nghẽn (như thấy ở bảng user_notifications trong ví dụ trên). Kết quả là applier thread trên slave node bị treo, khiến các thread truy cập bảng cũng bị treo, gây khó khăn trong việc truy cập slave node và dẫn đến lỗi ứng dụng.

3.3. Ảnh hưởng​

Khi lỗi này xảy ra, các truy vấn và giao dịch của người dùng bị chặn, dẫn đến gián đoạn truy cập dữ liệu và tăng độ trễ hệ thống. Trong môi trường MySQL Cluster, điều này có thể làm tắc nghẽn các giao dịch replication, gây trễ dữ liệu và giảm hiệu năng tổng thể của hệ thống.

3.4. Giải pháp và Khuyến nghị​

Để tránh Metadata Lock Storm trong trường hợp này, nên thực hiện các bước sau:

  • Bước 1: Tạm thời dừng các ứng dụng hoặc dịch vụ đang truy cập vào bảng liên quan đến tác vụ DDL → Mục đích: ngăn chặn việc tạo ra các truy vấn mới giữ metadata lock hoặc chờ lock.
  • Bước 2: Khởi động lại các slave node để giải phóng các thread đang giữ lock → Việc khởi động lại giúp kết thúc các session hoặc thread "bị kẹt", từ đó cho phép tiến trình DDL được áp dụng thành công.

Khuyến nghị : Khi nâng cấp cluster cơ sở dữ liệu với các lệnh DDL trên những bảng có QPS (Queries Per Second) cao, hãy ngắt kết nối ứng dụng khỏi các đối tượng (bảng, index, v.v.) mà câu lệnh DDL ảnh hưởng đến. Điều này giúp tránh Metadata Lock Storm, đảm bảo quá trình nâng cấp diễn ra suôn sẻ mà không gây gián đoạn hệ thống.

Cập nhật lần cuối vào bởi Dang Tran