2016년 12월 30일 금요일

Comparing Binary Log Size

Overview

Binary log는 DDL 및 DML 작업과 같이 데이터베이스 내에서 발생하는 변경 event들이 저장되는 로그 파일이다.
MySQL에서는 이러한 이벤트들을 Binary log 파일에 로깅할 때 로깅 포맷을 어떻게 가져갈 것인가에 대해 사용자가 선택할 수 있도록 binlog_format 이라는 설정변수(system variable)를 제공하고 있다.
  • binlog_format
    사용자는 아래 3가지 타입 중 하나를 선택하여 설정할 수 있다.
    # Possible values
    - STATEMENT : logging to be statement based
    - ROW : logging to be row based (Default value, >= 5.7.7)
    - MIXED : logging to use mixed format (statement와 row image가 mix되어 로깅됨)
    (MySQL 5.7.7 이전까지는 statement-based logging format이 디폴트 값이였으며, 5.7.7 부터는 row-based logging format 이 디폴트 값으로 지정되었다.)

Row-based logging format 에서 각각의 row change event 들은 before/after 2개의 row image (row data column set) 를 포함한다.
  • Before Image : 변경되기 전 row image
  • After Image : 변경된 row image

보통 MySQL에서는 Before Image, After Image 모두 full row image (all columns) 로 로깅하나, 사실 두 이미지 모두 모든 컬럼이 로깅될 필요는 없다. 필요로하는 최소 컬럼셋만 기록하면 되는 것이다.
  • Before Image
    : row를 유니크하게 식별할 수 있는 최소한의 컬럼 셋이 필요. PK가 있다면 PK가 로깅되고 UK(all not null) 가 있다면 유니크키가 로깅됨. PK & UK (without any null columns) 둘 다 없으면 전체 컬럼들이 모두 기록됨.
  • After Image
    : 변경된 컬럼만 기록

MySQL 5.5와 그 이전버전에서는 Row-based logging format을 사용하는 경우 무조건 row image가 full로 로깅됐었다.
이는 Statement-based 보다 빠르고 안전하다는 장점이 있지만 변경되는 row image가 모두 기록되다보니 대량의 delete/update 작업이 발생할 경우 디스크 용량, 네트워크 트래픽 등이 문제가 될 수 있었고 이러한 단점으로 인해 보통은 Statement-based를 사용하는 경우가 많았다.
MySQL 5.6.2 부터 binlog_row_image 라는 설정 변수가 도입되면서, Row-based logging format 일 때 row image를 최소 컬럼셋으로 로깅하게할지 full로 로깅하게할지 사용자가 선택할 수 있게 되었고 이를 통해 row-based logging format 을 사용할 때의 단점들도 보완할 수 있게 되었다.
  • binlog_row_image
    Row Before Image & After Image 에 저장될 column set을 결정하는 변수
    # Possible values
    - full: Log all columns in both the before image and the after image. (Default value)
    - minimal: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image that are actually changed.
    - noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.
     
  • binlog_row_image 사용 시 주의 사항
    - mysql 5.5 와 그 이전 버전에서는 full image 만 사용하므로, 그보다 더 높은 버전에서 5.5 혹은 그 이전 버전을 슬레이브로 둔다면 full을 사용해야 한다.
    - minimal / noblob mode를 사용하는 환경에서 delete/update 가 제대로 동작하기 위해서는 source 및 destination table에서 아래 조건들이 충족되어야 한다.
       1) 복제되는 테이블은 src와 dest에서 컬럼이 동일하게 존재해야하고 (컬럼 순서도 동일), 컬럼 데이터 타입도 같아야 한다.
       2) 복제되는 테이블은 PK 정의가 동일해야 한다. (즉, PK가 아닌 인덱스들을 제외하고는 테이블 정의가 같아야 함.)
    이 조건들이 모두 충족되지 않는 경우, destination table 에서 실제 delete/update 에 해당되는 대상 row에 정확하게 적용되지 않을 가능성이 있으며 이 경우 warning 이나 error 메세지가 발생하지 않기 때문에 쥐도새도 모르게 마스터-슬레이브 간 데이터 정합성이 깨질 수 있다.
