mysql服务器优化

关键字：mysql服务器优化

常用的命令：

查看目前状态：SHOW STATUS LIKE 'qcache%';
查看缓存总大小：SHOW VARIABLES LIKE 'query_cache_size';

修复碎片：RESET QUERY CACHE
清理已经使用的内存：FLUSH QUERY CACHE

重置计数器：FLUSH STATUS;



说明：本博客只限于mysql服务器的调优，不包含其他优化，后续将会在本文的基础上进行积累。


一、对查询进行缓存

很多 LAMP 应用程序都严重依赖于数据库，但却会反复执行相同的查询。每次执行查询时，数据库都必须要执行相同的工作 —— 对查询进行分析（区分大小写），确定如何执行查询，从磁盘中加载信息，然后将结果返回给客户机。MySQL 有一个特性称为查询缓存，它将（后面会用到的）查询结果保存在内存中。在很多情况下，这会极大地提高性能。不过，问题是查询缓存在默认情况下是禁用的。

将 query_cache_size = 32M（默认0） 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存（需要重启mysql）。

查看query_cache_size设置大小可用：SHOW VARIABLES LIKE 'query_cache_size';（单位byte）


查看服务器是否支持 缓存 用：SHOW VARIABLES LIKE 'have_query_cache';


移除所有已经使用的缓存（释放已使用的内存）：RESET QUERY CACHE;

监视查询缓存

在启用查询缓存之后，重要的是要理解它是否得到了有效的使用。MySQL 有几个可以查看的变量，可以用来了解缓存中的情况。清单 2 给出了缓存的状态。


清单 2. 显示查询缓存的统计信息

              

mysql> SHOW STATUS LIKE 'qcache%';

+-------------------------+------------+

| Variable_name           | Value      |

+-------------------------+------------+

| Qcache_free_blocks      | 5216       |

| Qcache_free_memory      | 14640664   |

| Qcache_hits             | 2581646882 |

| Qcache_inserts          | 360210964  |

| Qcache_lowmem_prunes    | 281680433  |

| Qcache_not_cached       | 79740667   |

| Qcache_queries_in_cache | 16927      |

| Qcache_total_blocks     | 47042      |

+-------------------------+------------+

8 rows in set (0.00 sec)


这些项的解释如表 1 所示。


表 1. MySQL 查询缓存变量




变量名说明


Qcache_free_blocks
缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。


Qcache_free_memory
缓存中的空闲内存。

Qcache_hits
每次查询在缓存中命中时就增大。

Qcache_inserts
每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。


Qcache_lowmem_prunes （这个变量可以判断你的缓存大小是否合理）
缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。

Qcache_not_cached
不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。


Qcache_queries_in_cache
当前缓存的查询（和响应）的数量。

Qcache_total_blocks
缓存中块的数量。










下面是在网上看到的一篇非常好的博客：有时间一一验证







