[MySQL] 잠금(Lock)운영 최적화-1

■ Internal Locking Methods

이 섹션에서는 내부 잠금에 대해 설명합니다. 즉, 여러 세션으로 테이블 내용에 대한 경합을 관리하기 위해 MySQL서버 자체에서 잠금이 수행됩니다. 이 유형의 잠금은 전적으로 서버에 의해 수행되며 다른 프로그램과 관련이 없기 때문에 내부적입니다.

+ Row-Level(행 수준) 잠금

+ 테이블 레벨(Table-Level) 잠금

+ 잠금 유형 선택

 

▶︎ 행 단위(Row-Level) Locking

MySQL은 InnoDB 테이블에 Row-level(행 수준) 잠금을 사용하여 여러 세션의 동시 쓰기 액세스를 지원하므로 다중 사용자, 동시성이 높은 OLTP 애플리케이션에 적합합니다.

 

단일 InnoDB 테이블에서 여러 개의 동시 쓰기 작업을 수행 할 때 교착 상태를 피하려면 트랜잭션의 후반부에 데이터 변경 명령문이 있어도 수정될 것으로 예상되는 각 행 그룹에 대해 SELECT ... FOR UPDATE 문을 실행하여 트랜잭션 시작시 필요한 잠금을 확보합니다. 트랜잭션이 둘 이상의 테이블을 수정하거나 잠금 상태이면 각 트랜잭션 내에서 동일한 순서로 적용 가능한 명령문을 발행합니다. InnoDB는 교착 상태를 자동으로 감지하고 영향을받는 트랜잭션중 하나를 롤백하기 때문에 교착 상태는 심각한 오류보다는 성능에 영향을줍니다.

 

동시성이 높은 시스템에서 교착 상태 감지로 인해 많은 스레드가 동일한 잠금을 기다릴 때 속도가 느려질 수 있습니다. 교착 상태가 발생할 때 교착 상태 감지를 비활성화하고 트랜잭션 롤백에 innodb_lock_wait_timeout 설정을 사용하는 것이 더 효율적일 수 있습니다. 교착 상태 탐지는 innodb_deadlock_detect 구성 옵션을 사용하여 비활성화 할 수 있습니다.

 

Row-level(행 수준) 잠금의 장점 :

+ 다른 세션이 다른 행에 액세스 할 때 잠금 충돌이 줄어 듭니다.

+ 롤백 변경이 적습니다.

+ 단일 행을 오랫동안 잠글 수 있습니다.

 

▶︎ Table-Level(테이블 단위) Locking

MySQL은 MyISAM, MEMORY 및 MERGE 테이블에 대해 테이블 수준 잠금을 사용하므로 한 번에 한 세션만 해당 테이블을 업데이트 할 수 있습니다. 이 잠금 수준으로 인해 이러한 스토리지 엔진이 읽기 전용, 대부분 읽기 또는 단일 사용자 애플리케이션에 더 적합합니다.

 

이러한 스토리지 엔진은 쿼리 시작시 항상 필요한 모든 잠금을 한 번에 요청하고 항상 같은 순서로 테이블을 잠금으로써 교착 상태를 피합니다. 트레이드 오프는 이 전략이 동시성을 줄인다는 것입니다. 테이블을 수정하려는 다른 세션은 현재 데이터 변경 명령문이 완료될 때까지 기다려야합니다.

 

Table-level(테이블 단위) 잠금의 장점 :

+ 상대적으로 적은 메모리 필요합니다. (행 잠금은 행 당(per row) 메모리 또는 행 그룹(Group of rows) 잠금이 필요함)

+ 단일 잠금만 관련되어 있어 테이블의 많은 부분에서 사용될때 빠릅니다.

+ 많은 양의 데이터에서 GROUP BY 작업을 자주 수행하거나 전체 테이블을 자주 스캔해야하는 경우 빠릅니다.

 

MySQL은 다음과 같이 테이블 쓰기 잠금을 부여합니다.

1. 테이블에 잠금이 없으면 쓰기 잠금을 설정합니다.

2. 그렇지 않으면 잠금 요청을 쓰기 잠금 대기열에 넣습니다.

 

MySQL은 다음과 같이 테이블 읽기 잠금을 부여합니다.

1. 테이블에 쓰기 잠금이 없으면 읽기 잠금을 설정합니다.

2. 그렇지 않으면 잠금 요청을 읽기 잠금 큐에 넣습니다.

 

