4.3 아키텍처 (하)

4.2.12 어댑티브 해시 인덱스

 일반적으로 '인덱스'는 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미한다. 또는 사용자가 직접 테이블에 생성해둔 인덱스가 우리가 일반적으로 알고있는 인덱스일 것이다. 하지만 '어댑티브 해시 인덱스'는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용하자 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며, 사용자는 innodb_adaptive_hash_index 시스템 변수를 이용해서 어댑티브 해시 인덱스 기능을 활성화하거나 비활성화 할 수 있다.

 

어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이다. InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다. B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어지고, 그만큼 CPU는 적은 일을 하지만 쿼리의 성능은 빨라진다. 

 

해시 인덱스는 '인덱스 키', 해당 인덱스 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 관리되는데, 인덱스 키 값은 'B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값' 조합으로 생성된다. 어댑티브 해시 인덱스의 키 값에 'B-Tree 인덱스 고유번호'가 포함되는 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재하기 때문이다. 특정 키 값이 어느 인덱스에 속한 것인지도 구분해야 하기 때문이다. 그리고 '데이터 페이지 주소'는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼 풀에 로딩 된 페이지의 주소를 의미한다. 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라진다.

 

예전 버전은 어댑티브 해시 인덱스가 하나의 메모리 객체인 이유로 어댑티브 해시 인덱스의 경합이 상당히 심했다. 그래서 8.0부터는 내부 잠금(세마포어)을 줄이기 위해 어댑티브 해시 인덱스의 파티션 기능을 제공한다.

 

innodb_adaptive_hash_index_parts 시스템 변수를 이용해 파티션 개수를 변경할 수 있는데, 기본값은 8개이며, 만약 어댑티브 해시 인덱스가 성능에 많은 도움이 된다면 파티션 개수를 더 많이 설정하는 것도 어댑티브 해시 인덱스의 내부 잠금 경합을 줄이는데 많은 도움이 될 것이다.

 

위까지 보면 어댑티브 해시 인덱스는 항상 좋아보이지만, 실제 의도적으로 비활성화하는 경우도 많다. 어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되지 않는 경우는 아래와 같다.

1. 디스크 읽기가 많은 경우

2. 특정 패턴의 쿼리가 많은 경우(JOIN, LIKE 패턴 검색)

3. 매우 큰 데이터를 가진 테이블의 레코드를 폭 넓게 읽는 경우

 

아래는 성능 향상에 도움이 되는 경우이다.

1. 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)

2. 동등 조건 검색(동등 비교와 IN연산자)가 많은경우

3. 쿼리가 데이터 중에서 일부 데이터만 집중되는 경우

 

확실한 것은 어댑티브 해시 인덱스는 페이지를 메모리(버퍼 풀)내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서 아무런 도움이 되지 않는다. 또한 어댑티브 해시 인덱스 또한 저장 공간인 메모리를 사용한다. 이뿐만 아니라 다양한 단점이 있는데, 이러한 정확한 판단을 내리고 어댑티브 해시 인덱스를 사용하는 가장 좋은 방법은 Mysql 서버의 상태 값을 확인하는 것이다. Mysql 서버에서 어댑티브 해시 인덱스는 기본적으로 활성화돼 있기 때문에 변경하지 않았다면 이미 사용중인 것이다.

 

 

4.2.13 InnoDB, MyISAM, MEMORY 스토리지 엔진 비교

 현재 8.0은 Mysql 서버의 모든 기능을 InnoDB 스토리지 엔진만으로 구현할 수 있게 되었다. MyISAM 스토리지 엔진은 하나의 스레드에서만 데이터를 읽고 쓴다면 InnoDB보다 빠를 수 있다. 하지만 Mysql 서버는 일반적으로 온라인 트랜잭션 처리를 위한 목적으로 사용되고, 동시 처리 성능이 중요하다. 또한 MEMORY 스토리지 엔진은 가변 길이 타입의 칼럼을 지원하지 않는다는 문제점 때문에 Mysql 8.0부터는 TempTable 스토리지 엔진이 MEMORY 스토리지 엔진을 대체해 사용되고 있다.

 

 

4.2.14 MyISAM 스토리지 엔진 아키텍처

이 챕터에서는 MyISAM 스토리지의 엔진 성능에 영향을 미치는 요소인 키 캐시와 운영체제의 캐시/버퍼에 대해 살펴보겠다.

 

1) 키 캐시
InnoDB 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM의 키 캐시다. 키 캐시는 인덱스만을 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다. 키 캐시의 효율성은 아래의 수식으로 확인할 수 있다.

 

