ºöÂÔmysqlµÄkey cache hit ratio
Why you should ignore MySQL’s key cache hit ratio
http://www.mysqlperformanceblog.com/2010/02/28/why-you-should-ignore-mysqls-key-cache-hit-ratio/
²»ÒªÏàÐÅmysqlµÄkey cache hit ratio£º
key cache hit ratio = 1 - key_reads / key_read_requests
key_reads:´Ó´ÅÅÌÖжÁ¿éµÄÊýÁ¿
key_read_requests£º´Ó»º´æÖÐÇëÇó¶Á¿éµÄÊýÁ¿
²»ºÃµÄ½¨ÒéÊÇ£º
£¨1£©ÄãÓ¦µ±×¢ÒâÕâ¸öcache hit ratio
£¨2£©²¢ÇÒÄãÓ¦µ±¸ù¾ÝÕâ¸ö±ÈÂÊÉèÖÃÄãµÄkey_buffer_sizeµÄ´óС
ÒÔÉϵĽ¨ÒéÓÐÁ½¸öÎÊÌ⣺
£¨1£©Õâ¸ö±ÈÂÊÊÇÎÞÁ¿¸Ù£¬¼´Äã²»ÖªµÀkey_read_requestsºÍkey_readsÊÇ´¦ÓÚʲôÊýÁ¿¼¶±ðµÄ£¬Òò´ËÄãÎÞ·¨ÆÀ¹ÀÕâ¸öÖµ
£¨2£©ËüûÓÐʱ¼äÒòËØ£¬¼´Õâ¸ö±ÈÖµ²»ÖªµÀÆäʱ¼ä¿ç¶È£¬Ò²ÎÞ·¨ÆÀ¹ÀÕâ¸öÖµµÄÓÅÁÓ
Èç¹ûҪȷʵʹÓÃÕâ¸öÖµÀ´ÆÀ¹ÀÓÅÁÓ£¬ÄÇÐèҪһЩ¼Ù¶¨£º
£¨1£©key_readÐè¶ÔÓ¦Ò»´Î´ÅÅÌI/OµÄÎïÀí¶ÁÈ¡
£¨2£©´Ó´ÅÅÌÖÐÕÒ´ËkeyºÍ´Ó»º´æÖÐÕÒ´ËkeyÊǸüÂýµÄ
£¨3£©Ò»¸ökey_readÊÇÒ»´ÎËæ»úµÄ´ÅÅÌ´æÈ¡
»ùÓÚÒÔÉϼ¸µã£ºÔòÐèÒª¼õÉÙkey_readsµÄÊýÁ¿
key_cache_miss_rate = key_reads / uptime
ÔõôÑùÉèÖÃkey_buffer_size´óÐ¡ÄØ£¿
¿ÉÒÔÉèÖÃÒ»¸öºÜ´óµÄkey_buffer_size£¬È»ºó²åÈëһЩÊý¾Ý£¬Ö´ÐÐһЩ²éѯ£¬Ã¿¸ôÒ»¶Îʱ¼ä²âÁ¿key_buffer_sizeµÄʹÓôóС£¬»æÖÆÇúÏßͼ£¬È¡Æä×î¸ßµãµÄÖµ×÷Ϊkey_buffer_sizeµÄ´óС¡£
Ïà¹ØÎĵµ£º
zz linux yum°²×°mysqlºóҪעÒâµÄһЩ³õʼ»¯ÎÊÌâ
ÎҵķþÎñÆ÷×°µÄÊÇcentos 5.3,ÓÉÓÚ×ʼ°²×°×öÁËÏêϸµÄ¼Ç¼,ËùÒÔÕû¸ö¹ý³ÌÒ²ÊÇÇá³µÊì·,һ·yumÏÂÀ´£¬¾Í¸ã¶¨ÁË£¬È»ºó¾ÍÊÇÊý¾Ý¿â¡¢ÍøÕ¾µÄ°á¼Ò£¬ÇáËɸ㶨~
¿ÉÊǽñÌìÔÚ¼Ò¿´ÁËϹ«Ë¾µÄÍøÕ¾£¬ÎÊÌâ³öÀ´ÁË~ ÓÐЩ²úÆ·µã»÷²é¿´ÏêϸÐÅÏ¢µÄʱºò£¬ÕÒ²»µ½ÍøÒ³~¾¹ýÒ»·¬Ë¼¿¼£¬·¢ÏÖÊÇmysql ......
±¾ÎÄÈí¼þµÄÏÂÔØµØÖ·¾Í²»Áгö£¬Ö÷ÒªÊǼǼ°²×°Ê±µÄ²½Öè¼°Ïà¹ØÎÊÌâµÄ´¦Àí·½·¨£»»ùÓÚ°²×°·½·¨Ä¿Ç°ÓÐÁ½ÖÖ£¬Ò»ÖÖÊÇÀûÓÃRPM°ü°²×°£¬Ò»ÖÖÔ´Âë±àÒë°²×°£»ÒÔÏÂÖ÷
ÒªÊÇÒÔÔ´Âë±àÒë°²×°£»ÒòΪԴÂë°²×°Èí¼þµÄÏ໥ÒÀÀµÐÔС£¬°²×°·½Ê½Óø÷ÖÖunix/linuxϵͳ£¬Î¬»¤·½±ã¡£Èç¹ûÏëÈ¥³ýÏà¹Ø·þÎñʱ£¬Ö±½Órm
–rfĿ¼ ¼´¿É£»½¨Òé²ÉÓÃÔ´Âë· ......
´¥·¢Æ÷ÔÚmysqlÖÐÓ¦ÓõIJ»¶à,ËüÊÇÔÚmysql5ÒÔÉϲſªÊ¼Ö§³ÖµÄ,²»Ö§³Ö²»´ú±íËü²»ÖØÒª,Ê×ÏÈÁ˽âÒ»ÏÂʲôÊÇ´¥·¢Æ÷.´¥·¢Æ÷ÊÇÔÚÊý¾Ý¿âÖÐΪÏìÓ¦Ò»¸öÌØÊâ±í¸ñÖеÄijЩʼþ¶ø×Ô¶¯Ö´ÐеijÌÐò´úÂë¡£(Wikipedia)˵µÃ¼òµ¥Ò»Ð©£¬ËüÊÇÔÚÒ»¸öÌØÊâµÄÊý¾Ý¿âʼþ£¬ÈçINSERT»òDELETE·¢Éúʱ£¬×Ô¶¯¼¤»îµÄÒ»¶Î´úÂë¡£´¥·¢Æ÷¿É·½±ãµØÓÃÓÚÈÕÖ¾¼Ç¼¡¢¶Ô ......
µ¼³ö.sqlÎļþ
1.½«Êý¾Ý¿âtransfer_server_dbµ¼³öµ½transfSRV.sqlÎļþÖУº
mysqldump -u root -p transfer_server_db > /home/eric/transfSRV.sql
2.½«Êý¾Ý¿âtransfer_server_dbÖеÄdevice_info_tableµ¼³öµ½table.sqlÎļþÖУº
mysqldump -u root -p transfer_server_db devi ......
1.Ê×ÏÈ£¬ÉèÖÃÊý¾Ý¿âÖ§³ÖÖÐÎÄgb2312/gbk¡£
¾ßÌå·½·¨£º
´ò¿ªmySQLÎļþ¼Ð£¬ÐÞ¸Ämy.iniÅäÖÃÎļþ¡£
[client]
port=3306
default-character-set=gb2312
ÒÔ¼°
[mysqld]쵀
default-character-set=gb2312
Ð޸쬱£´æ£¬ÖØÆô¡£ÈôÉèΪSystem·þÎñ£¬¿ÉÒÔÔÚ¿ØÖÆÃæ°å-¹ÜÀí¹¤¾ß-·þÎñÖÐÕÒµ½mySQL·þÎñ£¬ÖØÆô¡£
2.±£Ö¤Web ......