Ultimate Guide to Improving MySQL Query Performance
MySQL is certainly a powerful open source database management system, but even the most robust engine struggles when queries take an eternity to execute. For DBAs and developers, improving MySQL query performance is an ongoing goal. Efficient query performance is crucial for ensuring the smooth operation and optimal user experience of applications powered by MySQL databases. When businesses rely h
Re: Configuring sql.DB for Better Performance : DSAS開発者ã®éƒ¨å±‹
Configuring sql.DB for Better Performance ã¨ã„ã†è¨˜äº‹ã‚’知りã¾ã—ãŸã€‚ コãƒã‚¯ã‚·ãƒ§ãƒ³ãƒ—ールã®å¤§ãã•ã‚’制御ã™ã‚‹3ã¤ã®è¨å®šã‚’ä¸å¯§ã«è§£èª¬ã•ã‚ŒãŸã¨ã¦ã‚‚良ã„記事ã§ã™ã€‚ ã—ã‹ã—ã€ã“ã®è¨˜äº‹ã§æŽ¨å¥¨ã•ã‚Œã¦ã„ã‚‹è¨å®šã«ã¤ã„ã¦ã¯åŒæ„ã™ã‚‹ã“ã¨ãŒã§ãã¾ã›ã‚“。ç§ãŒæŽ¨å¥¨ã™ã‚‹è¨å®šã¨ãã®ç†ç”±ã‚’解説ã—ã¦ã„ããŸã„ã¨æ€ã„ã¾ã™ã€‚ Limit ConnMaxLifetime instead of MaxIdleConns Allowing just 1 idle connection to be retained and reused makes a massive difference to this particular benchmark — it cuts the average runtime by about 8 times and reduces memory usage by ab
MySQLã§ãƒ—ライマリã‚ーをUUIDã«ã™ã‚‹å‰ã«çŸ¥ã£ã¦ãŠã„ã¦æ¬²ã—ã„ã“㨠| Raccoon Tech Blog [æ ªå¼ä¼šç¤¾ãƒ©ã‚¯ãƒ¼ãƒ³ãƒ›ãƒ¼ãƒ«ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ 技術戦略部ブãƒã‚°]
æ ªå¼ä¼šç¤¾ãƒ©ã‚¯ãƒ¼ãƒ³ãƒ›ãƒ¼ãƒ«ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ã®ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢/デザイナーã‹ã‚‰æŠ€è¡“æƒ…å ±ã‚’ã¯ã˜ã‚ã€ä¸–ã®ä¸ã®ãŸã‚ã«ãªã‚‹ã“ã¨ã‚„社内ã®ã“ã¨ãªã©ã‚’発信ã—ã¦ã¾ã™ã€‚ bashパフォーマンスMySQLInnoDBDBè¨è¨ˆã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ ã“ã‚“ã«ã¡ã¯ã€ç¾½å±±ã§ã™ã€‚ 今回㯠MySQL ã®ãƒ—ライマリã‚ー㫠UUID を採用ã™ã‚‹å ´åˆã«èµ·ãるパフォーマンスã®å•é¡Œã‚’仕組ã¿ã‹ã‚‰è§£èª¬ã—ã¾ã™ã€‚ MySQL(InnoDB) & UUID ã®ãƒ‘フォーマンスã«ã¤ã„ã¦ã¯å„所ã§ã•ã‚“ã–ã‚“è°è«–・検証ã•ã‚Œã¦ã„ã¾ã™ãŒã€è«–ç†çš„ã«è§£èª¬ã—ãŸè¨˜äº‹ãŒå°‘ãªã‹ã£ãŸã‚Šä¸€éƒ¨ã«ã¯èª¤è§£ã‚’æ‹›ãよã†ãªã‚‚ã®ã‚‚ã‚ã‚‹ãŸã‚ã€ã—ã£ã‹ã‚Šã¨ç†ç”±ã‹ã‚‰ç†è§£ã™ã‚‹ãŸã‚ã®æƒ…å ±ã¨ã—ã¦å½¹ç«‹ã¤ã“ã¨ãŒã§ãã‚Œã°ã¨æ€ã£ã¦ã„ã¾ã™ã€‚ UUID ã¨æ¯”較ã•ã‚Œã‚‹å¤ã良ãæ˜‡é †/é™é †ã®ãƒ—ライマリã‚ーã¯ã¨ã„ã†ã¨ã€ MySQL ã® InnoDB ã«ãŠã„ã¦è‰¯ã„パフォーマンスを出ã™ãŸã‚ã«ç¸ã®ä¸‹ã®åŠ›æŒã¡ã®ã‚ˆã†ãªåƒãã‚’ã—ã¦ãã‚Œã¦ã„るケースãŒå®Ÿã¯å°‘ãª
![MySQLã§ãƒ—ライマリã‚ーをUUIDã«ã™ã‚‹å‰ã«çŸ¥ã£ã¦ãŠã„ã¦æ¬²ã—ã„ã“㨠| Raccoon Tech Blog [æ ªå¼ä¼šç¤¾ãƒ©ã‚¯ãƒ¼ãƒ³ãƒ›ãƒ¼ãƒ«ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ 技術戦略部ブãƒã‚°]](https://cdn-ak-scissors.b.st-hatena.com/image/square/5e9d48c3c6f38c2d5f71897d98efcb251289d1f9/height=288;version=1;width=512/https%3A%2F%2Ftechblog.raccoon.ne.jp%2Fwp-content%2Fuploads%2F2021%2F07%2Fboard-755792_800.jpg)
MySQL ã§ä½¿ç”¨ã™ã‚‹ãƒ¡ãƒ¢ãƒªã‚µã‚¤ã‚ºã®è¦‹ç©ã‚‚ã‚Š - å…ƒRX-7ä¹—ã‚Šã®é©å½“ãªæ—¥ã€…
最近ã€MySQLã®ãƒ‘ラメータã®èª¿æ•´ã‚’ã™ã‚‹æ©Ÿä¼šãŒã‚ã£ãŸã®ã§ã™ãŒã€ç‰¹å®šã®ãƒ‘ラメータを変更ã—ãŸéš›ã«ã€ãƒ¡ãƒ¢ãƒªã®æ¶ˆè²»é‡ã«ã©ã†å½±éŸ¿ã™ã‚‹ã®ã‹ã€ã¨ã„ã†ã®ã‚’調査ã™ã‚‹éš›ã«ã€ã‚¤ãƒ³ã‚¿ãƒ¼ãƒãƒƒãƒ„を彷徨ã£ãŸã¨ã“ã‚ã€ã‚µã‚¤ãƒˆã«ã‚ˆã£ã¦æ›¸ã„ã¦ã‚ã‚‹ã“ã¨ã«ãƒãƒ©ã¤ããŒã‚ã£ãŸã®ã§ã€è‡ªåˆ†ã§ã‚‚ã¾ã¨ã‚ã¦ã¿ã‚‹ã“ã¨ã«ã—ãŸã€‚ çµè«–ã‹ã‚‰æ›¸ãã¨ã€å‚考ã«ã—ãŸã®ã¯ä»¥ä¸‹ã®ã‚ªãƒ©ã‚¤ãƒªãƒ¼ã®æ›¸ç±ã€ŒMySQLトラブルシューティングã€ã§ã€è¨˜è¿°ãŒä¸€ç•ªã‚ã‹ã‚Šã‚„ã™ã書ã‹ã‚Œã¦ã„ãŸã€‚ ã“ã®ã‚¨ãƒ³ãƒˆãƒªã¯ã€ã“ã®æ›¸ç±ã® 「3.9.3 オプションã®å®‰å…¨å€¤ã‚’計算ã™ã‚‹ã€ ã«ã¦è¨˜è¼‰ãŒã‚る内容をã¾ã¨ã‚ãŸã‚‚ã®ã«ãªã‚‹ã€‚ MySQLトラブルシューティング 作者:Sveta SmirnovaオライリージャパンAmazon 著者ã«ã¤ã„㦠Sveta Smirnova(スヴェータ・スミルノヴァ): Oracle社MySQLサãƒãƒ¼ãƒˆã‚°ãƒ«ãƒ¼ãƒ—・ãƒã‚°æ¤œè¨¼ã‚°ãƒ«ãƒ¼ãƒ—ã®ä¸»å¸ãƒ†ã‚¯ãƒ‹ã‚«ãƒ«ã‚µãƒãƒ¼ãƒˆã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ã¨ã—ã¦æ¯Žæ—¥MySQ
Googleã€ORMãŒç”Ÿæˆã™ã‚‹SQLãŒé…ã„ã¨ãã®èª¿æŸ»ã‚’容易ã«ã™ã‚‹ã€Œsqlcommenterã€ã‚’オープンソースã§å…¬é–‹ã€‚Railsã€Springã€Djangoãªã©ä¸»è¦ãªãƒ•ãƒ¬ãƒ¼ãƒ ワークã«å¯¾å¿œ
Googleã€ORMãŒç”Ÿæˆã™ã‚‹SQLãŒé…ã„ã¨ãã®èª¿æŸ»ã‚’容易ã«ã™ã‚‹ã€Œsqlcommenterã€ã‚’オープンソースã§å…¬é–‹ã€‚Railsã€Springã€Djangoãªã©ä¸»è¦ãªãƒ•ãƒ¬ãƒ¼ãƒ ワークã«å¯¾å¿œ SQL文を直接書ã‹ãªãã¨ã‚‚ã€è‡ªå‹•çš„ã«SQL文を生æˆã€å®Ÿè¡Œã—ã¦ãれるORM（Object-Relational Mapper）ã¯ã€ãƒ—ãƒã‚°ãƒ©ãƒŸãƒ³ã‚°ã‚’容易ã«ã—ã¦ãれる技術ã¨ã—ã¦Railsã‚„Hibernateã€Springãªã©ã•ã¾ã–ã¾ãªãƒ•ãƒ¬ãƒ¼ãƒ ワークãªã©ã§æ´»ç”¨ã•ã‚Œã¦ã„ã¾ã™ã€‚ 一方ã§ã€ORMãŒç”Ÿæˆã™ã‚‹SQLæ–‡ã¯ã¨ãã«è¤‡é›‘ã«ã€ã‚ã‚‹ã„ã¯éžåŠ¹çŽ‡ãªã‚‚ã®ã¨ãªã‚Šã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å‡¦ç†ã®é…ã•ã«ã¤ãªãŒã‚‹ã“ã¨ã‚‚ã‚ã‚Šã¾ã™ã€‚ ã“ã®ã¨ãã€SQLæ–‡ã®ç”Ÿæˆã¨å®Ÿè¡Œã‚’明示的ã«ã‚³ãƒ¼ãƒ‰ã¨ã—ã¦è¨˜è¿°ã™ã‚‹å¿…è¦ãŒãªã„ã¨ã„ã†ORMã®ç‰¹å¾´ãŒã€ãªãœãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å‡¦ç†ãŒé…ããªã£ãŸã®ã‹ã€ã©ã®ã‚ˆã†ãªSQLæ–‡ãŒç”Ÿæˆã•ã‚Œã€ãã®ã©ã“ã«åŽŸå› ãŒã‚ã‚‹ã®ã‹ã€ã¨ã„ã£ãŸèª¿æŸ»ã‚’難ã—ãã¦ã„ã‚‹é¢ãŒã‚ã‚Š
MySQL ã®ãƒ¬ãƒ—リケーションã‹ã‚‰10年間逃ã’ã¦ããŸæˆ‘々ãŒå¦ã‚“ã ã“ã¨8é¸ - Cybozu Inside Out | サイボウズエンジニアã®ãƒ–ãƒã‚°
ã“ã‚“ã«ã¡ã¯ã€‚クラウドé‹ç”¨ãƒãƒ¼ãƒ 㧠SRE ã‚’ã—ã¦ã„る飯塚ã§ã™ã€‚ 今回ã¯ã€MySQL ã®ãƒ¬ãƒ—リケーション機能を約10å¹´ã‚‚ã®é–“ãšã£ã¨ä½¿ã£ã¦ã“ãªã‹ã£ãŸç§ãŸã¡ãŒã€ãƒ¬ãƒ—リケーションを使ã£ãŸé«˜å¯ç”¨æ€§æ§‹æˆã«ç§»è¡Œã™ã‚‹ãŸã‚ã®å–り組ã¿ã®ä¸ã§å¦ã‚“ã ã“ã¨ã«ã¤ã„ã¦ç´¹ä»‹ã—ã¾ã™ã€‚ 背景 巨大ãªãƒ†ãƒ¼ãƒ–ルã¸ã® primary key ã®ä»˜ä¸Ž トランザクションサイズãŒå¤§ãã„å ´åˆã«ã¯ tmpdir ã«æ³¨æ„ mysqldump ã§çµµæ–‡å—ãŒæ¶ˆãˆã¦ã„ãªã„ã‹è¦ãƒã‚§ãƒƒã‚¯ mysqldump ㌠Error 1412: Table definition has changed... ã§å¤±æ•—ã™ã‚‹ mysqldump ã—ãŸãƒ‡ãƒ¼ã‚¿ã®ãƒªã‚¹ãƒˆã‚¢ãŒ Duplicate entry 'xxx-yyy-PRIMARY-n_diff_pfx01' for key 'PRIMARY' ã§å¤±æ•—ã™ã‚‹ã“ã¨ãŒã‚ã‚‹ mysqldump ã—ãŸãƒ‡ãƒ¼ã‚¿ã®ãƒªã‚¹ãƒˆã‚¢æ™‚ã®ãƒ‡ã‚£ã‚¹ã‚¯
ç†å±ˆã§è€ƒãˆã‚‹ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚° | Raccoon Tech Blog [æ ªå¼ä¼šç¤¾ãƒ©ã‚¯ãƒ¼ãƒ³ãƒ›ãƒ¼ãƒ«ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ 技術戦略部ブãƒã‚°]
æ ªå¼ä¼šç¤¾ãƒ©ã‚¯ãƒ¼ãƒ³ãƒ›ãƒ¼ãƒ«ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ã®ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢/デザイナーã‹ã‚‰æŠ€è¡“æƒ…å ±ã‚’ã¯ã˜ã‚ã€ä¸–ã®ä¸ã®ãŸã‚ã«ãªã‚‹ã“ã¨ã‚„社内ã®ã“ã¨ãªã©ã‚’発信ã—ã¦ã¾ã™ã€‚ パフォーマンス勉強会OracleデータベースMySQLInnoDB ã“ã‚“ã«ã¡ã¯ã€ç¾½å±±ã§ã™ã€‚今回ã¯Oracleデータベースã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã§å°‘ã—è¸ã¿è¾¼ã‚“ã 内容ã§ã™ã€‚途ä¸ã§æ¯”較対象ã¨ã—ã¦MySQLã‚‚ç™»å ´ã—ã¾ã™ã€‚ æ—¥é ƒã‹ã‚‰SQLãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã®æ©Ÿä¼šãŒã‚ã£ã¦ãã‚Œãªã‚Šã«å¾—æ„ã¨ã—ã¦ã„ã‚‹ã®ã«ã€ãã‚Œã§ã‚‚ãªãœã‹ãƒ‘フォーマンスãŒã§ãªã„SQLã«æ‚©ã‚“ã 経験ã¯ã‚ã‚Šã¾ã›ã‚“ã‹ï¼Ÿ 謎ã®é…ã„ç¾è±¡ã¯ç‰¹ã«å¤§è¦æ¨¡ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã«ãªã£ã¦ãã‚‹ã¨ç™ºç”Ÿã—ãŒã¡ãªã®ã§ã™ãŒã€é€Ÿã„å ´åˆã‚‚é…ã„å ´åˆã‚‚å¿…ãšç†ç”±ãŒã‚ã‚Šã¾ã™ã€‚ãã“ã§æœ¬è¨˜äº‹ã§ã¯ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã«ãŠã„ã¦æ„外ã¨è¦‹è½ã¨ã•ã‚ŒãŒã¡ãªãƒãƒ¼ãƒ¬ãƒ™ãƒ«ãªéƒ¨åˆ†ã«ç€ç›®ã—ã¦ã€ã•ã‚‰ã«ä¸€æ©ä¸Šã®ãƒ‘フォーマンスãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã«å¿…è¦ãªçŸ¥è˜ã‚’解説ã—ã¾ã™ã€‚ ã“ã®è¨˜äº‹ã‚’書ããã£ã‹ã‘ã¨ãªã£ãŸã®ã¯ç§ãŸ
![ç†å±ˆã§è€ƒãˆã‚‹ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚° | Raccoon Tech Blog [æ ªå¼ä¼šç¤¾ãƒ©ã‚¯ãƒ¼ãƒ³ãƒ›ãƒ¼ãƒ«ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ 技術戦略部ブãƒã‚°]](https://cdn-ak-scissors.b.st-hatena.com/image/square/2163d340456cff9037bacb3b1e5b6e672b06095e/height=288;version=1;width=512/https%3A%2F%2Ftechblog.raccoon.ne.jp%2Fwp-content%2Fuploads%2F2020%2F10%2Fdb_performance_slides-thumb.jpg)
雑ãªMySQLパフォーマンスãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°
26. EXPLAIN mysql> EXPLAIN SELECT * FROM table_1 a JOIN `table_2` s ON a.user_id=s.`user_id` AND s.site_i d=120 WHERE app_id=8250G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,ix_table_1,ix2_table_2,ix3_table_1,idx_table_1_06,idx_table_1_07,idx_t able_1_09 key: idx_table_1_06 key_len: 4 ref: const rows: 13496 Ext
1
リリースã€éšœå®³æƒ…å ±ãªã©ã®ã‚µãƒ¼ãƒ“スã®ãŠçŸ¥ã‚‰ã›
最新ã®äººæ°—エントリーã®é…ä¿¡
j次ã®ãƒ–ックマーク
kå‰ã®ãƒ–ックマーク
lã‚ã¨ã§èªã‚€
eコメント一覧を開ã
oページを開ã
{{#tags}}- {{label}}
{{/tags}}