우리의 MySQL 서버들도 디스크 용량 등의 이슈로 인해 대부분 Statement-Based logging format 을 사용하고 있는 상태이다.
하지만 binlog_row_image 라는 변수가 도입되었고 또 5.7.7 에서 binlog_format의 디폴트 값이 Row-based 로 지정된 만큼, 실제로 Row-based 일 때에 문제였던 부분이 나아졌는지 확인하기 위해 테스트를 진행해보았다.
테스트에서는 디스크 용량을 중점적으로 살펴보았다.



Description

테스트는 MySQL version 별로 Row-based/Statement-based 일 때 하루치 Binary Log 전체 사이즈가 얼마나 차이나는지 비교해보는 방식으로 진행하였다.
우리 서비스들에서는 대량의 데이터 변경 작업이 지속적으로 진행되는 서비스가 많지 않고 대부분 OLTP성 서비스들이므로, Binary Log 양을 의도적으로 많이 쌓이게끔하여 극한의 환경에서의 사이즈/리소스 비교를 진행하진 않았음을 참고바란다.

  • 테스트 환경 구축
    1) 테스트 DB 서버들의 gtid_mode 는 모두 OFF 로 설정하였다.
    2) 선정은 대부분의 데이터가 Integer Type 인 것과 대부분의 데이터가 String Type 인 것 두 가지를 선정하여 서로 다른 두 환경에서의 Binary Log 양을 비교한다.
    3) Binary Log 사이즈에 영향을 줄만한 MySQL 설정변수들을 전부 살펴보면서 Binary Log에 최대한 불필요한 정보가 들어가지 않고 일반적으로 사용되는 셋팅으로 변수값을 설정하여 테스트를 진행한다.

    위 조건들을 바탕으로 선정한 서비스 및 테스트 DB 설정 변수들은 다음과 같다.
    Test1Test2
    Binary Log Size일별 100G 정도 쌓일 경우일별 100~200G 정도 쌓일 경우
    MySQL VersionMariaDB 5.5.24MySQL Facebook 5.6.23
    Data Type대부분 Integer (int, bigint)대부분 String (char, varchar, TEXT)
    Related Variablesbinlog_format
    binlog_row_image (>= 5.6.2)
    binlog_rows_query_log_events (>= 5.6.2)

    * binlog_rows_query_log_events
    5.6.2 부터 도입된 설정변수로 row-based logging format 일 때 해당 변수가 Enable 되어있으면 Binary log에 Row query log event 와 같은 정보성 로그 이벤트들이 기록된다.
    Default 값은 OFF 이며, 테스트 DB들은 모두 Default 값 (즉 Disable) 그대로 설정하였다.
    #161109 15:24:48 server id 1  end_log_pos 567 CRC32 0x96f51756  Rows_query
    # insert into t3 values (1,2,3),(2,1,3)


    TEST 1.  DB 데이터가 대부분 Integer type인 환경에서의 Binary log 파일 사이즈 비교


    • 테스트 셋 구성
      MySQL Version
      binlog_format
      binlog_row_image 
      binlog_rows_query_log_events
      Service DBMariaDB 5.5.24STATEMENTN/A (full이라고 할 수 있음)N/A
      TestDB 1MariaDB 5.5.24ROWN/A (full이라고 할 수 있음)N/A
      TestDB 2MySQL 5.6.28ROWminimalOFF
      TestDB 3MySQL 5.7.14ROWminimalOFF

    • Comparing Resource Usage
      1) Disk
      Log Disk Usage
      MariaDB 5.5.24 (Statement-based)97G
      MariaDB 5.5.24 (Row-based)97G
      MySQL 5.6.28 (Row-based)71G
      MySQL 5.7.14 (Row-based)89G

      2) CPU
      cpu_user (avg)
      cpu_sys (avg)
      cpu_iowait (avg)
      MariaDB 5.5.24 (Row-based)1.980.50
      MySQL 5.6.28 (Row-based)3.551.090
      MySQL 5.7.14 (Row-based)4.791.440


