MySQL性能优化-数据库配置

Mysql配置

配置文件的优化

vi /etc/my.cnf

[mysqd]
back_log = 500
key_buffer_size = 400M
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 614K
sort_buffer_size = 6M
read_buffer_size = 4M
join_buffer_size = 8M
thread_cache_size = 80
query_cache_size = 64M
tmp_table_size = 256M
max_connections = 1024
table_cache = 1024
innodb_buffer_pool_size=3840M 
event_scheduler=ON  
default-storage-engine=INNODB

back_log

要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。

back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。

当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。默认数值是50，把它改为500。推荐为小于512的整数。

key_buffer_size

索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8M)，我的MySQL主机有2GB内存，所以我把它改为402649088(400MB)。

max_allowed_packet

max_allowed_packet指在网络传输中一次消息传输量的最大值。系统默认为1M，最大值是1GB，必须设定为1024的倍数，单位是字节。

thread_stack

指设置Mysql每个线程的堆栈大小，默认值足够大，可满足普通操作可设置128KB—4GB，默认192KB。

table_cache

指高速缓存的大小，Mysql访问一个表时，如果在Mysql表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区，这样做的好处是可以更快地访问表中的内容。如果open_tables接近table_cache的时候，并且Opened_tables这个值在逐步增加，那就要考虑这个值的大小。

sort_buffer_size

指查询排序时所能使用的缓冲区大小，默认为2MB，此参数对应的分配内存是每连接独占的，内存4G的服务器，推荐设置为6—8M。

read_buffer_size

指读查询操作所能使用的缓冲区大小，该参数对应的分配内存也是每连接独享。

join_buffer_size

指联合查询操作所能使用的缓冲区大小，该参数对应的分配内存也是每连接独享。

thread_cache_size

可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。

query_cache_size

指Mysql查询缓冲区的大小。

tmp_table_size

用于设置内存临时表最大值。

max_connections

允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 Too many connections 错误。 默认数值是100，我把它改为1024 。

wait_timeout

指定一个请求的最大连接时间，4G的内存服务器设置为5—10.

Mysql上线后根据status状态进行优化

mysql > show global status;
或者：
mysql > show status like ‘查询值%’;

慢查询

为了定位系统中效率比较低下的query语名，需要打开慢查询日志，就是slow query log。

mysql > show variables like ‘%slow%’;
mysql > show global status like ‘%slow%’;

打开慢查询日志会对系统性能有影响，如果是主从复制结构，可以考虑打开其中一台从服务器慢查询日志，下面语句可以查出访问次数最多的20个sql语句：

mysqldumpslow –s c –t 20 host –slow.log

连接数

mysql > show variables like ‘max_connections%’;
mysql > show global status like  ‘Max_used_connections’;

key_buffer_size

设置MyISAM表索引索引引擎缓存空间的大小，此参数对MyISAM表性能影响最大。

mysql > show variables like ‘key_buffer_size’;
mysql > show global status like  ‘key_read%’;

key_cache_miss_rate = key_reads/key_read_requests*100%
key_cache_miss_rate在0.1%以下都很好（每1000个请求有一个直接读硬盘）

key_blocks_*参数：

mysql > show global status like  ‘key_blocks_u%’;

key_blocks_unused表示未使用的缓存簇（blocks）数；key_blocks_used表示曾经用到的最大blocks数。
理想设置：
key_blocks_used/(key_blocks_unused+key_blocks_used)*100% = 80%

临时表

mysql > show global status like  ‘created_tmp%’;

显示已经被创造了的隐含临时表的数量。
Created_tmp_disk_tables/created_tmp_tables*100% <=25%

Open table 的情况

open_tables表示打开表的数量，opened_tables表示打开过的表数量。

mysql > show global status like  ‘open%tables%’;

如果opend_tables数量过大，说明配置中table_cache(5.1.3版本后叫table_open_cache)的值可能太小。

mysql > show variables like ‘table_cache’;

比较合适的值：
Open_tables/opend_tables*100% >=85%
Open_tables/table_cache*100% <=95%

进程使用情况

如果设置了thread_cache_size，当客户端断开时，服务器处理此客户请求的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。threads_created表示创建过的线程数。

mysql > show global status like  ‘Thread%’;

如果threads_created值过大，表明Mysql服务器一直在创建线程，这样比较耗资源，可适当修改thread_cache_size的值。

mysql > show variables like 'thread_cache_size';

查询缓存

query_cache_size用于设置MySQL的查询缓存（query cache）大小。
query_cache_size用于设置使用查询缓存的类型。

mysql > show global status like  ‘qcache%’;

排序使用情况

mysql > show global status like  ‘sort%’;

文件打开数

当open_files大于open_file_limit值，数据库会卡住。

mysql > show variables like ‘open_files_limit’;
mysql > show global status like  ‘open_files’;

比较合适的设置：
Open_files/open_files_limit * 100% <= 75%

Innodb_buffer_pool_size合理设置

Innodb不仅仅缓存索引，同时还会缓存实际数据。

mysql > show global status like  ‘Innodb_buffer_pool_%’;

一般设置为整个系统内存的50%–80%。

查询缓冲提高查询速度

MySQL为我们提供了查询缓冲的功能（只能在MySQL 4.0.1及以上版本使用查询缓冲）。我们可以通过查询缓冲在一定程度上提高查询性能。

我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲。设置也非常简单，只需要将query_cache_type设为1即可。在设置了这个 属性后，MySQL在执行任何SELECT语句之前，都会在它的缓冲区中查询是否在相同的SELECT语句被执行过，如果有，并且执行结果没有过期，那么 就直接取查询结果返回给客户端。但在写SQL语句时注意，MySQL的查询缓冲是区分大小写的。

虽 然不设置查询缓冲，有时可能带来性能上的损失，但有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）。这样就需要把缓冲关 了。当然，这可以通过设置query_cache_type的值来关闭查询缓冲，但这就将查询缓冲永久地关闭了。在MySQL 5.0中提供了一种可以临时关闭查询缓冲的方法：SQL_NO_CACHE。

SELECT SQL_NO_CACHE field1, field2 FROM TABLE

我们还可以将my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。

SELECT SQL_CALHE * FROM TABLE1 

操作系统配置

目前大多数MySQL都是安装在Linux系统之上：

网络方面的配置，要修改/etc/sysctl.conf文件：

#增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
#减少断开连接时，资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10

打开文件数的限制，可以使用ulimit -a 查看目录的各种限制，可以秀阿贵/etc/security/limits.conf文件，增加以下内容以修改打开文件数量的限制：

* soft nofile 65535
* hard nofile 65535

除此之外最好在MySQL服务器上关闭iptables，selinux等防火墙软件。