4.2.8~4.2.13

[IAM20]

4.2.8 Double write buffer

Partial-page, Ton-page

• 페이지가 일부만 기록되는 현상
• 하드웨어의 오작동, 시스템의 비정상 종료 시 발생
• 리두 로그가 공간 절약을 위해 페이지 변경 내용만 기록하기 때문.

Double write

• InnoDB 에서 Partial-page, Ton-page 를 막기 위함.

1. A~D 를 한 번에 디스크에 기록한다.
2. 진짜 쓰기를 실행한다.

3. 시스템 재시작시 DoubleWrite 버퍼의 내용과 실제 내용을 비교하여 복구한다.

   • HDD 에서는 순차디스크쓰기를 하기 때문에 부담스럽지 않은 비용
   • SSD 에서는 랜덤IO 여서 조금 부담스럽긴함 (그냥 2배)
   • 근데 무결성 중요하면 하라고 함.
   • 리두 로그 동기화가 꺼져있으면 DoubleWrite 도 꺼야한다.
     • 리두 로그의 Partial-page 현상 때문에 있는거라..

4.2.9 언두 로그

InnoDB 는 DML 로 변경되기 전의 데이터를 별도로 백업한다. 이를 언두로그라 한다. 좋은 역할을 하지만, 관리비용이 만만치않은 친구다.

• TX 보장을 위해 사용된다.
  • TX Rollback 시 사용
• 격리 수준 보장
  • 격리 수준에 맞게 SELECT 시 업데이트된 값이 아닌 언두 로그의 값을 읽도록 한다.
  • 이를 통해 높은 동시성을 확보할 수 있다.

언두로그는 DML 시에 항상 생성된다. DML 이 실행되면 실제 파일에 변화를 기록하고 이전 데이터는 언두로그에 쓰는 방식이다.

4.2.9.1 언두 로그 모니터링

• 1억개의 100GB 데이터를 삭제하였을 때 언두 로그도 100GB 가 생성된다.
• TX 가 길어지면 언두로그의 양이 커진다.
  • 관련 데이터에 대한 트랜잭션이 하나라도 있으면.. 계속 유지된다..
• 언두 로그가 커지면 레코드 조회가 (어딘가에선 언두 로그도 기 때문에) 전체적으로 성능이 느려진다.

다행히 8.0 부터는 자동으로 언두 로그를 줄여주고 한다. (GC ...?) 언두 로그 레코드의 수를 모니터링 하는 것이 좋다.

SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' and NAME='trx_rseg_history_len';

4.2.9.2 언두 테이블스페이스 관리

언두 로그가 저장되는 곳을 언두 테이블스페이스라고 한다.

언두 테이블스페이스

역사

• 5.6 이전 : 모든 언두 로그가 ibdata.ibd 에 저장되었음.
  • 서버 초기화시 생성되어 확장이 힘듦.
• 5.6 이후 : innodb_undo_tablespaces 를 2보다 큰 값으로 설정하면 별도의 언두 로그 파일을 사용
• 8.0 이후 : 해당 설정값 Deprecated 항상 언두 로그 파일 사용.
  • 한 번 생성된 언두 로그의 변경도 가능해짐.
  • CREATE UNDO TABLESPACE, DROP TABLESPACE

ALTER UNDO TABLESPACE :tablespace_name SET INACTIVE;
DROP UNDO TABLESPACE :tablespace_name;

구성

• 테이블 스페이스는 1 ~ 128 이하의 롤백 세그먼트
• 롤백 세그먼트는 1개 이상의 언두 슬롯
  • InnoDB 의 페이지 크기를 16바이트로 나눈 값의 개수 만큼 언두 슬롯을 가진다.
  • 하나의 트랜잭션은 언두 슬롯을 최대 4개를 사용한다.
  • 일반적으론 언두 슬롯을 2개까지만 사용한다.
  • 언두 슬롯이 부족하면 TX 실행조차 되지 않기 때문에 설정 잘 해주세용.. (근데 기본값이라면 동시에 13만이라 .,.)

Undo tablespace truncate

• 언두 테이블 스페이스 공간을 필요한 만큼만 남기고 반납하는 것
• 자동 모드 : 퍼지 스레드가 주기적으로 깨어나서 언두 로그를 삭제.
  • innodb_purge_rseg_truncate_frequency 값 조정으로 빈도 조절가능.
• 수동 모드 : 퍼지가 일을 제대로 못할경우 수동으로도 할 수 있음.
  • ALTER UNDO TABLESPACE :tablespace_name SET INACTIVE 형태로 비활성화 하면 퍼지가 가져감.
  • 이후 꼭 다시 ACTIVE 해줘야함.

4.2.10 체인지 버퍼

INSERT 나 UPDATE 시 데이터 파일 변경 뿐 아니라 인덱스 업데이트도 있다. 이 때 디스크 Random access 가 있기 때문에 조금 부담스러운데, 이를 버퍼에 작업해두고 사용자에게 바로 결과를 뱉는다. 이 때 사용하는 버퍼가 체인지 버퍼.