TEST 2.  DB 데이터가 대부분 String type인 환경에서의 Binary log 파일 사이즈 비교


  • 테스트 셋 구성
  • MySQL Version
    binlog_format
    binlog_row_image 
    binlog_rows_query_log_events
    Service DBMySQL Facebook 5.6.23STATEMENTOFF
    TestDB 1MySQL 5.6.28ROWminimalOFF
    TestDB 2MySQL 5.7.14ROWminimalOFF
    TestDB 3MySQL 5.7.14STATEMENTOFF
    Comparing Resource Usage
  • 1) Disk
    Log Disk Usage
    MySQL 5.6.23 (Statement-based)143G
    MySQL 5.6.28 (Row-based)103G
    MySQL 5.7.14 (Row-based)107G
    MySQL 5.7.14 (Statement-based)148G

    2) CPU 
    cpu_user (avg)
    cpu_sys (avg)
    cpu_iowait (avg)
    MySQL 5.6.28 (Row-based)4.820.890.11
    MySQL 5.7.14 (Row-based)4.830.920.22
    MySQL 5.7.14 (Statement-based)4.90.880.02


두 테스트를 통해 도출된 결과는 다음과 같다.
  • Row-based logging format 을 사용하더라도 디스크 용량을 많이 차지하지 않는다. (binlog_row_image 변수로 인해 오히려 Statement-based 보다 용량이 더 적음)
  • 리소스 사용 (대표적으로 CPU) 에도 큰 영향을 주지 않는다. (MySQL Version이 올라갈수록 CPU Usage가 늘어났긴하지만 문제되는 수준은 아니라고 판단함. 같은 버전에서도 ROW/STATEMENT 간에 별반 차이는 없음 - Test2 5.7.14 참고)

위 테스트 결과 및 현재 우리의 MySQL 표준 version이 5.7.16 임을 고려해보았을 때, 앞으로는 binlog_format 을 Row-based 로 가져가도 무방해보인다.
하지만 테스트 결과에서 의아한 부분이 하나 있는데, 바로 5.6.28 Row-based 일 때와 5.7.14 Row-based 일 때 Binary Log 사이즈 크기를 비교해보면 오히려 5.6.28 에서 더 적다는 점이다.
MySQL 5.7 Release Note 를 살펴본 결과, 5.7.6 에서 패치된 기능으로 인해 Binary Log 사이즈에 변화가 있었던 것으로 보인다.
5.7.6 부터 gtid_mode 가 온라인으로도 변경할 수 있도록 패치되었다. 이를 가능하게하기 위해 Previous_gtids event 가 gtid_mode 값에 상관없이 매 binary log 에 기록이 되며, 마찬가지로 gtid_mode 가 OFF 일때도 Anonymous_gtid event 가 매 트랜잭션 실행전에 항상 기록이 된다.
이로 인해 우리가 테스트한 5.7.14 의 Binary Log 양이 5.6.28 보다 더 컸던것으로 추측한다. (즉 5.6에서는 gitd_mode 가 OFF 여도 Binary Log에 Anonymous_gtid event 등이 기록되지 않지만, 5.7.6 이상의 버전에서는 계속 기록이 되므로 사이즈가 더 큼)
아래는 패치내용 전문 및 추가된 정보를 담고있는 Binary Log 샘플내용이다.
* 5.7.6 Release note
Replication: There is now a Previous_gtids event in every binary log, regardless of the value of gtid_mode.
In previous versions, it was only generated when gtid_mode=on. Similarly, there is now an Anonymous_gtid event before every transaction when gtid_mode=off.
These changes ensure that similar per-transaction events are generated regardless of the type of binary logging in use.
As well as enabling the newly added ability to change gtid_mode online, this also has a positive impact on the recovery of gtid_purged and gtid_executed.
  
