WikipediaåQŒè‡ªç”Þqš„癄¡§‘全书

同时在线讉K—®é‡ç‘ô¾l­å¢žå¤?对于1G内存的服务器明显感觉到吃力严重时甚至每天都会æ­ÀLœº 或者时不时的服务器卡一ä¸?˜q™ä¸ªé—®é¢˜æ›„¡»å›°æ‰°äº†æˆ‘半个多月MySQL使用是很具äŽ×¾~©æ€§çš„½Ž—法åQŒå› æ­¤ä½ é€šå¸¸èƒ½ç”¨å¾ˆå°‘的内存运行或¾l™MySQL更多的被存以得到更好的性能ã€?

安装好mysql后，配制文äšg应该åœ?usr/local/mysql/share/mysql目录中，配制文äšg有几个，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的¾|‘站和不同配制的服务器环境，当然需要有不同的配制文件了ã€?

一般的情况下，my-medium.cnf˜q™ä¸ªé…åˆ¶æ–‡äšgž®Þpƒ½æ»¡èƒö我们的大多需要；一般我们会把配¾|®æ–‡ä»¶æ‹·è´åˆ°/etc/my.cnf 只需要修改这个配¾|®æ–‡ä»¶å°±å¯ä»¥äº†ï¼Œä½¿ç”¨mysqladmin variables extended-status –u root –p 可以看到目前的参敎ͼŒæœ‰ï¼“个配¾|®å‚数是最重要的，即key_buffer_size,query_cache_size,table_cacheã€?

key_buffer_size只对MyISAM表è“v作用åQ?

key_buffer_size指定索引¾~“冲区的大小åQŒå®ƒå†›_®šç´¢å¼•å¤„理的速度åQŒå°¤å…¶æ˜¯ç´¢å¼•è¯Èš„速度。一般我们设ä¸?6M,实际上稍微大一点的站点　˜q™ä¸ªæ•°å­—是远˜qœä¸å¤Ÿçš„åQŒé€šè¿‡‹‚€æŸ¥çŠ¶æ€å€¼Key_read_requestså’ŒKey_reads,可以知道key_buffer_size讄¡½®æ˜¯å¦åˆç†ã€‚比例key_reads / key_read_requests应该ž®½å¯èƒ½çš„低，臛_°‘æ˜?:100åQ?:1000更好åQˆä¸Š˜q°çŠ¶æ€å€¼å¯ä»¥ä‹É用SHOW STATUS LIKE ‘key_read%’获得åQ‰ã€?或者如果你装了phpmyadmin 可以通过服务器运行状态看åˆ?½W”者推荐用phpmyadmin½Ž¡ç†mysqlåQŒä»¥ä¸‹çš„状态值都是本人通过phpmyadmin获得的实例分æž?

˜q™ä¸ªæœåŠ¡å™¨å·²¾lè¿è¡Œäº†20å¤?

key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112

比例接近1:8000 健康状况非常�

另外一个估计key_buffer_size的办法　把你¾|‘站数据库的每个表的索引所占空间大ž®åŠ èµäh¥çœ‹çœ‹ä»¥æ­¤æœåŠ¡å™¨äØ“ä¾?比较大的几个表烦引加èµäh¥å¤§æ¦‚125M ˜q™ä¸ªæ•°å­—会随着表变大而变大ã€?

ä»?.0.1开始，MySQL提供了查询缓冲机制。ä‹É用查询缓å†ÔŒ¼ŒMySQLž®†SELECT语句和查询结果存攑֜¨¾~“冲åŒÞZ¸­åQŒä»ŠåŽå¯¹äºŽåŒæ ïLš„SELECT语句åQˆåŒºåˆ†å¤§ž®å†™åQ‰ï¼Œž®†ç›´æŽ¥ä»Ž¾~“冲åŒÞZ¸­è¯Õd–¾l“果。根据MySQL用户手册åQŒä‹É用查询缓冲最多可以达åˆ?38%的效率ã€?

通过调节以下几个参数可以知道query_cache_size讄¡½®å¾—是否合ç?

Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks

Qcache_lowmem_prunes的值非常大åQŒåˆ™è¡¨æ˜Ž¾lå¸¸å‡ºçŽ°¾~“冲不够的情å†?同时Qcache_hits的值非常大åQŒåˆ™è¡¨æ˜ŽæŸ¥è¯¢¾~“冲使用非常频繁åQŒæ­¤æ—‰™œ€è¦å¢žåŠ ç¼“冲大ž®Qcache_hitsçš„å€ég¸å¤§ï¼Œåˆ™è¡¨æ˜Žä½ çš„查询重复率很低åQŒè¿™¿Uæƒ…况下使用查询¾~“冲反而会影响效率åQŒé‚£ä¹ˆå¯ä»¥è€ƒè™‘不用查询¾~“冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不ä‹É用查询缓册Ӏ?

