MySQL/チューニング のバックアップ(No.1)

[ トップ ]   [ 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ | ログイン ]

• バックアップ一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• MySQL/チューニング へ行く。
  • 1 (2017-04-05 (水) 12:22:39)
  • 2 (2022-10-25 (火) 10:08:53)
  • 3 (2022-10-25 (火) 12:13:34)
  • 4 (2022-10-25 (火) 14:38:49)
  • 5 (2022-10-26 (水) 11:30:13)
  • 6 (2022-11-15 (火) 10:52:10)
  • 7 (2022-12-05 (月) 13:07:17)
  • 8 (2022-12-06 (火) 10:17:08)
  • 9 (2023-02-06 (月) 18:44:07)
  • 10 (2023-02-06 (月) 18:51:52)
  • 11 (2023-02-07 (火) 09:02:49)

---

• MySQL?∫?ｃ??iki?吾?????潟?
  • MySQL
  • MySQL/5.5
  • MySQL/InnoDB
  • MySQL/?????
  • MySQL/?????鴻?帥???潟?
  • MySQL/?泣?若??腱糸??
  • MySQL/???ャ?若???潟?
  • MySQL/???若?吾?с?
  • MySQL/???????≪????
  • MySQL/???????＜?ゃ???潟?
  • MySQL/???潟?????若?
  • MySQL/???????宴?若?激?с?
  • MySQL/?????
  • MySQL/?狗??

---

• MySQL 5.1 ?????＜???潟?鴻?????ャ?≪? 6.??????
• ??????ySQL??蕭?????????7?ゃ???号?
• Sundry MySQL Scripts and Docs
  • MySQL Performance Tuning Primer Script ... my.cnf??Information Schema?????宴????荐?????≪?????ゃ?鴻????????????鴻????????

?????????? †

• MySQL??XPLAIN??緇劫?茹ｈ?!!
• show profile?с???с?若?冴???????茵???????荀???

?泣?若?????? †

• MySQL?????ャ?若???潟?逸?

荐???ゃ??腆肴? †

mysql> show variables;

腟沿????宴?????? †

• 絎?????????怨??????鴻?鴻??http://www.thinkit.co.jp/free/article/0707/2/1/

  mysql> show global status; (5.0.2篁ヤ????翫??)
  mysql> show status; (5.0.2??????翫??)