• 유니크 인덱스의 경우는 체인지 버퍼를 사용하지 않는다.
• 버퍼에 임시로 저장된 인덱스 레코드들은 백그라운드 스레드가 병합한다.
  • Merge thread 라고함.
• 8.0 부터는 INSERT, DELETE, UPDATE 세 가지 모두 사용 가능하다.
  • 난 DELETE 는 필요없는데? 하시면 innodb_change_buffering 값을 수정하면 된다.
  • 변수 값
    • all : 전체를 버퍼링
    • none : 난 버퍼 필요없어.
    • inserts : 인덱스에 새로운 레코드 추가만 버퍼링
    • deletes : 인덱스에서 삭제만 버퍼링
    • changes : inserts + deletes
    • purges : 영구적으로 삭제하는 작업만 버퍼링
• 기본적으로 버퍼풀의 25% 를 사용가능
  • innodb_change_buffer_max_size 로 비율 수정 가능하다.

체인지 버퍼의 모니터링

아래 쿼리를 실행하여 사용중인 메모리를 확인할 수 있다.

SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME='/memory/innodb/ibuf0ibuf';

오퍼레이션 처리 횟수는 아래 명령어로 알 수 있다.

SHOW ENGINE INNODB STATUS \G

4.2.11 리두 로그 및 로그 버퍼

리두 로그는 ACID 의 D 와 연관이 깊다. 서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.

• 대부분의 DBMS 는 읽기가 더 효율적이다.
• 쓰기는 보통 읽기보다 부하가 더 많이드는데, 이로 인한 성능 저하를 막기 위해 쓰기 편한 자료구조를 따로 둔다.
  • 이것이 리두 로그.
• 성능을 위해 리두 로그는 버퍼링이 가능하다.
  • InnoDB 의 버퍼 풀
  • 로그 버퍼 (리두 로그의 버퍼링용 자료구조)
• 보통 리두 로그는 바로 디스크에 넣도록 권장하는데, 이렇게하면 부하가 커진다.
  • innodb_flush_log_at_trx_commit 이라는 시스템변수로 동작을 정할 수 있다.
    • 0 : 1초에 한 번 디스크에 기록
    • 1 : 매번 트랜잭션이 커밋될 때 마다 기록
    • 2 : 매번 트랜잭션이 커밋될 때 마다 기록하지만 동기화는 1초에 한 번 (매 트랜잭션마다는 운영체제 메모리 버퍼에 쓴다는 뜻)
• 리두 로그의 크기는 버퍼 풀의 효율성을 결정하기 때문에 신중히 결정해야 한다.
  • 보통 기본값을 사용하는게 좋긴 한데, BLOB, TEXT 의 변경이 잦은 경우엔 조금 크게 설정하면 좋다.

비정상 종료를 복구시키는 방법

1. 커밋되었지만 데이터 파일에 기록되지 않은 데이터
   • 이 경우에는 리두 로그의 데이터를 복사하기만 하면 된다.
2. 롤백되었지만 데이터 파일에 기록된 데이터
   • 이 경우에는 언두 로그의 데이터를 복사한다.
   • 리두 로그에서는 해당 TX 가 어떤 상태였는지 확인하기 위하여 조회하게된다.

4.2.11.1 리두 로그 아카이빙

리두 로그는 데이터 변경이 너무 많아서 다른 툴이 리두 로그를 사용하기도 전에 덮어 쓰일 수 있다. 이는 아카이빙으로 해결 가능하다.

사용 절차

1. 리두 로그가 저장될 디렉터리를 시스템 변수로 설정
   • SET GLOBAL innodb_redo_log_archive_dirs='backup:/var/log/mysql_redo_archive';
2. 리두 로그 아카이빙 함수 실행.
   • DO innodb_redo_log_archive_start('backup', '20200722');

3. 아카이빙을 종료하고 싶을 때는

   • DO innodb_redo_log_archive_stop(); 실행
   • 아카이빙된 리두 로그는 비활성화해도 삭제되지 않기 때문에, 필요없다면 사용자가 삭제하여야한다.
   • 아카이빙을 시작한 세션이 끊어지면 아카이브도 자동적으로 삭제되기 때문에 정상적으로 사용하려면 꼭 아카이빙 종료 함수를 실행하자.

4.2.11.2 리두 로그 활성화 및 비활성화

보통의 경우 리두 로그를 비활성화 하는 것을 추천하지 않는다. 다만 초기 데이터 세팅이라던가, 데이터 복구, 또는 대용량 데이터 삭제등의 큰 정지작업의 경우엔 비활성화하면 성능상에 이점을 볼 수 있다.

• ALTER INSTANCE DISABLE INNODB REDO_LOG; 로 비활성화 할 수 있다.
• ALTER INSTANCE ENABLE INNODB REDO_LOG; 작업이 완료되면 꼭 다시 활성화 하자.

리두로그가 비활성화된 채로 MySQL 이 비정상 종료가 되면, 데이터는 그야말로 대혼돈이기 때문에 (10시의 버전과 10시 1분 버전의 혼재) 꼭 다시 활성화 해주자.

4.2.12 어댑티브 해시 인덱스

B-Tree index 의 자주 읽히는 페이지 인덱스의 해시 인덱스를 만들어 필요할 때 마다 사용하는 방식

