2015년 11월 10일 화요일

MySQL 5.7 Mutex 경합 성능 개선 테스트

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 트랜잭션 리스트에 트랜잭션을 넣으려면, 다음 명령을 수행해야 한다.
    START TRANSACTION READ ONLY ;

    이 명령을 추가하는 데는 두 가지 이슈가 존재한다.
    1. 가용성이 증대된 이 기능을 사용하기 위해서는, 사용자 코드가 변경되어야 한다.
    2. 추가적인 텍스트가 쓰임에 따른 네트워크 부하와 파싱 부하가 존재한다.
  • MySQL 5.7
    MySQL 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.5
    MySQL 5.7
    innodb_adaptive_hash_indexONOFF

  • 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: reservation count 182299705, signal count 2164792728
      --- Mutex 항목 있음 ---
      Mutex spin waits 12246334704, rounds 14805390978, OS waits 23561506  
      RW-shared spins 3489305418, rounds 25531001879, OS waits 128501569
      RW-excl spins 11223239, rounds 7049531444, OS waits 28533022
      Spin 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: reservation count 334847032
      OS WAIT ARRAY INFO: signal count 337142831
      --- Mutex 항목이 없음 !! ---
      RW-shared spins 1318803701, rounds 1600121440, OS waits 282108481
      RW-excl spins 21654939, rounds 63290498046, OS waits 22268616
      RW-sx spins 172, rounds 16793, OS waits 161
      Spin rounds per wait: 1.21 RW-shared, 2922.68 RW-excl, 97.63 RW-sx


  • MySQL 5.7 부터는 mutex 항목을 모니터링 하려면 다음과 같은 설정이 필요하다.

    -- 1. MySQL 5.7 버전에서는 performance_schema 의 수집 항목을 지정하면, 
    아래 명령으로 항목의 값을 확인할 수 있다.
    # vi my.cnf
    ...
    performance-schema-instrument='wait/synch/mutex/innodb/%=ON'
    mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR
        -> FROM performance_schema.events_waits_summary_global_by_event_name
        -> WHERE SUM_TIMER_WAIT > 0
        -> AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%'
        -> ORDER BY SUM_TIMER_WAIT DESC, COUNT_STAR DESC;
    +--------------------------------------------------+---------------+--------------+
    | 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                                      |
    ...
    | InnoDB | sum rwlock: buf0buf.cc:1374 | waits=17550                                    |
    +--------+-----------------------------+------------------------------------------------+
    135 rows in set (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 경합이 적은 것을 확인할 수 있다.
  • CPU 리소스 사용
    • MariaDB 5.5 가 더 적은 CPU 리소스를 사용함.
      • MariaDB 5.5 보다 MySQL 5.7 이 사용하는 CPU 리소스 평균 값이 더 크다.
        즉, MySQL 5.7 이 사용하는 CPU 리소스 사용량이 더 많음.