MySQL 5.1.46リリース InnoDB PluginãŒæ£å¼ç‰ˆã«
出ã¾ã—ãŸã€‚今回ã¯æ©Ÿèƒ½è¿½åŠ ãŒ1件ã€ãƒã‚°ä¿®æ£ãŒ55件ã‚ã‚Šã¾ã™ã€‚ãƒã‚°ä¿®æ£ã®ã†ã¡ã‚»ã‚ュリティã«é–¢ã™ã‚‹ã‚‚ã®ãŒ1件ã€ãƒ‘ーティショニングã«é–¢ã™ã‚‹ã‚‚ã®ãŒ5件ã€ãƒ¬ãƒ—リケーションã«é–¢ã™ã‚‹ã‚‚ã®ãŒ7件ã¨ãªã£ã¦ã„ã¾ã™ã€‚
MySQL 5.1.38ã‹ã‚‰æœ¬ä½“ã«ä»˜å±žã™ã‚‹ã‚ˆã†ã«ãªã£ãŸInnoDB Pluginã§ã™ãŒã€ãƒãƒ¼ã‚¸ãƒ§ãƒ³ãŒ1.0.7ã«ä¸ŠãŒã‚ŠRCã‹ã‚‰GA(Generally Availableã€æ£å¼ç‰ˆ)ã¨ãªã‚Šã¾ã—ãŸã€‚ã¤ã„ã«æ£å¼ãƒªãƒªãƒ¼ã‚¹ã§ã™ã€‚ã¨ã„ã†ã‚ã‘ã§ä½•åº¦ã‹ç¹°ã‚Šè¿”ã—ã¦ã„る話題ã§ã™ãŒã€ä»Šå›žã¯InnoDB Pluginã«ã¤ã„ã¦å†åº¦ãŠã•ã‚‰ã„ã‚’ã—ã¦ãŠããŸã„ã¨æ€ã„ã¾ã™ã€‚
InnoDB Pluginã®ä½¿ã„æ–¹
MySQL 5.1.38以é™ã§ã‚ã‚Œã°InnoDB Pluginを使ã†ã‚ˆã†ã«è¨å®šã™ã‚‹ã®ã¯ç°¡å˜ã§ã™ã€‚/etc/my.cnfã«ä»¥ä¸‹ã®è¨å®šã‚’書ãåŠ ãˆã‚‹ã“ã¨ã§InnoDB PluginãŒæœ‰åŠ¹åŒ–ã•ã‚Œã¾ã™ã€‚
ignore-builtin-innodb plugin-load = innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so; innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so; innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so; innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so (plugin-loadã¯å®Ÿéš›ã«ã¯1è¡Œ)
Windowsã®å ´åˆã¯æ‹¡å¼µåãŒ.dllã¨ãªã‚Šã¾ã™ã€‚my.iniã«ä»¥ä¸‹ã®è¨å®šã‚’書ãåŠ ãˆã¦ãã ã•ã„。
ignore-builtin-innodb plugin-load = innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll; innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll; innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll; innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll (plugin-loadã¯å®Ÿéš›ã«ã¯1è¡Œ)
InnoDB PluginãŒæœ‰åŠ¹åŒ–ã•ã‚ŒãŸã“ã¨ã¯ã€èµ·å‹•æ™‚ã®ãƒã‚°ã§ç¢ºèªã§ãã¾ã™ã€‚
100425 22:22:44 InnoDB Plugin 1.0.7 started; log sequence number 80684451285
Bug #47621ã¨Bug #47622ã«ã¤ã„ã¦
MySQL 5.1.41ã®ã‚¨ãƒ³ãƒˆãƒªã§Yasufumi先生ã«æŒ‡æ‘˜ã„ãŸã ã„ã¦ã€ã•ã‚‰ã«id:hirose31ã•ã‚“ã«ãƒ¡ãƒ¢: InnoDB Pluginã®ãƒã‚° - (ã²)メモã§è¿½è©¦ã—ã¦ã„ãŸã ã„ã¦ã„るよã†ã«ã€InnoDB Plugin 1.0.6ã«ã¯å¤§ããªãƒã‚°ãŒ2ã¤æ®‹ã£ã¦ã„ã¾ã—ãŸã€‚
- Bug #47621 MySQL and InnoDB data dictionaries will become out of sync when renaming columns
- Bug #47622 the new index is added before the existing ones in MySQL, but after one in SE
ã“れら二ã¤ã¨ã‚‚ã€MySQL 5.1.46+InnoDB Plugin 1.0.7ã§ä¿®æ£ã•ã‚Œã¾ã—ãŸã€‚一安心ã§ã™ã。
InnoDB Pluginã®ç‰¹é•·
å†æŽ²ã—ã¾ã™ã€‚
- 高速ãªã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ä½œæˆã€‚従æ¥InnoDBã®CREATE INDEXã¯ãƒ†ãƒ¼ãƒ–ルã®å†ä½œæˆã‚’ä¼´ã£ã¦ã„ã¾ã—ãŸ
- テーブルã¨ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã®åœ§ç¸® (検証çµæžœãã®1ã€ãã®2)
- INFORMATION_SCHEMAã«ã‚ˆã‚‹ãƒãƒƒã‚¯ç«¶åˆã®æ¤œå‡º (検証çµæžœ)
- CPUスケーラビリティã®å‘上 (1.0.3ã‹ã‚‰ã€‚本エントリã§è§£èª¬)
- ãƒãƒƒã‚¯ã‚°ãƒ©ã‚¦ãƒ³ãƒ‰I/Oスレッドã®å¢—åŠ (1.0.4ã‹ã‚‰ã€‚検証çµæžœ)
- グループコミットã®ä¿®æ£ (1.0.4ã‹ã‚‰ã€‚Sunæ¾ä¿¡æ°ã«ã‚ˆã‚‹æ¤œè¨¼çµæžœ)
- ãƒãƒƒãƒ•ã‚¡ãƒ—ール管ç†ã‚¢ãƒ«ã‚´ãƒªã‚ºãƒ ã®å¼·åŒ– (1.0.5ã‹ã‚‰ã€‚解説エントリ)
ã¾ã 解説ã—ã¦ã„ãªã„残り二ã¤ã®ç‰¹é•·ã§ã™ãŒã€ä»Šå›žã¯ãã®ã†ã¡CPUスケーラビリティã«ã¤ã„ã¦ç¢ºèªã—ã¦ã„ããŸã„ã¨æ€ã„ã¾ã™ã€‚
CPUスケーラビリティã¨ã¯
ã‚‚ã—ã‹ã—ãŸã‚‰é¦´æŸ“ã¿ã®ãªã„言葉ã‹ã‚‚ã—ã‚Œãªã„ã®ã§èª¬æ˜Žã—ã¦ãŠãã¾ã™ã€‚CPUスケーラビリティã¨ã¯ã€ŒCPUã‚ã‚‹ã„ã¯CPUコアã®æ•°ã‚’増やã™ã“ã¨ã«ã‚ˆã£ã¦ã€ã©ã“ã¾ã§ãƒªãƒ‹ã‚¢ã«æ€§èƒ½ã‚’伸ã°ã™ã“ã¨ãŒã§ãã‚‹ã‹ã€ã¨ã„ã†æ¦‚念ã§ã™ã€‚シングルコアã«æ¯”ã¹ã¦ãƒ‡ãƒ¥ã‚¢ãƒ«ã‚³ã‚¢ã§2å€ã€ã‚¯ã‚¢ãƒƒãƒ‰ã‚³ã‚¢ã§4å€ã¸ã¨æ€§èƒ½ãŒä¼¸ã³ã‚Œã°è©±ã¯æ—©ã„ã®ã§ã™ãŒã€æ®‹å¿µãªãŒã‚‰ä¸–ã®ä¸ã«ã¯ã‚¢ãƒ ダールã®æ³•å‰‡ã¨ã„ã†ã‚‚ã®ãŒã‚ã£ã¦ã€æ€§èƒ½ãŒCPUæ•°ã«å®Œå…¨ã«æ¯”例ã—ã¦ä¼¸ã³ã‚‹ã“ã¨ã¯ã¾ãšã‚ã‚Šã¾ã›ã‚“。特ã«MySQLã¯CPUスケーラビリティãŒä»–ã®RDBMSã«æ¯”ã¹ã¦æ‚ªãã€ã“ã‚ŒãŒæ”¹å–„ã•ã‚Œã‚‹ã“ã¨ã‚’多ãã®ãƒ¦ãƒ¼ã‚¶ãŒå¾…ã¡æœ›ã‚“ã§ã„ã¾ã—ãŸã€‚
Dimitri KRAVTCHUKæ°ã«ã‚ˆã‚‹æ¤œè¨¼çµæžœ
ç§ã¯æ®‹å¿µãªãŒã‚‰CPUスケーラビリティを測定ã§ãるよã†ãªã‚µãƒ¼ãƒã‚’æŒã¡åˆã‚ã›ã¦ã„ãªã„ã®ã§ã€ä»Šå›žã¯Dimitri KRAVTCHUKæ°ã®æ¤œè¨¼çµæžœã‚’ã”紹介ã—ãŸã„ã¨æ€ã„ã¾ã™ã€‚Dimitriæ°ã¯Sun Microsystemsã®ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ã§ã€db_STRESSã¨ã„ã†ãƒ™ãƒ³ãƒãƒžãƒ¼ã‚¯ãƒ„ールã®ä½œè€…ã§ã‚‚ã‚ã‚Šã¾ã™ã€‚Dimitriæ°ã®ã‚¦ã‚§ãƒ–サイトã«ã¯db_STRESSを用ã„ãŸãƒ™ãƒ³ãƒãƒžãƒ¼ã‚¯çµæžœãŒå¤šæ•°æŽ²è¼‰ã•ã‚Œã¦ã„ã¾ã™ã€‚
一番分ã‹ã‚Šã‚„ã™ã„ã¨ã“ã‚ã§ã€2009å¹´4月ã®MySQL Performance: MySQL-5.4.0 and other InnoDB engines @dbSTRESS Benchmarkã‹ã‚‰Full Benchmark Result Listã®Read-Only resultsã€Avg-TPSã®çµæžœã‚’引用ã—ã¾ã™ã€‚
3æžšã®ã‚°ãƒ©ãƒ•ã¯CPUコア数を8ã€16ã€32ã¨å¢—ã‚„ã—ã¦ã„ã£ãŸæ§˜åを表ã—ã¦ã„ã¾ã™ã€‚ãã‚Œãžã‚Œã®ã‚°ãƒ©ãƒ•ã«ãŠã„ã¦æ¨ªè»¸ã¯MySQLã«å¯¾ã™ã‚‹åŒæ™‚接続数ã€ç¸¦è»¸ã¯1秒ã‚ãŸã‚Šã®å‡¦ç†ãƒˆãƒ©ãƒ³ã‚¶ã‚¯ã‚·ãƒ§ãƒ³æ•°ã‚’示ã—ã¦ã„ã¾ã™ã€‚グラフã®ç³»åˆ—ãŒãŸãã•ã‚“ã‚ã‚‹ã®ã§ã™ãŒã€ã“ã“ã§ã¯é’ã®ãƒ“ルトインInnoDBã€ãã‚Œã‹ã‚‰èµ¤ã®InnoDB Pluginã«æ³¨ç›®ã—ã¦ãã ã•ã„。
一見ã—ã¦ã€é’ã®ãƒ“ルトインInnoDBãŒã‹ãªã‚Šæ‚ªã„ã“ã¨ãŒåˆ†ã‹ã‚‹ã¨æ€ã„ã¾ã™ã€‚
- åŒæ™‚接続数8をピークã¨ã—ã¦ã€ãれ以上åŒæ™‚接続数を上ã’ã‚‹ã¨æ€§èƒ½ãŒè½ã¡ã¦ã—ã¾ã†
- åŒæ™‚接続数を8ã«å›ºå®šã—ã¦ã‚‚ã€ã‚µãƒ¼ãƒã®CPUã‚’16コアã€32コアã¨å¢—ã‚„ã™ã¨æ€§èƒ½ãŒè½ã¡ã¦ã—ã¾ã†
ã“ã®ã€ŒCPUを増やã›ã°å¢—ã‚„ã™ã»ã©æ€§èƒ½ãŒè½ã¡ã‚‹ã€ã¨ã„ã†ã®ã¯ãªã‹ãªã‹ã«è¡æ’ƒçš„ã ã¨æ€ã„ã¾ã™ã€‚ç¾å®Ÿã¯ã‚¢ãƒ ダールã®æ³•å‰‡ã‚ˆã‚Šã‚‚厳ã—ã„ã®ã§ã™ã€‚ã‚ã¾ã‚Šã«è¡æ’ƒçš„ãªã®ã§ã€InnoDB PluginãŒæ£å¼ãƒªãƒªãƒ¼ã‚¹ã•ã‚Œã‚‹ã¾ã§ã“ã®ãƒ–ãƒã‚°ã§ã¯ã‚ãˆã¦ç´¹ä»‹ã—ã¾ã›ã‚“ã§ã—ãŸã€‚よã†ã‚„ã解ç¦ã§ã™ã€‚
一方ã€èµ¤ã®InnoDB Pluginã§ã¯æ€§èƒ½ãŒå¤§ãã改善ã•ã‚Œã¦ã„ã¾ã™ã€‚å°‘ãªãã¨ã‚‚8コアã¾ã§ã¯å®‰å¿ƒã—ã¦ä½¿ãˆã¾ã™ã—ã€åŒæ™‚接続数やinnodb_thread_concurrencyã‚’ãã£ã¡ã‚Šãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã™ã‚Œã°16コアã§ã‚‚å•é¡Œãªãé‹ç”¨ã§ãã‚‹ã¨æ€ã„ã¾ã™ã€‚
今年ã¯Intelã‹ã‚‰6コアãŠã‚ˆã³8コアã€AMDã‹ã‚‰ã¯12コアã®CPUãŒç™ºè¡¨ã•ã‚Œã¦ã„ã¾ã™ã€‚ã“ã†ã—ãŸCPUãŒçœŸã®å®ŸåŠ›ã‚’発æ®ã™ã‚‹ã«ã¯ã€InnoDB Pluginã®åˆ©ç”¨ãŒä¸å¯æ¬ ã¨è¨€ãˆã‚‹ã®ã§ã¯ãªã„ã§ã—ょã†ã‹ã€‚
- パフォーマンスã¨çœé›»åŠ›åŒ–ã§é€²åŒ–ã—ãŸXeon 5600ç•ªå° - Enterprise Watch (6コア)
- x86サーãƒãƒ¼ã§ãƒ¡ã‚¤ãƒ³ãƒ•ãƒ¬ãƒ¼ãƒ 並ã¿ã®ä¿¡é ¼æ€§ã‚’実ç¾ã™ã‚‹Xeon 7500ç•ªå° - Enterprise Watch (8コア)
- 2〜4ソケットサーãƒãƒ¼ã§ä½¿ãˆã‚‹ä½Žä¾¡æ ¼ãƒ»12コアã®ã€ŒOpteron 6100〠- Enterprise Watch (12コア)
ãŠã¾ã‘
MySQL 5.1.46ã‹ã‚‰ã€mysqlコマンドラインクライアント起動時ã®ãƒ¡ãƒƒã‚»ãƒ¼ã‚¸ãŒå¤‰æ›´ã•ã‚Œã¾ã—ãŸã€‚
MySQL 5.1.45
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.45-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
MySQL 5.1.46
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 155 Server version: 5.1.46-community-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Oracleã•ã‚“…