Overview
MySQL 5.7 버전 부터 InnoDB 의 Mutex 경합과 관련된 성능이 개선되었다.
링크의 내용은 MySQL 5.7 버전 부터 read-only 트랜잭션을 분리하여 처리함으로써,
mutex 경합과 관련된 부분에 성능 개선이 되었다는 것이다.
이를 바탕으로 MySQL 버전 별로 트랜잭션의 mutex 획득과 관련된 동작 방식을 정리하면 다음과 같다.
- MySQL 5.5
트랜잭션들은 트랜잭션 ID 를 내림차순으로 정렬한 하나의 리스트로 관리되었다.
MVCC 를 위해서 명시적인 레코드 락을 암시적인 레코드 락으로 변환할 때, single global mutex 인 kernel mutex 를 획득하고 액티브 트랜잭션 리스트를 모두 조회해야 했다. - MySQL 5.6싱글 트랜잭션 리스트를 2 개의 리스트로 분리하였다.
- RW (Read-Write) 트랜잭션 리스트
- RO (Read-Only) 트랜잭션 리스트 (not auto-commit, non-locking select)
이러한 분리는, read-write 트랜잭션 리스트의 크기를 감소시키고 trx_sys_t::mutex 경합을 감소시켰다.결과적으로 가용성이 증대되었다.
그러나 MySQL 5.6 의 RO 트랜잭션 리스트에 트랜잭션을 넣으려면, 다음 명령을 수행해야 한다.이 명령을 추가하는 데는 두 가지 이슈가 존재한다.
1. 가용성이 증대된 이 기능을 사용하기 위해서는, 사용자 코드가 변경되어야 한다.
2. 추가적인 텍스트가 쓰임에 따른 네트워크 부하와 파싱 부하가 존재한다. - MySQL 5.7MySQL 5.7.2 부터 기본적으로 모든 트랜잭션이 Read-Only 트랜잭션으로 다뤄지고,
트랜잭션은 최초의 업데이트를 수행하려고 시도할 때 트랜잭션 ID 와 롤백세그먼트가 할당된다.모든 트랜잭션을 read-only 로 다루는 최적화가 기본으로써 발생되는 추가적인 장점은
해당 기능을 사용하기 위해 어플리케이션을 변경한다거나, 사용자가 추가적인 문법을 사용하지 않아도 된다는 것이다.
목적
- MySQL 5.7 에서 Read-only 트랜잭션에 대한 mutex 경합의 성능 개선 여부를 확인 한다.
- 동일한 상황에서 MariaDB 5.5 와 MySQL 5.7 버전의 성능을 비교 분석한다.
테스트 시나리오
- MariaDB 5.5 와 MySQL 5.7 을 동일한 환경으로 DB 를 구성한다.
- 그리고 각 DB 에 가능한 최대의 부하를 주는 상황을 재현하여 부하 테스트를 진행한다.
- mutex 경합의 횟수를 증가시키는 부하를 주기 위해서,하나의 테이블에 PRIMARY KEY (B-tree) 를 사용하는 OLTP 성 SELECT 쿼리를 QPS 최소 2,000 ~ 최대 20,000 정도의 트래픽으로 각 DB 에 부하를 준다.MySQL 5.5 (MariaDB 5.5) 버전 이하에서는 SELECT 쿼리만 실행하는 Read only 트랜잭션 이더라도, kernel mutex 를 획득하기 때문에과도한 동시 SELECT 트래픽으로 인하여 MySQL 내부적으로 mutex 경합 수치가 높아져, 처리량이 저하되는 현상을 재현할 수 있다.Adaptive Hash Index 활용MySQL 5.5 (또는 MariaDB 5.5) 버전을 사용하는 서비스 환경에서mutex 경합으로 인한 성능 저하가 발생할 경우, 동시 SELECT 쿼리의 처리 속도를 높이고자자주 사용되는 데이터에 대해 HASH index 를 사용하여 - Adaptive hash index 를 활성화(innodb_adaptive_hash_index = ON) 하여 - 경합에 대한 부하를 줄일 수 있다.
- QPS 를 최대 20,000 정도 트래픽을 동시에 실행할 경우 MariaDB 5.5 버전에서는 서비스 지연 (query delay) 이 발생된다.
이 상태에서 MariaDB 5.5 에 Adaptive hash index 를 적용하면 지연 현상이 완화된다. - 이를 바탕으로 MariaDB 5.5 에서는 innodb_adaptive_hash_index 가 활성화된 상태 / MySQL 5.7 은 비활성 상태로 설정한다.시스템 변수MariaDB 5.5MySQL 5.7
innodb_adaptive_hash_index ON OFF
- MySQL 5.7 에서 Adaptive hash index 를 활성화 하지 않은 상태로 서비스 지연 (query delay) 이 발생하지 않는지 확인한다.
- MariaDB 5.5 와 MySQL 5.7 간 mutex 경합 수치가 실제로 줄어들었는지 확인한다.
모니터링 특이사항
- mutex 값을 모니터링 하는 항목을 확인하는 방법이 DB 버전에 따라서 차이가 있다.
- ( 현재 테스트할 버전인 MariaDB 5.5 버전과 MySQL 5.7 이 다르다. )
- 코드 내부적으로는 위에서 언급한 바와 같이, MySQL 5.5 ( MariaDB 5.5 ) 에서는 mutex 를 kernel_mutex 하나로 관리하였으나,
MySQL 5.7 에는 kernel_mutex 가 제거된 상태이다. (정확히는 MySQL 5.6 버전부터 kernel_mutex 삭제됨) - mutex 모니터링
- MySQL 5.5 (MariaDB 5.5) , MySQL 5.6
: "SHOW ENGINE INNODB STATUS ;" 명령의 결과에서 Mutex spin waits, rounds, OS waits 항목으로 확인.
또는 performance_schema 를 활성화 하여 kernel_mutex 를 모니터링 할 수 있다. - MySQL 5.7
: "SHOW ENGINE INNODB STATUS;" 명령으로는 Mutex 항목을 확인할 수 없음.
그래서 mutex 를 모니터링 하려면, 두 가지 방법이 있다.
1) performance_schema 스키마의 테이블을 조회하여 확인
2) innodb_monitor_enable 시스템 변수의 값을 latch 로 설정하여 " SHOW ENGINE INNODB MUTEX ; " 명령으로 확인
-- ===============================================-- MariaDB 5.5-- ===============================================mysql> show engine innodb status \G...----------SEMAPHORES----------OS WAIT ARRAY INFO: reservationcount182299705, signalcount2164792728--- Mutex 항목 있음 ---Mutex spin waits 12246334704, rounds 14805390978, OS waits 23561506RW-shared spins 3489305418, rounds 25531001879, OS waits 128501569RW-excl spins 11223239, rounds 7049531444, OS waits 28533022Spin rounds per wait: 1.21 mutex, 7.32 RW-shared, 628.12 RW-excl...-- ===============================================-- MySQL 5.7.9-- ===============================================mysql> show engine innodb status \G...----------SEMAPHORES----------OS WAIT ARRAY INFO: reservationcount334847032OS WAIT ARRAY INFO: signalcount337142831--- Mutex 항목이 없음 !! ---RW-shared spins 1318803701, rounds 1600121440, OS waits 282108481RW-excl spins 21654939, rounds 63290498046, OS waits 22268616RW-sx spins 172, rounds 16793, OS waits 161Spin rounds per wait: 1.21 RW-shared, 2922.68 RW-excl, 97.63 RW-sx
- MySQL 5.5 (MariaDB 5.5) , MySQL 5.6
- MySQL 5.7 부터는 mutex 항목을 모니터링 하려면 다음과 같은 설정이 필요하다.
-- 1. MySQL 5.7 버전에서는 performance_schema 의 수집 항목을 지정하면,아래 명령으로 항목의 값을 확인할 수 있다.# vi my.cnf...performance-schema-instrument='wait/synch/mutex/innodb/%=ON'mysql>SELECTEVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR->FROMperformance_schema.events_waits_summary_global_by_event_name->WHERESUM_TIMER_WAIT > 0->ANDEVENT_NAMELIKE'wait/synch/mutex/innodb/%'->ORDERBYSUM_TIMER_WAITDESC, COUNT_STARDESC;+--------------------------------------------------+---------------+--------------+| EVENT_NAME | WAIT_MS | COUNT_STAR |+--------------------------------------------------+---------------+--------------+| wait/synch/mutex/innodb/trx_sys_mutex | 12682410.3952 | 9183229861 || wait/synch/mutex/innodb/trx_mutex | 6012485.2007 | 137433920530 || wait/synch/mutex/innodb/redo_rseg_mutex | 287646.8573 | 7515148314 || wait/synch/mutex/innodb/srv_sys_mutex | 105087.8160 | 813635368 || wait/synch/mutex/innodb/buf_pool_mutex | 42527.9134 | 194333619 || wait/synch/mutex/innodb/log_sys_mutex | 36183.4424 | 813686987 || wait/synch/mutex/innodb/lock_mutex | 29912.9640 | 622681938 |...| wait/synch/mutex/innodb/srv_dict_tmpfile_mutex | 0.0001 | 1 || wait/synch/mutex/innodb/recv_writer_mutex | 0.0000 | 1 |+--------------------------------------------------+---------------+--------------+-- 2. set global innodb_monitor_enable = 'latch'; 시스템 변수를 변경하면,"SHOW ENGINE MUTEX" 명령으로다음 항목들을 확인할 수 있다.+--------+-----------------------------+------------------------------------------------+| Type |Name| Status |+--------+-----------------------------+------------------------------------------------+| InnoDB | TRX_SYS | spins=3596086085,waits=8390986,calls=269669475 || InnoDB | SRV_SYS | spins=63544754,waits=632838,calls=643249 || InnoDB | LOG_SYS | spins=3648193,waits=30299,calls=62186 || InnoDB | REDO_RSEG | spins=707610,waits=6878,calls=11453 || InnoDB | REDO_RSEG | spins=698087,waits=6774,calls=11599 || InnoDB | REDO_RSEG | spins=685905,waits=6770,calls=9522 |...| InnoDB | rwlock: btr0sea.cc:195 | waits=212916705 || InnoDB | rwlock: btr0sea.cc:195 | waits=56916931 || InnoDB | rwlock: hash0hash.cc:353 | waits=587 || InnoDB | rwlock: hash0hash.cc:353 | waits=422 |...+--------+-----------------------------+------------------------------------------------+135rowsinset(0.06 sec) - 이 내용을 참고로 하여 테스트를 수행하고, mutex 항목을 비교 분석하기로 한다.
Mutex
- MariaDB 5.5 와 MySQL 5.7 DB 간 mutex 경합 수치를 비교한다.
- MariaDB 5.5 - innodb_adaptive_hash_index = ON
- MySQL 5.7 - innodb_adaptive_hash_index = OFF
- 다음 결과는 초당 mutex call 횟수이다.
- 위 결과로 MySQL 5.7 에서 mutex 횟수가 MariaDB 5.5 + adaptive hash index 보다 대략 5배 감소한 것을 확인할 수 있다.
CPU Utilization
- CPU Utilization 수치를 비교 해보면 MySQL 5.7 이 MariaDB 5.5 보다 높은 것을 확인할 수 있다.
- Total ( Kernel + User + Wait IO ) 수치의 평균 값을 비교하면 다음과 같다.
- MariaDB 5.5.24 : 16
- MySQL 5.7.9 : 21
Conclusion
- Read Only 트랜잭션 경합 성능 개선
- MySQL 5.7 성능 개선됨.
- MariaDB 5.5 에서는 PK SELECT 가 집중적으로 발생하여 mutex 경합이 많은 상태에서 innodb_adaptive_hash_index = ON 으로 설정해야 안정적인 반면
MySQL5.7 에서 innodb_adaptive_hash_index = OFF 로 하여도 MariaDB 5.5 + Adaptive_Hash_index 를 사용하는 것 보다 mutex 경합이 적은 것을 확인할 수 있다.
- MariaDB 5.5 에서는 PK SELECT 가 집중적으로 발생하여 mutex 경합이 많은 상태에서 innodb_adaptive_hash_index = ON 으로 설정해야 안정적인 반면
- MySQL 5.7 성능 개선됨.
- CPU 리소스 사용
- MariaDB 5.5 가 더 적은 CPU 리소스를 사용함.
- MariaDB 5.5 보다 MySQL 5.7 이 사용하는 CPU 리소스 평균 값이 더 크다.
즉, MySQL 5.7 이 사용하는 CPU 리소스 사용량이 더 많음.
- MariaDB 5.5 보다 MySQL 5.7 이 사용하는 CPU 리소스 평균 값이 더 크다.
- MariaDB 5.5 가 더 적은 CPU 리소스를 사용함.
글 잘봤습니다. 제 블로그에 현재 게시글을 조금 인용해도 괜찮을까요?
답글삭제출처는 표시하겠습니다.
QPS는 어땟나요? cpu를 더 많이 썻다면 처리량이 더 높아서 그런건 아닌가요?
답글삭제