Qcache_free_blocksåQŒå¦‚果该值非常大åQŒåˆ™è¡¨æ˜Ž¾~“冲åŒÞZ¸­¼„Žç‰‡å¾ˆå¤šquery_cache_type指定是否使用查询¾~“冲

我设¾|?

query_cache_size = 32M
query_cache_type= 1

得到如下状态�

Qcache queries in cache 12737 表明目前¾~“存的条æ•?
Qcache inserts 20649006
Qcache hits 79060095 　看来重复查询率还挺高�
Qcache lowmem prunes 617913　有这么多‹Æ¡å‡ºçŽ°ç¼“存过低的情况
Qcache not cached 189896 　　
Qcache free memory 18573912　　目前剩余¾~“å­˜½Iºé—´
Qcache free blocks 5328 ˜q™ä¸ªæ•°å­—ä¼ég¹Žæœ‰ç‚¹å¤§ã€€¼„Žç‰‡ä¸å°‘
Qcache total blocks 30953

如果内存允许32M应该要往上加�

table_cache指定表高速缓存的大小。每当MySQL讉K—®ä¸€ä¸ªè¡¨æ—Óž¼Œå¦‚果在表¾~“冲åŒÞZ¸­˜q˜æœ‰½Iºé—´åQŒè¯¥è¡¨å°±è¢«æ‰“å¼€òq¶æ”¾å…¥å…¶ä¸­ï¼Œ˜q™æ ·å¯ä»¥æ›´å¿«åœ°è®¿é—®è¡¨å†…容。通过‹‚€æŸ¥å³°å€¼æ—¶é—´çš„状态值Open_tableså’ŒOpened_tablesåQŒå¯ä»¥å†³å®šæ˜¯å¦éœ€è¦å¢žåŠ table_cache的倹{€‚如果你发现open_tables½{‰äºŽtable_cacheåQŒåƈ且opened_tables在不断增长，那么你就需要增加table_cacheçš„å€égº†åQˆä¸Š˜q°çŠ¶æ€å€¼å¯ä»¥ä‹É用SHOW STATUS LIKE ‘Open%tables’获得åQ‰ã€‚注意，不能盲目地把table_cache讄¡½®æˆå¾ˆå¤§çš„倹{€‚如果设¾|®å¾—太高åQŒå¯èƒ½ä¼šé€ æˆæ–‡äšg描述½W¦ä¸­‘»I¼Œä»Žè€Œé€ æˆæ€§èƒ½ä¸ç¨³å®šæˆ–者连接失败ã€?

对于æœ?G内存的机器，推荐值是128åQ?56ã€?

½W”者设¾|®table_cache = 256

得到以下状�

Open tables 256
Opened tables 9046

虽然open_tables已经½{‰äºŽtable_cacheåQŒä½†æ˜¯ç›¸å¯¹äºŽæœåŠ¡å™¨è¿è¡Œæ—¶é—´æ¥è¯?已经˜qè¡Œäº?0天，opened_tablesçš„å€ég¹Ÿéžå¸¸ä½Žã€‚因此，增加table_cache的值应该用处不大。如果运行了6个小时就出现上述å€?那就要考虑增大table_cacheã€?

如果你不需要记å½?˜q›åˆ¶log ž®±æŠŠ˜q™ä¸ªåŠŸèƒ½å…ÏxŽ‰åQŒæ³¨æ„å…³æŽ‰ä»¥åŽå°±ä¸èƒ½æ¢å¤å‡ºé—®é¢˜å‰çš„数据了åQŒéœ€è¦æ‚¨æ‰‹åŠ¨å¤‡ä†¾åQŒäºŒ˜q›åˆ¶æ—¥å¿—包含所有更新数据的语句åQŒå…¶ç›®çš„是在恢复数据库时用它来把数据ž®½å¯èƒ½æ¢å¤åˆ°æœ€åŽçš„状态。另外，如果做同步复åˆ? Replication )的话åQŒä¹Ÿéœ€è¦ä‹É用二˜q›åˆ¶æ—¥å¿—传送修æ”ÒŽƒ…å†üc€?

log_bin指定日志文äšgåQŒå¦‚果不提供文äšg名，MySQLž®†è‡ªå·×ƒñ”生缺省文件名。MySQL会在文äšg名后面自动添加数字引åQŒæ¯‹Æ¡å¯åŠ¨æœåŠ¡æ—¶åQŒéƒ½ä¼šé‡æ–°ç”Ÿæˆä¸€ä¸ªæ–°çš„二˜q›åˆ¶æ–‡äšg。此外，使用log-bin-index可以指定索引文äšgåQ›ä‹É用binlog-do-db可以指定记录的数据库åQ›ä‹É用binlog-ignore-db可以指定不记录的数据库。注意的是：binlog-do-dbå’Œbinlog-ignore-db一‹Æ¡åªæŒ‡å®šä¸€ä¸ªæ•°æ®åº“åQŒæŒ‡å®šå¤šä¸ªæ•°æ®åº“需要多个语句。而且åQŒMySQL会将所有的数据库名¿U°æ”¹æˆå°å†™ï¼Œåœ¨æŒ‡å®šæ•°æ®åº“时必™åÕd…¨éƒ¨ä‹É用小写名字，否则不会起作用ã€?

å…ÏxŽ‰˜q™ä¸ªåŠŸèƒ½åªéœ€è¦åœ¨ä»–前面加ä¸?å?

#log-bin

开启慢查询日志( slow query log ) 慢查询日志对于跟ítªæœ‰é—®é¢˜çš„查询非常有用。它记录所有查˜q‡long_query_time的查询，如果需要，˜q˜å¯ä»¥è®°å½•ä¸ä½¿ç”¨ç´¢å¼•çš„记录。下面是一个慢查询日志的例子：

开启慢查询日志åQŒéœ€è¦è®¾¾|®å‚æ•°log_slow_queries、long_query_times、log-queries-not-using-indexesã€?

log_slow_queries指定日志文äšgåQŒå¦‚果不提供文äšg名，MySQLž®†è‡ªå·×ƒñ”生缺省文件名。long_query_times指定慢查询的阈å€û|¼Œ¾~ºçœæ˜?0¿U’。log-queries-not-using-indexesæ˜?.1.0以后引入的参敎ͼŒå®ƒæŒ‡½Cø™®°å½•ä¸ä½¿ç”¨ç´¢å¼•çš„查询。笔者设¾|®long_query_time=10

½W”者设¾|?

sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4

参数说明:

back_log

要求MySQL能有的连接数量。当主要MySQL¾U¿ç¨‹åœ¨ä¸€ä¸ªå¾ˆçŸ­æ—¶é—´å†…得到非常多的˜qžæŽ¥è¯äh±‚åQŒè¿™ž®Þp“v作用åQŒç„¶åŽä¸»¾U¿ç¨‹èŠ×ƒº›æ—‰™—´(ž®½ç®¡å¾ˆçŸ­)‹‚€æŸ¥è¿žæŽ¥åƈ且启动一个新¾U¿ç¨‹ã€‚back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短旉™—´å†…有很多˜qžæŽ¥åQŒä½ éœ€è¦å¢žåŠ å®ƒåQŒæ¢å¥è¯è¯ß_¼Œ˜q™å€¼å¯¹åˆ°æ¥çš„TCP/IP˜qžæŽ¥çš„侦听队列的大小。你的操作系¾lŸåœ¨˜q™ä¸ªé˜Ÿåˆ—大小上有它自å·Þqš„限制ã€?Unix listen(2)¾pȝ»Ÿè°ƒç”¨çš„手册页应该有更多的¾l†èŠ‚。检查你的OS文档扑ևº˜q™ä¸ªå˜é‡çš„最大倹{€‚试图设定back_log高于你的操作¾pȝ»Ÿçš„限制将是无效的ã€?

max_connections

òq¶å‘˜qžæŽ¥æ•°ç›®æœ€å¤§ï¼Œ120 ­‘…过˜q™ä¸ªå€¼å°±ä¼šè‡ªåŠ¨æ¢å¤ï¼Œå‡ÞZº†é—®é¢˜èƒ½è‡ªåŠ¨è§£å†?

thread_cache

没找到具体说明，不过讄¡½®ä¸?2å?20天才创徏äº?00多个¾U¿ç¨‹ 而以前一天就创徏了上千个¾U¿ç¨‹ 所以还是有用的

thread_concurrency

#讄¡½®ä¸ÞZ½ çš„cpuæ•°ç›®x2,例如åQŒåªæœ‰ä¸€ä¸ªcpu,那么thread_concurrency=2
#�个cpu,那么thread_concurrency=4
skip-innodb
#åŽÀLŽ‰innodb支持

代码:

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.


# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs


# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
myisam_sort_buffer_size = 1M
max_connections=120
#addnew config
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
skip-innodb
skip-bdb
skip-name-resolve
join_buffer_size=512k
query_cache_size = 32M
interactive_timeout=120
long_query_time=10
log_slow_queries= /usr/local/mysql4/logs/slow_query.log
query_cache_type= 1
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4


#end new config
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking


# Replication Master Server (default)
# binary logging is required for replication
#log-bin


# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1


# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host =
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user =
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port =
#
# binary logging - not required for slaves, but recommended
#log-bin


# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname


# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000


# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50


[mysqldump]
quick
max_allowed_packet = 16M


[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates


[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M


[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M


[mysqlhotcopy]
interactive-timeout

补充

优化table_cachetable_cache指定表高速缓存的大小。每当MySQL讉K—®ä¸€ä¸ªè¡¨æ—Óž¼Œå¦‚果在表¾~“冲åŒÞZ¸­˜q˜æœ‰½Iºé—´åQŒè¯¥è¡¨å°±è¢«æ‰“å¼€òq¶æ”¾å…¥å…¶ä¸­ï¼Œ˜q™æ ·å¯ä»¥æ›´å¿«åœ°è®¿é—®è¡¨å†…容。通过‹‚€æŸ¥å³°å€¼æ—¶é—´çš„状态值Open_tableså’ŒOpened_tablesåQŒå¯ä»¥å†³å®šæ˜¯å¦éœ€è¦å¢žåŠ table_cache的倹{€‚如果你发现open_tables½{‰äºŽtable_cacheåQŒåƈ且opened_tables在不断增长，那么你就需要增加table_cacheçš„å€égº†åQˆä¸Š˜q°çŠ¶æ€å€¼å¯ä»¥ä‹É用SHOW STATUS LIKE ‘Open%tables’获得åQ‰ã€‚注意，不能盲目地把table_cache讄¡½®æˆå¾ˆå¤§çš„倹{€‚如果设¾|®å¾—太高åQŒå¯èƒ½ä¼šé€ æˆæ–‡äšg描述½W¦ä¸­‘»I¼Œä»Žè€Œé€ æˆæ€§èƒ½ä¸ç¨³å®šæˆ–者连接失败。对于有1G内存的机器，推荐值是128åQ?56ã€?

案例1åQšè¯¥æ¡ˆä¾‹æ¥è‡ªä¸€ä¸ªä¸æ˜¯ç‰¹åˆ«ç¹å¿™çš„服务器table_cache – 512open_tables – 103opened_tables – 1273uptime – 4021421 (measured in seconds)该案例中table_cacheä¼ég¹Žè®„¡½®å¾—太高了。在峰值时é—ß_¼Œæ‰“开表的数目比table_cache要少得多ã€?

案例2åQšè¯¥æ¡ˆä¾‹æ¥è‡ªä¸€å°å¼€å‘服务器。table_cache – 64open_tables – 64opened-tables – 431uptime – 1662790 (measured in seconds)虽然open_tables已经½{‰äºŽtable_cacheåQŒä½†æ˜¯ç›¸å¯¹äºŽæœåŠ¡å™¨è¿è¡Œæ—¶é—´æ¥è¯ß_¼Œopened_tablesçš„å€ég¹Ÿéžå¸¸ä½Žã€‚因此，增加table_cache的值应该用处不大。案ä¾?åQšè¯¥æ¡ˆä¾‹æ¥è‡ªä¸€ä¸ªupderperforming的服务器table_cache – 64open_tables – 64opened_tables – 22423uptime – 19538该案例中table_cache讄¡½®å¾—太低了。虽然运行时间不åˆ?ž®æ—¶åQŒopen_tables辑ֈ°äº†æœ€å¤§å€û|¼Œopened_tablesçš„å€ég¹Ÿéžå¸¸é«˜ã€‚这样就需要增加table_cache的倹{€‚优化key_buffer_sizekey_buffer_size指定索引¾~“冲区的大小åQŒå®ƒå†›_®šç´¢å¼•å¤„理的速度åQŒå°¤å…¶æ˜¯ç´¢å¼•è¯Èš„速度。通过‹‚€æŸ¥çŠ¶æ€å€¼Key_read_requestså’ŒKey_readsåQŒå¯ä»¥çŸ¥é“key_buffer_size讄¡½®æ˜¯å¦åˆç†ã€‚比例key_reads / key_read_requests应该ž®½å¯èƒ½çš„低，臛_°‘æ˜?:100åQ?:1000更好åQˆä¸Š˜q°çŠ¶æ€å€¼å¯ä»¥ä‹É用SHOW STATUS LIKE ‘key_read%’获得åQ‰ã€‚key_buffer_size只对MyISAM表è“v作用。即使你不ä‹É用MyISAM表，但是内部的äÍ时磁盘表是MyISAM表，也要使用该倹{€‚可以ä‹É用检查状态值created_tmp_disk_tables得知详情。对äº?G内存的机器，如果不ä‹É用MyISAM表，推荐值是16MåQ?-64MåQ‰ã€?

案例1åQšå¥åºïLŠ¶å†µkey_buffer_size – 402649088 (384M)key_read_requests – 597579931key_reads - 56188案例2åQšè­¦æŠ¥çŠ¶æ€key_buffer_size – 16777216 (16M)key_read_requests – 597579931key_reads - 53832731案例1中比例低äº?:10000åQŒæ˜¯å¥åº·çš„情况；案例2中比例达åˆ?:11åQŒè­¦æŠ¥å·²¾læ‹‰å“ã€?