?????????key_read_buffer ????? 8M ??????е???????????????????????? RAM * 1/4
????mysql????????????key_blocks_*??????
????mysql> show global status like ‘key_blocks_u%‘;
????+————————+————-+
????| variable_name???????? | value???????? |
????+————————+————-+
????| key_blocks_unused????| 0???????????? |
????| key_blocks_used?????? | 413543 ???? |
????+————————+————-+
????key_blocks_unused ???δ????????(blocks)??
????key_blocks_used ??????????????blocks??
??????????????????????е???涼???????????? key_buffer_size????????????????????????????????????????
????key_blocks_used / (key_blocks_unused + key_blocks_used) * ≈ 80%
????3.4 ?????????? sort_buffer
????mysql> show global status like ‘sort%‘;
????+——————-+————+
????| variable_name???? | value?????? |
????+——————-+————+
????| sort_merge_passes | 29?????? |
????| sort_range????????| 37432840?? |
????| sort_rows???????? | 9178691532 |
????| sort_scan???????? | 1860569???? |
????+——————-+————+
????sort_merge_passes ??????????mysql ????????????????????????????С???????? sort_buffer_size ??????????????С?????????е?????????????У?mysql ?????????????????????浽???????У??? mysql ??????м???????????????е?????????????????????????? sort_merge_passes????????mysql ?????????????????????????????????????????? sort_merge_passes ???????????????????????????????????????????????????????????????? sort_buffer_size ????? sort_merge_passes ?? ??????????????????????????? sort_buffer_size ?????????????????? how fast can you sort data with mysql???????http://qroom.blogspot.com/2007/09/mysql-select-sort.html ??ò??????
????????????read_rnd_buffer_size(3.2.3??record_rnd_buffer_size)?????????????????????????μ???http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/
????3.5 ???????
????mysql> show global status like ‘qcache%‘;
????+————————-+———–+
????| variable_name??????????| value?????? |
????+————————-+———–+
????| qcache_free_blocks?? | 22756???? |
????| qcache_free_memory??| 76764704?? |
????| qcache_hits????????????| 213028692 |
????| qcache_inserts???????? | 208894227 |
????| qcache_lowmem_prunes | 4010916 |
????| qcache_not_cached????| 13385031 |
????| qcache_queries_in_cache | 43560?? |
????| qcache_total_blocks?? | 111212 ?? |
????+————————-+———–+
????mysql ???????????????
????qcache_free_blocks?????????????????????????????????????????flush query cache???????е?????????????????????????п顣
????qcache_free_memory???????е??????檔
????qcache_hits????β??????????????????
????qcache_inserts????β?????????????????д??????????????????б????
????qcache_lowmem_prunes???????????治????????????????????????????????????????????ó??????????????????????????????????????????????????????????????????? free_blocks??free_memory??????????????????????
????qcache_not_cached?????????л?????????????????????????Щ??????? select ??????????now()?????????
????qcache_queries_in_cache?????????????????????????????
????qcache_total_blocks???????п????????
??????????????·?????????query_cache???????
????mysql> show variables like ‘query_cache%‘;
????+——————————+———–+
????| variable_name?????????????? | value?????? |
????+——————————+———–+
????| query_cache_limit?????????? | 2097152?? |
????| query_cache_min_res_unit??| 4096??????|
????| query_cache_size?????????? | 203423744 |
????| query_cache_type??????????| on???????? |
????| query_cache_wlock_invalidate | off???? |
????+——————————+———-+
????????ε?????
????query_cache_limit?????????С????????????
????query_cache_min_res_unit????????С??С
????query_cache_size??????????С
????query_cache_type?????????????????????????????????б???????? select sql_no_cache ???
????query_cache_wlock_invalidate????????????????????myisam?????д???????????????query cache?У??????cache????????д???????????????????
????query_cache_min_res_unit???????????”??н?”???????4kb??????????????????к??????????????????С???????????????????????????
???????????????? = qcache_free_blocks / qcache_total_blocks *
??????????????????????20%????????flush query cache?????????????????????Сquery_cache_min_res_unit??????????????С???????????
????????????????? = (query_cache_size - qcache_free_memory) / query_cache_size *
???????????????????25%???μ?????query_cache_size???????????????С?????????????????80?????????qcache_lowmem_prunes > 50??????query_cache_size?????е?С?????????????
????????????????? = (qcache_hits - qcache_inserts) / qcache_hits *
????????????? ???????????? ?? 20.46????????????????? ?? 62.26????????????????? ?? 1.94????????????????д??????????????????????Щ?????
????4 ????
????4.1 read_buffer_size
????4.2 ?????
????mysql> show variables like ‘%slow%‘;
????+——————+——-+
????| variable_name?? | value |
????+——————+——-+
????| log_slow_queries | on???? |
????| slow_launch_time | 2?? ?? |
????+——————+——-+
????mysql> show global status like ‘%slow%‘;
????+———————+——-+
????| variable_name?????? | value |
????+———————+——-+
????| slow_launch_threads | 0 ?? |
????| slow_queries????????| 4148 |
????+———————+——-+
?????????д????????????????????2???????????????????4148?????????????????????????????????????sql????????????????ù????????????岻??????5????????????????????????????????????mysql??????http://www.percona.com/docs/wiki/release:start???????????汾??
?????????????????????????????????????????mysql???????????????????????????????????????????????????????????????????????????С??