테이블 업데이트는 테이블 검색보다 우선 순위가 높습니다. 따라서 잠금이 해제되면 쓰기 잠금 큐의 요청과 읽기 잠금 큐의 요청에 잠금을 사용할 수 있습니다. 이렇게하면 테이블에 대한 SELECT 활동이 많은 경우에도 테이블 업데이트가 "우선순위가 밀리지 않고 많은 양이 대기상태"로 되지 않습니다. 그러나 테이블에 대한 업데이트가 많은 경우 SELECT 문은 더 이상 업데이트가 없을 때까지 기다립니다.

 

읽기 및 쓰기 우선 순위 변경에 대한 자세한 내용은 8.11.2 절“테이블 잠금 문제”를 참조하십시오.

 

Table_locks_immediate 및 Table_locks_waited 상태 변수를 확인하여 시스템에서 테이블 잠금 경합을 분석할 수 있습니다. 이는 테이블 잠금 요청을 즉시 승인할 수 있는 횟수와 각각 대기해야하는 횟수를 나타냅니다.

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

 

Performance Schema의 lock 테이블에서도 Locking 정보를 확인할 수 있습니다.

SQL> select * from performance_schema.table_lock <——이게 맞는지 확인해볼것.

 

MyISAM 스토리지 엔진은 주어진 테이블에 대한 읽기와 쓰기 간의 경합을 줄이기 위해 동시 삽입을 지원합니다. MyISAM 테이블의 데이터 파일 중간에 사용 가능한 블록이 없는 경우 행은 항상 데이터 파일의 끝에 삽입됩니다. 이 경우 잠금없이 MyISAM 테이블에 대해 동시 INSERT 및 SELECT 문을 자유롭게 혼합 할 수 있습니다. 즉, 다른 클라이언트가 읽는 동안 MyISAM 테이블에 행을 삽입할 수 있습니다. 테이블 중간에서 행이 삭제되거나 업데이트되어 공간이 생길 수 있습니다. 공간이 있는 경우 동시 인서트는 비활성화되지만 모든 홀에 새 데이터가 채워지면 자동으로 다시 활성화됩니다. 이 동작을 제어하려면 concurrent_insert 시스템 변수를 사용합니다.

 

LOCK TABLES를 사용하여 테이블 잠금을 명시적으로 확보한 경우, 테이블을 잠근 상태에서 다른 세션이 동시 삽입을 수행 할 수 있도록 READ 잠금 대신 READ LOCAL 잠금을 요청할 수 있습니다.

 

동시 삽입이 불가능할 때 테이블 t1에 대해 많은 INSERT 및 SELECT 조작을 수행하기 위해 임시 테이블 temp_t1에 행을 삽입하고 임시 테이블의 행으로 실제 테이블을 업데이트 할 수 있습니다.

mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql> UNLOCK TABLES;

 

▶︎ 잠금 유형 선택

일반적으로 다음과 같은 경우 테이블 잠금이 행 레벨 잠금보다 우수합니다.

+ 테이블에 대한 대부분의 문장을 읽습니다.

+ 테이블에 대한 명령문은 읽기와 쓰기가 혼합된 것으로, 쓰기는 하나의 키 읽기로 가져올 수 있는 단일 행에 대한 업데이트 또는 삭제입니다.

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

DELETE FROM tbl_name WHERE unique_key_col=key_value;

+ 동시 INSERT 문과 결합된 SELECT 및 UPDATE 또는 DELETE 문이 거의 없습니다.

+ 쓰기없이 전체 테이블에 대한 많은 스캔 또는 GROUP BY 작업.

 

높은 수준의 잠금을 사용하면 잠금 오버 헤드가 행 수준 잠금보다 작기 때문에 다른 유형의 잠금을 지원하여 응용 프로그램을보다 쉽게 ​​조정할 수 있습니다.

 

 

# 행 수준 잠금 이외의 옵션 :

+ 많은 읽기와 동시에 한개의 쓰기를 가질 수 있는 버전 지정(Versioning)입니다. (예 : 동시 삽입을 위해 MySQL에서 사용됨). 이는 데이터베이스 또는 테이블이 액세스 시작시기에 따라 데이터에 대해 다른보기를 지원함을 의미합니다. 이에 대한 다른 일반적인 용어는 "time travel", "copy on write"또는 "copy on demand"입니다.

+ 주문형 복사는 많은 경우 행 수준 잠금보다 우수합니다. 그러나 최악의 경우 일반 잠금을 사용하는 것보다 훨씬 많은 메모리를 사용할 수 있습니다.

+ 행 수준 잠금을 사용하는 대신 MySQL에서 GET_LOCK() 및 RELEASE_LOCK()이 제공하는 것과 같은 응용 프로그램 수준 잠금을 사용할 수 있습니다. 이들은 권고 잠금이므로 서로 협력하는 응용 프로그램에서만 작동합니다.

 

 

