blog

MySQL Tips

날짜: 2022-03-20

기본지식

• MySQL 서버는 프로세스가 아닌 스레드 기반으로 작동
• MySQL 엔진은 스토리지 엔진을 조정하기위해 핸들러 라는것을 사용한다
• 트랜잭션: 논리적인 쿼리작업 set을 모두 완벽하게 DBMS에 반영하거나, 완전히 반영되지 않도록 만들어주는 기능
• 트랜잭션 격리수준: 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간 작업 내용을 어떻게 서로 공유하고 차단할것인지르 결정하는 정도를 의미

가장 보편적인 MySQL 세팅

• 스토리지 엔진 = InnoDB
• 인덱스 알고리즘 = B-tree
• 트랜잭션 격리수준 (Isolation level) = REAPEATABLE READ

InnoDB 스토리지 엔진

• 자동 데드락 감지 기능
  • innodb_table_locks 시스템 변수를 활성화 하면 레코드락 뿐만아닌 테이블락도 감지할 수 있다
  • 데드락감지 스레드는 데드락 점검을 위해 잠금목록을 확인할때 “새로운 잠금” 을 걸고 데드락 스레드를 찾게되는데, 이로인해 성능저하가 발생할 수 있다
  • innodb_deadlock_detect 시스템 변수를 OFF 하면 데드락 자동 감지 기능이 꺼진다.
  • innodb_lock_wait_timeout 시스템 변수를 설정하면 데드락 걸린 상황이 일정시간이후에 자동실패하게 되는데, 만약 데드락 자동감지 기능을 안쓸거면 기본값이 50초 이므로 확 낮춰서 설정해두는것이 좋다
• 장애복구
  • 자동 장애복구 기능이 있긴 한데, 장애 종류에 따라 자동 복구가 불가능한 경우도 있음
  • 자동복구가 안되는 상황이면 innodb_force_recovery 시스템 변수를 1~6 으로 하나씩 바꿔보면서 재기동을 시도해 보아야 함.
  • 가장 깔끔한 방법은 장애나기전 풀백업 데이터 + 백업 이후로부터 장애시점까지의 binary log 를 이용하여 DB 복원하는 것.
• 버퍼 풀
  • DB서버 성능향상을 위한 데이터 캐싱 및 쓰기 버퍼링 용도로 활용됨
  • 사이즈 설정(innodb_buffer_pool_size): 메모리가 8G 미만이면 50%, 그 이상이면 50% 로 설정했다가 성능을 보면서 차츰차츰 올려줘 보자
  • 인스턴스 개수 설정 (innodb_buffer_pool_instances): 메모리 40G 이하이면 기본값(8) 사용, 만약 그 이상이면 5G 당 하나로 계산하면 됨
• 트랜잭션과 잠금
  • 레코드 잠금을 걸때 조건절에 사용되는 필드에 따라 사용가능한 index 가 존재할경우 인덱스에 잠금을 건다.
  • 잠금을 걸 인덱스가 없으면 해당 테이블 전체 레코드에 잠금을 건다.

Index

• 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다
• index 를 거는 필드의 사이즈가 작을 수록 성능이 좋다
• index 거는 필드 값에 중복이 없을 수록 성능이 좋다
• 단순 쓰기 작업이 1이면, 인덱스가 있는경우 하나당 1.5의 작업 비용이 더 든다고 생각하면 됨 (즉, 인덱스가 3개이면 1 + (1.5*3) = 5.5)
• B(Balanced)-tree 알고리즘이 가장 일반적으로 사용된다
• B-tree 인덱스는 해당 컬럼 값의 100% 일치 또는 prefix 일치 검색을 시도하였을 경우만 활용가능
• 커밋 되었을때는 Redo log, 롤백 되었을때는 Undo log 를 활용한다
• 조회 하려는 데이터가 테이블 전체 데이터의 약 25% 이상일 경우, 풀스캔하는 것이 인덱스 사용하는것보다 더 효율적이다.

---

목록으로