MySQL Tunning 效能分析工具
<P>要分析 MySQL 為何緩慢的工具還真不少, 上次用了 mysqlreport 後...又發現神秘好心人寫的<BR><FONT color=navy size=4>檔案下載:</FONT></P>
<P>
<BR><A href="http://www.day32.com/MySQL/" target=_blank><FONT color=#800080>tuning-primer.sh</FONT></A><BR><FONT color=#800080></FONT><BR>基本上, 它是利用 MySQL 本身提供的訊息, 做點判斷, 同時提供一些建議.<BR>以下是它執行產出的結果. 它建議 mysql 至少跑超過 48 小時之後, 收集的資訊跟建議會比較準確.<BR><BR>-- MYSQL PERFORMANCE TUNING PRIMER --<BR>- By: Matthew Montgomery - MySQL Version 5.0.27-log x86_64 Uptime = 0 days 1 hrs 10 min 21 sec<BR>Avg. qps = 73<BR>Total Questions = 311395<BR><BR>Threads Connected = 83 Warning: Server has not been running for at least 48hrs.<BR>It may not be safe to use these recommendations To find out more information on how each of these<BR>runtime variables effects performance visit:<BR><A href="http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html" target=_blank><FONT color=#0066cc>http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html</FONT></A><BR>Visit <A href="http://www.mysql.com/products/enterprise/advisors.html" target=_blank><FONT color=#0066cc>http://www.mysql.com/products/enterprise/advisors.html</FONT></A><BR>for info about MySQL's Enterprise Monitoring and Advisory Service <BR><BR><B>SLOW QUERIES</B><BR>Current long_query_time = 5 sec.<BR>You have <FONT color=#ff0000>2613</FONT> out of <FONT color=#ff0000>311407</FONT> that take longer than 5 sec. to complete<BR>The slow query log is enabled.<BR><FONT color=#00ff00>Your long_query_time seems to be fine</FONT> <BR><BR><B>WORKER THREADS<BR></B>Current thread_cache_size = 16<BR>Current threads_cached = 14<BR>Current threads_per_sec = 0<BR>Historic threads_per_sec = 0<BR><FONT color=#00ff00>Your thread_cache_size is fine</FONT> <BR><BR><B>MAX CONNECTIONS<BR></B>Current max_connections = 512<BR>Current threads_connected = 83<BR>Historic max_used_connections = 130<BR>The number of used connections is 25% of the configured maximum.<BR><FONT color=#00ff00>Your max_connections variable seems to be fine.</FONT> <BR><BR><B>MEMORY USAGE</B><BR><FONT color=#ff0000>Max Memory Ever Allocated : 4 G<BR>Configured Max Per-thread Buffers : 14 G<BR>Configured Max Global Buffers : 1 G<BR>Configured Max Memory Limit : 15 G<BR>Physical Memory : 1.96 G </FONT><FONT color=#ff0000>Max memory limit exceeds 90% of physical memory</FONT> <BR><BR><B>KEY BUFFER</B><BR>Current MyISAM index space = 440 M<BR>Current key_buffer_size = 768 M<BR>Key cache miss rate is 1 : 507<BR>Key buffer fill ratio = 5.00 %<BR><FONT color=#ff0000>Your key_buffer_size seems to be too high.<BR>Perhaps you can use these resources elsewhere</FONT> <BR><BR><B>QUERY CACHE</B><BR>Query cache is enabled<BR>Current query_cache_size = 256 M<BR>Current query_cache_used = 10 M<BR>Current query_cache_limit = 4 M<BR>Current Query cache Memory fill ratio = 4.17 %<BR>Current query_cache_min_res_unit = 2 K<BR><FONT color=#ff0000>Query Cache is 14 % fragmented<BR>Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory<BR>If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.<BR>Your query_cache_size seems to be too high.<BR>Perhaps you can use these resources elsewhere</FONT><BR>MySQL won't cache query results that are larger than query_cache_limit in size <BR><BR><B>SORT OPERATIONS</B><BR>Current sort_buffer_size = 16 M<BR>Current record/read_rnd_buffer_size = 3 M<BR><FONT color=#00ff00>Sort buffer seems to be fine</FONT> <BR><BR><B>JOINS</B><BR>Current join_buffer_size = 256.00 K<BR>You have had 2 queries where a join could not use an index properly<BR>You should enable "log-queries-not-using-indexes"<BR>Then look for non indexed joins in the slow query log.<BR>If you are unable to optimize your queries you may want to increase your<BR>join_buffer_size to accommodate larger joins in one pass. <FONT color=#ff0000>Note! This script will still suggest raising the join_buffer_size when<BR>ANY joins not using indexes are found.</FONT> <BR><BR><B>OPEN FILES LIMIT</B><BR>Current open_files_limit = 2570 files<BR>The open_files_limit should typically be set to at least 2x-3x<BR>that of table_cache if you have heavy MyISAM usage.<BR><FONT color=#00ff00>Your open_files_limit value seems to be fine</FONT> <BR><BR><B>TABLE CACHE</B><BR>Current table_cache value = 1024 tables<BR>You have a total of 224 tables<BR>You have 284 open tables.<BR><FONT color=#00ff00>The table_cache value seems to be fine</FONT> <BR><BR><B>TEMP TABLES</B><BR>Current max_heap_table_size = 63 M<BR>Current tmp_table_size = 64 M<BR>Of 24475 temp tables, 60% were created on disk<BR>Effective in-memory tmp_table_size is limited to max_heap_table_size.<BR><FONT color=#ff0000>Perhaps you should increase your tmp_table_size and/or max_heap_table_size<BR>to reduce the number of disk-based temporary tables<BR></FONT>Note! BLOB and TEXT columns are not allow in memory tables.<BR>If you are using these columns raising these values might not impact your<BR>ratio of on disk temp tables. <BR><BR><B>TABLE SCANS</B><BR>Current read_buffer_size = 7 M<BR>Current table scan ratio = 37 : 1<BR><FONT color=#00ff00>read_buffer_size seems to be fine</FONT> <BR><BR><B>TABLE LOCKING</B><BR>Current Lock Wait ratio = <FONT color=#ff0000>1 : 2015</FONT><BR>You may benefit from selective use of InnoDB.<BR><BR>From: <A href="http://www.mw.net.tw/user/chinian/blog/2008/02/01/110/77063/" target=_blank>http://www.mw.net.tw/user/chinian/blog/2008/02/01/110/77063/</A></P>