MySQL Ver. 5.7 DML 작업 성능 개선

MySQL Ver. 5.7에서 DML 작업의 성능 개선을 위한 어떤 내용들이 변경되었는지 간단히 알아보도록 하자. 

1. Fix index->lock contention

InnoDB는 Primary Index나 Secondary index나 모든 인덱스에 대해 RW Lock을 사용하여 인덱스를 보호한다. 

MySQL Ver. 5.7 이전에는 모든 변경 작업에 대해 다른 스레드의 접근을 막기 위해 모든 인덱스를 X-lock을 사용하여 접근을 막았었다. (즉, leaf 페이지 와 non-leaf페이지를 포함하여 다 접근을 막았다. ) 이와 같은 로직을 사용했었기 때문에 동시에 진행되는 DML 작업에 대해 indx->lock 경합이 자주 발생했었다. 

MySQL Ver. 5.7에서는 non-leaf 페이지에 대해서는 동시에 수정하지 않는 이상 접근하는것을 가능하게 허용하였다. 이 변경으로 인해 주된 경합을 많이 줄일 수 있게 되었다. 

2. Page Cleaner Thread Optimizations 

MySQL Ver. 5.6에서 Dedicated page cleaner thread를 추가하해서 백그라운드 작업으로 버퍼풀의 더티 페이지 플러쉬 작업과 free page의 수를 유지하는 작업을 진행할 수 있게 기능 개선을 하였다. 메인 스레드에서 플러쉬및 클린 작업이 제외되면서 사용자 스레드의 작업 속도는 더 빨라졌다. 그리고, 이와 같은 기능 개선을 통해 CPU 사용 방식을 개선하여 CPU bound에 의해 발생하는 몇몇 문제들을 해결할 수 있었다. 그러나, 여전히 DML 작업에 대해서는 문제가 되었는데, 바로 해당 작업을 진행하는 스레드가 하나였기 때문이다. 즉 하나의 Page Cleaner Thread가 작업하기에 너무 많은 DML이 발생하는 서비스에서 이 부분에 대한 병목이 발생하게 된 것이다. 

그래서 MySQL Ver. 5.7에서는 다음과 같이 개선하였다. 

1. 플러쉬 작업을 위한 버퍼풀 리스트 스캔 작업을 최적화 하고 비용을 줄이도록 하였다. 이것은 사용자 스레드에서 직접 flush/evict 페이지 작업을 하도록 개선하여 처리했는데, 이 기능은 page clear thread의 작업이 많이 밀린 경우에 필요하다. 

2. 여러개의 page Cleaner Thread를 생성하여 사용할 수 있게 기능 추가가 되었다. 그래서 여러 개의 스레드가 병렬로 해당 작업을 처리할 수 있게 구성하였다. 특히, 멀티 버퍼풀 인스턴스를 사용하는 경우에 그 수에 맞게 Page Cleaner Thread 갯수를 설정하면 더 효과적으로 사용할 수 있다.

3. log_sys->mutex optimization 

MySQL Ver. 5.7은 log_sys->mutex의 충격을 줄이게 기능 개선을 하였다. 이것은 log buffer에 접근하여 작업하는 것을 컨트롤 할 수 있게 하여 수정하였다. 바로, 동기화 하지 않은 log writing이 동기화 될때까지 기다리는 동안 블럭되지 않게 처리하도록 수정하였다.  이것의 효과는 innodb_flush_log_at_trx_commit 시스템 변수값을 2로 했을때 가장 크게 느낄 수 있다. (The impact of this change is most visible when innodb_flush_log_at_trx_commit=2, because the log writing without sync is not blocked waiting for a sync by the change.)

4. Avoiding the 'read-on-write' during transaction log writing

MySQL Ver. 5.7.4 부터 InnoDB에서 사용하는 로그의 페이지 사이즈와 OS 또는 파일시스템에서 사용하는 시스템 캐쉬 블럭 사이즈를 맞추어 성능 향상을 꾀하였다. 즉, Read-on-Write 를 피해, 불필요한 디스크 I/O를 줄이려고 한 것이다. 

그래서 추가된 시스템 변수가 innodb_log_write_ahead_size이다. 이 시스템 변수를 통해 InnoDB에서 트랜잭션 로그 I/O 단위와 시스템의 I/O 단위의 불일치로 인해 발생하는 성능 저하를 해결할 수 있다. 

innodb_log_write_ahead_size는 Redo Log의 write-ahead 블럭 사이즈를 지정하는 변수로 일반적으로 OS의 페이지 캐쉬 사이즈를 맞춰서 설정하면 좋다. 값은 InnoDB Log 파일의 블럭 사이즈의 배수로 설정이 가능하다. 즉 2^n 으로만 설정이 가능하다. 최대값은 innodb_page_size로 설정한 값까지 설정이 가능하며 최소값은 InnoDB Log 파일의 블럭 사이즈인 512 byte이다. 만약 최소값으로 설정하면 Write Ahead 기능은 동작하지 않게 된다. 즉, 최소값 이상일 때만 Write Ahead 기능이 동작한다. 

5. 위의 변화에 따른 MySQL Ver. 5.7의 변화 (즉, 주의점)

위와 같은 변경 사항에 더불어 MySQL Ver. 5.7은 플러쉬 관련 시스템 변수들에 좀 더 예민하게 적용되도록 수정되었다. 그래서, 현재 처리량의 최적화 보다 현재 하드웨어의 디바이스의 I/O 처리량에 더 맞춰져 동작할 수도 있다. 

5.1 innodb_io_capacity_max <= [실제 초당 최대 쓰기 페이지 갯수]인 경우 

MySQL Ver. 5.7은 innodb_io_capacity_max 시스템 변수의 설정값을 항상 유지하도록 노력한다. 만약, 눈에 띄게 작업량이 많으면 page cleaner는 flush_list를 플러쉬하는데 많은 시간을 소비할지도 모른다. 그리고, 그로 인해 다른 작업들이 완료되지 못할지도 모른다. 실제 초당 최대 쓰기 페이지 갯수는 information_schema.innodb_buffer_pool_stats의 PAGES_WRITTEN_RATE를 확인하면 알 수 있다. 

5.2 innodb_buffer_pool_instances X innodb_lru_scan_depth >= [실제 초당 최대 읽기 페이지 갯수]인 경우 

innodb_lru_scan_depth 시스템 변수의 설정은 page cleaner가 플러쉬 작업을 하여 만들어진 각 버퍼풀 인스턴스의 free 상태의 페이지수를 고려하는 것이 좋다. 보통 page cleaner가 한번 처리하는 작업은 1초 안에 완료가 된다. 헌데, 현재 처리하는 I/O 작업량이 많은데 innodb_lru_scan_depth 시스템 변수값을 높게 설정하는 경우, free 페이지를 유지하기 위해 너무 오랜 시간이 소요될 수도 있다. 그래서, innodb_lru_scan_depth 시스템 변수의 값은 너무 높게 설정하는 것을 권장하지 않는다. 

실제 초당 최대 읽기 페이지 갯수는 information_schema.innodb_buffer_pool_stats의 PAGE_READ_RATE를 확인하면 알 수 있다. 

위 내용은 다음의 url에서 더 정확히 확인할 수 있다. 

http://mysqlserverteam.com/mysql-5-7-improves-dml-oriented-workloads/

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_write_ahead_size

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth