스토리지 엔진 2

• InnoDB 스토리지 엔진

1. 버퍼 풀 성능 개선
   1. NUMA
   2. 버퍼 풀 메모리 초기 할당
      1. 리눅스에서는 InnoDB 버퍼 풀에 할당된 메모리를 점유하는 것이 아니라 사용할 것이라고 예약만 해두고 실제 응용 프로그램에서 접근할 때에만 리눅스가 응용 프로그램으로 할당해줌
         1. 장점 : 사용자의 실수를 막을 수 있음, 메모리 낭비를 막을 수 있음
         2. 단점 : 메모리 할당에 대해서 db 서비스를 시작할 때 관리자가 알 수 없음 -> 잘못된 메모리 할당으로 스왑을 사용하게 되거나 프로세스가 강제 종료 될 수 있음
         3. 단점을 보완하기 위해서 XtraDB는 버퍼 풀로 할당된 메모리 공간을 운영체제로부터 미리 모두 할당 받는 기능이 추가됨 -> 메모리 사용량 고정
   3. InnoDB 잠금 세분화 (MySQL 5.6)
      1. mutex를 세분화해서 잠금 경합을 줄임
         1. flush_state_mutex : 플러시 상태의 더티 페이지를 보호하는 뮤텍스
         2. LRU_list_mutex : 버퍼 풀에서 LRU 리스트에 저장된 페이지들을 동기화하는데 사용되는 뮤텍스
         3. flush_list_mutex: 버퍼 풀에서 플러시해야 할 더티 페이지들의 리스트를 동기화하는데 사용되는 뮤텍스
         4. free_list_mutex: 버퍼 풀에서 사용되지 않는 프리 페이지들의 리스트를 관리하는 뮤텍스
         5. zip_free_mutex: 압축된 페이지들을 처리하기 위해서 사용되는 프리 페이지들의 목록을 관리하는 뮤텍스
         6. zip_hash_mutex: 압축된 페이지들을 검색하기 위한 해시 테이블을 보호하는 뮤텍스
      2. 기존에 LRU_list에서 사용하던 뮤텍스를 제거
   4. I/O 기반의 워크로드 성능 향상
      1. 디스크의 데이터 파일 크기가 InnoDB 버퍼 풀 보다 커서 디스크 읽기가 빈번하게 발생함 -> 프리 페이지 필요함 -> 프리 페이지는 뮤텍스 획득을 해야 사용할 수 있는데 뮤텍스를 획득하기 위해서 많은 대기현상이 발생
         1. innodb_empty_free_list_algorithm
         2. legacy -> 기존 알고리즘: 프리 페이지를 생성하는 페이지 클리너 스레드의 뮤텍스 획득 우선순위를 부여, Free_list에 프리 페이지가 없는 경우 페이지 클린 스레드와 사용자 스레드에서 동시에 프리 페이지를 준비하고 Free_list에 추가 -> 뮤텍스 경합 유발
         3. backoff -> 새로운 알고리즘: Free_list에 프리 페이지가 없는 경우 XtraDB에서는 LRU_list 플러시를 사용자 스레드에서 수행하지 않고 페이지 클리너 스레드에서만 수행
   5. 어댑티브 해시 파티션
      1. PK나 인덱스에 대한 룩업이 빈번하게 발생하는 경우, 해당 인덱스의 일부 값들에 대해서 자동으로 해시 인덱스를 생성
         1. MySQL 5.6에서는 읽기와 쓰기를 섞어서 사용하는 쿼리가 실행될 때 동시성 문제가 발생할 수 있음
         2. XtraDB에서는 동시성 문제를 해결하기 위해서 시스템 변수를 도입(innodb_adaptive_hash_index_partitions)
            1. 1 : MySQL 5.6의 InnoDB와 동일하게 작동
            2. 2 이상 : 지정된 개수만큼의 어댑티브 해시 인덱스를 생성(64비트 : 최대 64, 32비트 : 최대 32)