* Binary Log Sample
[5.7.13 GTID_MODE = ON]
#161109 20:10:14 server id 1683533  end_log_pos 194 CRC32 0x6bf43649    Previous-GTIDs
# 8bd0f811-5f92-11e6-8cf9-14187756309d:1-5375996665
# at 194
#161109 20:10:14 server id 173853  end_log_pos 259 CRC32 0x6338bd3e     GTID    last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= '8bd0f811-5f92-11e6-8cf9-14187756309d:5375996666'/*!*/;
[5.7.14 GTID_MODE = OFF]
#161109 18:36:34 server id 1740165  end_log_pos 154 CRC32 0xf7be6f91    Previous-GTIDs
# [empty]
# at 154
#161109 18:36:34 server id 6029202  end_log_pos 219 CRC32 0x35501799    Anonymous_GTID  last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;


Conclusion

MySQL 의 transaction isolation 은 Default가 REPEATABLE-READ 이다.
REPEATABLE-READ 레벨이상부터 Statement-based logging format을 사용할 수 있고 또한 5.7.7 이전까지는 binlog_format 의 디폴트 값이 STATEMENT 였기 때문에 Row-based 의 단점을 막론하고 많은 사용자들이 대부분 binlog_format 을 STATEMENT 로 사용하고 있을 것이라 생각한다.
테스트 결과를 통해 binlog_format을 Row-based 로 가져간다해도 큰 문제가 없음을 확인했다.
그럼 현재 REPEATABLE-READ + Statement-based 셋팅으로 사용하고 있는 DB 서버들을 모두 Row-based 로 변경해야할까? 그건 아니다.
REPEATABLE-READ Level 을 사용하고 있는 경우에는 필요에 맞게 binlog_format 을 설정해서 사용하면 될 것이다.
우리가 Row-based 를 사용함으로서 얻게되는 가장 큰 장점은 transaction isolation 레벨을 더이상 REPEATABLE-READ 로 고집하지 않아도 된다는 것이다.
서비스 성향에 따라서 오히려 tx_isolation 레벨을 READ-COMMITTED 로 변경해서 사용하는 것이 더 효율적일 수 있다.
이는 REPEATABLE-READ 일때가 READ-COMMITTED 일때보다 동시성 저하를 유발하기 때문이다.
REPEATABLE-READ 에서는 Range Scan 시 Gap Lock 이 사용되며, 한 트랜잭션내에서 Lock을 Holding 하는 방식도 READ-COMMITTED와는 다르다.
READ-COMMITTED는 트랜잭션을 오픈하더라도 하나의 구문이 실행완료되면 실제로 변경된 row를 제외한 extra row들에 대한 lock은 release 되지만 REPEATABLE-READ 에서는 트랜잭션이 닫힐때까지 extra row들에 대한 lock도 계속 홀딩하고 있다.
이러한 부분들로 인해 REPEATABLE-READ가 READ-COMMITTED 보다 동시성이 떨어진다. 자세한 내용은 메뉴얼을 참고하기 바란다.
(Reference : http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html)
기존에 대부분의 DB서버에서 REPEATABLE-READ 가 사용되고 있고 현재도 그렇게 쓰고있지만, 앞으로는 서비스에서 사용되는 트래픽과 쿼리 패턴을 좀 더 면밀히 파악하여 어떤 레벨이 서비스에 최적화되어 사용할 수 있는지 고려해볼 필요가 있을 것 같다.

* 참고로 기존에 Master-Slave 구조로 Statement-Based logging format 을 사용하고 있는 DB 서버셋을 Row-based 로 변경한 후에는,
대형 테이블 컬럼 추가 작업 등으로 인해 Slave 에서 먼저 컬럼 추가 후 스위칭해서 기존 Master 에 추가하는 경우 신규 컬럼이 기존 컬럼들의 중간 위치에 들어갈 수 없고 마지막 위치에 들어가야지만 해당 방식으로 작업이 가능하므로 binlog format 변경 후 유의하기 바란다.

