MySQLÓÅ»¯
MySQLÓÅ»¯
ͬʱÔÚÏß·ÃÎÊÁ¿¼ÌÐøÔö´ó£¬¶ÔÓÚ1GÄÚ´æµÄ·þÎñÆ÷Ã÷ÏԸоõµ½³ÔÁ¦ÑÏÖØʱÉõÖÁÿÌ춼»áËÀ»ú£¬»òÕßʱ²»Ê±µÄ·þÎñÆ÷¿¨Ò»Ï£¬Õâ¸öÎÊÌâÔø¾À§ÈÅÁËÎÒ°ë¸ö¶àÔ¡£MySQLʹÓÃÊǺܾßÉìËõÐÔµÄËã·¨£¬Òò´ËÄãͨ³£ÄÜÓúÜÉÙµÄÄÚ´æÔËÐлò¸øMySQL¸ü¶àµÄ±»´æÒԵõ½¸üºÃµÄÐÔÄÜ¡£
°²×°ºÃmysqlºó£¬ÅäÖÆÎļþÓ¦¸ÃÔÚ/usr/local/mysql/share/mysqlĿ¼ÖУ¬ÅäÖÆÎļþÓм¸¸ö£¬ÓÐmy- huge.cnf my-medium.cnf my-large.cnf my-small.cnf£¬²»Í¬µÄÁ÷Á¿µÄÍøÕ¾ºÍ²»Í¬ÅäÖƵķþÎñÆ÷»·¾³£¬µ±È»ÐèÒªÓв»Í¬µÄÅäÖÆÎļþÁË¡£Ò»°ãµÄÇé¿öÏ£¬my-medium.cnfÕâ¸öÅäÖÆÎļþ¾ÍÄÜÂú×ãÎÒÃǵĴó¶àÐèÒª£»Ò»°ãÎÒÃÇ»á°ÑÅäÖÃÎļþ¿½±´µ½/etc/my.cnf Ö»ÐèÒªÐÞ¸ÄÕâ¸öÅäÖÃÎļþ¾Í¿ÉÒÔÁË£¬Ê¹ÓÃmysqladmin variables extended-status –u root –p ¿ÉÒÔ¿´µ½Ä¿Ç°µÄ²ÎÊý£¬ÓУ³¸öÅäÖòÎÊýÊÇ×îÖØÒªµÄ£¬¼´key_buffer_size¡¢query_cache_size¡¢table_cache¡£
key_buffer_sizeÖ»¶ÔMyISAM±íÆð×÷Óã¬key_buffer_sizeÖ¸¶¨Ë÷Òý»º³åÇøµÄ´óС£¬Ëü¾ö¶¨Ë÷Òý´¦ÀíµÄËٶȣ¬ÓÈÆäÊÇË÷Òý¶ÁµÄËٶȡ£Ò»°ãÎÒÃÇÉèΪ16M£¬Êµ¼ÊÉÏÉÔ΢´óÒ»µãµÄÕ¾µã¡¡Õâ¸öÊý×ÖÊÇÔ¶Ô¶²»¹»µÄ£¬Í¨¹ý¼ì²é״ֵ̬Key_read_requestsºÍKey_reads£¬¿ÉÒÔÖªµÀkey_buffer_sizeÉèÖÃÊÇ·ñºÏÀí¡£±ÈÀýkey_reads / key_read_requestsÓ¦¸Ã¾¡¿ÉÄܵĵͣ¬ÖÁÉÙÊÇ1:100£¬1:1000¸üºÃ(ÉÏÊö״ֵ̬¿ÉÒÔʹÓÃSHOW STATUS LIKE 'key_read%'»ñµÃ)¡£ »òÕßÈç¹ûÄã×°ÁËphpmyadmin ¿ÉÒÔͨ¹ý·þÎñÆ÷ÔËÐÐ״̬¿´µ½£¬±ÊÕßÍƼöÓÃphpmyadmin¹ÜÀímysql£¬ÒÔϵÄ״ֵ̬¶¼ÊDZ¾ÈËͨ¹ýphpmyadmin»ñµÃµÄʵÀý·ÖÎö:
key_buffer_size – 128M
Õâ¸ö·þÎñÆ÷ÒѾÔËÐÐÁË20Ì죬²éѯ³öÀ´µÄÖµ£º
key_read_requests – 650759289
key_reads - 79112 ±ÈÀý½Ó½ü1:8000 ½¡¿µ×´¿ö·Ç³£ºÃ
ÁíÍâÒ»¸ö¹À¼Ækey_buffer_sizeµÄ°ì·¨£º°ÑÄãÍøÕ¾Êý¾Ý¿âµÄÿ¸ö±íµÄË÷ÒýËùÕ¼¿Õ¼ä´óС¼ÓÆðÀ´¿´¿´¡£ÒÔ´Ë·þÎñÆ÷ΪÀý£¬±È½Ï´óµÄ¼¸¸ö±íË÷Òý¼ÓÆðÀ´´ó¸Å125M£¬Õâ¸öÊý×Ö»áËæ×űí±ä´ó¶ø±ä´ó¡£
´Ó4.0.1¿ªÊ¼£¬MySQLÌṩÁ˲éѯ»º³å»úÖÆ¡£Ê¹Óòéѯ»º³å£¬MySQL½«SELECTÓï¾äºÍ²éѯ½á¹û´æ·ÅÔÚ»º³åÇøÖУ¬½ñºó¶ÔÓÚͬÑùµÄSELECTÓï¾ä(Çø·Ö´óСд)£¬½«Ö±½Ó´Ó»º³åÇøÖжÁÈ¡½á¹û¡£¸ù¾ÝMySQLÓû§ÊֲᣬʹÓòéѯ»º³å×î¶à¿ÉÒÔ´ïµ½238%µÄЧÂÊ¡£
ͨ¹ýSHOW STATUS LIKE 'Qcache%'²éѯÒÔϼ¸¸ö״ֵ̬¿ÉÒÔÖªµÀquery_cache_sizeÉèÖõÃÊÇ·ñºÏÀí£º
Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks
Èç¹ûQcac
Ïà¹ØÎĵµ£º
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_request ......
package com.lovo.cq.shopping10_1.common;
import java.sql.*;
public class DbUtil {
private PreparedStatement pstmt = null;
private Connection con = null;
public DbUtil() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://l ......
1.µÇ¼£º mysql -uroot -p
2.LinuxÏÂmysql ²»ÄÜÍâÍø·ÃÎÊ
ÒѾÔÚMYSQLÀïgrantÁËȨÏÞÁË¡£ÔÚÍâÍø»¹ÊDz»ÄÜ·ÃÎÊ .
ÎÊÌ⣺
cat /etc/mysql/my.conf
ÕÒµ½ÏÂÃæÒ»ÐÐ
bind-address = 127.0.0.1
Î ......
½ñÌì¸øÐÂÀ´µÄÔ±¹¤Á¬½ÓÊý¾Ý¿â£¬ÐÞ¸ÄÁËÒ»ÏÂmysql rootÓû§µÄÃÜÂ룬ÎÒÏÂÔصÄÊÇ´ò°üµÄredMine£¬Ä¬ÈÏÃÜÂëÊÇ¿Õ£¬ÐÞ¸ÄÃÜÂëÖ®ºóÖØÆôredmine£¬phpAdmin Á´½Ó²»ÉÏmysqlÊý¾Ý¿âÁË£¬ÎÒÒ²ÐÞ¸ÄÁËphpadminÎļþ¼ÐϵÄconfig.inc.phpµÄÃÜÂ룬»¹ÊDz»ÐУ¬Ìáʾ#1130 - Host 'localhost' is not allowed to connect to this MySQL server´íÎó£¬Ã÷ ......
¡¡¡¡(1)¡¢back_log£º
¡¡¡¡ÒªÇó MySQL ÄÜÓеÄÁ¬½ÓÊýÁ¿¡£µ±Ö÷ÒªMySQLÏß³ÌÔÚÒ»¸öºÜ¶Ìʱ¼äÄڵõ½·Ç³£¶àµÄÁ¬½ÓÇëÇó£¬Õâ¾ÍÆð×÷Óã¬È»ºóÖ÷Ï̻߳¨Ð©Ê±¼ä(¾¡¹ÜºÜ¶Ì)¼ì²éÁ¬½Ó²¢ÇÒÆô¶¯Ò»¸öÐÂÏ̡߳£
¡¡¡¡back_logÖµÖ¸³öÔÚMySQLÔÝʱֹͣ»Ø´ðÐÂÇëÇó֮ǰµÄ¶Ìʱ¼äÄÚ¶àÉÙ¸öÇëÇó¿ÉÒÔ±»´æÔÚ¶ÑÕ»ÖС£Ö»ÓÐÈç¹ûÆÚÍûÔÚÒ»¸ö¶Ìʱ¼äÄÚÓкܶàÁ¬½Ó£¬ ......