• ???若?????????潟?若???違?????若?帥?泣?ゃ?冴???ゃ?潟???????鴻?泣?ゃ?冴??????茯炊?

  select table_name, engine,
  table_rows as tbl_rows,
  avg_row_length as rlen,
  floor((data_length+index_length)/1024/1024) as allMB,
  floor((data_length)/1024/1024) as dMB,
  floor((index_length)/1024/1024) as iMB
  from information_schema.tables
  where table_schema=database()
  order by (data_length+index_length) desc;

  • [[INFORMATION_SCHEMA ???若?帥???若??http://dev.mysql.com/doc/refman/5.1/ja/information-schema.html]]

?鴻???若?祉???????若?????違?????с??? †

?ョ??倶???∈茯? †

$ mysqladmin -p extended-status | grep -E 'Max|Threads'
Enter password:
| Max_used_connections              | 51             |
| Threads_cached                    | 4              |
| Threads_connected                 | 21             |
| Threads_created                   | 18015          |
| Threads_running                   | 11             |

???若?帥?泣?ゃ?冴??????＜?≪?????????? †

• ?????若?帥?泣?ゃ?削??＜?≪????????綽???????
  • ???ｃ?鴻?????＜?≪??????炊?吾????綺???00筝?????勲??????
  • B?????若?ゃ?潟???????鴻??｢膣≪?????若?????鴻???ｃ?恰????ぇ????激?若?????榊?????????????ｃ?鴻????с?????????＜?≪????с???????с????????勲???冴??
  • MySQL??S?????＜?ゃ?????ｃ???激?ャ???若?????????????т???????????
  • OS?????＜?ゃ?????ｃ???激?ャ??MySQL絨????????????＜?????＜???с??????
  • ???若?帥?????????＜?≪??????障???????翫???????ゃ?潟???????鴻?????с???＜?≪??????障????????????

???若????┃荐???SQL????眼?? †

• ?????若?????若?????堺??篁ｃ??QL???ャ?若???潟?医?榊?鐚?鐚?鐚?EXPLAIN??http://mtl.recruit.co.jp/blog/2011/02/sql.html
• ?????若?????若?????堺??篁ｃ??QL???ャ?若???潟?医?榊?鐚?鐚?鐚?SQL?????ｃ?＜????????膩?http://mtl.recruit.co.jp/blog/2011/02/sqlsql1.html
• ?????若?????若?????堺??篁ｃ??QL???ャ?若???潟?医?榊?鐚?鐚?鐚?SQL?????ｃ?＜??????緇?膩?http://mtl.recruit.co.jp/blog/2011/02/sqlsql.html

• MySQL???ゃ?潟???????鴻???狗????絵?????
  • WHERE?ャ??RDER BY?ャ???????с?????ゃ?潟???????鴻????ゃ?障?
  • 荅括完??[SELECT?鴻???若???＜?潟???????潟?????????????????????:http://dev.mysql.com/doc/refman/5.1/ja/query-speed.html]]?????с?????
• ?＞散???????絎??????????ч??荐???綽?荀????????篁倶?違????????????若??????????????菴遵????????????????????荀?????????????
• ?≪???????剛信???????若?????贋?井????幻??申???????????????ゃ???贋?違??????????????

???ャ?若???潟? †

篏睡???＜?≪?????? †

• MySQL?泣?若????羔?莢祉?????＜?≪??http://www.mysqlpracticewiki.com/index.php/MySQL%e3%82%b5%e3%83%bc%e3%83%90%e3%81%8c%e6%b6%88%e8%b2%bb%e3%81%99%e3%82%8b%e3%83%a1%e3%83%a2%e3%83%aa

???＜?ゃ?????若???恰?違???? †

蕭?莢??激????????札筝????潟???潟????絎?茵?

$ mysqladmin -u root -p extended-status | grep Open
Enter password: 
| Open_files                        | 515        |
| Open_streams                      | 0          |
| Open_tables                       | 256        |
| Opened_tables                     | 45281      |

Open_files, Open_tables???違??紊????翫??? /etc/my.cnf ??札筝???茯炊?眼??open_files???ゃ????鴻??able_cache???ゃ??紊с????????????Error in accept: Too many open files???????若???榊??????MySQL??罘??巡??????ャ?????ц?羈????

[mysqld]
open_files=2048
table_cache=512

筝?荐????違??紜????????????????若?吟?????????＜?ゃ?????ｃ?鴻???????炊?違??????羈????綽?荀?????紜???????

• ???＜?ゃ?????ｃ?鴻???????帥????紊ф?違??紜?????

????????

• max_connections??hread_cache?????ャ?若???潟?違??茵???

I/O????? †

• ???若?帥???ｃ?????????????
  • ???ｃ?????????????????若???ｃ?激?с?潟???????潟??????
  • ???ｃ???????????激?潟???????????潟???????????若?帥?????????若???ｃ?激?с?潟??舟?

• MyISAM???＜?ゃ???с???????若?帥???＜?ゃ??MYD)???ゃ?潟???????鴻???＜?ゃ??MYI)??臀?????ｃ????????????絎??с????

InnoDB?劫?? †

• ?????若??????InnoDB??
  • InnoDB??yISAM???ｃ?＜?????????
    • InnoDB vs MyISAM vs Falcon benchmarks
  • [[緇劫?罸?莠?!! MySQL???潟?吾??http://www.thinkit.co.jp/free/article/0608/1/6/]]
  • MyISAM????InnoDB?後?????帥??????????絵???http://nippondanji.blogspot.com/2009/02/myisaminnodb.html

• MySQL?????＜???潟?鴻?????ャ?≪? 13.5.3.1. Per-Table ???若?????鴻???若?鴻??????????
  • innodb_file_per_table?????激?с?潟??篏睡????????1 InnoDB???若?????1???＜?ゃ?????????????潟?????激?潟???????????潟???у???ｃ????????
  • InnoDB ?????掩?????若?????鴻???若?刻?ibdata1 etc鐚??????????若?帥???ｃ?????????????羔??????違??臀?????с?????ゃ????????綽?荀?????????.ibd ???＜?ゃ???????с? InnoDB ????篏?????????
  • innodb_file_per_table??????bdata1????????????

• MySQL?????＜???潟?鴻?????ャ?≪? 7.5.13.3. InnoDB ???若?????????????違?＜?潟????
  • ?≦宍????膓????? InnoDB ?????若?帥???＜?ゃ?????泣?ゃ?冴??絨????????号?

my.cnf †

MyISAM/InnoDB?演?? †

• query_cache_size
  • ?????????茵?腟????????ｃ???激?ャ??????Query?????ｃ???激?ャ???????翫????QL??絎?茵??????????ｃ???激?ャ????絎鴻??菴??????с???泣?ゃ?冴??ぇ?????鴻????????
  • 荅峨????????若????????絎鴻??紊??眼???????翫???????????ｃ???激?ャ????????????с?????ｃ???激?ャ??????筝??桁????????????????????

MyISAM?∫? †

• key_buffer_size
  • MyISAM?????若?????ｃ???激?ャ???????????＜???泣?ゃ?
  • MyISAM???帥??ySQL絨????泣?若??????RAM??/4腮?綺??蚊??綵???????????

• table_open_cache
  • MySQL ?с?????若?????????若???潟???????若?冴???号?
  • ?ョ??違??200???翫??????篏 200 ? N ?????若???????ｃ???激?ャ?泣?ゃ?冴??綽?荀?

• max_connections
  • ?泣?若????荐怨???????紊у?????ョ??

InnoDB?∫? †

• innodb_file_per_table
  • ???若??????篏?? .ibd ???＜?ゃ????篏???
  • 綽???鐚?鐚???絎????????
• innodb_buffer_pool_size
  • InnoDB ???若?????????若?帥???ゃ?潟???????鴻??篆?絖??????????????????＜??ぇ????
  • MySQL????nnoDB絨????泣?若??????RAM? 70-80% 篏??障?т???????????????荐??????????????絎????????????純?＜???????????????с??50%???????????
• innodb_log_file_size
  • ???若?帥???吾??莨若?帥??緇???ぇ?????泣?ゃ?冴?????若?帥???吾??莨若?炊?ц?純??襲?帥????
  • 紊с?????泣?ゃ?冴?祉?????若?帥????????????????荀?????
  • ?泣?ゃ?冴??紊??眼?????翫????罩ｅ幻??罩√???篏???羝??帥?????違???＜?ゃ????????????? /var/lib/mysql/ib_logfile**)?????ゃ????mysql?泣?若????莎桁??????????綽?荀?????
• innodb_flush_method
  • [[MySQL innodb_flush_method = O_DIRECT???荐?http://d.hatena.ne.jp/sh2/20101205]]
  • ??????????鐚?fdatasync鐚??ц???????ф????絎???K

???若?帥???＜?ゃ?????? †

• ?≦宍????膓????? InnoDB ?????若?帥???＜?ゃ?????泣?ゃ?冴??絨????????号?
• [[??MySQL??????????膃?2?? MySQL Enterprise ?????????若?吾?с?潟?≪?????????若?帥???若?鴻???＜?ゃ???????泣?ゃ??http://www.itproexpo.jp/article/COLUMN/20061216/257101/?P=1&ST=lin-server]]

???ャ?若???潟?違?????? †

• MySQL???ャ?若???潟??http://www.inter-office.co.jp/contents/category/mysql-tuning/
• MySQL ?泣?若???若??茯炊?眼????
• MySQL?＜???ｃ???膣????ャ?若???潟??http://www.s-style.co.jp/about_mysql/tech_info/tuning/

• ?????障??ySQL??篏帥??絨純???!!

• ??????????ySQL???ャ?若???潟?
  • 膃????鐚?key_buffer_size???????????????????若???潟?号?莠?
  • 膃????鐚?query_cache_size???????????????????若???潟?号?莠?

• MySQL???????＜?若?帥???ャ?若???潟??http://shoulder.jp/archives/007585.php

• MySQL鐚??ゃ?潟???????鴻?障?????＜??http://www.res-system.com/item/550
• MySQL????????
• [[??MySQL??????????膃??? MySQL???ャ?若???潟?違????????????http://itpro.nikkeibp.co.jp/members/ITPro/oss/20040624/146333/?ST=oss]]

• MySQL?????????

• MySQL????????
  • ?泣?若???????＜?若?帥?????ャ?若???潟??http://dev.mysql.com/doc/refman/5.1/ja/server-parameters.html
  • [[MySQL ?с???＜?≪????戎??http://dev.mysql.com/doc/refman/5.1/ja/memory-use.html]]
  • ?激?鴻????紊???http://dev.mysql.com/doc/refman/5.1/ja/server-system-variables.html
  • ?????激?鴻????紊???http://dev.mysql.com/doc/refman/5.1/ja/dynamic-system-variables.html
• Default value of tmp_table_size is meaningless
• MySQL?泣?若???鴻???若?帥??http://www.limy.org/program/db/mysql/mysql_status.html

• MySQL????????
• ?泣?若???取┃絎???荀??眼????ySQL???ц?純??綣????冴??
• MySQL?????ャ?若???潟??http://allabout.co.jp/internet/database/closeup/CU20040722A/
• 5???с?с??????MySQL???＜?≪???≫??????ャ?若???潟?逸?
• MySQL???ャ?若???潟??http://d.hatena.ne.jp/tolerance/20070322

• MySQL???????若???潟?鴻?祉???ャ?若???潟??http://www.inter-office.co.jp/contents/category/mysql-tuning/
• MySQL????????????????????10??ips
• ???＜???ｃ??????? ????ySQL????????????????????10??ips
• MySQL Performance Blog
  • MySQL Performance Presentations
• Top 84 MySQL Performance Tips

• MySQL Performance Tuning Key
  • [MySQL Performance Tuning Primer:http://day32.com/MySQL/]]

• [[[MySQL] ???????若???潟?拷?∫?ｃ?＜??http://w.koshigoe.jp/study/?%5BMySQL%5D+%A5%D1%A5%D5%A5%A9%A1%BC%A5%DE%A5%F3%A5%B9%B4%D8%CF%A2%A5%E1%A5%E2#l31]]
• 罧??????????鴻??蕋??違?????????宴?????潟?????若????????????鐚?鐚?

???若?????х軒 †

myisampack †

• MyISAM??YD???＜?ゃ?????х軒
• ?????＜?ゃ????0??70%

• ???若????????????

???若? †

Maatkit †

• MOONGIFT: >> MySQL???綺?????????若????Maatkit??

mytop †

MySQL Activity Report †

• ?????若??箴?

mysqlreport †

茲??医?茵? †

• ???????激?割??с???? MySQL ?泣?若?????茵?

     

Site admin: anonymous

PukiWiki 1.5.1 © 2001-2016 PukiWiki Development Team. Powered by PHP 5.6.99-hhvm. HTML convert time: 0.025 sec.