前言：几个月前已经使用过的配置（修改后会发现程序在性能上会有一个质的飞跃，靠谱），今天想来再转载一下，以后要是忘了方便回顾。
基本思路：
（1）性能瓶颈定位
Show命令
慢查询日志
explain分析查询
profiling分析查询
（2）索引及查询优化
（3）配置优化
MySQL数据库最常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。
除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。
1、查询与索引优化分析
在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
性能瓶颈定位
show命令
可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：
查看MySQL服务器配置信息mysql> show variables;
查看MySQL服务器运行的各种状态值mysql> show global status;
-
#
mysqladmin
variables
-u
username
-ppassword——显示系统变量
-
#
mysqladmin
extended-status
-u
username
-ppassword——显示状态信息
比较全的show命令的使用可参考：mysql>help show
或http://dev.mysql.com/doc/refman/5.7/en/show.html
慢查询日志
慢查询日志开启：
在配置文件my.cnf中在[mysqld]一行下面加入3个配置参数，并重启mysql服务
slow_query_log = 1 //0关闭 1开启
slow_query_log_file = /usr/local/mysql/data/slow-query.log //慢查询日志存放地点
long_query_time = 1 //表示查询超过1秒才记录
在my.cnf中添加log-queries-not-using-indexes参数，表示向慢查询日志中记录下没有使用索引的查询。
慢查询日志开启方法二：
我们也可以通过命令行设置变量来即时启动慢日志查询
mysql> set global slow_query_log =on;
mysql> set long_query_time = 0.01;
mysql> set global slow_query_log_file= "/usr/local/mysql/data/slow-query.log";
查看慢查询的设置信息
-
mysql>
show
variables
like '%
slow_query_log%';
-
mysql>
show
variables
like '%
long_query_time%';
我们可以通过打开log文件查看得知哪些SQL执行效率低下
[root@mysql data]# cat slow-query.log
-
#
Time: 2016
-09-06T14
:17
:12.582189Z
-
#
User@
Host: root[root] @ localhost [] Id:
3
-
# Query_time:
0.008316 Lock_time:
0.000304 Rows_sent:
1 Rows_examined:
20002
-
SET timestamp=
1473171432;
-
select *
from
test1
.tb1
where
stusex='0'; //没有使用索引的
query
-
-
#
Time: 2016
-09-06T15
:54
:42.648291Z
-
#
User@
Host: root[root] @ localhost [] Id:
14
-
# Query_time:
0.017319 Lock_time:
0.000612 Rows_sent:
1 Rows_examined:
20001
-
SET timestamp=
1473177282;
-
select *
from
test1
.tb1
where
entertime <> '2016
-9-3'; //慢查询
query
select * from test1.tb1 where entertime<> '2016-9-3'; //慢查询query
从日志中，可以发现查询时间超过0.01 秒的SQL，而小于0.01秒的没有出现在此日志中。
如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。
有关mysqldumpslow命令的用法可以参考其帮助：#mysqldumpslow --help
-
[root@mysql data]#
mysqldumpslow
-a
-s
at
-r
slow-query
.log
-
-
Reading
mysql
slow
query
log
from
slow-query
.log
-
Count: 1
Time=0
.00s (0
s)
Lock=0
.00s (0
s)
Rows=0
.0 (0), 0
users@
0hosts
-
Time:
2016-
09-
06T15:
54:
42.648291Z
-
# User@Host: root[root] @ localhost [] Id:
14
-
# Query_time:
0.017319 Lock_time:
0.000612 Rows_sent:
1 Rows_examined:
20001
-
SET timestamp=
1473177282;
select * from test1.tb1 where entertime <> '2016-9-3'
上面显示结果中就是一条慢查询，如何优化呢？
优化前查询0.01秒
一是在entertime列上创建索引优化查询
mysql> create index index_entertime on test1.tb1(entertime);
二是优化这个sql查询语句
mysql> select * from test1.tb1 where entertime < '2016-9-3' or entertime > '2016-9-3';
从下图可以看查询0.00秒
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。
explain分析查询
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
-
mysql>
explain
select *
from
test1
.tb1
where
stuname='
admin'\
G;
-
*************************** 1.
row ***************************
-
id: 1
-
select_type:
SIMPLE
-
table:
tb1
-
partitions:
NULL
-
type:
ALL //全表扫描
-
possible_keys:
NULL
-
key:
NULL
-
key_len:
NULL
-
ref:
NULL
-
rows: 19986
-
filtered: 10
.00
-
Extra:
Using
where //使用
where过滤数据
-
1
row
in
set, 1
warning (0
.00
sec)
-
EXPLAIN字段：
-
Table：显示这一行的数据是关于哪张表的
-
type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为
system、
const、
eq_reg、
ref、
range、
index和
ALL
-
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。
-
key：实际使用的索引。如果为
NULL，则没有使用索引。
-
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
-
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
-
rows：
MySQL认为必须检索的用来返回请求数据的行数
-
Extra：关于
MYSQL如何解析查询的额外信息
从上面的explain模拟优化器执行sql语句来看是没有使用索引查询的，而是全表扫描
优化方法：在stuname列上创建索引
mysql> create index index_stuname on test1.tb1(stuname);
再次执行explain
显示结果说明该查询语句使用了index_stuname索引查询数据而非全表扫描。
profiling分析查询
通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。
profiling默认是关闭的。可以通过以下语句查看
mysql> show variables like '%profiling%'; //off表示未开启
或
mysql> select @@profiling; //0表示未开启
打开profiling功能： mysql>set profiling=1; 执行需要测试的sql 语句：
mysql> select @@profiling;
执行要测试的sql语句
mysql> show profile for query 2; //得到对应SQL语句执行的详细信息
status:是profile里的状态，duration：是status状态下的耗时。因此我们关注的就是那个状态最耗时，这些状态中那些可以优化。
当然也可以查看更多的信息如CPU等等
-
SHOW
PROFILE
[type [, type] ... ]
[FOR QUERY n]
-
type:
-
ALL:显示所有的开销信息
-
BLOCK
IO:显示块
IO相关开销
-
CPU:显示用户
CPU时间、系统
CPU时间
-
IPC:显示发送和接收相关开销信息
-
PAGE
FAULTS:显示页面错误相关开销信息
-
SWAPS:显示交换次数相关开销的信息
测试完成之以后，记得要关闭调试功能，以免影响数据库的正常使用：
mysql> set profiling=0;
2、配置优化
Mysql参数优化对于不同的网站，及其在线量，访问量，帖子数量，网络情况，以及机器硬件配置都有关系，优化不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效果。
下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量
1.连接请求的变量：
（1.）max_connections
MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小会经常出现ERROR 1040: Too many connections错误，可以过mysql> showstatus like 'connections';通配符查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接数)，以定夺该值的大小。
-
mysql>
show
variables
like ‘
max_connections’ 最大连接数
-
mysql>
show
status
like ‘
max_used_connections’ 响应的连接数
-
max_used_connections /
max_connections * 100% （理想值≈ 85%）
如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。
如何设置max_connections?
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024
max_connections = 1024
重启mysql服务
（2.）back_log
MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，它就会起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它。
当观察你主机进程列表（mysql> show full processlist），发现大量
xxxxx | unauthenticated user |xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log的值了或加大max_connections的值。
通过mysql> show variables like 'back_log';查看back_log的设置
如何设置back_log?
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024
back_log = 数值
重启mysql服务
（3.）wait_timeout和interactive_timeout
wait_timeout -- 指的是MySQL在关闭一个非交互的连接之前所要等待的秒数
interactive_time -- 指的是mysql在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上进入mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动断开。默认数值是28800，可调优为7200。
对性能的影响：
wait_timeout：
（1）如果设置大小，那么连接关闭的很快，从而使一些持久的连接不起作用
（2）如果设置太大，容易造成连接打开时间过长，在show processlist时，能看到太多的sleep状态的连接，从而造成too many connections错误
（3）一般希望wait_timeout尽可能地低
interactive_timeout的设置将要对你的webapplication没有多大的影响
查看wait_timeout和interactive_timeout
-
mysql>
show
variables
like '%
wait_tmeout%';
-
mysql>
show
variables
like '%
interactive_timeout%';
如何设置wait_timeout和interactive_timeout?
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
-
wait_timeout=100
-
interactive_timeout=100
重启MySQL Server进入后，查看设置已经生效。
2.绶冲区变量全局缓冲：
（1.）key_buffer_sizekey_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOWSTATUS LIKE ‘key_read%’获得）。
一共有14 个索引读取请求，有5 个请求在内存中没有找到直接从硬盘读取索引，计算索引 未命中缓存的概率 ：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50%
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
如何调整key_buffer_size
默认配置数值是8388608(8M)，主机有4GB内存，可以调优值为268435456(256MB)
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
key_buffer_size=268435456或key_buffer_size=256M
重启MySQL Server进入后，查看设置已经生效。
（2.）query_cache_size(查询缓存简称QC)使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。
一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存。
注：两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。
通过检查状态值’Qcache%’，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。
Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
注意：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes：多少条Query 因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；
Qcache_total_blocks：当前Query Cache 中的block 数量；。
我们再查询一下服务器关于query_cache的配置：
上图可以看出query_cache_type为off表示不缓存任何查询
各字段的解释：query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小 ，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024 byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：
如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。
如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
修改/etc/my.cnf,配置完后的部分文件如下：
-
query_cache_size=256
M
-
query_cache_type=1
保存文件，重新启动MYSQL服务，然后通过如下查询来验证是否真正开启了：
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在querycache中，是否返回cache结果还是等写操作完成再读表获取结果。
-
查询缓存碎片率 =
Qcache_free_blocks /
Qcache_total_blocks * 100%
-
如果查询缓存碎片率超过20%，可以用
FLUSH
QUERY
CACHE整理缓存碎片，或者试试减小
query_cache_min_res_unit，如果你的查询都是小数据量的话。
-
查询缓存利用率 = (
query_cache_size –
Qcache_free_memory) /
query_cache_size * 100%
-
查询缓存利用率在25%以下的话说明
query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且
Qcache_lowmem_prunes > 50的话说明
query_cache_size可能有点小，要不就是碎片太多。
-
查询缓存命中率 =
Qcache_hits/(
Qcache_hits +
Qcache_inserts) * 100%
Query Cache的限制
a) 所有子查询中的外部查询SQL 不能被Cache；
b) 在Procedure，Function 以及Trigger 中的Query 不能被Cache；
c) 包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。
鉴于上面的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入Query Cache，仅仅让某些Query的查询结果被Cache。
如何设置query_cache_size？
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
-
query_cache_size=256
M
-
query_cache_type=1
重启MySQL Server进入后，查看设置已经生效。
（3.）max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况,当超过指定次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。max_connect_errors的值与性能并无太大关系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
max_connect_errors=20
重启MySQL Server进入后，查看设置已经生效。
（4.）sort_buffer_size
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUPBY操作。
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进入后，查看设置已经生效。
（5.） max_allowed_packet = 32M
MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。最大值是1GB，必须设置1024的倍数。
（6.）join_buffer_size = 2M
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
（7.）thread_cache_size = 300服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
试图连接到MySQL(不管是否连接成功)的连接数
-
Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
-
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
-
Threads_created :代表从最近一次服务启动，已创建线程的数量，如果发现
Threads_created值过大的话，表明
MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中
thread_cache_size值。
-
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于
sleep状态。
（1.）innodb_buffer_pool_size
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。
设置innodb_buffer_pool_size
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
innodb_buffer_pool_size = 2048M
（2.）innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数 1写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
（3.）innodb_thread_concurrency = 0此参数用来设置innodb线程的并发数量，默认值为0表示不限制，若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8.
（4.）innodb_log_buffer_size
此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
innodb_log_buffer_size=32M
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
（6.）innodb_log_files_in_group= 3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
（7.）read_buffer_size = 1MMySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
（8.）read_rnd_buffer_size = 16M
MySql 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。
批量插入数据缓存大小，可以有效提高插入效率，默认为8M
（10.）binary loglog-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 也可以重新开始新的binarylog
在优化之前执行mysqlslap工具进行测试
-
mysql>
mysqlslap
--defaults-file=/
etc/
my
.cnf
--concurrency=10
--iterations=1
--create-schema='
tables'
--query='
select *
from
tables
.tb1'
--engine=
innodb
--number-of-queries=2000
-uroot
-ppwd123 –
verbose;
-
显示结果：
优化之后执行mysqlslap工具进行测试
相关优化参数总结：
-
[mysqld]
-
slow_query_log = 1
-
slow_query_log_file = /
usr/
local/
mysql/
data/
slow-query
.log
-
long_query_time = 1
-
log-queries-not-using-indexes
-
max_connections = 1024
-
back_log = 128
-
wait_timeout = 60
-
interactive_timeout = 7200
-
key_buffer_size=256
M
-
query_cache_size = 256
M
-
query_cache_type=1
-
query_cache_limit=50
M
-
max_connect_errors=20
-
sort_buffer_size = 2
M
-
max_allowed_packet=32
M
-
join_buffer_size=2
M
-
thread_cache_size=200
-
innodb_buffer_pool_size = 2048
M
-
innodb_flush_log_at_trx_commit = 1
-
innodb_log_buffer_size=32
M
-
innodb_log_file_size=128
M
-
innodb_log_files_in_group=3
-
log-bin=
mysql-bin
-
binlog_cache_size=2
M
-
max_binlog_cache_size=8
M
-
max_binlog_size=512
M
-
expire_logs_days=7
-
read_buffer_size=1
M
-
read_rnd_buffer_size=16
M
-
bulk_insert_buffer_size=64
M
-
log-error = /
usr/
local/
mysql/
data/
mysqld
.err
1181
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