MySQL优化经验 key_buffer_size,query_cache_size,ta【 来源：网络 作者：佚名 更新时间：2009-03-04 | 字体：大 中 小】
同时在线访问量继续增大 对于1G内存的服务器明显感觉到吃力严重时甚至每天都会死机 或者时不时的服务器卡一下 这个问题曾经困扰了我半个多月MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。
8 a/ y6 g; @& D1 [+ Y9 u8 j) g) N" J) i/ n1 A0 b
安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境，当然需要有不同的配制文件了。 * W  `% m. m- M
) U. s4 C% w, S5 B, N
一般的情况下，my-medium.cnf这个配制文件就能满足我们的大多需要；一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了，使用mysqladmin variables extended-status –u root –p 可以看到目前的参数，有３个配置参数是最重要的，即key_buffer_size,query_cache_size,table_cache。 林海谐缘论坛' ^) w$ V. l' W/ k( c" C, j
www.linwan.net6 Y. g6 |. ?0 P% H% l
key_buffer_size只对MyISAM表起作用，
; p3 _5 N; ?# # d. E
. B& O( j! M8 l4 W% S4 okey_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，以下的状态值都是本人通过phpmyadmin获得的实例分析:
5 x0 ~/ w# 3 t) j9 _www.linwan.net
: k) [0 a2 Z2 X5 A! E! m% }这个服务器已经运行了20天
2 h& h: w% k* r$ n: I2 u+ K% V6 N- D打造技术与娱乐的天作之合林海谐缘论坛" e% V. d( f) M, X. z; G9 W
key_buffer_size – 128M
  s7 ^2 k' ]: V6 t4 ( fwww.linwan.netkey_read_requests – 650759289
/ B/ {# @. 5 @( H9 p打造技术与娱乐的天作之合key_reads - 79112 www.linwan.net8 Y4 v; q7 Y$ ~
打造技术与娱乐的天作之合6 O. Q; P5 O) H+ l: e8 P* ]4 a" l
比例接近1:8000 健康状况非常好 ) P" h: r3 j- M& k, O  ]

6 [3 Z" F* b2 z* g$ @% l$ Jwww.linwan.net另外一个估计key_buffer_size的办法　把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。 www.linwan.net/ n' V& i3 P, o$ T

* @" Y- b' o: c% r1 d从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。 林海谐缘论坛: }" M. [4 W0 X  k) u( {; r

* Y- X% L9 a7 R0 }+ w通过调节以下几个参数可以知道query_cache_size设置得是否合理
6 R+ h6 D- D2 A/ xwww.linwan.net
: K5 E8 h* ]" Q& ^% _www.linwan.netQcache inserts " Y' }, N& d# v2 t9 `& }0 F
Qcache hits
' ! r  p8 y% S) Y; C打造技术与娱乐的天作之合Qcache lowmem prunes 7 K) ]9 S; R5 p6 M# J
Qcache free blocks
$ Q1 T. L) ]0 a/ N; y5 qwww.linwan.netQcache total blocks
- X( W& t( K4 c' _$ s4 d林海谐缘论坛# t: w' t6 l2 l8 d5 k7 E
Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。 www.linwan.net. o! ?) W3 {' j' }/ N& 2 t4 A1 |

4 |, y3 J; O! C7 TQcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲
3 M% F( N5 [* v$ y5 ]! |+ ?打造技术与娱乐的天作之合1 w8 L$ v: ]' M( V
我设置: ! y% x: l# g( `- o& ?8 Y0 m

8 }+ ]5 J. Q) P6 O% S9 uquery_cache_size = 32M www.linwan.net6 }( u; W; S( X2 m: T
query_cache_type= 1 www.linwan.net! m. m1 f# }/ y. ]
, r$ r; v0 T1 A. ~5 O0 T
得到如下状态值: 打造技术与娱乐的天作之合% f6 ~& n, B' |/ [
+ D( O3 X2 Y# j9 Y$ {9 F
Qcache queries in cache 12737 表明目前缓存的条数
0 g' R! Q% _( |5 x, QQcache inserts 20649006
+ ?( x" `6 H# }5 B# I/ Hwww.linwan.netQcache hits 79060095 　看来重复查询率还挺高的 $ [- Q% z7 {9 M/ f; ]5 l" m; q' [
Qcache lowmem prunes 617913　有这么多次出现缓存过低的情况 : z. f+ C8 K) v; {) i2 F
Qcache not cached 189896 　　 www.linwan.net8 e  ]7 m* x3 d' R+ G8 I
Qcache free memory 18573912　　目前剩余缓存空间 . w% a3 U% V1 a" o+ m: ^$ G
Qcache free blocks 5328 这个数字似乎有点大　碎片不少 1 p; u, D1 k5 e$ m
Qcache total blocks 30953 林海谐缘论坛( x1 P$ Z6 e1 S) J9 u3 e& i- K
4 g( u$ D- h; F
如果内存允许32M应该要往上加点
2 h+ e8 q7 u3 x4 }% Cwww.linwan.net
3 m# Q3 {. E* v8 btable_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。 " Q  H$ Y) i3 K9 U* U+ B+ a

2 x2 ?" F. O/ |& `+ p2 Mwww.linwan.net对于有1G内存的机器，推荐值是128－256。
6 @5 S* E* R6 A0 b+ T林海谐缘论坛! N1 Q6 ^8 T: ^! z
笔者设置table_cache = 256
' f: J- t. a- H+ h9 `
) O5 x( 4 n% K4 b+ H* e得到以下状态:
& V9 J; q4 $ a$ Swww.linwan.net
. P# o" g) # `$ a- `打造技术与娱乐的天作之合Open tables 256 4 ~3 A2 L- W( ?4 ?8 W1 x* A
Opened tables 9046 打造技术与娱乐的天作之合1 {6 ~+ M. G1 ~  X3 y- u

2 l& f# f; ]( A) ~9 V- F4 F. |www.linwan.net虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说,已经运行了20天，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大table_cache。
% u( P8 s# E( a) N- x
2 Y. d% O- H+ |如果你不需要记录2进制log 就把这个功能关掉，注意关掉以后就不能恢复出问题前的数据了，需要您手动备份，二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。
& R  {1 c- a4 ~% X林海谐缘论坛打造技术与娱乐的天作之合1 ]1 }' ~* L7 h
log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引，每次启动服务时，都会重新生成一个新的二进制文件。此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定记录的数据库；使用binlog-ignore-db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。
8 e# l% e, m# C& f0 x0 q7 H0 A9 A# G/ ]/ }+ q
关掉这个功能只需要在他前面加上#号 林海谐缘论坛7 e* J5 N( O) C: I/ j7 i

8 ~* v* ~! G# _林海谐缘论坛#log-bin / R( Y8 ~# |9 j
% F2 N. n; a, q9 i; t& c5 Y
开启慢查询日志( slow query log ) 慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询，如果需要，还可以记录不使用索引的记录。下面是一个慢查询日志的例子： ; H; {; A! x+ W5 u, e, t* e

+ D$ I/ A/ K/ n# P  B8 Y9 vwww.linwan.net开启慢查询日志，需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。 www.linwan.net' Z. C: F5 k7 P" m! Q7 R

( L. f6 G3 X% b5 qlog_slow_queries指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的参数，它指示记录不使用索引的查询。笔者设置long_query_time=10 www.linwan.net& `, J, @" ]4 I& @3 I2 m, l& G
打造技术与娱乐的天作之合) Y$ N: n. u1 x5 K4 & L! {$ ?+ s
笔者设置:
1 e* U5 ?0 ?, V% U4 `3 }8 S3 B林海谐缘论坛' X" K2 f! e) Z# P
sort_buffer_size = 1M $ a( K3 A1 @' g+ k0 g
max_connections=120 林海谐缘论坛* T" [! e  P. H* g4 h3 o' _
wait_timeout =120
* a; U7 l$ o  f" c$ a$ f% Q% tback_log=100
8 `  F0 [" P+ R0 Q' J/ N打造技术与娱乐的天作之合read_buffer_size = 1M
6 d* T0 H+ R$ q8 O; Zthread_cache=32
. a, J6 q$ H% X- O, j" z" |打造技术与娱乐的天作之合interactive_timeout=120 ( m" @& X3 Z* L! R7 J0 C
thread_concurrency = 4
) m& L: N. {# |6 f4 B+ X! R林海谐缘论坛林海谐缘论坛/ {; y* n) A, t+ Y
参数说明: 打造技术与娱乐的天作之合$ `6 |% L2 [7 V: J- W( H

' K2 H* A; T: `3 @( ?' |林海谐缘论坛back_log , v/ f, t. n$ K' c: o
; K+ G  C- B% f7 G1 _( L
要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
3 z$ }2 ^' ?5 T6 w: Kwww.linwan.net; a3 ^* D0 U4 q5 F" R& _
max_connections
5 g& ?& [. a$ a6 @: C1 , ^8 ]  R8 z! E打造技术与娱乐的天作之合www.linwan.net. A5 {5 z1 g* ~" ?0 L
并发连接数目最大，120 超过这个值就会自动恢复，出了问题能自动解决 , g$ Z/ U7 h% A) Q3 S& G
2 i3 @- I2 u3 @  j, y8 H2 P
thread_cache 林海谐缘论坛) s) S( ?4 `* D3 K; # F

, K/ t7 Q( S0 M* G+ f0 ?+ L  Q林海谐缘论坛没找到具体说明，不过设置为32后 20天才创建了400多个线程 而以前一天就创建了上千个线程 所以还是有用的
" F% x4 K+ P; g+ `4 t4 }. q: c
3 D! m- ~% H: o$ ~: c9 j; dthread_concurrency
: U( s% Z! b  x! x0 b# q$ {+ d
+ m# P; T6 h3 r% J9 B! bwww.linwan.net#设置为你的cpu数目x2,例如，只有一个cpu,那么thread_concurrency=2 . U5 W7 m6 f3 R) U: e+ U
#有2个cpu,那么thread_concurrency=4 www.linwan.net1 E* u/ R1 ?0 G3 N5 n/ {
skip-innodb
% p2 w/ f! I1 b5 {www.linwan.net#去掉innodb支持
6 z4 U5 ' L6 Y( m6 p3 X3 @www.linwan.net' @0 }# q' d" v, @: B
代码:
' D6 P2 l5 y8 A1 o4 w  {0 @( V& q0 L0 E9 _) u% c7 I
# Example MySQL config file for medium systems.
% @5 [7 m& g. a#   l) {: t3 b& h5 E3 N! x
# This is for a system with little memory (32M - 64M) where MySQL plays
1 Y4 s# p9 e, W3 S& ]# an important part, or systems up to 128M where MySQL is used together with
" b( 4 O0 g% @. x" L7 Uwww.linwan.net# other programs (such as a web server) 打造技术与娱乐的天作之合* D& `0 ^, X3 I
# 林海谐缘论坛- V8 P+ p6 H' n- _& K. s$ n; }8 p
# You can copy this file to
9 ^" ]; r2 ?  H4 T& O6 jwww.linwan.net# /etc/my.cnf to set global options,
2 V6 c4 n9 |- c! r% t, Q" N. g6 Z林海谐缘论坛# mysql-data-dir/my.cnf to set server-specific options (in this www.linwan.net6 O& a$ @4 n$ t+ {! S# h
# installation this directory is /var/lib/mysql) or www.linwan.net, {4 i3 t4 g. O# @. H8 ~( I
# ~/.my.cnf to set user-specific options. " w. W& e8 ?9 F" q9 m
#
3 z3 P# U! }1 L2 r0 g0 Bwww.linwan.net# In this file, you can use all long options that a program supports.
3 c( K9 ~9 @4 S, |1 T* K; s" p林海谐缘论坛# If you want to know which options a program supports, run the program
, [2 U2 S8 B3 I5 [" B4 Q) G. N# with the "--help" option.
9 F0 U: H8 T3 M' b1 ]' G' y打造技术与娱乐的天作之合林海谐缘论坛" ~/ H1 v* b7 T; P
# The following options will be passed to all MySQL clients 打造技术与娱乐的天作之合$ I5 u% f8 ^5 z  E1 _; P0 ]
[client]
2 W" k8 Z; N8 [1 i" B#password = your_password
6 ~3 I$ k' e" b- s3 V1 s打造技术与娱乐的天作之合port = 3306
8 X( P- o9 J( |) H# w4 p6 hwww.linwan.netsocket = /tmp/mysql.sock
) n+ W' z9 t% D" G#socket = /var/lib/mysql/mysql.sock
) Z& C7 `  S9 M4 f6 F! J6 u打造技术与娱乐的天作之合# Here follows entries for some specific programs
; L, {8 f% S  }6 l7 e# ~+ w/ x3 ^打造技术与娱乐的天作之合www.linwan.net$ e) F9 s; U+ m
# The MySQL server
3 w% A. y" H6 }- ^- ?[mysqld] 打造技术与娱乐的天作之合% H; n: O" |% T' }* R1 G  ]
port = 3306 打造技术与娱乐的天作之合5 Z7 {4 S7 @) B) r& H7 R
socket = /tmp/mysql.sock
* E4 L& L! W5 i- Q: @0 Vwww.linwan.net#socket = /var/lib/mysql/mysql.sock
2 G5 I+ w' X/ Y% x% O* Fskip-locking
6 v7 C4 e3 f( ~) p* Pkey_buffer = 128M
- Q' z* $ G6 X/ K0 ?7 H林海谐缘论坛max_allowed_packet = 1M 林海谐缘论坛& x6 h, c3 f3 M$ C
table_cache = 256
. c3 g$ b( S' G! y, ~6 g9 U5 u$ L6 Bsort_buffer_size = 1M . Q3 L! u& c" s! p0 Y2 p, H
net_buffer_length = 16K
; P( b3 {$ J9 I% Emyisam_sort_buffer_size = 1M
; v  C, p; w7 [* g/ Jmax_connections=120
. ~/ s* T9 Q0 H7 U6 www.linwan.net#addnew config www.linwan.net! b+ k7 [: r/ k
wait_timeout =120
; t  u) L; Y& X1 lback_log=100
, S( T: t+ D6 w# Ywww.linwan.netread_buffer_size = 1M 林海谐缘论坛! F5 U5 t$ f8 t+ p, @  Y& i
thread_cache=32 : l1 M( g1 k/ s; e# G( O2 T/ H2 s
skip-innodb
+ c8 R/ i; }1 i. Q8 _skip-bdb
8 w* S) ^, L5 V0 U4 V5 ]www.linwan.netskip-name-resolve 林海谐缘论坛1 V" B3 K7 V+ B7 m
join_buffer_size=512k & K7 W9 W5 K5 x
query_cache_size = 32M / y/ ?0 X. S: C. B# N1 [
interactive_timeout=120
* T# x# F' J$ O+ mlong_query_time=10
5 p8 |- f  j% N* [log_slow_queries= /usr/local/mysql4/logs/slow_query.log
& M; U( L- }4 F( g( H& f9 `打造技术与娱乐的天作之合query_cache_type= 1 林海谐缘论坛5 k! I0 W' `( 5 r1 d. u2 k4 N6 J
# Try number of CPU's*2 for thread_concurrency 6 U6 V1 f9 Q9 R; F7 q
thread_concurrency = 4
/ X; a( X! G8 t7 ~; R$ _' I打造技术与娱乐的天作之合& i0 [0 D2 V* ?! f+ n; T+ k
#end new config 0 ~! Q" U1 T3 I, R! A/ u2 v; l$ O
# Don't listen on a TCP/IP port at all. This can be a security enhancement, www.linwan.net- l1 T1 r& g, b( V  U1 x8 M: v; E
# if all processes that need to connect to mysqld run on the same host. 林海谐缘论坛( u& @; c$ }1 d( i8 e
# All interaction with mysqld must be made via Unix sockets or named pipes.
. l7 e3 {- Y9 J2 I打造技术与娱乐的天作之合# Note that using this option without enabling named pipes on Windows www.linwan.net% z, W7 x2 t# y$ e2 B
# (via the "enable-named-pipe" option) will render mysqld useless! * S! @' z% w% ]' Q% R$ g0 {& v9 Y9 [
# 林海谐缘论坛" K* ~2 }5 P' e2 f2 r
#skip-networking 打造技术与娱乐的天作之合6 2 X# M0 [( q& w0 G( m+ A5 T. ]

6 p7 }/ W9 c3 d9 M3 _林海谐缘论坛# Replication Master Server (default)
$ J+ f  T% Z( S# i$ Y# binary logging is required for replication
1 X! e$ y7 X& O林海谐缘论坛#log-bin
& n* C2 |* k" F7 u7 U% M( v
# c' x* y6 A  u4 d林海谐缘论坛# required unique id between 1 and 2^32 - 1
+ O, _/ B0 W6 L3 n9 S" K. M打造技术与娱乐的天作之合# defaults to 1 if master-host is not set
  `- ! S( T. s1 j# but will not function as a master if omitted
. [( q: H1 s0 B8 N. N打造技术与娱乐的天作之合server-id = 1
7 k/ s8 `& J7 S3 J林海谐缘论坛打造技术与娱乐的天作之合" s  L4 e* x$ D6 A2 Q
# Replication Slave (comment out master section to use this) 打造技术与娱乐的天作之合1 U* {2 ]4 y7 ]" ]2 U  D" v
# . g$ V8 ]. [/ y+ T) v$ o8 x4 U# w
# To configure this host as a replication slave, you can choose between
, F; k; y! P7 V! J8 n打造技术与娱乐的天作之合# two methods : 林海谐缘论坛0 l% I$ D3 |$ b
# www.linwan.net' p0 P! y0 e* 6 j' t
# 1) Use the CHANGE MASTER TO command (fully described in our manual) - & |! G# V( e+ }9 t; Z
# the syntax is:
" ^( R5 p8 j1 bwww.linwan.net#
9 z6 u7 T$ P2 [3 f& o# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, 林海谐缘论坛' O) R; Z% Z9 l* v/ b9 k( }/ ?
# MASTER_USER=, MASTER_PASSWORD= ; 林海谐缘论坛* J& s$ g" v- u# s5 z
# 打造技术与娱乐的天作之合; q! e) ?) n: t" [4 M
# where you replace , , by quoted strings and ; W; z( $ }, P; t4 x. f6 ]: @
# by the master's port number (3306 by default). 0 g6 q' t0 z& O& F% Q# ?; i
# * D) z1 y( M' ?
# Example: 打造技术与娱乐的天作之合/ C$ K6 ^; c7 t6 L; h) G
# 打造技术与娱乐的天作之合' - ~- f/ [) 8 [* A
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, 3 [) a  n3 X% B5 n# O$ s/ A: b
# MASTER_USER='joe', MASTER_PASSWORD='secret';
0 @* C$ b7 l  ^4 ewww.linwan.net#
8 E& i# {9 M0 e$ `打造技术与娱乐的天作之合# OR # r- n2 b9 @7 l/ q
#
5 d" y; T% @' Y6 X' J# 2) Set the variables below. However, in case you choose this method, then
; }0 k& n5 Y" }# h2 i6 |www.linwan.net# start replication for the first time (even unsuccessfully, for example
4 o% _$ g& E2 R( n; J# if you mistyped the password in master-password and the slave fails to 3 C8 O+ H. _; t# u: e& L
# connect), the slave will create a master.info file, and any later
/ f3 h  n' U2 k  W+ Cwww.linwan.net# change in this file to the variables' values below will be ignored and 林海谐缘论坛9 e5 [) G# e. H7 e
# overridden by the content of the master.info file, unless you shutdown 林海谐缘论坛* m0 o& u8 z% s. J+ r
# the slave server, delete master.info and restart the slaver server.
$ x% d2 p6 Y+ l0 i% T% kwww.linwan.net# For that reason, you may want to leave the lines below untouched
7 O; e! _) K6 `$ j) M# (commented) and instead use CHANGE MASTER TO (see above) www.linwan.net3 x3 S; - e$ V4 j& O6 s
# 林海谐缘论坛# l  G* @# c" @6 O
# required unique id between 2 and 2^32 - 1
- c* _# J* V  * H; M# (and different from the master) 林海谐缘论坛7 R- }' . x5 w( @% {. O, B" Q; a# N  o
# defaults to 2 if master-host is set
' l7 P% O  |5 Q7 x林海谐缘论坛# but will not function as a slave if omitted www.linwan.net" {! a6 q8 l' c/ H; X5 l* K
#server-id = 2 打造技术与娱乐的天作之合) f1 c. H* b9 d% z' v$ {
# 打造技术与娱乐的天作之合* B5 A/ b2 X0 K1 I" B; T. j- E3 u$ N
# The replication master for this slave - required
/ ^$ v! q2 b) w0 E3 V- v& mwww.linwan.net#master-host =
  a+ b9 O# J* q  i2 j$ t打造技术与娱乐的天作之合# www.linwan.net8 G# d8 H. H/ r+ I, d: |/ T4 G
# The username the slave will use for authentication when connecting
% [3 }. [$ A4 z: l# to the master - required
# N' r6 i" K7 ^' a- X#master-user = www.linwan.net/ q8 `9 v& L$ n& ~. [4 |/ z
#
8 J- {7 n; y: h9 M/ P8 |7 _- d打造技术与娱乐的天作之合# The password the slave will authenticate with when connecting to 6 N8 Q/ Y% h$ v- O6 {
# the master - required 打造技术与娱乐的天作之合/ G2 I% X  N( ~
#master-password =
' f4 b6 V* X; Q  M' A) c! xwww.linwan.net# 6 C0 X% H1 Z  S* b
# The port the master is listening on.
7 e1 F) G6 M5 s! G! V9 Bwww.linwan.net# optional - defaults to 3306 8 Q' C* g" J+ w$ ~7 {1 X, K0 x" v
#master-port =
4 T- h) A9 b# M" p. z- J+ Cwww.linwan.net# 打造技术与娱乐的天作之合, i/ X. |( Q+ N% L7 f! F
# binary logging - not required for slaves, but recommended 打造技术与娱乐的天作之合1 Y1 A4   k. j& D/ O
#log-bin
) H$ f% }3 b# {3 k* K
8 ?8 $ }" y( d# Point the following paths to different dedicated disks
) [! F! k/ v$ I* q* W4 Q#tmpdir = /tmp/ 林海谐缘论坛3 L) g+ m- X, y4 K  {! n9 a
#log-update = /path-to-dedicated-directory/hostname 林海谐缘论坛5 }5 A  D5 9 y# W0 j! z6 S% y& |

% ?' }9 g" U! A: g5 }# Uncomment the following if you are using BDB tables ) [$ v0 f5 v+ t( s
#bdb_cache_size = 4M 打造技术与娱乐的天作之合& }& R* O7 ~5 B3 F$ M& C
#bdb_max_lock = 10000
1 R1 t# P4 h& [. O4 @  q  ^打造技术与娱乐的天作之合3 a7 G5 _" E( {4 i) m' a
# Uncomment the following if you are using InnoDB tables 0 w6 a1 P2 i- ^# [
#innodb_data_home_dir = /var/lib/mysql/
1 q- j; q8 k0 X3 L/ a6 n* #innodb_data_file_path = ibdata1:10M:autoextend " E1 }7 b: M  X8 d6 y2 a3 M
#innodb_log_group_home_dir = /var/lib/mysql/
: O0 I$ H& A) ]# P. A/ b8 T#innodb_log_arch_dir = /var/lib/mysql/ www.linwan.net9 y0 E6 T2 O) `' ?( O4 D1 n
# You can set .._buffer_pool_size up to 50 - 80 %
8 q. O* H: D6 f6 G: B+ [打造技术与娱乐的天作之合# of RAM but beware of setting memory usage too high 6 3 b& h" }" t7 U+ @
#innodb_buffer_pool_size = 16M ( Z4 b* S0 U, n! O! D6 C+ r, b
#innodb_additional_mem_pool_size = 2M 打造技术与娱乐的天作之合. U. b5 r; s2 c! m8 @$ i1 ]
# Set .._log_file_size to 25 % of buffer pool size 打造技术与娱乐的天作之合- U* V; V& c0 W
#innodb_log_file_size = 5M 林海谐缘论坛5 X3 G, v/ B! D# D, G' x
#innodb_log_buffer_size = 8M www.linwan.net" N- v* O8 h9 P# H2 Q
#innodb_flush_log_at_trx_commit = 1 8 D: s& }5 b+ D( E; K
#innodb_lock_wait_timeout = 50
* S* ?# O- |5 O1 t! _8 ]
) @8 R* O& p: c9 |1 h. f" o打造技术与娱乐的天作之合[mysqldump]
( H# X+ R4 W- z' |. N& W林海谐缘论坛quick
3 H8 z+ F& A- ]& `: R7 cmax_allowed_packet = 16M $ m2 E  ^) [( L, a" U  T

+ @/ D/ l: u, l( }2 C) t- p打造技术与娱乐的天作之合[mysql] 4 ]: a  `! M6 i
no-auto-rehash ) ?8 A( l( ], ?6 v5 t& Y
# Remove the next comment character if you are not familiar with SQL www.linwan.net6 I4 t& I  f" E! |$ o
#safe-updates
& ~. J+ [& D: U6 o: x% @林海谐缘论坛( _: F) a1 N5 p( Y5 d. p
[isamchk] www.linwan.net3 t& D' T- ?+ Z% X3 U; w! v* P
key_buffer = 20M
6 i5 Y1 f5 }; V9 v9 {3 @* H7 ~sort_buffer_size = 20M
$ C: y" I5 J: o% h; x2 l9 G打造技术与娱乐的天作之合read_buffer = 2M
8 R- O! c6 O4 |3 u- Xwrite_buffer = 2M 打造技术与娱乐的天作之合5 v$ J, r4 k7 z) ^2 {0 A

, z. Y5 f3 x- |& a[myisamchk]
' j0 h3 X3 {0 z2 u打造技术与娱乐的天作之合key_buffer = 20M
5 Y) W7 K1 j1 Y# jsort_buffer_size = 20M
) W) X0 m* z) {7 l$ ?read_buffer = 2M
( b+ z% E$ Z* y- d- iwrite_buffer = 2M 林海谐缘论坛, c$ }6 y0 |- u6 r* C2 l" t: a
% [; V' G2 _; n8 v
[mysqlhotcopy]
' @- d0 r; B5 Z& e. W7 ?www.linwan.netinteractive-timeout打造技术与娱乐的天作之合3 n9 h1 D& |8 V0 R/ @8 O/ n
林海谐缘论坛! S! P$ T* n9 s
补充 www.linwan.net8 X! J7 H& e  f

% z" G( {5 v) G6 ]; H$ b/ wwww.linwan.net优化table_cachetable_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。对于有1G内存的机器，推荐值是128－256。
2 f5 |0 O4 G6 G$ ~; ~1 J: P$ ~; D# D# `6 V, h0 W* u( _  & f
案例1：该案例来自一个不是特别繁忙的服务器table_cache – 512open_tables – 103opened_tables – 1273uptime – 4021421 (measured in seconds)该案例中table_cache似乎设置得太高了。在峰值时间，打开表的数目比table_cache要少得多。 www.linwan.net8 $ O) X- S1 j+ - Q( C
打造技术与娱乐的天作之合, N( m9 S- n. Z) f9 u
案例2：该案例来自一台开发服务器。table_cache – 64open_tables – 64opened-tables – 431uptime – 1662790 (measured in seconds)虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。案例3：该案例来自一个upderperforming的服务器table_cache – 64open_tables – 64opened_tables – 22423uptime – 19538该案例中table_cache设置得太低了。虽然运行时间不到6小时，open_tables达到了最大值，opened_tables的值也非常高。这样就需要增加table_cache的值。优化key_buffer_sizekey_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。 ( P: g: _& M0 p; S, D6 f8 H% ~# Y
- ^' F) 6 r: O; A/ F6 g; E
案例1：健康状况key_buffer_size – 402649088 (384M)key_read_requests – 597579931key_reads - 56188案例2：警报状态key_buffer_size – 16777216 (16M)key_read_requests – 597579931key_reads - 53832731案例1中比例低于1:10000，是健康的情况；案例2中比例达到1:11，警报已经拉响。

原文：http://www.myispace.cn/posts/b3d46ecebf95473c9e20902176881d2b.htm