• 어댑티브 해시 인덱스는 InnoDB Engine 에 한개만 존재하기 때문에 (파티션은 되지만 관리 포인트는 하나)
• B-Tree index 의 고유번호 + B-Tree index 의 실제 키 값으로 생성됨.
• 기존엔 하나의 메모리 객체로 관리되었으나, 파티션되어 락 경합 횟수가 줄게되었음.

장점

• B-Tree 검색 시간을 줄일 수 있음.
• B-Tree 검색이 줄어 세마포어 횟수도 줄어든다.
• 따라서 쿼리 속도가 빨라지고, CPU 사용량도 줄어든다.

단점

• 기끔씩 메모리를 많이 사용함.
• 어댑티브 해시 인덱스에 있던 없던 테이블을 한 번 거치게됨.
• 해시 인덱스가 또 하나의 관리 포인트가 되어 인덱스 업데이트 시 같이 삭제한다던가의 오버헤드가 생김.
• 테이블 삭제나, 스키마 변경시 어댑티브 해시 테이블에 많은 부하가 가게됨.

이처럼 trade-off 가 극명하기 때문에 써야할 때와 쓰지 말아야할 때를 가려야 합니다.

써야할 때

1. 동등조건으로 조회하는 경우가 많은 경우
2. 쿼리가 일부 데이터에 집중되는 경우
3. 디스크의 데이터가 InnoDB 의 버퍼 풀 크기와 비슷한 경우

안써도 되는 때

1. 디스크 읽기가 많은 경우
2. LIKE, JOIN 이 많은 경우
3. 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

해시 인덱스가 사용하고 있는 메모리에 비해 히트율이 굉장히 낮다면, 비활성화를 고려해보아야 합니다. (이거 완전 캐시..)

어댑티브 해시 인덱스의 메모리 사용량은 아래 쿼리로 확인 가능하다

SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME='memory/innodb/adaptive hash index';

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

• MyISAM : 옛날에 많이 썼는데 8.0 부터는 InnoDB 보다 이점이 없음.
• MEMORY : 작명이 너무 좋아서 좋아보이는데 실제론 그냥 그럼
  • 모든 처리를 메모리에서 하기 때문에(!) 한 스레드에선 굉장히 빠르다.
  • 온라인 트랜잭션은 한 스레드가 아니라 여러 스레드가 동시에 데이터를 사용한다.
  • 메모리에서 데이터를 처리하면 경합 문제때문에 성능이 느려진다.
  • 임시테이블용으로 MySQL 에서 사용했는데 TempTable 엔진으로 바뀌었고 아마 곧 삭제될듯.

[IAM20]

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

4.2.13 에 설명했듯이 거의 Deprecated 급의 엔진인듯. 가볍게 넘어갑시당.

4.3.1 키 캐시

• InnoDB 의 버퍼 풀 역할
• InnoDB 와는 다르게 index 만을 대상으로 한다.
• 인덱스의 디스크 쓰기 작업에 대해서만 버퍼링을 한다.
• 히트율로 효율성을 판단.
  • 99% 를 유지하도록 권장 (그것도 캐시 사이즈를 늘려서...)
  • 만약 32비트 운영체제라면 캐시를 4GB 보다 더 늘릴 수 없는데, 이 때는 기본 키 캐시 말고 다른 키 캐시를 추가할 수 있다.

사용자 키 캐시 추가하기

1. 먼저 시스템 파라미터를 추가하고

   key_buffer_size = 4GB
   kbuf_board.key_buffer_size = 2GB
   kbuf_comment.key_buffer_size = 2GB

2. 각 테이블 인덱스가 사용할 캐시를 생성한다.

   CACHE INDEX db1.board, db2.board IN kbuf_board;
   CACHE INDEX db1.comment, db2.comment IN kbuf_comment;

4.3.2 운영체제의 캐시 및 버퍼

왜 있는지 모르겠는 장.

앞서 버퍼가 없다고 했는데, 없어서 DISK I/O 는 항상 운영체제의 캐시와 버퍼를 사용한다. 운영체제의 디스크 캐시는 보통 남는 메모리를 사용한다. 따라서 운영체제의 디스크 캐시를 사용할 수 있도록 키 캐시는 최대 물리 메모리의 40% 이하로 설정하자.

4.3.3 데이터 파일과 PK 구조

• InnoDB 는 PK 에 의해 클러스터링 되어 저장
• MyISAM 은 Heap 처럼 들어오는 순서대로 데이터 파일에 저장됨.
  • ROWID 라는 물리적 주솟값을 가지고, 인덱스들은 이를 포인터로 가져간다.

고정길이 ROWID

• 이를 사용하면 레코드의 개수가 한정됨.
• INSERT 된 순번이 ROWID

가변길이 ROWID

• 첫 번째 바이트는 ROWID 의 길이
• 나머지 바이트는 실제 위치의 offset
• 기본적으로 256TB 의 레코드를 가질 수 있음
  • 더 필요하면 myisam_data_pointer_size 를 8 이상으로 설정하면 64PB 이상으로 가져갈 수 있다