-MySQL?∫?c??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/?狗?? ---- -[[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??緇劫?茹h?!!: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] -?????若?帥?泣?ゃ?削??<?≪????????綽??????? --???c?鴻?????<?≪??????炊?吾????綺???00筝?????勲?????? --B?????若?ゃ?潟???????鴻??「膣≪?????若?????鴻???c?恰????ぇ????激?若?????榊?????????????c?鴻????с?????????<?≪????с???????с????????勲???冴?? --MySQL??S?????<?ゃ?????c???激?ャ???若?????????????т??????????? --OS?????<?ゃ?????c???激?ャ??MySQL絨????????????<?????<???с?????? --???若?帥?????????<?≪??????障???????翫???????ゃ?潟???????鴻?????с???<?≪??????障???????????? * ???若????┃荐???SQL????眼?? [#n1bda048] -[[?????若?????若?????堺??篁c??QL???ャ?若???潟?医?榊?鐚?鐚?鐚?EXPLAIN??http://mtl.recruit.co.jp/blog/2011/02/sql.html]] -[[?????若?????若?????堺??篁c??QL???ャ?若???潟?医?榊?鐚?鐚?鐚?SQL?????c?<????????膩?http://mtl.recruit.co.jp/blog/2011/02/sqlsql1.html]] -[[?????若?????若?????堺??篁c??QL???ャ?若???潟?医?榊?鐚?鐚?鐚?SQL?????c?<??????緇?膩?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 筝?荐????違??紜????????????????若?吟?????????<?ゃ?????c?鴻???????炊?違??????羈????綽?荀?????紜??????? -[[???<?ゃ?????c?鴻???????帥????紊ф?違??紜?????: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] -???若?帥???c????????????? --???c?????????????????若???c?激?с?潟???????潟?????? --???c???????????激?潟???????????潟???????????若?帥?????????若???c?激?с?潟??舟? -MyISAM???<?ゃ???с???????若?帥???<?ゃ??MYD)???ゃ?潟???????鴻???<?ゃ??MYI)??臀?????c????????????絎??с???? * InnoDB?劫?? [#g544d331] -?????若??????InnoDB?? --[[InnoDB??yISAM???c?<?????????: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???<?ゃ?????????????潟?????激?潟???????????潟???у???c???????? --InnoDB ?????掩?????若?????鴻???若?刻?ibdata1 etc鐚??????????若?帥???c?????????????羔??????違??臀?????с?????ゃ????????綽?荀?????????.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 --?????????茵?腟????????c???激?ャ??????Query?????c???激?ャ???????翫????QL??絎?茵??????????c???激?ャ????絎鴻??菴??????с???泣?ゃ?冴??ぇ?????鴻???????? --荅峨????????若????????絎鴻??紊??眼???????翫???????????c???激?ャ????????????с?????c???激?ャ??????筝??桁???????????????????? ** MyISAM?∫? [#m75c4a5a] -key_buffer_size --MyISAM?????若?????c???激?ャ???????????<???泣?ゃ? --MyISAM???帥??ySQL絨????泣?若??????RAM??/4腮?綺??蚊??綵??????????? -table_open_cache --[[MySQL ?с?????若?????????若???潟???????若?冴???号?:http://dev.mysql.com/doc/refman/5.1/ja/table-cache.html]] --?ョ??違??200???翫??????篏 200 ? N ?????若???????c???激?ャ?泣?ゃ?冴??綽?荀? -max_connections --?泣?若????荐怨???????紊у?????ョ?? ** InnoDB?∫? [#x81a3293] -innodb_file_per_table --???若??????篏?? .ibd ???<?ゃ????篏??? --綽???鐚?鐚???絎???????? -innodb_buffer_pool_size --InnoDB ???若?????????若?帥???ゃ?潟???????鴻??篆?絖??????????????????<??ぇ???? --MySQL????nnoDB絨????泣?若??????RAM? 70-80% 篏??障?т???????????????荐??????????????絎????????????純?<???????????????с??50%??????????? -innodb_log_file_size --???若?帥???吾??莨若?帥??緇???ぇ?????泣?ゃ?冴?????若?帥???吾??莨若?炊?ц?純??襲?帥???? --紊с?????泣?ゃ?冴?祉?????若?帥????????????????荀????? --?泣?ゃ?冴??紊??眼?????翫????罩e幻??罩√???篏???羝??帥?????違???<?ゃ????????????? /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?<???c???膣????ャ?若???潟??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]] -[[???<???c??????? ????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] ???????若???潟?拷?∫?c?<??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]]