MYSQL性能优化之Mysql服务器参数

最新推荐文章于 2017-06-26 16:10:51 发布

侠客岛的含笑

最新推荐文章于 2017-06-26 16:10:51 发布

阅读量608

点赞数 1

分类专栏：数据库 Mysql架构设计文章标签：操作系统 mysql session 性能优化服务器

本文链接：https://blog.csdn.net/qq_33936481/article/details/72354185

版权

17 篇文章 0 订阅

订阅专栏

13 篇文章 9 订阅

订阅专栏

这里写图片描述

不同操作系统，mysql读取配置文件的位置顺序可能是不同的

MySQL配置参数根据作用域：分为全局参数和会话参数（动态调整）

这里写图片描述

会话参数在没有单独指定的时候，会使用相同名称的全局参数做为默认值；
有的全局变量修改后只对新的session有效，对于已经连接的session还是使用原来的参数（重新登陆才会更新）

Session1:
这里写图片描述
Session2:

重新登陆：

这里写图片描述

生产环境使用mysql专用服务器
不能通过修改配置实现：mysql服务器运行，解析，查询，以及内部管理所消耗的内存
能通过修改配置实现：各类的缓冲池所使用的内存等
mysql手册表示缓存池的大小是服务器75%以上（不能完全按照此配置）
如果当前InnoDB表的数目很小，并且长时期可预计，那就没有必须把剩余的所有内存都分配给缓存池。因为缓冲池的内存总量如果超过InnDB表的总数量加索引所占用的空间的大小的话，无意义。
如果数据量会快速增长，InnoDB表缓冲池就要多预留一些空间（mysql5.7之前，缓存池的配置需要重启服务器）
当然很大的缓冲池也会在InnoDB表关闭时，需要更多的时间把脏页刷新到磁盘上
key_buffer_size所配置的缓冲池主要是MyISAM。缓存池只会存储索引，数据依赖操作系统的缓存。
mysql的系统表还在使用MyISAM存储引擎（所以无论有没有MyISAM表的使用，都需设置一定的空间大小）
根据系统环境来调整，不可能存在通用的配置项

如果同步mysql的缓存池数据到磁盘上，以进行数据修改持久化的保存。（IO操作成本是很高的，性能和安全的平衡）

Innodb I/O配置：

这里写图片描述
2：Mysql进程崩溃不会丢失数据和事务，只有服务器宕机才会丢失一秒钟的事务

这里写图片描述

这里写图片描述

expire_logs_days覆盖两天全备所兼并的天数，>=7天
max_allowed_packet：32mb(如果主从复制，保持一致)
read_only:从库设置，只接受从主库传送过来的数据变更

这里写图片描述
默认情况下，mysql对语法的检查时比较宽松的

SELECT @@global.sql_mode\G;为空

严格模式是指将SQL_MODE变量设置为

STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一种。
STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表(例如表的存储引擎为InnoDB)中，则中断当前的操作不影响非事务表(例如表的存储引擎为MyISAM)。
no_engine_subtitution的作用：mysql 在create table 时可以指定engine子句；这个engine子句用于指定表的存储引擎
NO_ZERO_DATE：在非严格模式下，可以插入形如“0000-00-00 00:00:00”的非法日期，MySQL数据库仅抛出一个警告。而启用该选项后，MySQL数据库不允许插入零日期，插入零日期会抛出错误而非警告。
　NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零。如“2011-00-01”和“2011-01-00”这样的格式是不允许的。采用日期或月份为零的格式时MySQL都会直接抛出错误而非警告
ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列没有在GROUP BY中出现，那么这句SQL是不合法的

这里写图片描述
sync_binlog：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。但是在主从复制中，还是建议主设置为1，因为主崩溃后，会发生cache中的日志没有同步到二进制日志中的情况。主从服务器的数据可能出现不同步的情况，并且很难恢复
从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

tmp_table_size和max_heap_table_siz，超出转换成磁盘临时表。两值最好保持一致，不要设置过大，以防内存溢出

mysql的一些内存分配是以连接为单位的。通常2000.

关注