{"id":15043,"date":"2015-12-22T12:00:19","date_gmt":"2015-12-22T03:00:19","guid":{"rendered":"http:\/\/labs.gree.jp\/blog\/?p=15043"},"modified":"2015-12-21T10:22:04","modified_gmt":"2015-12-21T01:22:04","slug":"innodb-%e3%81%ae-mutex-%e3%81%ae%e8%a9%b1%ef%bc%88%e5%9f%ba%e6%9c%ac%e7%b7%a8%ef%bc%89","status":"publish","type":"post","link":"https:\/\/labs.gree.jp\/blog\/2015\/12\/15043\/","title":{"rendered":"InnoDB \u306e mutex \u306e\u8a71\uff08\u5165\u9580\u7de8\uff09"},"content":{"rendered":"<p>\u3053\u306e\u30a8\u30f3\u30c8\u30ea\u306f GREE Advent Calendar 2015 22\u65e5\u76ee\u306e\u8a18\u4e8b\u3067\u3059\u3002<\/p>\n<p>\u3053\u3093\u306b\u3061\u308f\u3002\u305b\u3058\u307e\u3067\u3059\u3002\u4e45\u3005\u306b 10inch \u306e Android Tablet \u8cb7\u3044\u304b\u3048\u305f\u3068\u3053\u308d\u3001USB\u304c\u30ad\u30e3\u30c3\u30d7\u30ec\u30b9\u9632\u6c34\u306b\u306a\u3063\u3066\u308b\u308f\u304a\u98a8\u5442\u3067\u4f7f\u3063\u3066\u3082\u5185\u8535\u30b9\u30d4\u30fc\u30ab\u30fc\u3067\u305d\u3053\u305d\u3053\u97f3\u91cf\u53d6\u308c\u308b\u308f\u6c34\u6ef4\u3064\u3044\u305f\u72b6\u614b\u3067\u3082\u30bf\u30c3\u30c1\u30d1\u30cd\u30eb\u306e\u7cbe\u5ea6\u304c\u9ad8\u3044\u308f\u3068\u3001\u3053\u306e\u5206\u91ce\u306b\u65bc\u3051\u308b\u6280\u8853\u306e\u9032\u6b69\u30cf\u30f3\u30d1\u306a\u3044\u306a\u3068\u611f\u5fc3\u3055\u305b\u3089\u308c\u307e\u3057\u305f\u3002<\/p>\n<h2>\u306f\u3058\u3081\u306b<\/h2>\n<p>\u6700\u8fd1\u3001 InnoDB \u306e mutex \u5468\u308a\u306e\u5b9f\u88c5\u306b\u95a2\u5fc3\u304c\u3042\u308b\u306e\u3067\u3059\u304c\u3001\u3061\u3087\u3063\u3068\u8abf\u3079\u3066\u307f\u305f\u3068\u3053\u308d\u3001\u81ea\u5206\u304c\u8aad\u307f\u305f\u3044\u3088\u3046\u306a\u5185\u5bb9\u306e\u5165\u9580\u8a18\u4e8b\u304c\u3001\u82f1\u8a9e\u3084\u65e5\u672c\u8a9e\u306e blog \u306a\u3069\u3067\u306f\u898b\u3064\u3051\u3089\u308c\u306a\u304b\u3063\u305f\u3001\u3042\u308b\u3044\u306f\u6700\u8fd1\u306e\u8a18\u4e8b\u3067\u306f\u306a\u304b\u3063\u305f\u306e\u3067\u3001\u5099\u5fd8\u9332\u3092\u517c\u306d\u3066\u81ea\u5206\u3067\u66f8\u3044\u3066\u307f\u308b\u3053\u3068\u306b\u3057\u307e\u3057\u305f\u3002\u4eca\u56de\u306e\u30b4\u30fc\u30eb\u306f\u3001 SHOW ENGINE INNODB STATUS\\G \u3067\u306e\u6b21\u306e\u30bb\u30af\u30b7\u30e7\u30f3\u3092\u8aad\u307f\u89e3\u304f\u3068\u3053\u308d\u307e\u3067\u3067\u3059\u3002<\/p>\n<pre><code class=\"mysql\">----------\nSEMAPHORES\n----------\nOS WAIT ARRAY INFO: reservation count 7325520\nOS WAIT ARRAY INFO: signal count 6522860\nMutex spin waits 37943231, rounds 459507138, OS waits 5431910\nRW-shared spins 3088929, rounds 55181480, OS waits 1297446\nRW-excl spins 1347928, rounds 27340426, OS waits 281717\nSpin rounds per wait: 12.11 mutex, 17.86 RW-shared, 20.28 RW-excl\n------------------------\n<\/code><\/pre>\n<p>\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u306f\u8aad\u307f\u3084\u3059\u3044\u306e\u3067 MySQL5.6 \u3092\u524d\u63d0\u3068\u3057\u307e\u3059\u304c\u3001 5.7 \u306b\u3064\u3044\u3066\u3082\u5c11\u3057\u89e6\u308c\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002 5.5 \u306f\u30fb\u30fb\u30fb mutex \u306e\u7af6\u5408\u3092\u6c17\u306b\u3059\u308b\u3088\u3046\u306b\u306a\u3063\u3066\u304d\u305f\u3089\u3001 MySQL \u306e\u30d0\u30fc\u30b8\u30e7\u30f3\u30a2\u30c3\u30d7\u3092\u691c\u8a0e\u3057\u305f\u307b\u3046\u304c\u3044\u3044\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002MySQL\u306e\u4e2d\u306e\u4eba\u3082\u304d\u3063\u3068\u305d\u3046\u8a00\u3046\u3067\u3057\u3087\u3046\u3002<\/p>\n<h2>mutex_spin_wait()<\/h2>\n<p>OS WAIT ARRAY INFO \u306f\u3044\u308f\u3086\u308b sync array \u3068\u547c\u3070\u308c\u308b\u3082\u306e\u306b\u3064\u3044\u3066\u306e\u60c5\u5831\u306a\u3093\u3067\u3059\u304c\u3001\u3068\u308a\u3042\u3048\u305a\u3053\u306e\u90e8\u5206\u306f\u5f8c\u56de\u3057\u306b\u3057\u3066\u3001 \u6b21\u306e\u884c\u304b\u3089\u898b\u3066\u3044\u304d\u307e\u3057\u3087\u3046\u3002<\/p>\n<pre><code class=\"mysql\">Mutex spin waits 37943231, rounds 459507138, OS waits 5431910\n<\/code><\/pre>\n<p>\u5148\u305a\u306f\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3092\u958b\u3044\u3066\u307f\u307e\u3059\u3002<br \/>\n<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/8e3680c0fad36737a7e6240ac01696ae285a322d\/storage\/innobase\/sync\/sync0sync.cc#L1553-L1589\">sync_print_wait_info()<\/a>\u3092\u8aad\u3080\u3068\u3001<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/8e3680c0fad36737a7e6240ac01696ae285a322d\/storage\/innobase\/sync\/sync0sync.cc#L173-L181\">mutex_spin_round_count,mutex_spin_wait_count,mutex_os_wait_count<\/a> \u306f\u305d\u308c\u305e\u308c\u3001 ib_counter_t class \u306e\u5909\u6570\u3067\u3042\u308a\u3001 mutex_spin_wait() \u3067\u52a0\u7b97\u3055\u308c\u308b\u3088\u3046\u3067\u3059\u3002<\/p>\n<p>\u203b\u3061\u306a\u307f\u306b\u3001 ib_count_t \u306f\u3042\u308b\u7a0b\u5ea6\u30de\u30eb\u30c1\u30b9\u30ec\u30c3\u30c9\u74b0\u5883\u3092\u60f3\u5b9a\u3057\u305f\u30b3\u30fc\u30c9\u306b\u306a\u3063\u3066\u308b\u3068\u601d\u3046\u306e\u3067\u3059\u304c\u3001\u3000MySQL5.5 \u306e\u3068\u304d mutex_spin_wait_count \u306a\u3069\u306f\u305f\u3060\u306e static \u306a 64bit \u306e\u6574\u6570\u578b\u3067\u3042\u308a\u3001\u8907\u6570\u306e\u30b9\u30ec\u30c3\u30c9\u304b\u3089\u540c\u6642\u306b\u66f4\u65b0\u3055\u308c\u3066\u3044\u305f\u306e\u3067\u3001\u304b\u306a\u308a\u9069\u5f53\u306a\u5024\u306b\u306a\u3063\u3066\u305f\u6c17\u304c\u3057\u307e\u3059\u3002 ib_count_t \u3082\u5024\u304c\u6b63\u78ba\u306b\u306a\u308b\u308f\u3051\u3067\u306f\u306a\u3044\u306e\u3067\u3059\u304c\u3001 CPU \u306e\u30ad\u30e3\u30c3\u30b7\u30e5\u30e9\u30a4\u30f3\u3092\u610f\u8b58\u3057\u305f\u6700\u9069\u5316\u304c\u65bd\u3055\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>\u3067\u306f\u3001\u6b21\u306b <a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/8e3680c0fad36737a7e6240ac01696ae285a322d\/storage\/innobase\/sync\/sync0sync.cc#L464-L583\">mutex_spin_wait()<\/a> \u3092\u773a\u3081\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<p>\u3056\u3063\u3068\u307e\u3068\u3081\u308b\u3068<\/p>\n<ol>\n<li>mutex_spin_wait() \u304c\u547c\u3070\u308c\u305f\u3089\u3001\u306a\u306b\u306f\u3068\u3082\u3042\u308c mutex_spin_wait_count \u304c\u52a0\u7b97\u3055\u308c\u308b\u3002\u3064\u307e\u308a\u3001 Mutex spin waits \u306f mutex_spin_wait() \u304c\u547c\u3070\u308c\u305f\u56de\u6570\u3067\u3042\u308b<\/li>\n<li>mutex_spin_wait() \u306f spin \u3068 mutex \u306e\u4e8c\u6bb5\u69cb\u3048\u3067\u3042\u308a\u3001\u305d\u308c\u3067\u3082mutex\u304c\u53d6\u308c\u306a\u304b\u3063\u305f\u3068\u304d\u306b sync array \u3067 event \u3092\u5f85\u3064\uff08MySQL\u306e\u5b9f\u88c5\u7684\u306b\u3044\u3046\u3068\u3001\u6761\u4ef6\u5909\u6570\u3092\u5f85\u3063\u3066\u308b\uff09\n<ol>\n<li>mutex \u306b\u7d10\u4ed8\u3044\u305f lockword \u304c 0 \u306e\u3068\u304d\u3001\u305d\u306e mutex \u3092\u4f7f\u3063\u3066\u308b\u30b9\u30ec\u30c3\u30c9\u306f\u3044\u306a\u3044\uff08mutex\u306f\u958b\u653e\u3055\u308c\u3066\u3044\u308b\uff09\u3068\u3044\u3046\u5b9f\u88c5\u306a\u306e\u3067\u3001 mutex \u53d6\u5f97\u3059\u308b\u524d\u306bmutex_get_lock_word() \u3067 lockword \u3092\u53d6\u5f97\u3057\u3001 0 \u304b\u3069\u3046\u304b\u30c6\u30b9\u30c8\u3059\u308b\u3002<\/li>\n<li>lockword \u304c 0 \u306b\u306a\u308b\u306e\u3092\u76e3\u8996\u3059\u308b spin loop \u304c\u3001SYNC_SPIN_ROUNDS\uff08\u5177\u4f53\u7684\u306b\u306f <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/ja\/innodb-parameters.html#sysvar_innodb_sync_spin_loops\">innodb_sync_spin_loops<\/a> \u306a\u306e\u3067\u30c7\u30d5\u30a9\u30eb\u30c8\u3060\u306830\u56de\uff09\u306e\u6570\u3060\u3051\u5b9f\u884c\u3055\u308c\u308b<\/li>\n<li>spin loop \u56de\u3063\u305f\u56de\u6570\u3060\u3051 mutex_spin_round_count \u304c\u52a0\u7b97\u3055\u308c\u308b\u3002\u3064\u307e\u308a\u3001 mutex_spin_round_count \u306f\u3001 spin loop \u3067\u7a7a\u632f\u3063\u305f\u56de\u6570\u3067\u3042\u308b\u3002<br \/>\nSYNC_SPIN_ROUNDS \u306e\u56de\u6570\u3060\u3051 spin loop \u56de\u3063\u3066\u3082 lockword \u304c 0 \u306b\u306a\u3089\u306a\u304b\u3063\u305f\u3001\u3042\u308b\u3044\u306f\u4ed6\u306e\u30b9\u30ec\u30c3\u30c9\u306b mutex \u53d6\u3089\u308c\u3066\u3057\u307e\u3063\u305f\u5834\u5408\u3001\u6700\u5927\uff14\u56de mutex \u306e\u53d6\u5f97\u3092\u8a66\u307f\u308b\u3002<\/li>\n<li>\u305d\u308c\u3067\u3082 mutex \u304c\u53d6\u5f97\u3067\u304d\u306a\u304b\u3063\u305f\u5834\u5408\u3001 mutex_os_wait_count \u3092 +1 \u3057\u3066\u3001 sync_array_event_wait() \u3092\u5b9f\u884c\u3057\u3066\u3001\u6761\u4ef6\u5909\u6570\u66f8\u304d\u63db\u308f\u308b\u306e\u3092\u5f85\u3064\u3002\u3064\u307e\u308a\u3001 mutex_os_wait_count \u306f sync_array_event_wait() \u304c mutex_spin_wait() \u306e\u4e2d\u3067\u547c\u3070\u308c\u305f\u56de\u6570\u3067\u3042\u308b\u3002<\/li>\n<\/ol>\n<\/li>\n<li>sync_array_event_wait() \u304b\u3089 return \u3057\u305f\u3089\u3001 spin loop \u304b\u3089\u3084\u308a\u76f4\u3059<\/li>\n<\/ol>\n<p>\u3068\u3044\u3046\u611f\u3058\u306b\u306a\u308b\u306e\u3067\u3059\u304c\u3001\u4e00\u3064\u4e00\u3064\u898b\u3066\u3044\u304d\u307e\u3057\u3087\u3046\u3002<\/p>\n<p>\u203b\u4fbf\u5b9c\u4e0a mutex \u3068\u8a00\u3044\u307e\u3057\u305f\u304c\u3001 <a href=\"https:\/\/ja.wikipedia.org\/wiki\/%E3%83%86%E3%82%B9%E3%83%88%E3%83%BB%E3%82%A2%E3%83%B3%E3%83%89%E3%83%BB%E3%82%BB%E3%83%83%E3%83%88\">Test-and-Set\u547d\u4ee4<\/a>\u3092\u4f7f\u3048\u308b\u74b0\u5883\u3067\u306f\u3001 mutex_spin_wait() \u3067\u306f pthread_mutex_trylock() \u306e\u304b\u308f\u308a\u306bTAS\u3092\u4f7f\u3046\u3088\u3046\u3067\u3059\u3002\u307e\u3041\u3056\u3063\u304f\u308a\u300c\u540c\u671f\u3092\u3068\u3063\u3066\u308b\u300d\u304f\u3089\u3044\u306e\u7406\u89e3\u3067\u3082\u5927\u4e08\u592b\u3060\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<h2>\u306a\u305c spin loop \u304c\u5fc5\u8981\u306a\u306e\u304b<\/h2>\n<p>spin loop\uff08\u3044\u308f\u3086\u308b<a href=\"https:\/\/ja.wikipedia.org\/wiki\/%E3%82%B9%E3%83%94%E3%83%B3%E3%83%AD%E3%83%83%E3%82%AF\">spinlock<\/a>\uff09\u3068\u3044\u3046\u3082\u306e\u306f\u7279\u306b\u73cd\u3057\u3044\u3082\u306e\u3067\u306f\u306a\u304f\u3001OS\u306e\u5185\u90e8\u3067\u4f7f\u308f\u308c\u3066\u3044\u308b\u3082\u306e\u3067\u3059\u304c\u3001\u305d\u306e\u3078\u3093\u306e\u7d4c\u7def\u306f<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/8e3680c0fad36737a7e6240ac01696ae285a322d\/storage\/innobase\/sync\/sync0sync.cc#L49-L169\">\u30b3\u30e1\u30f3\u30c8<\/a>\u3067\u66f8\u3044\u3066\u3042\u308a\u307e\u3059<\/p>\n<p>\u5fd9\u3057\u3044\u4eba\u306e\u305f\u3081\u306b\u4e09\u884c\u3067\u307e\u3068\u3081\u308b\u3068<\/p>\n<ul>\n<li>mutex \u306f\u30e1\u30e2\u30ea\u306b\u5bfe\u3059\u308b\u30a2\u30c8\u30df\u30c3\u30af\u306a\u66f8\u304d\u8fbc\u307f\u3068\u8aad\u307f\u8fbc\u307f\u3092\u5fc5\u8981\u3068\u3059\u308b<\/li>\n<li>spin loop \u306f\u30e1\u30e2\u30ea\u304b\u3089\u8aad\u3080\u3060\u3051\u306a\u306e\u3067\uff08\u5834\u5408\u306b\u3088\u3063\u3066\u306fCPU\u306e\u30ad\u30e3\u30c3\u30b7\u30e5\u304b\u3089\u8aad\u3080\u3060\u3051\u3067\u3044\u3044\u306e\u3067\uff09 mutex \u3088\u308a\u8efd\u3044<\/li>\n<li>\u8907\u6570\u306e\u30b9\u30ec\u30c3\u30c9\uff08\u8907\u6570\u306eCPU\u306e\u30b3\u30a2\uff09\u304b\u3089\u3001\u540c\u3058mutex\u306b\u30a2\u30af\u30bb\u30b9\u3059\u308b\u3068\u3001\u540c\u3058\u30e1\u30e2\u30ea\u306e\u9818\u57df\u306b\u5bfe\u3057\u3066\u30a2\u30c8\u30df\u30c3\u30af\u306b\u8aad\u307f\u66f8\u304d\u3059\u308b\u3053\u3068\u306b\u306a\u308b\u306e\u3067\u3001\u30ea\u30bd\u30fc\u30b9\u7af6\u5408\u3059\u308b\u3057\u30e1\u30e2\u30ea\u306e\u5e2f\u57df\u3082\u4f7f\u3063\u3061\u3083\u3046\u306e\u3067\u91cd\u3044\u3002<\/li>\n<\/ul>\n<p>\u3068\u3044\u3063\u305f\u3068\u3053\u308d\u304b\u306a\u3068\u601d\u3044\u307e\u3059\u3002\u305f\u3060\u3001\u4f11\u307f\u306a\u304f innodb_sync_spin_loops \u306e\u56de\u6570\u5206\u30eb\u30fc\u30d7\u56de\u308b\u306e\u3082\u3082\u3063\u305f\u3044\u306a\u3044\u306e\u3067\u3001<\/p>\n<pre><code class=\"cpp\">        if (srv_spin_wait_delay) {\n            ut_delay(ut_rnd_interval(0, srv_spin_wait_delay));\n        }\n<\/code><\/pre>\n<p>ut_rnd_internal \u3067\uff080\u304b\u3089 <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.6\/ja\/innodb-parameters.html#sysvar_innodb_spin_wait_delay\">innodb_spin_wait_delay<\/a> \u306e\u7bc4\u56f2\u3067\uff09\u5b9f\u884c\u56de\u6570\u3092\u30e9\u30f3\u30c0\u30de\u30a4\u30ba\u3057\u3064\u3064\u3001\u6b21\u56de lock_word \u306e\u5024\u3092\u30c1\u30a7\u30c3\u30af\u3059\u308b\u307e\u3067\u3042\u308b\u3066\u3044\u3069\u306e\u6642\u9593\u3092\u8a2d\u3051\u3066\u3044\u307e\u3059\u3002 pause \u547d\u4ee4\u3092\u4f7f\u3048\u308bCPU\u3067\u3042\u308c\u3070\u3001 ut_delay \u306e\u4e2d\u3067 pause \u547d\u4ee4\u3092\u5b9f\u884c\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>\u203bpause \u547d\u4ee4\u306b\u3064\u3044\u3066\u306f<a href=\"http:\/\/www.isus.jp\/article\/parallel-special\/benefitting-sleep-loops\/\">Intel\u3055\u3093\u306b\u3088\u308b\u3053\u3061\u3089\u306e\u8a18\u4e8b<\/a>\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<p>SEMAPHORES \u30bb\u30af\u30b7\u30e7\u30f3\u306e\u6700\u5f8c\u306b\u51fa\u529b\u3055\u308c\u3066\u3044\u308b<\/p>\n<pre><code class=\"mysql\">Spin rounds per wait: 12.11 mutex, 17.86 RW-shared, 20.28 RW-excl\n<\/code><\/pre>\n<p>\u3053\u3053\u3067\u8868\u793a\u3055\u308c\u3066\u3044\u308b mutex \u306f\u3001 mutex_spin_round_count \/mutex_spin_wait_count \u3068\u3044\u3063\u305f\u5024\u306b\u306a\u308a\u307e\u3059\u3002\u3053\u306e\u5024\u304c\u5927\u304d\u3051\u308c\u3070\u5927\u304d\u3044\u307b\u3069\u3001 spin loop \u3092\u4f55\u56de\u3082\u56de\u3063\u3066\u308b\u3063\u3066\u3053\u3068\u3067\u3057\u3087\u3046\u304b\u3089\u3001 \u305d\u306e\u3068\u304d\u306f innodb_spin_wait_delay \u3092\u30c1\u30e5\u30fc\u30cb\u30f3\u30b0\u3059\u308b\u4f59\u5730\u304c\u3042\u308b\u3063\u3066\u3053\u3068\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002<\/p>\n<p>\uff08\u3053\u3053\u304b\u3089\u5148\u306f\u5b8c\u5168\u306b\u63a8\u6e2c\u306e\u57df\u3092\u51fa\u306a\u3044\u306e\u3067\u3059\u304c\uff09 MySQL Performance Architect \u306e DimitriK \u304c\u516c\u958b\u3059\u308b\u8cc7\u6599\u3067\u306f\u3001\u3088\u304f<\/p>\n<pre><code class=\"mysql\">innodb_spin_wait_delay = 96\n<\/code><\/pre>\n<p>\u3068\u3044\u3046\u610f\u5473\u4e0d\u660e\u3059\u304e\u308b\u8b0e\u306e\u6570\u5b57\u304c\u51fa\u3066\u304f\u308b\u306e\u3067\u3059\u304c\u3001\u3072\u3087\u3063\u3068\u3057\u305f\u3089\u3001innodb_spin_wait_delay \u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u304c 6 \u306a\u306e\u3067\u3001DimitriK \u306e\u5b9f\u884c\u3057\u3066\u3044\u308b\u30d9\u30f3\u30c1\u30de\u30fc\u30af\u3068\u74b0\u5883\u306e\u7d44\u307f\u5408\u308f\u305b\u3060\u3068\u3001<\/p>\n<pre class=\"lang:default decode:true \" >Spin rounds per wait &gt;= (96 \/ 6)\n<\/pre>\n<p>\u3068\u3044\u3046\u611f\u3058\u3067\u3001 Spin rounds per wait \u304c 16 \u3092\u8d85\u3048\u3066\u308b\u306e\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u306d\u3002<\/p>\n<h2>sync array \u3068 Thundering herd problem<\/h2>\n<p>\u30d9\u30b9\u30c8\u30b1\u30fc\u30b9\u306f\u3001 mutex_spin_round_count \u3082 mutex_os_wait_count \u3082\u52a0\u7b97\u3055\u308c\u306a\u3044\u30b1\u30fc\u30b9\u3001mutex_spin_wait_count \u306e\u307f\u304c\u52a0\u7b97\u3055\u308c\u308b\u30b1\u30fc\u30b9\u3067\u3059\u3002\u305d\u306e\u3068\u304d\u3001 mutex \u306e\u7af6\u5408\u306f\u767a\u751f\u3057\u3066\u3044\u307e\u305b\u3093\u3002\u3057\u304b\u3057\u3001 mutex_os_wait_count \u304c\u307e\u3063\u305f\u304f\u52a0\u7b97\u3055\u308c\u306a\u3044\u3063\u3066\u3053\u3068\u306f\u3001\u305d\u3046\u305d\u3046\u306a\u3044\u3067\u3057\u3087\u3046\u3002\u3067\u306f\u3001 sync_array_event_wait() \u3067\u306f\u4f55\u304c\u884c\u308f\u308c\u3066\u3044\u308b\u306e\u3067\u3057\u3087\u3046\u304b\u3002<br \/>\nInnoDB \u306e\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u306f\u306a\u304b\u306a\u304b\u89aa\u5207\u3067\u3001 sync array \uff08WAIT ARRAY\uff09\u306b\u3064\u3044\u3066\u3082<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/8e3680c0fad36737a7e6240ac01696ae285a322d\/storage\/innobase\/sync\/sync0arr.cc#L46-L77\">\u9577\u6587\u306e\u30b3\u30e1\u30f3\u30c8<\/a>\u3067\u3061\u3083\u3093\u3068\u89e3\u8aac\u304c\u3055\u308c\u3066\u3044\u307e\u3059\u3002<br \/>\n\u3053\u308c\u3082\u5fd9\u3057\u3044\u4eba\u306e\u305f\u3081\u306b\u4e09\u884c\u3067\u307e\u3068\u3081\u308b\u3068<\/p>\n<ul>\n<li>\u53e4\u306e windows \u306a\u3069\u306fOS\u306e\u5236\u9650\u304c\u53b3\u3057\u304b\u3063\u305f<\/li>\n<li>OS\u306e\u540c\u671f\u6a5f\u69cb\u3060\u3051\u3067\u540c\u671f\u6a5f\u69cb\u3092\u5b9f\u73fe\u3059\u308b\u306e\u306f\u96e3\u3057\u304b\u3063\u305f\u306e\u3067\u3001\u30e9\u30c3\u30d1\u30fc\u3068\u3057\u3066 sync array \u306a\u308b\u3082\u306e\u304c\u4f5c\u3089\u308c\u305f<\/li>\n<li>\u73fe\u5728\u3067\u306f\u3001 InnoDB\u5185\u90e8\u3067\u30cf\u30f3\u30b0\u30a2\u30c3\u30d7\u3092\u691c\u51fa\u3059\u308b\u305f\u3081\u306b\u3001 error monitor thread \u304c\u76e3\u8996\u3059\u308b\u305f\u3081\u306b sync array(wait array) \u304c\u4f7f\u308f\u308c\u3066\u3044\u308b<\/li>\n<\/ul>\n<p>\u3068\u306e\u3053\u3068\u3067\u3059\u3002  SHOW ENGINE INNODB STATUS\\G \u3067\u3044\u3046\u3068<\/p>\n<pre><code class=\"mysql\">OS WAIT ARRAY INFO: reservation count 7325520\nOS WAIT ARRAY INFO: signal count 6522860\n<\/code><\/pre>\n<p>\u3053\u3053\u306e\u90e8\u5206\u3067\u3059\u306d\u3002\u3067\u306f\u3001\u4f55\u3092\u3084\u3063\u305f\u3089 reservation count \u304c\u5897\u3048\u3066\u4f55\u3092\u3084\u3063\u305f\u3089 signal count \u304c\u5897\u3048\u308b\u304b\u3067\u3059\u304c\u3002<\/p>\n<p>\u203b mutex \u5f85\u3063\u3066\u308b thread \u304c\u3044\u308b\u3068 reservation count \u306e\u884c\u3068 signal count \u306e\u884c\u306e\u9593\u306b\u3044\u308d\u3044\u308d\u8868\u793a\u3055\u308c\u308b\u3093\u3067\u3059\u304c\u305d\u308c\u306b\u3064\u3044\u3066\u306f\u307e\u305f\u5f8c\u307b\u3069<\/p>\n<p>reservation count \u3067\u3059\u304c\u3001\u3053\u308c\u306f sync_array_event_wait() \u3092\u547c\u3076\u305f\u3081\u306b sync array \u304b\u3089 cell \u3092\u78ba\u4fdd\u3059\u308b\u3068\u304d\uff08sync_array_reserve_cell()\u304c\u547c\u3070\u308c\u305f\u3068\u304d\uff09\u306b\u52a0\u7b97\u3055\u308c\u3001 sync_array_event_wait() \u3067mutex\u306e\u958b\u653e\u3092\u5f85\u3063\u3066\u308b\u30b9\u30ec\u30c3\u30c9\u304c\u3044\u308b\u3068\u304d\u3001 \u305d\u306emutex\u3092 unlock \u3059\u308b\u5ea6\u306b signal count \u306f\u52a0\u7b97\u3055\u308c\u307e\u3059\u3002 reservation count \u3068 signal count \u306e\u5dee\u5206\u304c\u5927\u304d\u3051\u308c\u3070\u5927\u304d\u3044\u307b\u3069\u3001mutex\u3092\u4e88\u7d04\u3059\u308b\u30b9\u30ec\u30c3\u30c9\u3068mutex\u3092\u958b\u653e\u3059\u308b\u30b9\u30ec\u30c3\u30c9\u306e\u6570\u306e\u5dee\u5206\u304c\u591a\u3044\u3068\u3044\u3046\u3053\u3068\u306a\u306e\u3067\u3001\u4e00\u3064\u306emutex\u3092\u591a\u304f\u306e\u30b9\u30ec\u30c3\u30c9\u304c\u53d6\u308a\u5408\u3063\u3066\u308b\u7af6\u5408\u72b6\u614b\u306b\u3042\u308b\u3068\u3044\u3048\u308b\u3067\u3057\u3087\u3046\u3002<\/p>\n<p>mutex_spin_wait() \u306a\u3069\u3067 sync_array_event_wait() \u3092\u547c\u3076\u3068\u3001\u6700\u7d42\u7684\u306b os_event_wait_low() \u3092\u547c\u3093\u3067\u3001 os_cond_wait() \u304b\u3089\u6700\u7d42\u7684\u306b\uff08Linux\u3067\u306f\uff09 pthread_cond_wait() \u3092\u547c\u3093\u3067\u5f85\u3061\u7d9a\u3051\u308b\u3053\u3068\u306b\u306a\u308b\u306e\u3067\u3059\u304c\u3001\u5f85\u3063\u3066\u308b\u30b9\u30ec\u30c3\u30c9\u305f\u3061\u306e mutex \u3064\u304b\u3093\u3067\u305f\u30b9\u30ec\u30c3\u30c9\u304c mutex \u3092 unlock \u3059\u308b\u30bf\u30a4\u30df\u30f3\u30b0\u3067\u3001\u305d\u306e\u3068\u304d pthread_cond_broadcast() \u3067\u3001\u5f85\u3063\u3066\u305f\u30b9\u30ec\u30c3\u30c9\u305f\u3061\u3092\u305c\u3093\u3076\u8d77\u5e8a\u3057\u307e\u3059\u3002<\/p>\n<p>\u305d\u3046\u3001 broadcast \u3059\u308b\u306e\u3067\u3059\uff01\u5f85\u3063\u3066\u305f\u30b9\u30ec\u30c3\u30c9\u305f\u3061\u306f\u3001\u3059\u3079\u3066 spin loop \u3092\u3084\u308a\u306a\u304a\u3057\u3066\u3057\u307e\u3046\u308f\u3051\u3067\u3059\uff01\u305d\u306e\u4e2d\u3067\u9078\u3070\u308c\u3057\u3082\u306e\u3060\u3051\u304c mutex \u3092\u63b4\u307f\u53d6\u308b\u3053\u3068\u306b\u6210\u529f\u3057\u3001\u307e\u305f\u540c\u3058\u3088\u3046\u306b\u3001 mutex \u3092\u53d6\u308c\u306a\u304b\u3063\u305f\u30b9\u30ec\u30c3\u30c9\u305f\u3061\u306f pthread_cond_wait()  \u3067\u5f85\u3064\u3068\u3044\u3046\u30eb\u30fc\u30d7\u306b\u5165\u308a\u307e\u3059\u3002\u3044\u308f\u3086\u308b <a href=\"https:\/\/en.wikipedia.org\/wiki\/Thundering_herd_problem\">Thundering herd problem<\/a> \u3067\u3059\u306d\u3002mutex_os_wait_count \u304c\u30ac\u30c3\u3068\u5897\u3048\u308b\u72b6\u6cc1\u304c\u3001\u5982\u4f55\u306b\u307e\u305a\u305d\u3046\u304b\u304a\u308f\u304b\u308a\u3044\u305f\u3060\u3051\u305f\u304b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<h2>\u4e00\u5fdc\u3001 RW-shared \u3084 RW-excl<\/h2>\n<p>\u8aac\u660e\u306f\u3057\u3087\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u304c\u3001 Mutex \u4ee5\u5916\u306b\u3082 RW-shared\uff08\u5171\u6709\u30ed\u30c3\u30af\uff09\u3084 RW-excl\uff08\u6392\u4ed6\u30ed\u30c3\u30af\uff09 \u3068\u3044\u3046\u3082\u306e\u304c\u3042\u308a\u307e\u3059\u304c\u3001 spin \u3068\u306e\u4e8c\u6bb5\u69cb\u3048\u3067\u3042\u308b\u3053\u3068\u3001\u305d\u308c\u3067\u3082\u30ed\u30c3\u30af\u53d6\u308c\u306a\u304b\u3063\u305f\u3089 pthread_cond_wait() \u3068\u3044\u3046\u5b9f\u88c5\u306a\u306e\u3067\u3001 SHOW ENGINE INNODB STATUS\\G \u306eRW-shared spins \u306a\u3069\u306f\u3001 Mutex spin waits \u306a\u3069\u3068\u540c\u3058\u3088\u3046\u306b\u898b\u3066\u3044\u305f\u3060\u3051\u308c\u3070\u3001\u3068\u308a\u3042\u3048\u305a\u554f\u984c\u306a\u3044\u306e\u3067\u306f\u306a\u3044\u3067\u3057\u3087\u3046\u304b\u3002\u8a73\u7d30\u304c\u6c17\u306b\u306a\u308b\u65b9\u306f\u3001 sync\/sync0rw.cc \u3092\u8aad\u3093\u3067\u307f\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<h2>\u3067\u306f\u3069\u3046\u3057\u3088\u3046<\/h2>\n<p>\u305d\u3046\u306f\u3044\u3063\u3066\u3082\u3001 spin lock \u3067\u3059\u3079\u3066\u306e\u554f\u984c\u306f\u89e3\u6c7a\u3067\u304d\u306a\u3044\u3067\u3057\u3087\u3046\u3057\u3001\u306a\u3093\u3089\u304b\u306e\u65b9\u6cd5\u3067\u30b9\u30ec\u30c3\u30c9\u306b\u5f85\u3063\u3066\u3082\u3089\u3046\u3053\u3068\u306f\u5fc5\u8981\u3067\u3057\u3087\u3046\u3002\u305d\u3046\u8003\u3048\u308b\u3068\u3001 spin lock \u3067\u89e3\u6c7a\u3067\u304d\u306a\u304b\u3063\u305f\u5834\u5408\u3001 pthread_cond_wait() \u3067\u5f85\u3063\u3066\u3082\u3089\u3046\u306e\u306f\u7121\u96e3\u3067\u306f\u306a\u3044\u304b\u3068\u601d\u3044\u307e\u3059\u3002\u305d\u3046\u306a\u308b\u3068\u3001\u8d77\u5e8a\u3059\u308b\u30b9\u30ec\u30c3\u30c9\u306e\u6570\u304c\u5c11\u306a\u304f\u306a\u308c\u3070\u3001\u72b6\u6cc1\u306f\u6539\u5584\u3059\u308b\u306f\u305a\u3067\u3059\u3002\u8d77\u5e8a\u3059\u308b\u30b9\u30ec\u30c3\u30c9\u306e\u6570\u3092\u5236\u9650\u3059\u308b\u3068\u3057\u305f\u3089\u3001\u6b21\u306e\u3088\u3046\u306a\u30a2\u30d7\u30ed\u30fc\u30c1\u304c\u8003\u3048\u3089\u308c\u307e\u3059\u3002<\/p>\n<ol>\n<li>mutex \u306e\u5206\u5272\n<ul>\n<li>InnoDB\u306f\u4f1d\u7d71\u7684\u306b\u3053\u306e\u6539\u5584\u3092\u7740\u3005\u3068\u3084\u3063\u3066\u3044\u308b\u5370\u8c61\u3002\u4f8b\u3048\u3070\u3001 MySQL 5.7.8 \u304b\u3089\u8ffd\u52a0\u3055\u308c\u305f <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-parameters.html#sysvar_innodb_adaptive_hash_index_parts\">innodb_adaptive_hash_index_parts<\/a> \u306b\u3088\u308a\u3001 Adaptive Hash Index \u306e latch\uff08mutex\uff09 \u306e\u6570\u3092\u5897\u3084\u3059\u3053\u3068\u304c\u3067\u304d\u307e\u3059<\/li>\n<\/ul>\n<\/li>\n<li>\u540c\u6642\u5b9f\u884c\u3055\u308c\u308b\u30b9\u30ec\u30c3\u30c9\u6570\u3092\u5236\u9650\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.6\/ja\/innodb-performance-thread_concurrency.html\">innodb_thread_concurrency<\/a> \u3092\u5909\u66f4\u3057\u3066 InnoDB \u3067\u540c\u6642\u5b9f\u884c\u3055\u308c\u308b\u30b9\u30ec\u30c3\u30c9\u306e\u6570\u3092\u5236\u9650\u3059\u308b<br \/>\n-- \u30c7\u30d5\u30a9\u30eb\u30c8\u306f 0 \u3067\u5236\u9650\u304c\u306a\u3044\u306e\u3067\u3001\u6700\u60aa\u3001CPU\u306e\u30b3\u30a2\u6570\u3088\u308a\u6841\u9055\u3044\u306b\u591a\u3044\u30b9\u30ec\u30c3\u30c9\u304c\u3001\u4e00\u3064\u306emutex\u3092\u3081\u3050\u3063\u3066 pthread_cond_wait() \u3067\u5f85\u3064\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059<\/li>\n<li>thread pool \u3067 InnoDB \u306b\u30a2\u30af\u30bb\u30b9\u3067\u304d\u308b\u30b9\u30ec\u30c3\u30c9\u6570\u3092\u305d\u3082\u305d\u3082\u5236\u9650<br \/>\n-- MySQL Enterprise Edition \u306e Thread Pool Plugin \u3068\u304b<br \/>\n-- Amazon Aurora \u3082 ThreadPool \u5de5\u592b\u3057\u3066\u308b\u305d\u3046\u306a\u306e\u3067\u3001\u3053\u306e\u30a2\u30d7\u30ed\u30fc\u30c1\u306a\u3093\u3067\u3057\u3087\u3046\u306d<\/li>\n<\/ul>\n<\/li>\n<li>\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u30b5\u30fc\u30d0\u3067\u30b3\u30cd\u30af\u30b7\u30e7\u30f3\u30d7\u30fc\u30ea\u30f3\u30b0\u3057\u3001MySQL\u306b\u5f35\u308c\u308b\u30b3\u30cd\u30af\u30b7\u30e7\u30f3\u306e\u6570\u3092\u5236\u9650\n<ul>\n<li>\u4f8b\u3048\u3070Java\u3060\u3068\u3084\u308a\u3084\u3059\u3044\u3067\u3057\u3087\u3046\u306d<\/li>\n<\/ul>\n<\/li>\n<li>SQL\u3084table\u306e\u8a2d\u8a08\u3092\u898b\u76f4\u3057\u3066\u3001 mutex \u306e\u7af6\u5408\u3092\u524a\u6e1b<\/li>\n<\/ol>\n<p>secondary index \u306e\u66f4\u65b0\u304c\u7af6\u5408\u3059\u308b\u30b1\u30fc\u30b9\u306a\u3069\u3001\u5165\u308a\u53e3\u3092\u3069\u3093\u306a\u306b\u7d5e\u3063\u3066\u3082\u6539\u5584\u3057\u306a\u3044\u30b1\u30fc\u30b9\u306f\u3042\u308a\u3048\u308b\u3067\u3057\u3087\u3046\u3002\u305d\u3046\u306a\u308b\u3068\u3084\u306f\u308a\u3001 SQL\u3084table\u3092\u898b\u76f4\u3059\u3068\u3044\u3046\u306e\u304c\u3001\u4e00\u756a\u7121\u96e3\u306a\u3068\u3053\u308d\u3068\u3044\u3048\u308b\u3067\u3057\u3087\u3046\u3002<\/p>\n<h2>SQL\u3092\u898b\u76f4\u3059\u305f\u3081\u306b<\/h2>\n<p>\u4f55\u306e\u60c5\u5831\u3082\u306a\u3044\u72b6\u6cc1\u3067SQL\u3092\u6700\u9069\u5316\u3059\u308b\u3068\u3044\u3046\u306e\u3082\u3064\u3089\u3044\u8a71\u3067\u3059\u3002\u3067\u304d\u308c\u3070\u4f55\u304b\u30e2\u30cb\u30bf\u30ea\u30f3\u30b0\u3057\u3066\u3001\u30d2\u30f3\u30c8\u3092\u5f97\u305f\u3044\u3068\u3053\u308d\u3067\u3059\u3002\u3053\u3053\u3067\u3001 SHOW ENGINE INNODB STATUS\\G \u306b\u51fa\u529b\u3055\u308c\u3066\u3044\u308b\u60c5\u5831\u304c\u5f79\u306b\u7acb\u3061\u307e\u3059\u3002\uff08 performance_schema &amp; SYS Schema \u3067\u3082\u3044\u308d\u3044\u308d\u5206\u304b\u308a\u305d\u3046\u3067\u3059\u304c\u3001\u4eca\u56de\u306f\u3068\u308a\u3042\u3048\u305a SHOW ENGINE INNODB STATUS \u3067\uff09<\/p>\n<p>\u8a66\u3057\u306b sysbench \u8d70\u3089\u305b\u3066\u308b\u6700\u4e2d\u3001\u6b21\u306e\u30b3\u30de\u30f3\u30c9\u3092\u5b9f\u884c\u3059\u308b\u3068<\/p>\n<pre><code class=\"bash\">$ mysql -e 'show engine innodb status\\G' | egrep -e '^Mutex at' -e 'Last time.*locked\n<\/code><\/pre>\n<p>\u6b21\u306e\u3088\u3046\u306a\u60c5\u5831\u304c\u5f97\u3089\u308c\u307e\u3059\u3002<\/p>\n<pre><code class=\"bash\">$ mysql -e 'show engine innodb status\\G' | egrep -e '^Mutex at' -e 'Last time.*locked'\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 1\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 1\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nMutex at 0x7f3a78379088 created file lock0lock.cc line 609, lock var 0\nLast time read locked in file btr0sea.cc line 931\nLast time write locked in file \/export\/home\/pb2\/build\/sb_0-10831761-1384711588.85\/mysql-5.6.15\/storage\/innobase\/row\/row0upd.cc line 2391\n$\n<\/code><\/pre>\n<p>\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3068\u884c\u6570\u304c\u7279\u5b9a\u3067\u304d\u308b\u306e\u3067\u3001\u3069\u3053\u3067 mutex \u3084 rw_lock \u3092\u53d6\u5f97\u3057\u3066\u308b\u30b9\u30ec\u30c3\u30c9\u304c\u591a\u3044\u306e\u304b\u304c\u308f\u304b\u308a\u307e\u3059\u3002\u5b9a\u671f\u7684\u306b SHOW ENGINE INNODB STATUS\\G \u3057\u3066\u3001\u307e\u305a\u306f\u3053\u308c\u3092 fluentd \u306a\u3069\u3067\u96c6\u3081\u3066\u307f\u308b\u306e\u3082\u826f\u3055\u305d\u3046\u3067\u3059\u3002<\/p>\n<p>\u6b32\u3092\u3044\u3046\u3068\u3001\u30d7\u30ed\u30c0\u30af\u30b7\u30e7\u30f3\u74b0\u5883\u3067\u3053\u3046\u3044\u3063\u305f\u60c5\u5831\u3092\u53ce\u96c6\u3057\u3066RRD\u306a\u3069\u306b\u4fdd\u5b58\u3057\u3001\u904e\u53bb\u306e\u72b6\u6cc1\u3068\u6bd4\u8f03\u3057\u3066\u307f\u305f\u3044\u3082\u306e\u3067\u3059\u3002\u308f\u305f\u3057\u306f\u4ee5\u524d\u304b\u3089\u3053\u3046\u3044\u3046\u30e1\u30c8\u30ea\u30c3\u30af\u3092\u53d6\u5f97\u3057\u305f\u3044\u3068\u8003\u3048\u3066\u3044\u305f\u3093\u3067\u3059\u304c\u3001\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u306e\u884c\u6570\u306fMySQL\u306e\u30de\u30a4\u30ca\u30fc\u30d0\u30fc\u30b8\u30e7\u30f3\u9055\u3046\u3060\u3051\u3067\u5909\u308f\u308a\u3046\u308b\u306e\u3067\u3001\u3069\u3046\u3057\u3088\u3046\u304b\u306a\u3068\u3001\u304b\u3064\u3066\u306f\u601d\u3063\u3066\u3044\u307e\u3057\u305f\u3002\u305f\u3060\u3001\u300c\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u304c\u7279\u5b9a\u3067\u304d\u308c\u3070\u3001\u3042\u308b\u7a0b\u5ea6\u4f55\u3092\u3084\u3063\u3066\u308b\u304b\u308f\u304b\u308b\u304b\u3089\u5225\u306b\u3044\u3044\u3093\u3058\u3083\u306a\u3044\uff1f\u300d\u3063\u3066\u5272\u308a\u5207\u308c\u3070\u3044\u3044\u304b\u3068\u6700\u8fd1\u5439\u3063\u5207\u308c\u307e\u3057\u305f\u306e\u3067\u3002<br \/>\n\u4f8b\u3048\u3070\u3053\u3046\u3057\u307e\u3059\u3002<\/p>\n<pre><code class=\"bash\">$ mysql -e 'show engine innodb status\\G' | egrep -e '^Mutex at' -e 'Last time.*locked' | sed 's\/\\\/.*\\\/\/\/'| sed -e 's\/^Last time \\([a-z][a-z]*\\) locked in file \\([a-z0-9][_a-z0-9]*\\)\\.[chi].*\/my_\\1_locked_\\2\/'  -e 's\/^Last time \\([a-z][_a-z]*\\) locked in file not yet.*\/my_\\1_locked_not_yet\/' -e 's\/^Mutex at.*created file \\([a-z0-9][_a-z0-9]*\\)\\.[chi].*\/my_mutex_\\1\/' | sort | uniq -c\n     19 my_read_locked_btr0sea\n      3 my_read_locked_row0sel\n      4 my_write_locked_btr0sea\n      1 my_write_locked_row0ins\n      3 my_write_locked_row0purge\n     14 my_write_locked_row0upd\n$ \n<\/code><\/pre>\n<p>\u3053\u308c\u306a\u3089RRD\u304b\u306a\u306b\u304b\u306b\u4fdd\u5b58\u3067\u304d\u305d\u3046\u3067\u3059\u3002 row0sel.cc \u306f SELECT\u3001row0ins.cc \u306f INSERT row0upd.cc \u306f UPDATE\uff08\u3042\u308b\u3044\u306fDELETE\uff09 \u3001\u3000row0purge.cc \u306f\uff08InnoDB\u306e\u5185\u90e8\u7684\u306a\uff09purge\u3001 btr0sea.cc \u306fAdaptive Hash Index \u3060\u3068\u3001\u305d\u308c\u305e\u308c\u306e\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u306e\u5148\u982d\u306b\u3042\u308b\u30b3\u30e1\u30f3\u30c8\u306a\u3069\u8aad\u3080\u3068\u8aad\u307f\u53d6\u308c\u307e\u3059\u3002\u3068\u3044\u3046\u308f\u3051\u3067\u3001\u4e0a\u306e\u4f8b\u3067\u3044\u3046\u3068\u300cINSERT\/UPDATE\uff08\u3042\u308b\u3044\u306fDELETE\uff09\/SELECT\u3092\u5b9f\u884c\u4e2d\u306a\u306e\u3060\u304c\u3001 Adaptive Hash Index \u306e mutex \u304c\u7af6\u5408\u3057\u3066\u308b\u306e\u304b\u3082\u3057\u308c\u306a\u3044\u300d\u3068\u3044\u3046\u898b\u5f53\u304c\u3064\u3051\u3089\u308c\u308b\u306e\u3067\u306f\u306a\u3044\u3067\u3057\u3087\u3046\u304b\u3002\u3053\u3053\u307e\u3067\u5177\u4f53\u5316\u3067\u304d\u308b\u3068\u3001\u5b9f\u884c\u3055\u308c\u3066\u3044\u308bSQL\u3092\u773a\u3081\u306a\u304c\u3089\u300c\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u66f4\u65b0\u304c\u7af6\u5408\u3057\u3066\u308b\u3093\u3060\u308d\u3046\u304b\uff1f\u300d\u3068\u8003\u3048\u305f\u308a\u3001\u30b0\u30fc\u30b0\u30eb\u5148\u751f\u306b\u304a\u4f3a\u3044\u3092\u7acb\u3066\u308b\u3053\u3068\u3082\u3084\u308a\u3084\u3059\u304f\u306a\u308b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<p>\u3056\u3063\u304f\u308a\u96c6\u8a08\u5bfe\u8c61\u3068\u306a\u308a\u305d\u3046\u306a\u30d5\u30a1\u30a4\u30eb\u540d\u3092 grep \u306a\u3069\u3057\u3066\u62bd\u51fa\u3057\u3066\u307f\u307e\u3057\u305f\u304c\u3001<\/p>\n<p>5.6 \u3060\u3068 71<\/p>\n<pre><code class=\"bash\">$ find . -type f | xargs egrep -l '(mutex_(create|enter)|rw_lock_create|rw_lock_(s|x|sx)_lock(|_inline|_gen|_gen_nowait|_nowait)\\()' | sed 's#.\/[a-z]*\/\\([0_a-z]*\\)\\..*#\\1#' | sort | uniq | wc -l\n71\n$ find . -type f | xargs egrep -l '(mutex_(create|enter)|rw_lock_create|rw_lock_(s|x|sx)_lock(|_inline|_gen|_gen_nowait|_nowait)\\()' | sed 's#.\/[a-z]*\/\\([0_a-z]*\\)\\..*#\\1#' | sort | uniq | head\napi0api\nbtr0btr\nbtr0cur\nbtr0sea\nbuf0buddy\nbuf0buf\nbuf0dblwr\nbuf0dump\nbuf0flu\nbuf0lru\n$ \n<\/code><\/pre>\n<p>5.7\u3060\u306873\u3042\u308b\u3088\u3046\u306a\u306e\u3067<\/p>\n<pre><code class=\"bash\">$ find . -type f | xargs egrep -l '(mutex_(create|enter)|rw_lock_create|rw_lock_(s|x|sx)_lock(|_inline|_gen|_gen_nowait|_nowait)\\()' | sed 's#.\/[a-z]*\/\\([0_a-z]*\\)\\..*#\\1#' | sort | uniq | wc -l\n73\n$ find . -type f | xargs egrep -l '(mutex_(create|enter)|rw_lock_create|rw_lock_(s|x|sx)_lock(|_inline|_gen|_gen_nowait|_nowait)\\()' | sed 's#.\/[a-z]*\/\\([0_a-z]*\\)\\..*#\\1#' | sort | uniq | head\napi0api\nbtr0btr\nbtr0cur\nbtr0sea\nbuf0buddy\nbuf0buf\nbuf0dblwr\nbuf0dump\nbuf0flu\nbuf0lru\n$\n<\/code><\/pre>\n<p>RRD\u306b\u6b8b\u3059\u5834\u5408\u3001\u9805\u76ee\u7d5e\u3063\u305f\u307b\u3046\u304c\u3044\u3044\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002\u307e\u3060\u30c7\u30fc\u30bf\u3092\u5168\u7136\u96c6\u8a08\u3067\u304d\u3066\u306a\u3044\u306e\u3067\u30a4\u30e1\u30fc\u30b8\u3060\u3051\u3067\u3044\u3046\u3068\u3001\u307e\u305a\u306f<\/p>\n<ul>\n<li>btr0sea.cc<\/li>\n<li>buf0buf.cc<\/li>\n<li>row0{ins,upd,sel}.cc<\/li>\n<li>trx0rseg.cc<\/li>\n<\/ul>\n<p>\u3053\u306e\u3042\u305f\u308a\u304b\u3089\u53d6\u3063\u3066\u307f\u3088\u3046\u304b\u306a\u3041\u3068\u307c\u3093\u3084\u308a\u8003\u3048\u3066\u3044\u307e\u3059\u3002\u307e\u3041\u3001\u3053\u3053\u304b\u3089\u3055\u304d\u306f\u5165\u9580\u7de8\u3067\u306f\u306a\u304f\u5fdc\u7528\u7de8\u306e\u5185\u5bb9\u3068\u3044\u3046\u6c17\u3082\u3059\u308b\u306e\u3067\u3001\u307e\u305f\u306e\u6a5f\u4f1a\u306b\u3055\u305b\u3066\u3044\u305f\u3060\u304d\u307e\u3059\u3002<\/p>\n<h2>MySQL5.7 \u3067\u306f<\/h2>\n<p>SHOW ENGINE INNODB STATUS\\G \u306e SEMAPHOES \u30bb\u30af\u30b7\u30e7\u30f3\u306e\u8868\u793a\u304c\u6b21\u306e\u3088\u3046\u306b\u5909\u308f\u308a\u307e\u3057\u305f\u3002<\/p>\n<pre><code class=\"mysql\">----------\nSEMAPHORES\n----------\nOS WAIT ARRAY INFO: reservation count 27522\nOS WAIT ARRAY INFO: signal count 38076\nRW-shared spins 0, rounds 33935, OS waits 6577\nRW-excl spins 0, rounds 76207, OS waits 2196\nRW-sx spins 884, rounds 13659, OS waits 107\nSpin rounds per wait: 33935.00 RW-shared, 76207.00 RW-excl, 15.45 RW-sx\n------------------------\n<\/code><\/pre>\n<p>Mutex spin wait \u306e\u884c\u304c\u306a\u304f\u306a\u308a\u3001\u4ee3\u308f\u308a\u306b RW-sx \u304c\u8ffd\u52a0\u3055\u308c\u307e\u3057\u305f\u3002 SX-lock \u306b\u3064\u3044\u3066\u306e WorkLog \u306f<a href=\"http:\/\/dev.mysql.com\/worklog\/task\/?id=6363\">\u3053\u3061\u3089<\/a>\u306b\u306a\u308a\u307e\u3059\u3002 SX-lock \u306f <a href=\"http:\/\/dev.mysql.com\/worklog\/task\/?id=6326\">index->lock \u306e\u6539\u5584<\/a> \u306e\u305f\u3081\u306b\u5fc5\u8981\u3060\u3063\u305f\u3088\u3046\u3067\u3059\u306d\u3002<br \/>\nMutex spin wait \u304c\u306a\u304f\u306a\u3063\u3066\u307e\u3059\u3051\u3069\u3001 5.7 \u306e sync0sync.cc \u3092\u8aad\u3093\u3067\u3044\u305f\u3060\u304f\u3068\u308f\u304b\u308b\u306e\u3067\u3059\u304c\u3001\u304b\u306a\u308a\u30ea\u30d5\u30a1\u30af\u30bf\u30ea\u30f3\u30b0\u3055\u308c\u3066\u3044\u307e\u3059\u3002\u3042\u307e\u308a\u5ba3\u4f1d\u3055\u308c\u3066\u306a\u3044\u6c17\u304c\u3057\u307e\u3059\u3051\u3069\u3001WorkLog \u3067\u3044\u3046\u3068<a href=\"http:\/\/dev.mysql.com\/worklog\/task\/?id=6044\">\u3053\u3061\u3089\u306eWorkLog<\/a>\u3067\u3059\u306d\u3002<\/p>\n<blockquote><p>\n  Futex - Only on Linux<br \/>\n  \u3000This a a TTAS type of mutex that uses Futexes instead of os_event_t to wait when there is contention. The advantage is that we avoid the thundering herd problem by using the Linux futex infrastructure.\n<\/p><\/blockquote>\n<p>\u7d20\u6674\u3089\u3057\u3044\uff01InnoDB \u958b\u767a\u30c1\u30fc\u30e0\u306f\u304b\u306a\u308a\u5730\u9053\u306b\u6539\u5584\u3092\u7d9a\u3051\u3066\u3044\u308b\u3088\u3046\u3067\u3059\u3002<\/p>\n<p>\u3068\u3001\u304a\u3082\u3044\u304d\u3084\u30fb\u30fb\u30fb<\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/source-configuration-options.html#option_cmake_mutex_type\">Cmake\u306e\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u898b\u3066\u307f\u308b\u3068<\/a><\/p>\n<blockquote><p>\n  event: Use event mutexes. This is the default value and the original InnoDB mutex implementation.\n<\/p><\/blockquote>\n<p>\u4eca\u306e\u3068\u3053\u308d\u307e\u3060 futex \u306f build \u6642\u306e\u30aa\u30d7\u30b7\u30e7\u30f3\u6271\u3044\u306e\u3088\u3046\u3067\u3059\u3002 dev.mysql.com \u304b\u3089\u914d\u5e03\u3059\u308b\u30d0\u30a4\u30ca\u30ea\u306f\u3001\u53e4\u3044\u74b0\u5883\u306b\u914d\u616e\u3057\u3066\u3044\u308b\u3068\u3044\u3046\u3053\u3068\u3067\u3057\u3087\u3046\u304b\uff1f\u65e9\u304f\u30c7\u30d5\u30a9\u30eb\u30c8\u306b\u3067\u304d\u308b\u6642\u4ee3\u304c\u6765\u3066\u307b\u3057\u3044\u3082\u306e\u3067\u3059\u3002<\/p>\n<h2>\u6700\u5f8c\u306b<\/h2>\n<p>\u3053\u306e\u3042\u305f\u308a\u3067\u4eca\u56de\u306e\u5165\u9580\u7de8\u306f\u7d42\u308f\u308a\u306b\u3055\u305b\u3066\u3044\u305f\u3060\u3053\u3046\u3068\u601d\u3044\u307e\u3059\u3002\u3042\u308b\u7a0b\u5ea6\u30ce\u30a6\u30cf\u30a6\u304c\u305f\u307e\u3063\u3066\u304d\u305f\u3089\u3001\u5fdc\u7528\u7de8\u304b\u3051\u305f\u3089\u3044\u3044\u306a\u3068\u601d\u3063\u3066\u307e\u3059\u3002<\/p>\n<p>\u660e\u65e5\u306f\u82e5\u624b\u306e\u4e0a\u7af9\u304f\u3093\u3067\u3059\u3002\u304a\u697d\u3057\u307f\u306b\uff01<\/p>\n<h2>References<\/h2>\n<ul>\n<li><a href=\"http:\/\/buildup-db.blogspot.jp\/2015\/03\/innodb-deep-talk-2.html\">InnoDB Deep Talk #2 (\u4eee) \u306b\u5f15\u3063\u5f35\u308a\u3060\u3055\u308c\u307e\u3057\u305f\u3002<\/a><\/li>\n<li><a href=\"http:\/\/conferences.oreilly.com\/mysql2010\/public\/schedule\/detail\/12660\">How to Fulfil the Potential of InnoDB's Performance and Scalability<\/a><\/li>\n<li><a href=\"https:\/\/www.percona.com\/blog\/2006\/07\/17\/show-innodb-status-walk-through\/\">SHOW INNODB STATUS walk through<\/a><\/li>\n<li><a href=\"https:\/\/www.percona.com\/blog\/2006\/09\/07\/internals-of-innodb-mutexes\/\">Internals of InnoDB mutexes<\/a><\/li>\n<li><a href=\"http:\/\/dimitrik.free.fr\/blog\/archives\/2015\/11\/slides-from-my-talk-about-mysql-performance-openworld-2015.html\">Slides from my talk about MySQL Performance @OpenWorld 2015<\/a><\/li>\n<li><a href=\"http:\/\/dimitrik.free.fr\/blog\/archives\/2012\/06\/mysql-performance-pfs-overhead-in-56.html\">MySQL Performance: PFS Overhead in 5.6<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u306e\u30a8\u30f3\u30c8\u30ea\u306f GREE Advent Calendar 2015 22\u65e5\u76ee\u306e\u8a18\u4e8b\u3067\u3059\u3002 \u3053\u3093\u306b\u3061\u308f\u3002\u305b\u3058\u307e\u3067\u3059\u3002\u4e45\u3005\u306b 10inch \u306e Android Tablet \u8cb7\u3044\u304b\u3048\u305f\u3068\u3053\u308d\u3001USB\u304c\u30ad\u30e3\u30c3\u30d7\u30ec\u30b9\u9632\u6c34\u306b\u306a\u3063\u3066 [&hellip;]<\/p>\n","protected":false},"author":137,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[54,17],"class_list":["post-15043","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","tag-advent-calendar","tag-mysql"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/15043","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/users\/137"}],"replies":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/comments?post=15043"}],"version-history":[{"count":3,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/15043\/revisions"}],"predecessor-version":[{"id":15070,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/15043\/revisions\/15070"}],"wp:attachment":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/media?parent=15043"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/categories?post=15043"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/tags?post=15043"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}