2016년 6월 9일 목요일

MySQL CPU Saturation Analysis


분석 배경

최근 서비스의 DB 장비에 동일한 쿼리가 동시다발적으로 실행되면서 서버의 User CPU 사용률이 거의 100% 에 도달하여 서비스 장애로까지 이어졌던 일이 있었습니다.
그때 당시, 부하를 유발하던 SELECT 쿼리 이외에 다른 DML 쿼리가 문제되진 않았었으며, SELECT 쿼리로 인한 부하임에도 불구하고 DB 내부적으로 경합이 매우 심했고 DML 쿼리들도 평소보다 유입이 확 줄어들었었습니다.
사실 이러한 케이스들은 이전에도 있었으며, 이렇게 SELECT 쿼리로 인해 부하가 높은 상태에서 DML 유입이 줄고 DB 내부 경합이 심해지는게 우리가 이미 여러 DB서버에서 많이 사용하고 있는 DB내 쓰레드풀 기능의 영향인건지 원인을 파악하기 위해 분석 테스트를 진행하였습니다.
이를 통해 추후에 발생하는 동일한 형태의 장애를 예방 및 진단하는데 활용하고자 합니다.
아래는 문제 시점 당시 서버 및 MySQL 관련 지표들을 나타낸 그래프입니다.


원인 분석을 위한 테스트

문제 현상이 동일하게 재현될 수 있는 테스트 환경을 구축한 뒤 원인을 분석하였습니다.
  •  테스트 환경 구축
    MySQL version : 5.5.24-MariaDB_for_Kakao2
    CPU : 12 Core (2.40 Hz)
    Memory : 32G (Innodb buffer pool size is 24G)
    Disk : NVMe SSD
    1.  문제가 발생했던 DB 장비에 테스트 DB 장비를 연결
    2.  MRTE(Mysql Real Traffic Emulator)를 통해 실제 서비스 장비에 유입되고 있는 트래픽을 테스트 장비에 전송 (SELECT 쿼리 트래픽만)
    3.  문제 시점 당시 수행됐던 SELECT 쿼리를 약 30개 스레드에서 계속 실행하는 쉘 스크립트를 실행
    4.  현상 관찰
  • 모의 테스트 진행
    본래 장비와 동일하게 트래픽을 받는 상태에서 쉘 스크립트로 동일한 쿼리를 계속 수행하여 실제 장애 상황과 동일한 현상이 발생하는지 테스트 진행.
    테스트를 수행한 결과, 이전에 문제되었던 때와 동일하게 User CPU 사용률이 100% 까지 증가하고 InnoDB 내부 뮤텍스/세마포어 관련 수치가 매우 높게 올라갔음.
      
      
  • 문제 현상이 동일하게 재현되는 환경에서 원인 분석을 위해 아래와 같이 테스트를 진행하였습니다.

TEST 1.   Remove Thread Pool

-   분석 초기에 원인으로 추정했던 쓰레드 풀 사용을 중단하기 위해 Thread 설정을 'one-thread-per-connection' 으로 변경한 뒤 다시 테스트를 진행함.
-   테스트한 결과, 이전과 동일하게 부하와 경합이 발생하였음.

TEST 2.   InnoDB Buffer Pool Instance 갯수 변경 ( 1 -> 20 )

-   1번 테스트를 통해 쓰레드풀이 쿼리 실행시간 및 서버 부하에 큰 영향을 주지 않았다는 것을 알게됨.
-   Disk Read도 없는 상태에서 세마포어 경합 수치가 올라간 것을 미루어 보았을 때, 메모리 내부적으로 lock 경합이 있었을 것으로 추정하여 메모리 내부 뮤텍스/세마포어 등의 경합을 줄이기 위해 1개로 설정되어 있던 innodb_buffer_instance 갯수를 20개로 조정함.
     (참고로 기존 서비스 DB도 1로 설정되어 있는 상태임.)