2. 원자 단위의 쓰기
   1. Double_write 버퍼의 사용: 더티 페이지를 디스크로 플러시 하기 전에 시스템 테이블스페이스의 DoubleWrite에 기록 -> 플러시 진행 중 비정상적 종료 -> 재시작 시 InnoDB 스토리지 엔진은 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지 비교 후 다르면 복사
   2. FusionIO의 원자 단위의 쓰기: InnoDB 스토리지 엔진이 16KB를 디스크로 쓰기를 실행했을 때 FusionIO 드라이브는 16K가 100% 완전하게 기록되거나, 하나도 기록되지 않는 형태로 트랜잭션을 보장 -> 일부만 저장되지 않는 문제를 방지
   3. XtraDB에서는 시스템 변수를 이용해서 원자 단위의 쓰기를 허용함(innodb_use_atomic_write)
      1. ON: innodb_flush_method가 <fdatasync, O_DSYNC인 경우 O_DIRECT로 변경>, <O_DIRECT, ALL_O_DIRECT,O_DIRECT_NO_FSYNC인 경우 그대로 유지>
      2. 해당 시스템 변수가 ON일 경우, innodb_doublewrite는 자동으로 OFF
3. 확장된 InnoDB 엔진 상태 출력(SHOW ENGINE INNODB STATUS)

• innodb_show_verbose_locks: 잠긴 레코드 정보를 출력할지 여부|0: 잠긴 테이블 및 인덱스 명 출력, 1: 잠긴 내용 상세 출력, 기본값 0|
• innodb_show_locks_held: InnoDB의 각 트랜잭션 단위로 잠금의 개수를 출력할지 여부 결정| 기본값 100, 최대 1000|

1. 백그라운드 스레드 관련 상태 변수
2. 세마포어 관련 상태 변수
3. 인서트 버퍼와 어댑티브 해시 인덱스 관련 상태 변수
4. 로그 관련 상태 변수
5. 버퍼 풀 관련 상태 변수
6. 트랜잭션 관련 상태 변수

1. XtraDB 리두 로그 아카이빙
   1. 리두 로그가 재사용되기 전에 오래된 로그를 복사하는 기능이 추가 -> XtraDB 테이블에 대핸 변경 작업 로그를 수집할 수 있음
   2. 명령어
      1. PURGE ARCHIVED LOGS BEFORE <datetime> : 주어진 날짜 이전에 생성된 리두 로그 아카이브 파일 삭제
      2. PURGE ARCHIVED LOGS TO <log_filename> : log_filename 파일을 포함하여 이전에 생성된 리두 로그 아카이브 파일을 삭제
   3. 시스템 변수
      1. innodb_log_archive : 리두 로그 아카이빙 기능 활성화 여부|ON: 활성화, OFF: 비활성화|
      2. innodb_log_arch_dir: 아카이빙 리두 로그파일의 저장 디렉토리 설정, 설정 값이 없을 경우 데이터디렉토리에 저장
      3. innodb_log_arch_expire_sec: 설정된 시간이 지나면 아카이빙 리두 로그파일 자동 삭제|0: 기능 사용안 함, 기본값:0|
2. 변경된 페이지 트랙킹

• 리두 로그 엔트리를 기반으로 변경된 페이지를 추적 -> 증분 백업 성능 향상(innodb_track_changed_page: 기록 활성화 여부 시스템변수)

1. 명령어
   1. FLUSH CHANGED_PAGE_BITMAPS: 현재 리두 로그의 체크포인트 지점까지 변경된 페이지를 디스크의 비트맵 파일로 동기화 -> XtrBackup이 비트맵 파일을 참조할 필요가 있을 때 실행
   2. RESET CHANGED_PAGE_BITMAPS: 모든 비트맵 파일을 삭제하고 비트맵 파일의 시퀀스 초기화
   3. PURGE CHANGED_PAGE_BITMAPS BEFORE <lsn> : 주어진 로그 시뭔스 번호 이전까지의 모든 변경 페이지 이력 삭제

 

