원인 파악

오늘 담당하는 서비스에서 에러가 발생하여 로그를 확인해보니 MySQL 1206 'The total number of locks exceeds the lock table size' 에러가 발생하였다.

해당 내용을 검색 해보니 SELECT 시 설정 된 innodb_buffer_size보다 실제 저장된 buffer크기가 커서 테이블에 lock이 걸리면서 이후 테이블 update 또는 insert 시 해당 에러가 발생하였다.

조금 더 확인을 해보면 MySQL의 경우 'SELECT'문을 호출하면 해당 결과를 메모리로 가지고 온다. 그 메모리가 buffer이고 조회 된 결과가 buffer의 size를 초과하는 경우 MySQL이 조회하는 테이블에 변경을 막기 위해 테이블에 LOCK을 걸어버린다. 그 동안 다른 트랜잭션이 변경할 수 없도록 'The total number of locks exceeds the lock table size' 에러를 발생하여 변경을 못하게 한다.

조치사항

innodb_buffer_pool_size를 변경하는 방법은 다양하게 있다. 먼저 현재 상태를 확인해보자.

innodb_buffer_pool

버퍼 풀은 디스크 액세스를 줄이기 위한 캐시의 역할이다. OS에서 캐시가 크면 성능에 유리하듯이 버퍼 풀의 크기가 클수록 성능에 유리하다. 조회한 데이터가 메모리에 올라와 있으므로 Disk I/O 를 발생시키지 않기 때문이다.
보통 DB만 운영하는 서버의 경우 메모리의 50~80%까지 innodb_buffer_pool를 사용할 수 있다고 한다.

buffer pool size 확인

SHOW STATUS LIKE '%innodb_buffer_pool%';

Shell에서 변경

shell > mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16

MySQL 설정 파일

1. /{MySQL 경로}/my.cnf 또는 /etc/my.cnf 열기
2. [mysqld] 영역에서 innodb_buffer_pool_size 수정
3. 저장
4. service mysqld restart

MySQL에서 실행

mysql > SET global innodb_buffer_pool_size=4294967296

1. MySQL에서 설정하는 경우 Byte 단위로 입력해야한다.
2. MySQL에서 설정하는 방법은 MySQL5.7 이상부터 가능하다.