10.5.2 Optimizing InnoDB Transaction Management

10.5.2 InnoDB Transaction Management 최적화

InnoDB 트랜잭션 처리를 최적화하려면, 트랜잭션 기능이 가져오는 성능 오버헤드와 서버의 워크로드 사이에서 이상적인 균형을 찾아야 합니다. 예를 들어, 어떤 애플리케이션은 초당 수천 번 COMMIT을 수행할 때 성능 문제가 발생할 수 있고, 반대로 2~3시간에 한 번씩만 COMMIT을 수행할 때 또 다른 종류의 성능 문제가 발생할 수 있습니다.

• 기본 MySQL 설정인 AUTOCOMMIT=1 은 바쁜 데이터베이스 서버에서 성능적 제약을 야기할 수 있습니다. 가능하다면 여러 관련 데이터 변경 작업을 하나의 트랜잭션으로 묶어서, SET AUTOCOMMIT=0 또는 START TRANSACTION 구문을 실행한 다음, 모든 변경을 마친 후에 COMMIT 구문을 실행하십시오.

  InnoDB는 해당 트랜잭션이 데이터베이스에 대한 변경을 수행했다면 각 트랜잭션 COMMIT마다 로그를 디스크로 플러시해야 합니다. (기본 autocommit 설정처럼) 각 변경마다 COMMIT이 뒤따르면, 스토리지 장치의 I/O 처리량이 초당 수행 가능한 잠재적 작업 수에 상한을 두게 됩니다.

• 반대로, 단일 SELECT 구문으로만 구성된 트랜잭션의 경우, AUTOCOMMIT을 켜 두면 InnoDB가 읽기 전용 트랜잭션을 인식하고 이를 최적화하는 데 도움이 됩니다. 요구 사항은 Section 10.5.3, “Optimizing InnoDB Read-Only Transactions”을 참조하십시오.

• 매우 많은 row를 insert, update 또는 delete한 뒤에 롤백을 수행하는 것은 피하십시오. 큰 트랜잭션이 서버 성능을 저하시킨다면, 이를 롤백하는 것이 문제를 더 악화시킬 수 있으며, 원래의 데이터 변경 작업보다 여러 배 더 오래 걸릴 수 있습니다. 데이터베이스 프로세스를 kill해도 도움이 되지 않습니다. 서버가 다시 시작되면 롤백이 다시 시작되기 때문입니다.

이 문제가 발생할 가능성을 최소화하려면:

• buffer pool의 크기를 늘려서, 모든 데이터 변경이 즉시 디스크로 기록되는 대신 캐시될 수 있도록 합니다.

• innodb_change_buffering=all 로 설정하여 insert 외에도 update 및 delete 작업이 버퍼에 저장되도록 합니다.

• 대규모 데이터 변경 작업 중에 주기적으로 COMMIT 구문을 실행하는 것을 고려하십시오. 단일 delete 또는 update를 더 작은 수의 row에 대해 작동하는 여러 구문으로 나누는 방식일 수 있습니다.

이미 발생한 제어 불능 롤백을 제거하려면, buffer pool을 늘려서 롤백이 CPU 바운드가 되어 빠르게 실행되도록 하거나, 서버를 종료한 뒤 innodb_force_recovery=3으로 재시작하십시오. 자세한 내용은 Section 17.18.2, “InnoDB Recovery”를 참조하십시오.

• 예기치 않은 종료가 발생했을 때, 가장 최근에 COMMIT된 일부 트랜잭션 손실을 감수할 수 있다면, innodb_flush_log_at_trx_commit 파라미터를 0으로 설정할 수 있습니다. InnoDB는 어쨌든 1초에 한 번씩 로그를 플러시하려고 시도하지만, 이 플러시는 보장되지 않습니다.

• row가 변경되거나 삭제될 때, 해당 row와 연관된 undo logs는 즉시, 혹은 트랜잭션이 COMMIT된 직후에 즉시 물리적으로 제거되지 않습니다. 이전 상태의 데이터는 더 일찍 또는 동시에 시작된 트랜잭션이 완료될 때까지 보존되어, 그러한 트랜잭션이 변경 또는 삭제된 row의 이전 상태에 접근할 수 있도록 합니다. 따라서, long-running 트랜잭션은 다른 트랜잭션에 의해 변경된 데이터를 InnoDB가 purge하지 못하게 막을 수 있습니다.

• long-running 트랜잭션 내에서 row가 변경되거나 삭제되면, READ COMMITTED 및 REPEATABLE READ 격리 수준을 사용하는 다른 트랜잭션이 동일한 row를 읽을 경우, 오래된 데이터를 재구성하기 위해 더 많은 작업을 수행해야 합니다.

• long-running 트랜잭션이 테이블을 변경하는 경우, 다른 트랜잭션에서 해당 테이블을 대상으로 수행되는 쿼리는 covering index 기법을 사용하지 못합니다. 일반적으로 세컨더리 인덱스만으로 모든 결과 컬럼을 가져올 수 있는 쿼리도, 대신 테이블 데이터에서 적절한 값을 조회해야 합니다.

  세컨더리 인덱스 페이지의 PAGE_MAX_TRX_ID 값이 너무 최신이거나, 세컨더리 인덱스의 레코드가 delete-mark 처리된 것으로 발견되면, InnoDB는 클러스터드 인덱스를 사용하여 레코드 조회를 수행해야 할 수 있습니다.