MySQL性能优化前后对比测试

Table of Contents

一、MySQL优化参数详解

1) 连接请求的变量
Max_connections
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

修改/etc/my.cnf在[mysqld]下添加以下内容，设置最大连接数为1024，重启mysql服务验证
vim/etc/my.cnf
添加max_connections=1024

2）back_log
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
可以通过以下命令查看back_log的设置默认是254

我们可以通过MySQL主配置修改它的大小，设置最大连接数为：1024随后重启MySQL服务再次查看
vim /etc/my.cnf 添加：back_log=1024

3）wait_timeout和interactive_timeout
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeoutz：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接一般希望wait_timeout尽可能低
查看wait_timeout和interactive_timeout的设置

修改/etc/my.cnf，添加以下内容
wait_timeout=100
interactive_timeout=100

4）query_cache_size（查询缓存简称QC）
使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。
一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。
注：两个sql语句，只要想差哪怕是一个字符（列如大小写不一样；多一个空格等）,那么这两个sql将使用不同的一个cache。
上述状态值可以使用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因为内存不足而被清除出Query Cache。通过“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的配置：
mysql>show variables like '%query%';

query_cache_type为off表示不缓存任何查询
修改/etc/my.cnf,配置完后的部分文件如下：
query_cache_size=256M
query_cache_type=1

6）max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
max_connect_errors=20
修改完后重启mysql服务

7）sort_buffer_size
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或GROUP BY操作，Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存
修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=2M

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或更高内存，可配置更大。服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

11.innodb_buffer_pool_size
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。

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 也可以重新开始新的binarylog

二、新旧配置对比测试

1、被测机器软硬件配置

12核24线程处理器，内存64GB

2、编写shell脚本插入测试数据40000条
脚本如下，

#!/bin/bash
HOSTNAME="172.18.23.198"
PORT="8034"
USERNAME="root"
PASSWORD="123456"
DBNAME="test1"
TABLENAME="tb1"
#ceate database
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e"${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${create_table_sql}"
#insert data to table
i="1"
while [ $i -le 40000 ]
do
insert_sql="insert into ${TABLENAME} values($i,'zhangsan','1','21276387261874682','1999-10-10','2017-10-24','beijingchangpingqu')"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${select_sql}"

插入完成后的截图如下，

3、对未优化的MySQL服务器执行压力测试

未优化的MySQL主配置my.cnf文件内容如下，

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 8067
socket        = /data/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
datadir=/data/mysql
basedir=/usr/local/mysql
port        = 8067
socket        = /data/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 256M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

default_storage_engine=InnoDB

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     = <port>
#
# binary logging - not required for slaves, but recommended
log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16384M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 250M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120
innodb_file_per_table = ON

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

压力测试的shell脚本如下，共测试10次，平均耗时 10.6856 s。

#!/bin/bash

i="1"
test_log_file="/tmp/sql_load_test.txt"
>${test_log_file} 2>/dev/null

echo -e "\nLoad Test Begin...\n"

while [ ${i} -le 10 ]
do

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test1' --query='select * from test1.tb1' engine=innodb --number-of-queries=2000 -uroot -p123456 -P 8034 -h 172.18.23.198 --verbose 2>/dev/null >>${test_log_file}

ping -c 2 127.0.0.1 1>/dev/null 2>&1
let i++

done

awk '/Average.+all queries/{sum+=$09}END{printf "Average Query Time : %s seconds.\n\n",sum/10}' ${test_log_file}

执行结果如下图，

4、对优化后的MySQL服务器执行压力测试

优化后的MySQL主配置文件my.cnf内容如下，

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port       = 8067
socket       = /data/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
datadir=/data/mysql
basedir=/usr/local/mysql
port = 8067
socket = /data/mysql/mysql.sock
#log-error=/usr/local/mysql/log/error.log
#log=/usr/local/mysql/log/mysql.log
skip-external-locking
table_open_cache = 64
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
default_storage_engine=InnoDB
#log_slow_queries=/data/mysql/DELL_S7-slow.log
innodb_file_per_table = ON

slow_query_log=1
long_query_time=1
log-queries-not-using-indexes
max_connections=1024
back_log=1024
wait_timeout=100
interactive_timeout=1800
key_buffer_size=128M
query_cache_size=256M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=300
innodb_buffer_pool_size=32G
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=64M
innodb_log_files_in_group=3
bulk_insert_buffer_size=64M
log-bin=mysql-bin
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
read_buffer_size=1M
read_rnd_buffer_size=16M
server-id=1