mysql性能优化（二）

mysql性能优化（二）

query cache的限制

所有的子查询中的外部查询sql语句，不会被cache（缓存）

在procedure，function以及trigger中的query不被cache

包含其他很多每次执行可能得到不一样的结果函数query不能被cache

在/etc/my.cnf文件，在[mysqld]下面添加query_cache_size=256M query_cache_type=1(开启) 重启后生效

6.max_connect_error是一个mysql中与安全有关的计数器值，负责阻止过多的尝试失败的客户端，以防止暴力破解。修改配置文件/etc/my.cnf [mysqld]下添加max_connect_errors=次数

重启服务生效

7.sort_buffer_size每个进行线程分配大小缓冲区，增加这值加速order by或group by操作

Sort_Buffer_Size 是一个connection级参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500个连接将会消耗 500*sort_buffer_size(2M)=1G内存

例如设置sort_buffer_size

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

sort_buffer_size = 2M
重启MySQL Server进入后，查看设置已经生效。

8.max_allowed_packet = 32M

MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败。最大值是1GB，必须设置1024的倍数。
9．join_buffer_size = 2M
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

10.thread_cache_size = 300
服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB及以上内存，可配置更大。

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

3）配置InnoDB的几个变量

11.innodb_buffer_pool_size

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL服务器，最大可以把该值设置成物理内存的80%。根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。

mysql> show variables like 'innodb_buffer_pool_size';

设置innodb_buffer_pool_size

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

innodb_buffer_pool_size = 2048M
重启MySQL Server进入后，查看设置已经生效。

12.innodb_flush_log_at_trx_commit

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。

根据MySQL手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。

13.innodb_thread_concurrency = 0
此参数用来设置innodb线程的并发数量，默认值为0表示不限制，若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8.

14.innodb_log_buffer_size

此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

innodb_log_buffer_size=32M

15. innodb_log_file_size = 50M

此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.

16. innodb_log_files_in_group = 3

为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

17.read_buffer_size = 1M
MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
18.read_rnd_buffer_size = 16M
MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。
19.bulk_insert_buffer_size = 64M
批量插入数据缓存大小，可以有效提高插入效率，默认为8M

20.binary log

log-bin=/usr/local/mysql/data/mysql-bin

binlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M

max_binlog_cache_size = 8M //表示的是binlog能够使用的最大cache 内存大小

max_binlog_size = 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。

expire_logs_days = 7 //定义了mysql清除过期日志的时间。
二进制日志自动删除的天数。默认值为0,表示"没有自动删除"。

mysqladmin flush-logs 也可以重新开始新的binary log

在优化之前执行mysqlslap工具进行测试

[root@localhost ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1 --create-schema='test1' --query='select * from test1.tb1' --engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose

显示结果：

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 13.837 seconds

Minimum number of seconds to run all queries: 13.837 seconds

Maximum number of seconds to run all queries: 13.837 seconds

Number of clients running queries: 10

Average number of queries per client: 200