MySQL优化

未优化前的压力测试
db01 [(none)]>create database test01 charset utf8mb4;
db01 [(none)]>use test01;
db01 [test01]>set sql_log_bin=0;
db01 [test01]>source t100w.sql
db01 [test01]>grant all on . to root@‘localhost’ identified by ‘123’;

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test01’
–query=“select * from test01.t100w where k2=‘ABxy’” engine=innodb
–number-of-queries=200 -uroot -p123 -verbose

数据库实例(参数)

Max_connections *****
（1）简介
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。
（2）判断依据
show variables like ‘max_connections’;
±----------------±------+
| Variable_name | Value |
±----------------±------+
| max_connections | 151 |
±----------------±------+
show status like ‘Max_used_connections’;
±---------------------±------+
| Variable_name | Value |
±---------------------±------+
| Max_used_connections | 101 |
±---------------------±------+

（3）修改方式举例
vim /etc/my.cnf
Max_connections=1024

补充:
1.开启数据库时,我们可以临时设置一个比较大的测试值
2.观察show status like ‘Max_used_connections’;变化
3.如果max_used_connections跟max_connections相同,
那么就是max_connections设置过低或者超过服务器的负载上限了

back_log ***
（1）简介
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
（2）判断依据
show full processlist
发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
（3）修改方式举例
vim /etc/my.cnf
back_log=1024

wait_timeout和interactive_timeout ****

（1）简介
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。
wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
（2）设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低
（3）修改方式举例
wait_timeout=60
interactive_timeout=1200
长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。

key_buffer_size *****
(1) myisam 表的索引缓冲区
(2) 临时表的缓冲区
mysql> show status like “created_tmp%”;
±------------------------±------+
| Variable_name | Value |
±------------------------±------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |

Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 越高越好
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

控制在5%-10%以内
mysql> show variables like “key_buffer_size%”;

临时调整
set global key_buffer_size=18388608;

双1标注
innodb_flush_log_at_trx_commit=1
sync_binlog=1
(1) innodb_flush_log_at_trx_commit
1 每个事物提交的时候,写入OS buffer并且必须写入磁盘 1 默认模式
2 每秒写入 0
3 每个事物提交的时候只写入OS buffer 2 并不实时写入磁盘
(2)sync_binlog
每次事务提交都立即刷写binlog到磁盘

优化后的压力测试

innodb_flush_log_at_trx_commit=1
sync_binlog=1
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
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=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M