■ Table Locking Issues

InnoDB 테이블은 행 수준 잠금을 사용하므로 여러 세션과 응용 프로그램이 서로를 기다리거나 일관되지 않은 결과를 생성하지 않고도 동일한 테이블에서 동시에 읽고 쓸 수 있습니다. 이 스토리지 엔진의 경우 추가 보호 기능을 제공하지 않지만 대신 동시성을 줄이므로 LOCK TABLES 문을 사용하지 않는것이 좋습니다. 자동 행 수준 잠금을 사용하면 가장 중요한 데이터가 있는 사용량이 많은 데이터베이스에 적합할 뿐만 아니라 테이블을 잠그거나 잠금 해제할 필요가 없으므로 응용프로그램 로직을 단순화할 수 있습니다. 결과적으로 InnoDB 스토리지 엔진은 MySQL의 기본값입니다.

 

MySQL은 InnoDB를 제외한 모든 스토리지 엔진에 대해 테이블 잠금(페이지, 행 또는 컬럼 잠금 대신)을 사용합니다. 잠금 작업 자체에는 많은 오버 헤드가 없습니다. 그러나 한 번에 하나의 세션만 테이블에 쓸 수 있으므로 이러한 다른 스토리지 엔진과의 최상의 성능을 위해서는 주로 쿼리가 거의 이루어지지 않거나 거의 삽입되거나 업데이트되지 않는 테이블에 주로 사용합니다.

+ InnoDB에 유리한 성능 고려 사항

+ 성능 문제 잠금에 대한 해결 방법

 

▶︎ InnoDB에 유리한 성능 고려 사항

InnoDB 또는 다른 스토리지 엔진을 사용하여 테이블을 생성할지 여부를 선택할 때 다음과 같은 테이블 잠금 단점을 알아야 합니다.

+ 테이블 잠금을 사용하면 많은 세션을 동시에 테이블에서 읽을 수 있지만 세션이 테이블에 쓰려면 먼저 독점 액세스 권한을 가져야합니다. 즉, 다른 세션이 테이블에서 먼저 완료되기를 기다려야 할 수도 있습니다. 업데이트중에 이 특정 테이블에 액세스하려는 다른 모든 세션은 업데이트가 완료될 때까지 기다려야합니다.

+ 테이블 잠금은 디스크가 가득 차서 세션을 진행하기 전에 사용 가능한 공간이 필요하기 때문에 세션이 대기중일 때 문제를 일으킵니다. 이 경우 문제점 테이블에 액세스하려는 모든 세션도 사용 가능한 디스크 공간이 더 확보될 때까지 대기 상태가 됩니다.

+ 실행하는데 시간이 오래 걸리는 SELECT 문은 그 동안 다른 세션이 테이블을 업데이트하지 못하도록 하여 다른 세션이 느리거나 응답하지 않는 것처럼 보이게합니다. 세션이 업데이트를 위해 테이블에 독점적으로 액세스하기를 기다리는 동안 SELECT 문을 실행하는 다른 세션은 그 뒤에 대기하므로 읽기 전용 세션의 경우에도 동시성이 줄어 듭니다.

 

▶︎ Locking(잠금)에 대한 성능 이슈 해결 방법

다음 항목은 테이블 잠금으로 인한 경합을 피하거나 줄이는 몇 가지 방법을 설명합니다.

+ 설정중에 CREATE TABLE ... ENGINE=INNODB를 사용하거나 기존 테이블에 ALTER TABLE ... ENGINE = INNODB를 사용하여 테이블을 InnoDB 스토리지 엔진으로 전환하는 것을 적극 권장합니다. 특수한 경우가 아닌이상 InnoDB Storage Engine을 권장합니다.

+ 더 짧은 시간 동안 테이블을 잠그도록 SELECT 문을 최적화하여 더 빠르게 실행될 수 있도록 합니다. 이를 위해 요약 테이블을 작성해야 할 수도 있습니다.

+ --low-priority-updates로 mysqld를 시작합니다. 테이블 레벨 잠금만 사용하는 스토리지 엔진 (예 : MyISAM, MEMORY 및 MERGE)의 경우 SELECT 문보다 우선 순위가 낮은 테이블을 업데이트(혹은 수정)하는 모든 명령문을 제공합니다. 이 경우 이전 시나리오의 두번째 SELECT 문은 UPDATE 문보다 먼저 실행되며 첫 번째 SELECT가 완료될 때까지 기다리지 않습니다.