[  키 캐시 히트율 = 100 - (Key_reads / Key_read_requests * 100)  ]

 

Key_reads는 인덱스를 디스크에서 읽어 들인 횟수를 저장한는 상태 변수이고, requests는 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수다. 이 상태 값을 확인하려면 ==> SHOW GLOBAL STATUS LIKE 'Key%'; 로 확인이 가능하다. 메뉴얼에서는 일반적으로 키 캐시를 이용한 쿼리의 비율(Hit rate)을 99% 이상으로 유지하라고 권장한다. 만약 미만이라면 키 캐시를 조금 더 크게 설정하는 것이 좋다. 

 

2) 운영체제의 캐시 및 버퍼

MyISAM 테이블 인덱스는 키 캐시를 이용해 디스크를 검색하지 않고 충분히 빠르게 검색할 수 있다. 하지만 MyISAM 테이블의 데이터에 대해 디스크로부터 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능도 MyISAM 스토리지 엔진은 가지고 있지 않다. 그래서 MyISAM 테이블의 데이터 읽기, 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청될 수 밖에 없다.

 

 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙이다. 만약 전체 메모리가 8GB, Mysql이나 다른 애플리케이션에서 메모리를 모두 사용하면 캐시 용도로 사용할 메모리 공간이 없어진다. 이럴때 MyISAM 테이블의 데이터를 캐시하지 못하고, 결론적으로 쿼리 처리가 느려진다. MyISAM이 주로 사용하는 Mysql에서 일반적으로 키 캐시는 최대 물리 메모리의 40% 이상을 넘지않게 설정하고, 나머지 메모리 공간은 운영체제가 자체적인 파일 시스템을 위한 캐시 공간을 마련할 수 있게 해주는 것이 좋다.

 

 

4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조

 InnoDB 스토리지 엔진을 사용하는 테이블은 프라이머리 키에 의해서 클러스터링되어 저장되는 반면, MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙 공간처럼 활용된다. 즉 MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 엔덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다.

 

MyISAM 테이블에서 ROWID는 가변 길이와 고정 길이의 2가지 방법으로 저장될 수 있다.

 

1) 고정 길이 ROWID : 자주 사용되지 않지만 MyISAM 테이블을 생성할 때 MAX_ROWS 옵션으로 사용할 수 있는데, Mysql 서버는 최대로 가질 수 있는 레코드가 한정된 테이블을 생성한다.

 

2) 가변 길이 ROWID : MyISAM 테이블을 생성할 때 MAX_ROWS 옵션을 사용하지 않으면, 최대 myisam_data_pointer_size 시스템 변수에 설정된 바이트 수만큼의 공간을 사용할 수 있다.

 

 

4.4 Mysql 로그 파일

 Mysql 서버에서 서버의 상태를 진달할 수 있는 많은 도구가 지원되지만, 이런 기능들은 많은 지식이 필요로 하는 경우가 많다. 하지만 로그 파일을 보면 Mysql 서버의 깊은 내부 지식이 없어도 상태나 부하를 일으키는 원인을 쉽게 찾아서 해결할 수 있다. 많은 사용자가 로그 파일의 내용을 무시하고 다른 방법으로 해결책을 찾으려고 노력하는데, 무엇보다 Mysql 서버에 문제가 생겼을 때는 다음에 설명하는 로그 파일들을 자세히 확인하는 습관을 들일 필요가 있다.

 

1) 에러 로그파일

Mysql이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다. 에러 로그 파일의 위치는 Mysql 설정 파일(my.cnf)에서 log_errorf라는 이름의 파라미터로 정의된 경로에 생성된다. Mysql 파일에 별도로 정의되지 않은 경우 데이터 디렉터리(datadir 파라미터에 설정된 디렉터리)에 .err라는 확장자가 붙은 파일로 생성된다.

 

1-1) Mysql 시작하는 과정과 관련된 정보성 및 에러 메세지

: Mysql 서버가 정상적으로 기동했고('mysqld: ready for connections' 메세지 확인), 새로 변경하거나 추가한 파라미터에 대한 특별한 에러나 경고성 메시지가 없다면 정상적으로 적용되었다고 판단하면 된다. 그렇지 않고 변수가 무시된 경우에는 Mysql 서버는 정상적으로 기동하지만 해당 파라미터는 Mysql에 적용되지 못함을 알린다.

 

1-2) 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메시지

: InnoDB 경우 Mysql 서버가 비정상적 또는 강제적으로 종료됐다면 다시 시작되면서 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 진행한다.

 

1-3) 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지 -> 주기적으로 로그 파일을 검토해서 숨겨진 문제를 해결하자.

 

