Split log_sys mutex in MySQL 5.5

원문 : http://mikaelronstrom.blogspot.com/2010/09/split-logsys-mutex-in-mysql-55.html

One important bottleneck in the MySQL Server is the log_sys mutex in InnoDB.
MySQL 서버에서 하나의 중요한 병목 현상은 InnoDB에 있는 log_sys 뮤텍스입니다.

Experiments using mutex statistics showed that this mutex was accessed about 250k times per second and that about 75% of those accesses had to queue up to get the mutex.
뮤텍스 통계를 이용한 실험을 본다면 이 뮤텍스는 초당 25만 번에 대한 액세스를 하며, 이러한 액세스의
약 75 %가  대기열에 올라가는 뮤텍스를 얻었습니다.

One particular nuisance is that while holding the log_sys mutex it is necessary to grab the buffer pool mutex to put the changed pages to the start of the flush list indicating it is now the youngest dirty page in the buffer pool (this happens as part of the mini commit functionality in InnoDB).
하나의 주요 단점으로, log_sys 뮤텍스를 잡고있는 동안, 변경된 페이지들을 가장 최근에 변경된
더티 페이지임을 의미하는, 플러쉬 리스트의 시작지점에 집어넣기 위하여 버퍼풀 뮤텍스까지 함께
잡고 있어야 한다는 것입니다.

To some extent this contention point is decreased by splitting out the buffer flush list from the buffer pool mutex.
이러한 약간의 경합 포인트를 버퍼 풀 뮤텍스로부터 버퍼 플러시 리스트 밖으로 분할하여 감소 시킵니다.

We found a simple improvement of this particular problem.
우리는 이 특정 문제의 간단한 개선을 발견하였습니다.

The simple solution is to introduce a new mutex, log_flush_order mutex, this mutex is acquired while still holding the log_sys mutex, as soon as it is acquired we can release the log_sys mutex.
간단한 해결책으로 새로운 뮤텍스인 log_flush_order mutex를 소개합니다.
이 뮤텍스는 log_sys mutex가 대기하는 동안 얻을 수 있으며, 바로 log_sys mutex를 릴리스할 수 있습니다.
 
This gives us the property that the log_sys mutex is available for other operations such as starting
a new log write while we still serialise the input of the dirty pages into the buffer pool flush list.
이것은 우리에게  새로운 로그 쓰기 시작 등의 다른 작업이 가능하도록  log_sys 뮤텍스 속성을 제공합니다.
아직 버퍼 풀 플러시 목록으로 dirty pages 입력을 반복하는 동안 새 로그를 작성할 수 있습니다.

As can be easily seen this solution decrease the hold time of the log_sys mutex while not decreasing the frequency it is acquired.
In our experiments we saw that this very simple solution improved a Sysbench RW test by a few percent
쉽게 log_sys mutex의 빈도가 감소 되지 않는 동안에는 대기 시간이 감소 되는 솔루션을 볼 수 있습니다. 
우리는 매우 간단한 이 솔루션이 Sysbench RW 테스트에서  성능을  몇 % 향상 시키는  것을 볼 수 있었습니다.



참고 사항

1. http://dev.mysql.com/doc/innodb/1.1/en/innodb-improved-log-sys-mutex.html
   - Improved Log Sys Mutex ( 정의, 역할)
2.MYSQL 5.5  퍼포먼스 스카마를 사용한 성능 조회 참고

Type 'help;' OR '\h' FOR help. Type '\c' TO clear the current input statement.
 
mysql> SELECT *
    ->   FROM performance_schema.events_waits_summary_global_by_event_name
    ->  WHERE event_name LIKE 'wait/synch/mutex/innodb/%'
    ->  ORDER BY sum_timer_wait DESC;
+-------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME                                            | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| wait/synch/mutex/innodb/mutex_list_mutex              |       8367 |      382312980 |          34020 |          45692 |         158760 |
| wait/synch/mutex/innodb/rw_lock_list_mutex            |       8286 |      380714040 |          34020 |          45946 |        3549420 |
| wait/synch/mutex/innodb/kernel_mutex                  |        848 |      326206440 |          34020 |         384677 |       93589020 |
| wait/synch/mutex/innodb/log_sys_mutex                 |       1658 |      222532380 |          34020 |         134217 |        3961440 |
| wait/synch/mutex/innodb/rseg_mutex                    |       2847 |      204581160 |          34020 |          71858 |         283500 |
| wait/synch/mutex/innodb/buf_pool_mutex                |       3204 |      160389180 |          34020 |          50059 |         298620 |
| wait/synch/mutex/innodb/fil_system_mutex              |       2022 |      119584080 |          34020 |          59141 |         298620 |
| wait/synch/mutex/innodb/flush_list_mutex              |        773 |       29253420 |          34020 |          37844 |         136080 |
| wait/synch/mutex/innodb/recv_sys_mutex                |        166 |       10069920 |          45360 |          60662 |         147420 |
| wait/synch/mutex/innodb/log_flush_order_mutex         |        147 |        6486480 |          34020 |          44125 |         173880 |
| wait/synch/mutex/innodb/purge_sys_mutex               |         62 |        3938760 |          41580 |          63528 |         124740 |
| wait/synch/mutex/innodb/trx_undo_mutex                |         31 |        3424680 |          34020 |         110473 |         181440 |
| wait/synch/mutex/innodb/dict_sys_mutex                |         37 |        3073140 |          41580 |          83057 |         321300 |
| wait/synch/mutex/innodb/trx_doublewrite_mutex         |         59 |        2683800 |          34020 |          45488 |         158760 |
| wait/synch/mutex/innodb/autoinc_mutex                 |         24 |        2540160 |          56700 |         105840 |         215460 |
| wait/synch/mutex/innodb/thr_local_mutex               |         11 |         774900 |          41580 |          70445 |         136080 |
| wait/synch/mutex/innodb/srv_innodb_monitor_mutex      |         10 |         627480 |          41580 |          62748 |          94500 |
| wait/synch/mutex/innodb/innobase_share_mutex          |          3 |         340200 |          60480 |         113400 |         173880 |
| wait/synch/mutex/innodb/file_format_max_mutex         |          5 |         272160 |          34020 |          54432 |          86940 |
| wait/synch/mutex/innodb/ibuf_mutex                    |          3 |         117180 |          34020 |          39060 |          41580 |
| wait/synch/mutex/innodb/rw_lock_mutex                 |          0 |              0 |              0 |              0 |              0 |
| wait/synch/mutex/innodb/syn_arr_mutex                 |          0 |              0 |              0 |              0 |              0 |
| wait/synch/mutex/innodb/srv_dict_tmpfile_mutex        |          0 |              0 |              0 |              0 |              0 |