+ 특정 연결에서 발행된 모든 업데이트가 낮은 우선 순위로 수행되도록 지정하려면 low_priority_updates 서버 시스템 변수를 1로 설정합니다.

+ 특정 INSERT, UPDATE 또는 DELETE 문에 우선 순위를 낮추려면 LOW_PRIORITY 속성을 사용합니다.

+ 특정 SELECT 문에 우선 순위를 부여하려면 HIGH_PRIORITY 속성을 사용합니다.

+ max_write_lock_count 시스템 변수에 대해 낮은 값으로 mysqld를 시작하여 테이블에 특정 수의 삽입이 발생한 후 MySQL이 테이블을 기다리는 모든 SELECT 문의 우선 순위를 일시적으로 상승시킵니다. 이렇게하면 특정 수의 WRITE 잠금 후에 READ 잠금이 허용됩니다.

+ 혼합 SELECT 및 DELETE 문에 문제가 있는 경우 DELETE에 대한 LIMIT 옵션이 도움이 될 수 있습니다.

+ SELECT 문에 SQL_BUFFER_RESULT를 사용하면 테이블 잠금 기간을 단축 할 수 있습니다.

+ 테이블 내용을 별도의 테이블로 분할하면 한 테이블의 컬럼에 대해 쿼리를 실행할 수 있고 업데이트는 다른 테이블의 컬럼으로 제한되므로 성능에 도움이 될 수 있습니다.

+ 단일 대기열을 사용하도록 mysys/thr_lock.c의 잠금 코드를 변경할 수 있습니다. 이 경우 쓰기 잠금과 읽기 잠금의 우선 순위가 동일하므로 일부 응용 프로그램에 도움이 될 수 있습니다.

 

■ 동시 Insert(삽입)

MyISAM 스토리지 엔진은 주어진 테이블에 대한 리더와 라이터 간의 경합을 줄이기 위해 동시 삽입을 지원합니다. MyISAM 테이블에 데이터 파일의 중간중간에 빈 공간이 없는 경우 (중간에서 삭제된 행), INSERT 문을 실행하여 끝에 행을 추가 할 수 있습니다. SELECT 문이 테이블에서 행을 읽는 것과 동시에 테이블의 여러 개의 INSERT 문이 있으면 SELECT 문과 동시에 큐에 대기되고 수행됩니다. 동시 INSERT 결과는 즉시 보이지 않을 수 있습니다.

 

동시 삽입 처리를 수정하도록 concurrent_insert 시스템 변수를 설정할 수 있습니다. 기본적으로 변수는 AUTO (또는 1)로 설정되고 동시 삽입은 방금 설명한대로 처리됩니다. synchronize_insert가 NEVER (또는 0)로 설정되면 동시 삽입이 비활성화됩니다. 변수가 ALWAYS (또는 2)로 설정되면 행이 삭제된 테이블의 경우에도 테이블 끝에 동시 삽입이 허용됩니다. synchronize_insert 시스템 변수에 대해서도 확인이 필요힙니다.

 

바이너리 로그를 사용하는 경우 동시 삽입은 CREATE ... SELECT 또는 INSERT ... SELECT 문에 대한 일반 삽입으로 변환됩니다. 이는 백업 조작 중 로그를 적용하여 테이블의 정확한 복제본을 다시 작성할 수 있도록하기 위해 수행됩니다. 또한 해당 명령문의 경우 테이블에서 삽입이 차단되도록 선택된 시작 테이블에 읽기 잠금이 설정됩니다. 결과적으로 해당 테이블에 대한 동시 삽입도 대기해야 합니다.

 

LOAD DATA를 사용하면 동시 삽입 조건 (중간에 사용 가능한 블록이 없음)을 충족시키는 MyISAM 테이블로 CONCURRENT를 지정하면 LOAD DATA가 실행되는 동안 다른 세션이 테이블에서 데이터를 검색 할 수 있습니다. CONCURRENT 옵션을 사용하면 다른 세션이 동시에 테이블을 사용하지 않더라도 LOAD DATA의 성능에 약간 영향을 줍니다.

 

HIGH_PRIORITY를 지정하면 서버가 해당 옵션으로 시작된 경우 --low-priority-updates 옵션의 영향을 무시합니다. 또한 동시 삽입을 사용하지 않습니다.

 

LOCK TABLE의 경우, READ LOCAL과 READ의 차이점은 READ LOCAL은 잠금이 유지되는 동안 충돌하지 않는 INSERT 문 (동시 삽입)을 실행할 수 있다는 것입니다. 그러나 잠금을 보유한 상태에서 서버 외부의 프로세스를 사용하여 데이터베이스를 조작하려는 경우에는 사용할 수 없습니다.