innodb flush

mysql 내에 dirty page가 생기게 되면

fuzzy checkpointing 메카니즘을 통해 자연스럽게 flush가 일어나게 되는데요.

과도한 update로 인해 dirty page의 증가가 빠르게 일어나고

innodb_max_dirty_pages_pct와 innodb_log_file_size 등에 설정된 

임계치를 넘어서게 되면 강제 flush 가 발생하게 됩니다.

문제는 강제 flush가 발생하게 되면 그 시점 이후의 query 실행을 지연시켜

slow가 발생하는 것인데요.

OLTP 성격의 DB에서는 치명적일 수 있습니다.

InnoDB에는 LRU flush, Flushlist flush의 두가지 강제 flush가 존재하는데

buffer pool에 남은 free page가 없는데 데이터 읽기를 시도할 때는 LRU list를 참조한 flush가 일어나고,

buffer pool에 남은 dirty page가 innodb_max_dirty_pages_pct에 설정해놓은 점유율을 넘어설때는 flush list를 참조한 flush가 일어납니다.

고로, 전자의 상황에서는 innodb_adaptive_flushing, innodb_max_dirty_pages_pct, innodb_io_capacity, innodb_log_file_size의 변경은 의미가 없는거죠.

확인 방법은

show engine innodb status;

...

Pending writes: LRU 0, flush list 0, single page 0

...

그러므로 LRU list를 참조하는 flush가 대다수인 경우는, memory를 증설하거나, 시스템의 I/O를 업그레이드 하는 것이 제일 큰 이득이 있을 것이고, flush list를 참조하는 flush가 대다수인 경우는, innodb_adaptive_flushing, innodb_max_dirty_pages_pct, innodb_io_capacity, innodb_log_file_size의 변경이 중요하게 작용합니다.

물론 이 상황에서도 memory를 증설하거나, 시스템의 I/O를 업그레이드 하는 것은 도움이 됩니다.

제 해석은 주관적이거나 오역이 있을 수 있기에 원문을 첨부합니다.

(원문: http://www.mysqlperformanceblog.com/2011/01/13/different-flavors-of-innodb-flushing/ )

innodb_log_file_size의 증가는 더 많은 dirty page를 받아들이게 하는데 확실한 효과가 있습니다. (테스트 결과)

다르게 말하면, 강제 flush가 일어나는 시점을 좀 더 뒤로 미루는데 도움(?)을 준다는 거죠.

그런데 과연 이게 제대로된 문제 해결 방법인지 의구심이 드네요.

결국 그 임계치를 넘어서는 update가 또 발생한다면 다시 문제가 생길테니까요.

과도한 dirty page 증가로 인한 slow query 발생을 해결하는 방법은 하드웨어의 업그레이드 말고는 없는 것일까요?

출처 : http://cafe.naver.com/mysqlpg/441