mysql优化指南之配置参数、运行状态篇

mysql的监控方法大致分为两类：
1，连接到mysql数据库内部，使用show status，show variables，flush status 来查看mysql的各种性能指标。
2，直接使用mysqladmin查看其性能指标，例如：
 

mysqladmin两个参数，status，extended-status
shell > mysqladmin  -uroot -ppassword  variables status
可得到以下信息（后面详解）
 

最完整的信息
 

其他的信息
 

对配置参数的说明：
配置参数的格式如下：（shell > mysqladmin  -uroot -ppassword  variables extended-status）
 

注：value 值的单位是byte ，要得到M ，需除以2次1024
 

两个变量值都只能为1到65,535之间的整数值。设置为非整数值，则会给出错误。
这两个变量影响AUTO_INCREMENT列。
auto_increment_increment控制列中的值的增量值（步进量）。
auto_increment_offset确定AUTO_INCREMENT列值的初始值。
一般不去更改。更改方法：

几个参数的关系：
 

-----------------------
 
max_open_files 由 open_files_limit 参数决定。
mysql打开的最大文件数，受两个参数的影响：系统打开的最大文件数（ulimit -n）和   open_files_limit 。
加大max_open_files的值
在/etc/my.cnf加入open_files_limit=8192
在/etc/security/limits.conf添加
 

最好用sysctl或者修改/etc/sysctl.conf文件，同时还要在配置文件中把open_files_limit这个参数增大，对于4G内存服务器，open_files_limit至少要增大到4096，非特殊情况，设置成8192就可以了。
table_cache
MySQL 5.0升级到5.1，table_cache 改名table_open_cache
设置表高速缓存的数目。

表缓存的说明：
当 Mysql 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果还没有被缓存，但是在 Mysql 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区；如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存放，使用表缓存的好处是可以更快速地访问表中的内容。
每个连接进来，都会至少打开一个表缓存。因此， table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是网站程序一次查询所用到的表的最大值。
每个线程会独自持有一个数据文件的文件描述符，而索引文件的文件描述符是公用的。当table cache不够用的时候，MySQL会采用LRU算法踢掉最长时间没有使用的表。如果table_cache设置过小，MySQL就会反复打开、关闭 frm文件，造成一定的性能损失。如果table_cache设置过大，MySQL将会消耗很多CPU去做 table cache的算法运算。
而InnoDB的元数据管理是放在共享表空间里面做的，所以获取表的结构不需要去反复解析frm文件，这是比MyISAM强的地方。即使 table_cache设置过小，对于InnoDB的影响也是很小的，因为它根本不需要反复打开、关闭frm文件去获取元数据。
合理设置table_cache的大小：通过查看open_tables，Opened_tables，Flush tables 的值来比较。

查看当前的表缓存情况：
 

清空表缓存
 

如果发现 open_tables 接近 table_cache 的时候，如果 Opened_tables 随着重新运行 SHOW STATUS 命令快速增加，就说明缓存命中率不够。并且多次执行FLUSH TABLES(通过shell > mysqladmin  -uroot -ppassword  variables status )，那就说明可能 table_cache 设置的偏小，经常需要将缓存的表清出，将新的表放入缓存，这时可以考虑增加这个参数的大小来改善访问的效率。
如果 Open_tables 比 table_cache 设置小很多，就说明table_cache 设的太大了。
table_cache的值在2G内存以下的机器中的值默认时256到512，如果机器有4G内存,则默认这个值是2048，但这决意味着机器内存越大，这个值应该越大，因为table_cache加大后，使得mysql对SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。
注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
 

key_buffer_size设置索引块（index blocks）缓存的大小，保存了 MyISAM 表的索引块。它被所有线程共享，决定了数据库索引处理的速度，尤其是索引读的速度。理想情况下，对于这些块的请求应该来自于内存，而不是来自于磁盘。
只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。
key_buffer_size: 如果不使用MyISAM存储引擎，16MB足以，用来缓存一些系统表信息等。如果使用 MyISAM存储引擎，在内存允许的情况下，尽可能将所有索引放入内存，简单来说就是“越大越好”
合理设置key_buffer_size的方法：
查看Key_read_requests和Key_reads的比例，
Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数。命中磁盘的读请求数除以读请求总数就是不中比率。如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。
key_reads / key_read_requests的值应该尽可能的低，比如1:100，1:1000 ，1:10000。
对于内存在4GB左右的服务器该参数可设置为256M或384M。
注意：该参数值设置的过大反而会是服务器整体效率降低！

256MB内存和许多表，想要在中等数量的客户时获得最大性能，应使用：
shell> mysqld_safe --key_buffer_size=64M --table_cache=256 --sort_buffer_size=4M --read_buffer_size=1M &
每个连接到MySQL服务器的线程都需要有自己的缓冲，默认为其分配256K。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，例如存储查询语句的空间等。但如果对数据表做复杂的操作比较复杂，例如排序则需要使用临时表，此时会分配大约read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。
 
myisam_sort_buffer_size                 8388608
当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配之缓冲区。
sort_buffer_size                         2097144
每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作。
注意：该参数对应的分配内存是每个连接独享，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。
 

 如果 sort_merge_passes 很大，就表示需要注意 sort_buffer_size。
当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。如果 sort_merge_passes 状态变量很大，这就指示了磁盘的活动情况。
 

read_buffer_size是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。
每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描，可能需要增加该值， 默认值为131072。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。
read_rnd_buffer_size
read_rnd_buffer_size是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。该参数对应的分配内存也是每连接独享。

您可能感兴趣的文章：
mysql数据库性能优化技巧
mysql优化指南之配置参数、运行状态篇
mysql慢查询开启与配置
mysql变量参数的查看与设置
Ubuntu 11.10安装MySQL 5.5.x版本
mysql中的query_cache_limit进行数据库优化
mysql数据库性能优化 mysql配置文件my.cnf详解
shell脚本学习指南之shell变量
有关mysql运行状态及调优（一）
mysql性能优化 my.cnf配置文件详解