-   버퍼풀 인스턴스 갯수 조정 후 다시 테스트를 진행한 결과, 이전과 달라지는게 없었음. 동일하게 부하 및 경합이 발생함.

TEST 3.  innodb_buffer_pool_instance = 1 & performance_schema = ON

-   DB 내부적으로 어떤 부분에서 경합이 발생하는지 알아보기 위해 performance schema를 키고 다시 테스트 진행함.
     (innodb_buffer_pool_instance 는 다시 1로 조정하였음.)
-   performance schema를 킨 상태에서 부하를 준 결과, events_waits_current 테이블에서 각 스레드들이 buf_pool_page_hash_latch 에 read lock(S-lock)을 걸기 위해 대기하고 있는 상태가 포착됨.
     하지만 EVENT_ID 값이 계속 바뀌는 것으로 보아, buf_pool_page_hash_latch 에 대한 lock 경합이 실제로 부하를 유발하지 않는 것으로 보였음.
     (s-lock일 뿐더러, Disk read도 없었기 때문에 buffer pool page hash table이 변경될 가능성
     (즉,  buf_pool_page_hash_latch에 대한 x-lock 점유)이 없어보였음. 또한 테스트하는 동안
     performance_schema.rwlock_instances 테이블에서 buf_pool_page_hash_latch 에 대한 WRITE_LOCKED_BY_THREAD_ID 값이
     계속 NULL이였음.) 
-   테스트를 진행하던 도중 show engine innodb status 의 Semaphore 섹션에서 동일한 block->mutex(메모리 주소가 모두 같음)에 대하여 쓰레드들이 lock을 걸기 위해 대기하는 모습이 포착되었음.
----------

SEMAPHORES

----------
....

--Thread 139754297358080 has waited at buf0buf.ic line 372 for 0.0000 seconds the semaphore:

Mutex at 0x7f1b9b7983f8 '&block->mutex', lock var 1

waiters flag 1
--Thread 139782798149376 has waited at buf0buf.ic line 1269 for 0.0000 seconds the semaphore:
Mutex at 0x7f1b9b7983f8 '&block->mutex', lock var 1
waiters flag 1
--Thread 139782374692608 has waited at buf0buf.ic line 1269 for 0.0000 seconds the semaphore:
Mutex at 0x7f1b9b7983f8 '&block->mutex', lock var 1
waiters flag 1
--Thread 139754351617792 has waited at buf0buf.ic line 1269 for 0.0000 seconds the semaphore:
Mutex at 0x7f1b9b7983f8 '&block->mutex', lock var 1
waiters flag 1
--Thread 139782798681856 has waited at buf0buf.ic line 1269 for 0.0000 seconds the semaphore:
Mutex at 0x7f1b9b7983f8 '&block->mutex', lock var 1
waiters flag 1
--Thread 139782375491328 has waited at buf0buf.ic line 372 for 0.0000 seconds the semaphore:
Mutex at 0x7f1b9b7983f8 '&block->mutex', lock var 1
waiters flag 1
--Thread 139754423187200 has waited at buf0buf.ic line 372 for 0.0000 seconds the semaphore:
Mutex at 0x7f1b9b7983f8 '&block->mutex', lock var 1
waiters flag 1
--Thread 139754322257664 has waited at buf0buf.ic line 372 for 0.0000 seconds the semaphore:
Mutex at 0x7f1b9b7983f8 '&block->mutex', lock var 1
waiters flag 1
....
-   동일한 형태의 쿼리가 여러 스레드에서 실행되면서 버퍼풀 내의 특정 데이터 블록에 동시다발적으로 접근하다보니 뮤텍스 경합이 심해져 User CPU를 모두 소진하는 것으로 보였으며,
     이 결과를 바탕으로 innodb_sync_spin_loops/innodb_spin_wait_delay 값을 조정해가면서 CPU 사용률 등을 체크해보았음. (TEST 4)