• 전문 검색 엔진

1. 전문 검색 인덱스 추가

   

   테이블 생성

    

   

   데이터 입력

    

   

   전문 검색 엔진 생성

    

   

   경고메시지 확인-> FTS_DOC_ID는 전문 검색 인덱스와 테이블의 레코드를 연결하는 역할을 하며 처음으로 전문 검색 인덱스를 생성하는 경우에 컬럼이 추가됨

    

2. 전문 검색 인덱스를 위한 테이블스페이스

   

   전문 검색 인덱스 추가 시, 생성되는 ibd 파일 확인

    

3. 전문 검색 인덱스 관련 INFORMATION_SCHEMA 정보

• 테이블에 사용되는 테이블스페이스를 제외하고(employee_name_innodb.ibd) 나머지 테이블스페이스는 조회할 수 없음. 따라서 INFORMATION_SCHEMA 데이터베이스에서 각 테이블스페이스에 상응하는 테이블을 제공

  

1. InnoDB의 모든 전문 검색 인덱스에 적용되는 내용
   1. INNODB_FT_CONFIG: 전문 검색 인덱스의 기본 설정 저장
   2. INNODB_FT_DEFAULT_STOPWORD: 단위 검색어 생성 시 사용하는 기본 구분자 목록
2. 전문 검색 인덱스를 가진 테이블 단위로 적용되는 내용

• 테이블 단위로 적용되는 테이블은 innodb_ft_aux_table 시스템 변수에 대상 테이블의 데이터베이스와 테이블명을 명시해야함 (데이터베이스/테이블)

  

1. INNODB_FT_INDEX_TABLE: innodb_ft_aux_table 시스템 변수에 설정된 전문 검색 인덱스의 내용 조회

   

2. INNODB_FT_INDEX_CACHE: 전문 검색 인덱스를 가진 테이블에 INSERT된 레코드는 임시 공간에 저장 후 전문 검색 인덱스에 반영됨, 해당 테이블은 임시 공간에 저장된 레코드 확인
3. INNODB_FT_INSERTED/INNODB_FT_DELETED: 전문 검색 인덱스에 반영되기 전 추가된 레코드나 삭제된 레코드의 FTS_DOS_ID는 해당 테이블에 저장되고 쪼개진 검색어들은 INNODB_FT_INDEX_CACHE 테이블에 저장
4. INNODB_FT_BEING_INSERTED/INNODB_FT_BEING_DELETED: OPTIMIZE TABLE이나 ALTER TABLE 명령으로 테이블이 리빌드되면 INNODB_FT_INSERTED/INNODB_FT_DELETED 테이블의 내용이 인덱스에 반영됨, 이 때 작업 중인 레코드의 정보가 저장되는 테이블

1. 전문 검색 인덱스 사용

   

   MATCH 컬럼에 인덱스 컬럼 모두 명시되어야 하나 순서는 무관함 -> 전문 인덱스의 모든 컬럼을 명시하지 않으면 풀 테이블 스캔 발생

2. 주의사항
   1. MySQL5.5 버전에서 전문 인덱스를 MyISAM 테이블에서 InnoDB로 전환하고자 할 때에는 서로 사용하는 구분자 리스트가 다르므로 동일하게 설정해주어야 함

1. Memcached 플러그인
   1. 아키텍처
      1. MySQL 서버에서 모든 테이블의 읽고 쓰기는 핸들러 API 계층을 통해서 처리가 되는데, 모든 스토리지 엔진을 위한 공통 API이기 때문에 InnoDB에 최적으로 작동하지 못함
      2. Memcached 플로그인은 핸들러 API를 거치지 않고 직접 InnoDB 스토리지 엔진을 호출하여 빠름