1-4) 비정상적으로 종료된 커넥션 메시지(Aborted connection)

: 어떤 데이터베이스 서버의 로그 파일을 보면 이 메시지가 많이 누적되어 있다. 클라이언트 애플리케이션에서 정상적으로 접속 종료를 못하고, 프로그램이 종료되면 Mysql 서버의 에러 로그 파일에 이런 내용이 누적된다. 네트워크 같은 의도하지 않은 접속이 끊어질 때도 발생한다. max_connect_erros 시스템 변숫값이 너무 낮게 설정된 경우 클라이언트 프로그램이 Mysql 서버에 접속하지 못하고 "Host 'host_name' is blocked" 라는 에러가 발생할 수 있다. 이때는 max_connect_errors 변수의 값을 증가시키면 된다.

 

1-5) InnoDB 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은)의 결과 메세지

 InnoDB 테이블 모니터링이나 락 모니터링, 또는 InnoDB의 엔진 상태를 조회하는 명령은 상대적으로 큰 메시지를 에러 로그 파일에 기록한다. InnoDB 모니터링을 활성화 상태로 만들어 두고 그대로 유지하는 경우에는 에러 로그 파일이 매우 커져 파일 시스템의 공간을 다 사용해 버릴지도 모른다. 모니터링을 사용한 이후에는 다시 비활성화해서 에러 로그 파일이 커지지 않게 만들어야 한다.

 

 

4.2.2 제너럴 쿼리 로그 파일(제너럴 로그 파일, General Log)

 가끔 Mysql 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아 검토해 볼 때가 있는데, 이떄는 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 그 파일을 검토하면 된다. 제너럴 쿼리 로그는 실행되기 전에 Mysql이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록한다. 

 

쿼리 로그 파일의 경로는 general_log_file 이름의 파라미터에 설정돼 있다. 쿼리 로그를 파일이 아닌 테이블에 저장할 수 있고, 이때는 파일이 아닌 테이블을 SQL로 조회해서 검토해야 한다. 쿼리 로그를 파일로 저장할지 테이블로 저장할지는 log_output 파라미터로 결정된다. 

 

 

4.2.3 슬로우 쿼리 로그

 Mysql 서버의 쿼리 튜닝은 크게 서비스가 적용되기 전에 전체적으로 튜닝하는 경우와 서비스 운영중에 Mysql 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝으로 나눌 수 있다. 전자의 경우 검토 대상이 전부이기 때문에 모두 튜닝하면 되지만, 후자의 경우에는 어떤 쿼리가 문제의 쿼리인지 판단하기 어렵다. 이런 경우 서비스에서 사용되는 쿼리 중 어떤 쿼리가 문제인지를 판단하는 데 슬로우 쿼리 로그가 상당히 많은 도움이 된다.

 

슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다. 슬로우 쿼리 로그는 Mysql이 쿼리를 실행한 후, 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를 판단하기 떄문에 반드시 쿼리가 정상적으로 실행이 완료돼야 슬로우 쿼리 로그에 기록될 수 있다.

 

Mysql 잠금 처리는 Mysql 엔진 레벨과 스토리지 엔진 레벨의 두 가지 레이어로 처리되는데, MyISAM이나 MEMORY 스토리지 엔진 같은 경우 별도의 스토리지 엔진 레벨의 잠금을 가지지 않지만 InnoDB의 경우 Mysql 엔진 레벨의 잠금과 스토리지 엔진 자체 잠금을 가지고 있다.

 

MyISAM이나 MEMORY 스토리지 엔진에서는 테이블 단위의 잠금을 사용하고 MVCC 같은 메커니즘이 없기에 SELECT 쿼리라고 하더라도, Lock_time이 1초 이상 소요될 가능성이 있다. 하지만 가끔 InnoDB 테이블의 SELECT 쿼리도 Lock_time이 상대적으로 큰 값을 발생할 수 있는데 이는 InnoDB 레코드 수준의 잠금이 아닌 Mysql 엔진 레벨에서 설정한 테이블 잠금 때문일 가능성이 높다. 그래서 InnoDB 테이블에만 접근하는 쿼리 문장의 슬로우 쿼리 로그에서는 Lock_time 같은 튜닝이나 쿼리 분석에 별로 도움이 되지 않는다.

 

일반적으로 슬로우 쿼리 또는 제너럴 로그 파일의 내용이 많아서 직접 쿼리를 하나씩 검토하기에는 시간이 많이 걸려 어느 쿼리를 집중적으로 튜닝해야 할지 식별하기가 어려울 수도 있다. 이런 경우 Percona에서 개발한 Percona Toolkit의 pr-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.