[ DML ] Rollback 시간 산정

가끔 대용량 작업을 LIMIT 처리 없이 작업을 하다가 이슈가 발생하게 되었을 때 해당 SESSION을 KILL 하는 경우들이 발생한다. 이때 DML 작업 시간이 길었던 만큼 ROLLBACK 작업이 길어지는데 이때 해당 테이블에 DML, DDL작업이 불가능하게 되며 이로 인해 서비스 문제가 발생하게 되는 경우가 있다. ROLLBACK 시간을 산정하여 이슈에 어떻게 대처할지를 선택 해야 한다. EX) ROLLBACK을 기다릴지, 새로운 장비를 사용할지 등등

 

이번엔 Rollback이 완료 되는데 얼마큼의 시간이 걸리는지 확인하는 방법에 대해 작성해 보려 한다. 

 

DML

Rollback 데이터를 쌓기 위해서 적당히 큰 테이블에 전체 UPDATE 수행

[test]> update lee_test set reason='TEST';

 

KILL SESSION

문제가 생겨 Kill Session을 하게 되었고 Rollback이 수행되게 된다.

[(none)]> show processlist;
+---------+-----------------+----------------------+------+-------------+--------+-----------------------------------------------------------------+--------------------------------------+
| Id      | User            | Host                 | db   | Command     | Time   | State                                                           | Info                                 |
+---------+-----------------+----------------------+------+-------------+--------+-----------------------------------------------------------------+--------------------------------------+
|       5 | event_scheduler | localhost            | NULL | Daemon      | 957113 | Waiting on empty queue                                          | NULL                                 |
| 1336824 | lee             | localhost            | NULL | Query       |      0 | init                                                            | show processlist                     |
| 1337084 | lee             | localhost            | test | Query       |    193 | updating                                                        | update lee_test set reason='TEST'    |
+---------+-----------------+----------------------+------+-------------+--------+-----------------------------------------------------------------+--------------------------------------+
4 rows in set (0.00 sec)

( lee@localhost) [(none)]> kill 1337084;
Query OK, 0 rows affected (0.00 sec)

(none)]> show processlist;
+---------+-----------------+----------------------+------+-------------+--------+-----------------------------------------------------------------+--------------------------------------+
| Id      | User            | Host                 | db   | Command     | Time   | State                                                           | Info                                 |
+---------+-----------------+----------------------+------+-------------+--------+-----------------------------------------------------------------+--------------------------------------+
|       5 | event_scheduler | localhost            | NULL | Daemon      | 957128 | Waiting on empty queue                                          | NULL                                 |
| 1336824 | lee             | localhost            | NULL | Query       |      0 | init                                                            | show processlist                     |
| 1337084 | lee             | localhost            | test | Killed      |    208 | query end                                                       | update lee_test set reason='TEST'    |
+---------+-----------------+----------------------+------+-------------+--------+-----------------------------------------------------------------+--------------------------------------+
5 rows in set (0.00 sec)

 

 

ROLLBACK 상황 확인

Thraed 상태 확인 / Sleep 60 / Thread 상태 확인 순으로 쿼리를 수행하여 1분에 얼만큼의  trx_rows_modified가 변경 되고있는지 확인한다.

(lee@localhost) [(none)]> select * from information_schema.innodb_trx\G select sleep(60); select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 10787984
                 trx_state: LOCK WAIT
               trx_started: 2024-07-01 14:14:40
     trx_requested_lock_id: 140178294608680:537:615295:2:140178203168472
          trx_wait_started: 2024-07-01 14:14:40
                trx_weight: 2
       trx_mysql_thread_id: 1338269
                 trx_query: update lee_test set reason='TEST'
       trx_operation_state: fetching rows
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: 1
*************************** 2. row ***************************
                    trx_id: 10787979
                 trx_state: ROLLING BACK
               trx_started: 2024-07-01 14:05:00
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 4618983
       trx_mysql_thread_id: 1337084
                 trx_query: update lee_test set reason='TEST'
       trx_operation_state: rollback of SQL statement
         trx_tables_in_use: 1
         trx_tables_locked: 2
          trx_lock_structs: 50660
     trx_lock_memory_bytes: 5988472
           trx_rows_locked: 5992748
         trx_rows_modified: 4568323
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
2 rows in set (0.00 sec)

+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

*************************** 1. row ***************************
                    trx_id: 10787979
                 trx_state: ROLLING BACK
               trx_started: 2024-07-01 14:05:00
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 1597721
       trx_mysql_thread_id: 1337084
                 trx_query: update lee_test set reason='TEST'
       trx_operation_state: rollback of SQL statement
         trx_tables_in_use: 1
         trx_tables_locked: 2
          trx_lock_structs: 50660
     trx_lock_memory_bytes: 5988472
           trx_rows_locked: 5992748
         trx_rows_modified: 1547061
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

 

 

ROLLBACK 예상 소요 시간 계산

select SEC_TO_TIME(round((trx_rows_modified*60)/( trx_rows_modified- trx_rows_modified));

(lee@localhost) [(none)]> select SEC_TO_TIME(round((1547061*60)/(4568323-1547061))) as 'Estimation Time of Rollback';
+-----------------------------+
| Estimation Time of Rollback |
+-----------------------------+
| 00:00:31                    |
+-----------------------------+
1 row in set (0.00 sec)