TEST 4.  innodb_sync_spin_loops / innodb_spin_wait_delay 값 조정

-   innodb_sync_spin_loops, innodb_spin_wait_delay 변수 모두 쓰레드 대기와 관련이 있는 설정변수들임. 정확한 의미는 아래 내용을 참고. (MySQL Menual 페이지에서 발췌하였음.)
    • innodb_sync_spin_loops : The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended. (Default 30.)
    • innodb_spin_wait_delay : The maximum delay between polls for a spin lock. (Default 6.)
-    위 두 값을 조정하면서, CPU 사용률과 InnoDB Mutex 수치를 측정하였음.
      테스트 DB에 적용된 두 변수 값은 각각 innodb_sync_spin_loops = 100, innodb_spin_wait_delay = 6 이며, 이는 기존 서비스 DB 장비에 설정된 값과 동일함.

1)  innodb_spin_wait_delay = 6 인 상태에서,
     innodb_sync_spin_loops 값을 100 -> 50 -> 30 -> 10 -> 5 로 조정

           
           
           
- 10:35:30 : innodb_sync_spin_loops = 100 (Current value)
- 10:35:50 : innodb_sync_spin_loops = 50
- 10:36:10 : innodb_sync_spin_loops = 30  (Default value)
- 10:36:30 : innodb_sync_spin_loops = 10
- 10:36:50 : innodb_sync_spin_loops = 5
- 10:37:10 : innodb_sync_spin_loops = 10
innodb_sync_spin_loops 값에 따라 User CPU 사용률의 변화가 컸으며, Sys CPU 사용률과 Idle CPU 사용률을 보았을 때 값이 10일 때가 가장 적절해 보임.

2)   innodb_sync_spin_loops = 100 인 상태에서,
      innodb_spin_wait_delay 값을 6 -> 3 -> 1 -> 0 으로 조정

           
           
           
- 10:45:41 : innodb_spin_wait_delay = 6  (Current & Default value)
- 10:46:00 : innodb_spin_wait_delay = 3
- 10:46:20 : innodb_spin_wait_delay = 1
- 10:46:40 : innodb_spin_wait_delay = 0
- 10:47:02 : innodb_spin_wait_delay = 6
CPU 사용률이 높은 상황에선 innodb_spin_wait_delay 값을 줄여도 크게 영향을 미치진 않았음. 극단적으로 값을 0으로 설정해야 User CPU 사용이 줄어들었음.

3)  innodb_sync_spin_loops = 10 인 상태에서,
     innodb_spin_wait_delay 값을 6 -> 3 -> 1 -> 0 으로 조정

           
           
           
- 10:54:31 : innodb_spin_wait_delay = 6  (Current & Default value)
- 10:54:51 : innodb_spin_wait_delay = 3
- 10:55:11 : innodb_spin_wait_delay = 1
- 10:55:30 : innodb_spin_wait_delay = 0
- 10:55:50 : innodb_spin_wait_delay = 6
CPU가 어느정도 여유가 있는 상태에서 innodb_spin_wait_delay 값을 조정한 결과 어느정도 영향력을 가짐. 그러나 큰 차이는 없어서 디폴트값인 6으로 설정해도 무방해보임.

결론

동일한 쿼리가 동시다발적으로 유입되어 여러 스레드에서 메모리내의 같은 데이터 블록에 접근하여 뮤텍스 경합이 발생, CPU 부하가 높아지는 경우 innodb_sync_spin_loops 변수 값을 조정함으로써 긴급한 상황에서 부하를 좀 더 낮출 수 있을 것으로 보입니다.
innodb_sync_spin_loops 의 디폴트 값이 30 이나, 위에서 살펴본 것처럼 어느 정도 부하가 높은 상태에서는 30 이상의 값은 모두 user cpu를 많이 소모하였으므로 만일의 상황을 대비하여 초기에 설정 값을 10으로 가져가는 것도 나쁘지 않아 보입니다.