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

追加された行はこの色です。
削除された行はこの色です。
-MySQL?∫?ｃ??iki?吾?????潟?
--[[MySQL:http://wiki.gashimax.com/index.php?MySQL]]
--MySQL/5.5
--MySQL/InnoDB
--MySQL/?????
--MySQL/?????鴻?帥???潟?
--MySQL/?泣?若??腱糸??
--MySQL/???ャ?若???潟?
--MySQL/???若?吾?с?
--MySQL/???????≪????
--MySQL/???????＜?ゃ???潟?
--MySQL/???潟?????若?
--MySQL/???????宴?若?激?с?
--MySQL/?????
--MySQL/?狗??
----
#author("2022-10-25T01:08:53+00:00","default:admin","admin")

-[[MySQL 5.1 ?????＜???潟?鴻?????ャ?≪? 6.??????:http://dev.mysql.com/doc/refman/5.1/ja/optimization.html]]
-[[??????ySQL??蕭?????????7?ゃ???号?:http://nippondanji.blogspot.com/2009/03/mysql7.html]]
-[[Sundry MySQL Scripts and Docs:http://www.day32.com/MySQL/]]
--MySQL Performance Tuning Primer Script ... my.cnf??Information Schema?????宴????荐?????≪?????ゃ?鴻????????????鴻????????

* ?????????? [#od4ee83b]
-[[MySQL??XPLAIN??緇劫?茹ｈ?!!:http://nippondanji.blogspot.com/2009/03/mysqlexplain.html]]
-[[show profile?с???с?若?冴???????茵???????荀???:http://mysql-casual.org/2010/12/mysql-casual-advent-calendar-2010-12-05.html]]

* ?泣?若?????? [#ac7b00cf]
-[[MySQL?????ャ?若???潟?逸?:http://satospo.sakura.ne.jp/blog_archives/tech/linux_server/mysql4_tune.html]]

** 荐???ゃ??腆肴? [#kcb4a9ad]
 mysql> show variables;

** 腟沿????宴?????? [#cfdc2efc]
-[[絎?????????怨??????鴻?鴻??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]]

** ?鴻???若?祉???????若?????違?????с??? [#d5abb06d]

** ?ョ??倶???∈茯? [#m3f60c2b]
 $ 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             |

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

* ???若????┃荐???SQL????眼?? [#n1bda048]
-[[?????若?????若?????堺??篁ｃ??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]]?????с?????
-?＞散???????絎??????????ч??荐???綽?荀????????篁倶?違????????????若??????????????菴遵????????????????????荀?????????????
-?≪???????剛信???????若?????贋?井????幻??申???????????????ゃ???贋?違??????????????

* ???ャ?若???潟? [#g8f824e8]
** 篏睡???＜?≪?????? [#y1f7cfa2]
-[[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]]

** ???＜?ゃ?????若???恰?違???? [#h9ce150e]
蕭?莢??激????????札筝????潟???潟????絎?茵?
 $ 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

筝?荐????違??紜????????????????若?吟?????????＜?ゃ?????ｃ?鴻???????炊?違??????羈????綽?荀?????紜???????
-[[???＜?ゃ?????ｃ?鴻???????帥????紊ф?違??紜?????:http://www.cafechantant.com/trac/wiki/file_descriptor]]

????????
-[[max_connections??hread_cache?????ャ?若???潟?違??茵???:http://www.thinkit.co.jp/free/article/0707/2/3/]]

** I/O????? [#tab2e74c]
-???若?帥???ｃ?????????????
--???ｃ?????????????????若???ｃ?激?с?潟???????潟??????
--???ｃ???????????激?潟???????????潟???????????若?帥?????????若???ｃ?激?с?潟??舟?

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

* InnoDB?劫?? [#g544d331]
-?????若??????InnoDB??
--[[InnoDB??yISAM???ｃ?＜?????????:http://www.inter-office.co.jp/contents/95]]
---[[InnoDB vs MyISAM vs Falcon benchmarks:http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/]]
--[[緇劫?罸?莠?!! 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 ???若?????鴻???若?鴻??????????:http://dev.mysql.com/doc/refman/5.1/ja/multiple-tablespaces.html]]
--innodb_file_per_table?????激?с?潟??篏睡????????1 InnoDB???若?????1???＜?ゃ?????????????潟?????激?潟???????????潟???у???ｃ????????
--InnoDB ?????掩?????若?????鴻???若?刻?ibdata1 etc鐚??????????若?帥???ｃ?????????????羔??????違??臀?????с?????ゃ????????綽?荀?????????.ibd ???＜?ゃ???????с? InnoDB ????篏?????????
--[[innodb_file_per_table??????bdata1????????????:http://www.mysql.gr.jp/mysqlml/mysql/msg/14219]]

-[[MySQL?????＜???潟?鴻?????ャ?≪? 7.5.13.3. InnoDB ???若?????????????違?＜?潟????:http://dev.mysql.com/doc/refman/5.1/ja/innodb-file-defragmenting.html]]
--[[?≦宍????膓????? InnoDB ?????若?帥???＜?ゃ?????泣?ゃ?冴??絨????????号?:http://cl.pocari.org/2006-07-07-2.html]]

* my.cnf [#tee33efa]
** MyISAM/InnoDB?演?? [#aac8faf6]
-query_cache_size
--?????????茵?腟????????ｃ???激?ャ??????Query?????ｃ???激?ャ???????翫????QL??絎?茵??????????ｃ???激?ャ????絎鴻??菴??????с???泣?ゃ?冴??ぇ?????鴻????????
--荅峨????????若????????絎鴻??紊??眼???????翫???????????ｃ???激?ャ????????????с?????ｃ???激?ャ??????筝??桁????????????????????

** MyISAM?∫? [#m75c4a5a]
-key_buffer_size
--MyISAM?????若?????ｃ???激?ャ???????????＜???泣?ゃ?
--MyISAM???帥??ySQL絨????泣?若??????RAM??/4腮?綺??蚊??綵???????????

-table_open_cache
--[[MySQL ?с?????若?????????若???潟???????若?冴???号?:http://dev.mysql.com/doc/refman/5.1/ja/table-cache.html]]
--?ョ??違??200???翫??????篏 200 ? N ?????若???????ｃ???激?ャ?泣?ゃ?冴??綽?荀?

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

** InnoDB?∫? [#x81a3293]
-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

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

* ???ャ?若???潟?違?????? [#nb1f8bfe]
-[[MySQL???ャ?若???潟??http://www.inter-office.co.jp/contents/category/mysql-tuning/]]
-[[MySQL ?泣?若???若??茯炊?眼????:http://www.ibm.com/developerworks/jp/linux/library/l-tune-lamp-3.html]]
-[[MySQL?＜???ｃ???膣????ャ?若???潟??http://www.s-style.co.jp/about_mysql/tech_info/tuning/]]

-[[?????障??ySQL??篏帥??絨純???!!:http://nippondanji.blogspot.com/2009/05/mysql.html]]

-??????????ySQL???ャ?若???潟?
--[[膃????鐚?key_buffer_size???????????????????若???潟?号?莠?:http://www.thinkit.co.jp/free/article/0707/2/5/]]
--[[膃????鐚?query_cache_size???????????????????若???潟?号?莠?:http://www.thinkit.co.jp/free/article/0707/2/6/]]

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

-[[MySQL鐚??ゃ?潟???????鴻?障?????＜??http://www.res-system.com/item/550]]
-[[MySQL????????:http://slashdot.jp/journal.pl?op=display&uid=4&id=26710]]
-[[??MySQL??????????膃??? MySQL???ャ?若???潟?違????????????http://itpro.nikkeibp.co.jp/members/ITPro/oss/20040624/146333/?ST=oss]]

-[[MySQL?????????:http://www.buena-idea.net/~hironobu/mysql/index.html]]

-[[MySQL????????:http://dev.mysql.com/doc/refman/5.1/ja/optimization.html]]
--[[?泣?若???????＜?若?帥?????ャ?若???潟??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:http://bugs.mysql.com/bug.php?id=18875]]
-[[MySQL?泣?若???鴻???若?帥??http://www.limy.org/program/db/mysql/mysql_status.html]]

-[[MySQL????????:http://slashdot.jp/journal.pl?op=display&uid=4&id=26710]]
-[[?泣?若???取┃絎???荀??眼????ySQL???ц?純??綣????冴??:http://itpro.nikkeibp.co.jp/members/ITPro/oss/20050213/156109/?P=2&ST=oss]]
-[[MySQL?????ャ?若???潟??http://allabout.co.jp/internet/database/closeup/CU20040722A/]]
-[[5???с?с??????MySQL???＜?≪???≫??????ャ?若???潟?逸?:http://dsas.blog.klab.org/archives/50860867.html]]
-[[MySQL???ャ?若???潟??http://d.hatena.ne.jp/tolerance/20070322]]

-[[MySQL???????若???潟?鴻?祉???ャ?若???潟??http://www.inter-office.co.jp/contents/category/mysql-tuning/]]
-[[MySQL????????????????????10??ips:http://www.phppro.jp/news/362]]
-[[???＜???ｃ??????? ????ySQL????????????????????10??ips:http://codezine.jp/a/article/aid/1229.aspx]]
-[[MySQL Performance Blog:http://www.mysqlperformanceblog.com/]]
--[[MySQL Performance Presentations:http://www.mysqlperformanceblog.com/mysql-performance-presentations/]]
-[[Top 84 MySQL Performance Tips:http://www.debianadmin.com/top-84-mysql-performance-tips.html]]

-[[MySQL Performance Tuning Key:http://www.shinguz.ch/MySQL/mysql_performance_tuning_key.html]]
--[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]]
-[[罧??????????鴻??蕋??違?????????宴?????潟?????若????????????鐚?鐚?:http://nippondanji.blogspot.com/2010/08/blog-post_30.html]]

* ???若?????х軒 [#d7eb02cb]
** myisampack [#e8c6e264]
-MyISAM??YD???＜?ゃ?????х軒
-?????＜?ゃ????0??70%

-[[???若????????????:http://linux.mini13i.com/?MySQL%2F%BA%C7%C5%AC%B2%BD%2F%A5%C6%A1%BC%A5%D6%A5%EB]]

* ???若? [#a8cce428]
**[[Maatkit:http://www.maatkit.org/]] [#j695eb4f]
-[[MOONGIFT: >> MySQL???綺?????????若????Maatkit??:http://www.moongift.jp/2008/07/maatkit/]]

**[[mytop:http://jeremy.zawodny.com/mysql/mytop/]] [#l80552fb]

**[[MySQL Activity Report:http://gert.sos.be/en/]] [#t515cea4]
-[[?????若??箴?:http://gert.sos.be/demo/mysqlar/]]

**[[mysqlreport:http://hackmysql.com/mysqlreport]] [#a4f15906]

* 茲??医?茵? [#w92f4e36]
-[[???????激?割??с???? MySQL ?泣?若?????茵?:http://dev.mysql.com/doc/refman/5.1/ja/multiple-servers.html]]
MySQL/チューニング のバックアップ差分(No.2)

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