MySQL 퍼포먼스 향상 (1) 아키텍처

1. MySQL 의 아키텍처

   우선 MySQL 의 아키텍처 면에서 퍼포먼스를 생각해보자.

MySQL 은 논리적 3 계층으로 구성되어짐.

  - 접속관리 및 시큐리티 :  유저 1  명당 1개의 스레드가 작성되어지며, 항상 캐쉬처리 된다.

  - 최적화 및 실행 : 쿼리실행, 캐쉬, 함수, 프로시져, 트리거, 인덱스 등의 동작

  - 스토리지 엔진 : Row Lock

데이터베이스 락

  - MySQL 스레드에서 각 유저에 대한 처리를 동시적으로 처리하기 때문에, 동기화 전략이 필요하다. Lock기법을 통해 각 스레드가 쿼리순서를 동기화 시킨다.

  - 공유락은 트랜잭션에 읽기 허용. 배타락과는 충돌한다. 다른 트랜잭션은 공유락에 걸린 열을 읽기 O

  - 배타락은 트랙잭션에 갱신과 삭제를 허용. 공유락, 배타락 모두 충돌한다. 다른 트랜잭션은 배타락에 걸린 열 읽기 X, 수정 X, 삭제 X 

  - 락 취득, 체크, 해제 의 오버헤드가 리소스를 잡아먹어 퍼포먼스가 떨어진다.

  - 테이블 전체락은 유저 1 가 다 쓸때까지 읽기도 쓰기도 거부한다.

  - 쓰기는 언제나 읽기보다 우선순위가 높다.

트랜잭션

  - 트랜잭션은 1 개의 작업단위로서 쿼리의 모음이다. 여러 쿼리를 한개로 모아서 동작을 보장할때 사용한다.

  - ACID 를 지켜야 한다.

  - 트랜잭션도 퍼포먼스를 떨어뜨리는 동작이다. 필요하지 않으면 사용하지 말고, 테이블 작성시 MyISAM 엔진 선택도 고려해보자.

  - 2개 이상의 트랜잭션이 서로 엉켜서 데드락에 걸릴 수 있다. 데드락을 회피하기 위해서 타임아웃을 설정하자. InnoDB 에서 감지하여 배타락이 가장 적은 트랜잭션을 롤백시킨다.

  - InnoDB , MyISAM 을 함께 사용할때는 트랜잭션 동작 유무에 주의하자.

  - InnoDB 에서 MyISAM 으로 변경한 후 다시 InnoDB 로 돌려버리면, 외부키는 전부 사라진다.

  - MyISAM 은 동시삽입이 가능하지만, 읽기와 쓰기를 혼용하면 안된다.

스토리지 엔진 선택

  - 로그 고속기록에는 MyISAM 에 이름과 시간이 있는 컬럼을 만들어서 기록하는 것이 유리.

  - 읽기 전용 테이블에는 MyISAM 이 절대적으로 빠르다. 

  - 트랜잭션에는 InnoDB 추천

병목현상 검출

  - 지금의 퍼포먼스 측정, 변화 예측을 위하여 벤치마크를 실행시킨다.

  - 풀스택 벤치마크, 콤포넌트 벤치마크 두 개의 전략이 있다.

  - 벤치마크에서 측정되는 수치

    - 시간 단위의 트랜잭션 수 (TPC-C)

    - 응답시간 (Percentile value) : 측정결과을 나열하여 그 중 몇퍼센트를 차지하는 경계값. 예를 들어 90 퍼센타일이면 전체중 90% 차지하는 값중 가장 큰 값.

    - 범위성 (Scalability) : 캐퍼시티 측정

    - 동시에 작업하는 스레드 또는 접속의 수

    - 대표적인 툴에는 ab, http_load, JMeter, mysqlslap, sysbench 등이 있다.