??????????С??????????????????????? mysql ??????????????????Ч????
???????? mindmanger ???????·???γ???????????????????????????????????????γ?????????·??

????1 ???? Connections
??????????????”mysql: error 1040: too many connections”???????????????????????mysql????????????????????????????????????????????????????????mysql?????????max_connections???С??
????mysql> show variables like ‘max_connections‘;
????+—————–+——-+
????| variable_name?? | value |
????+—————–+——-+
????| max_connections | 256?? |
????+—————–+——-+
???????mysql????????????????256?????????·???????????????????
????mysql> show global status like ‘max_used_connections‘;
????mysql????????????????????245????д?????????????????256???????г???1040??????????????????
????max_used_connections / max_connections * ≈ 85%
?????????????????????????85???????????????????10%???￡?mysql???????????????????????????
????2 ??? Thread
????mysql> show global status like ‘thread%‘;
????+——————-+——-+
????| variable_name???? | value |
????+——————-+——-+
????| threads_cached?? | 46?? |
????| threads_connected | 2???? |
????| threads_created??| 570?? |
????| threads_running | 1?? ?? |
????+——————-+——-+
?????????????mysql?????????????????????thread_cache_size???????????????????????????????????????????????????????????????????????????δ?????????
????threads_created?????????????????????????threads_created?????????????mysql??????????????????????????????????????????????????thread_cache_size???
????????????? thread_cache_size ?????
????mysql> show variables like ‘thread_cache_size‘;
????+——————-+——-+
????| variable_name???? | value |
????+——————-+——-+
????| thread_cache_size | 64???? |
????+——————-+——-+
????????е??????????????????
????3 ???? cache
????3.1 ???????
????mysql> show global status like ‘open_files‘;
????+—————+——-+
????| variable_name | value |
????+—————+——-+
????| open_files???? | 1410?? |
????+—————+——-+
????mysql> show variables like ‘open_files_limit‘;
????+——————+——-+
????| variable_name???? | value |
????+——————+——-+
????| open_files_limit | 4590 |
????+——————+——-+
????????????????open_files / open_files_limit * <= 75??
????3.2 ?????
????3.2.1 ???? open_tables
????mysql> show global status like ‘open%tables%‘;
????+—————+——-+
????| variable_name | value |
????+—————+——-+
????| open_tables?? | 919?? |
????| opened_tables | 1951 |
????+—————+——-+
????open_tables: ?????????
????opened_tables: ???????????
??????? opened_tables ????????????????? table_cache(5.1.3???????????table_open_cache)??????С??????????·?????table_cache???
????mysql> show variables like ‘table_cache‘;
????+—————+——-+
????| variable_name | value |
????+—————+——-+
????| table_cache?????? | 2048?? |
????+—————+——-+
???????????????
????open_tables / opened_tables * >= 85%
????open_tables / table_cache * <= 95%
????3.2.2 ????? tmp_table
????mysql> show global status like ‘created_tmp%‘;
????+————————-+———+
????| variable_name?????????? | value???? |
????+————————-+———+
????| created_tmp_disk_tables | 21197?? |
????| created_tmp_files????????| 58?????? |
????| created_tmp_tables???? | 1771587 |
????+————————-+———+
??????δ?????????created_tmp_tables ??????????????????????????created_tmp_disk_tables??????created_tmp_files???mysql????????????????????????????????????
????created_tmp_disk_tables / created_tmp_tables * <= 25%
?????????????????? created_tmp_disk_tables / created_tmp_tables * ?? 1.20%??????????????????????mysql???????????????????
????mysql> show variables where variable_name in (‘tmp_table_size‘?? ‘max_heap_table_size‘);
????+———————+———–+
????| variable_name?????? | value?????????? |
????+———————+———–+
????| max_heap_table_size | 268435456 |
????| tmp_table_size?????? | 536870912 |
????+———————+———–+
??????? 256mb ???μ????????????????棬???????????????????
????3.2.3 ???????
????mysql> show global status like ‘table_locks%‘;
????+———————–+———–+
????| variable_name???????? | value?????? |
????+———————–+———–+
????| table_locks_immediate | 490206328 |
????| table_locks_waited????| 2084912?? |
????+———————–+———–+
????table_locks_immediate ?????????????????
????table_locks_waited ??????????????????
??????? table_locks_immediate / table_locks_waited > 5000???ò???innodb???棬???innodb????????myisam???????????????д??????innodbЧ?????Щ??
????????е?????? table_locks_immediate / table_locks_waited ?? 235??myisam?????
????3.2.4 ????????
????mysql> show global status like ‘handler_read%‘;
????+———————–+————-+
????| variable_name???????? | value???????? |
????+———————–+————-+
????| handler_read_first????| 5803750???? |
????| handler_read_key???? | 6049319850 |
????| handler_read_next?? | 94440908210 |
????| handler_read_prev?? | 34822001724 |
????| handler_read_rnd???? | 405482605?? |
????| handler_read_rnd_next | 18912877839 |
????+———————–+————-+
????????ν???μ? http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html ???????????????????????????
????mysql> show global status like ‘com_select‘;
????+—————+———–+
????| variable_name | value?????????? |
????+—————+———–+
????| com_select???????? | 222693559 |
????+—————+———–+
???????????????
??????????? ?? handler_read_rnd_next / com_select
????????????????? 4000?????????????????裬???п?????????н???????? read_buffer_size ??????Щ????????ò??????8mb??
????3.3 key_buffer_size
????key_buffer_size???myisam????????????????????????????myisam?????洢????????????????
????mysql> show variables like ‘key_buffer_size‘;
????+—————–+————+
????| variable_name?? | value?????? |
????+—————–+————+
????| key_buffer_size | 536870912 |
????+—————–+————+
?????????? 512mb ???? key_buffer_size ???????????? key_buffer_size ??????????
????mysql> show global status like ‘key_read%‘;
????+————————+————-+
????| variable_name??????????| value?? ???? |
????+————————+————-+
????| key_read_requests???? | 27813678764 |
????| key_reads???????????? | 6798830?????? |
????+————————+————-+
????????? 27813678764?? ????????????? 6798830?? ????????????????????????????????????????δ???л????????
????key_cache_miss_rate ?? key_reads / key_read_requests *
??????????????????key_cache_miss_rate?0.0244%??4000????????????????????????????????bt???key_cache_miss_rate??0.1%??????????1000????????????????????????key_cache_miss_rate??0.01%???μ????key_buffer_size????????????????????