MySQL性能优化(二)
成都创新互联-专业网站定制、快速模板网站建设、高性价比中宁网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式中宁网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖中宁地区。费用合理售后完善,10多年实体公司更值得信赖。
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