Mysql性能优化

1、压力测试

# --concurrency=50,100分别以50和100并发 进行10000次查询

 mysqlslap -uroot -pzsj381599113QQ! -a --concurrency=50,100 --number-of-queries 10000

mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 4.355 seconds
        Minimum number of seconds to run all queries: 4.355 seconds
        Maximum number of seconds to run all queries: 4.355 seconds
        Number of clients running queries: 50
        Average number of queries per client: 200

Benchmark
        Average number of seconds to run all queries: 4.368 seconds
        Minimum number of seconds to run all queries: 4.368 seconds
        Maximum number of seconds to run all queries: 4.368 seconds
        Number of clients running queries: 100

        Average number of queries per client: 100

# --concurrency=500,1000分别以500和1000并发 进行10000次查询

 mysqlslap -uroot -pzsj381599113QQ! -a --concurrency=500,1000 --number-of-queries 10000

出现以下错误

mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections

修改mysql配置文件

vi /etc/my.cnf

max_connections：允许客户端并发连接的最大数量，默认值是151，一般将该参数设置为500-2000

加大连接max_connections = 2000

重启mysql

service mysqld restart

再次进行测试

 mysqlslap -uroot -pzsj381599113QQ! -a --concurrency=500,1000 --number-of-queries 10000

测试结果如下

mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 5.299 seconds
        Minimum number of seconds to run all queries: 5.299 seconds
        Maximum number of seconds to run all queries: 5.299 seconds
        Number of clients running queries: 500
        Average number of queries per client: 20

Benchmark
        Average number of seconds to run all queries: 6.501 seconds
        Minimum number of seconds to run all queries: 6.501 seconds
        Maximum number of seconds to run all queries: 6.501 seconds
        Number of clients running queries: 1000

        Average number of queries per client: 10

innodb_buffer_pool_size
这个是Innodb最重要的参数，主要作用是缓存innodb表的索引，数据，插入数据时的缓冲，默认值为128M。
如果是一个专用DB服务器，那么它可以占到内存的70%-80%。
并不是设置的越大越好。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。

如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配innodb_buffer_pool_size＝64M就够了

本次设置innodb_buffer_pool_size＝256M

mysqlslap -uroot -pzsj381599113QQ! -a --concurrency=500,1000 --number-of-queries 10000

mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 4.231 seconds
        Minimum number of seconds to run all queries: 4.231 seconds
        Maximum number of seconds to run all queries: 4.231 seconds
        Number of clients running queries: 500
        Average number of queries per client: 20

Benchmark
        Average number of seconds to run all queries: 5.092 seconds
        Minimum number of seconds to run all queries: 5.092 seconds
        Maximum number of seconds to run all queries: 5.092 seconds
        Number of clients running queries: 1000

        Average number of queries per client: 10

innodb_log_file_size

这个参数指定在一个日志组中，每个log的大小。innodb的logfile就是事务日志，用来在mysql crash后的恢复.所以设置合理的大小对于mysql的性能非常重要，直接影响数据库的写入速度，事务大小，异常重启后的恢复。在mysql 5.5和5.5以前innodb的logfile最大设置为4GB,在5.6以后的版本中logfile最大的可以设为512GB。一般取256M可以兼顾性能和recovery的速度。

innodb_flush_method
这个参数控制着innodb数据文件及redo log的打开、刷写模式。
有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT
默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer
为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件
为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log。

在类unix操作系统中，文件的打开方式为O_DIRECT会最小化缓冲对io的影响，该文件的io是直接在用户空间的buffer上操作的，并且io操作是同步的，因此不管是read()系统调用还是write()系统调用，数据都保证是从磁盘上读取的

innodb_flush_method=O_DIREC

MySQL 5.7 提供了更加合适的默认值，一般情况下只要调整下面 3 个选项就可以了，其余参数根据实际情况再进行配置。

####innodb配置#################
innodb_buffer_pool_size=8G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT