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 변경 후 유의하기 바란다.

작성자: 시간: 댓글 없음:
피드 구독하기: 글 (Atom)