MySql优化 三

MySQL参数优化

1. max_connections是MySQL最大并发连接数，默认值151
2. MySQL允许的最大连接数上限是16384 实际连接数是最大连接数的85%较为合适
3. 查看当前设置的最大并发连接数： show variables like ‘max_connections’
4. 查看当前的实际连接数(有可能超过了最大并发连接数限制之后，新的连接请求就被丢弃了)
5. show status like ‘max_used_connections’ 修改最大并发连接数，修改配置文件my.cnf，在【mysqld】下面添加 max_connections=3000
6. 注意：MySQL会为每个连接创建缓冲区，所以不能盲目调大最大连接数

设置请求堆栈的大小

1. 堆栈的作用： 当客户端来了新的请求，没有空闲连接时，MySQL会将这些请求放入到堆栈中，当有新的连 接空闲时，会去处理堆栈中的请求。
2. show variables like ‘back_log’ back_log是存放执行请求的堆栈大小，默认值是80（mysql5.7）
3. 一般堆栈大小设置成最大连接数的1/3 修改堆栈大小，配置文件my.cnf，在【mysqld】下面添加 back_log=1000
   

设置并发线程数

1. show variables like ‘innodb_thread_concurrency’
2. innodb_thread_concurrency代表并发线程数，默认是0，表示没有上限
3. 并发线程数应该设置为CPU核心数的两倍 修改配置文件my.cnf，在【mysqld】下面添加 innodb_thread_concurrency=8

设置连接超时时间

1. wait_timeout是超时时间，单位是秒
2. 连接默认超时为8小时，连接长期不用又不销毁，浪费资源
3. 修改配置文件my.cnf，在【mysqld】下面添加 wait_timeout=60（单位秒）

InnoDB缓存大小

1. 注意：此处不是设置查询缓存
2. 内存操作效率快
3. innodb_buer_pool_size是InnoDB的缓存容量，默认是128M
4. InnoDB缓存的大小可以设置为主机内存的70%~80%（该服务器没有安装其他服务）
5. 修改配置文件my.cnf，在【mysqld】下面添加 innodb_buer_pool_size = 256M service mysqld restart （上述的所有修改都要重启服务）
   

性能压测

测试指标

1. QPS：每秒处理完的请求次数
2. TPS：每秒处理完的事务次数
3. 响应时间：一次请求所需要的请求处理时间
4. 并发量：系统能同时处理的请求数

关键概念区分

1. TPS vs QPS
   TPS是有事务的，而查询操作是没有事务的，所以查询操作会算到QPS上，但不会算到TPS上。

2. QPS vs 并发量
   假设现在系统来了100个并发请求，然后系统每秒可以处理90个，那么QPS就是90，而并发量是100.

压测工具

1. mysqlslap：无法对数据库长时间高并发测试
2. sysbench：可以帮我们生成测试数据，需要做24小时的测试，全部覆盖一整天的情况（推荐）

集群

为什么要搭建数据库集群

1. 如果在低并发的情况下，单节点MySQL的读写速度更快。因为在数据库集群中，多个MySQL节点的数据 要通过网络同步，所以读写速度不如单节点MySQL
2. 但是在高并发的情况下，大量的读写请求会让单节点MySQL的硬盘无法承受，所以速度会变得很慢。比 如说12306最开始上线的时候，大量用户涌入网站购票，结果系统就崩溃了。
3. 高并发恰恰是数据库集群的主场。大量的读写请求会被分散发往多个节点执行。众人拾柴火焰高，多个 MySQL执行读写请求，肯定比单节点MySQL快，所以在高负载的情况下，单节点MySQL接近崩溃，反而 数据库集群的读写速度更快。

常用数据库集群方案

1. 常用的方案有：PXC（同步传输）和replication（异步传输），一般是混合使用
2. PXC集群适合保存少量高价值数据（订单），replication集群适合保存大量低价值数据（评论，留言之类 的）。
3. PXC集群，必须是多台服务器同步完成之后，才能提交事务，所以效率会比较慢，但是数据有保证。