mysql 参数和部署规范 没有整理 仅供参考参数innodb_flush_log_at_trx_commit =0/1ACID的强一致性与commit之间性能的妥协0.每秒刷出一次log 避免性能问题1.在提交事务的时候 强制必须刷新所有log才算提交成功 每个事务提交和日志文件刷新到磁盘2. 在0和1之间自动调整innodb_flush_log_at_timeout可以控制刷出间隔多于1ssync_binlog=10 二进制日志不会同步到磁盘1.所有事务都是同步的,二进制日志之前提交。innodb_file_per_table#独立表空间on/offinnodb_data_file_pathinnodb数据文件和他们的大小。每个数据文件的完整目录路径是由链接innodb_data_home_dir指定路径long_query_time慢查询日志 慢查询时间slow_query_log开启慢查询日志slow_query_log_file慢查询日志位置lower_case_table_names大小写敏感0.区分大小写1.不分区大小写2.创建的表和DB依据语句上格式存放，凡是查找都是转换为小写进行log_queries_not_using_indexes记录没有使用索引的sql语句general_log on或者off开启查询日志general_log_file查询日志路径query_cache_size查询缓存大小query_cache_type0查询缓存禁用1.开启max_connection（最大连接数）、 最大不要超过800max_connection_error（最大错误数，建议设置为10万以上，open_files_limit、 最大打开文件数innodb_open_files、 innodb 打开最大文件数table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connection的大小max_user_connections 每个用户最大链接binlog_formatGTID相关（全局事物标识：global transaction identifieds）gtid_mode=on开启gtidenforce_gtid_consistencyOFF:都是允许违反GTID一致性。ON:不允许违反GTID事务一致性。推荐gtid_mode=onenforce_gtid_consistency=onmaster_info_repository# 记录到表中 master信息table mysql.slave_master_infoFILE 记录到 master.inforelay_log_info_repositoryFILE 记录到relay-log.infotable 记录到mysql.slave_relay_log_infoskip_slave_start=1#当服务停止 从的状态是关闭的auto_increment_increment没次增长的步长auto_increment_offset起始位置binlog_row_image较少DML操作产生的binlog体积binlog_rows_query_log_eventson 或者off记录用户的querytx_isolation 查看当前事务隔离级别binlog_rows_query_log_events#查看用户输入参数log_bin_use_v1_row_eventsbinlog版本log_slave_updatesinnodb_autoinc_lock_mode1.默认设置 可预判行数时使用新方式 不可预判时仍旧使用表锁 会造成autoinc列自增空间 不过影响很小0 即沿用旧的表级锁模式 每次请求都会等待锁 不过也非常快 不会影响整个事务 只影响当前的insert语句2.直接全部使用新方式 不安全 不适合replication环境log_timestamps 坑爹参数 UTC(系统默认)和SYSTEM(本地系统时区)该参数主要是控制 error log、genera log，等等记录日志的显示时间参数time_zone系统的时区参数lock_wait_timeout 锁超时阀值定义 默认是1年 记得调小5.7 error log使用系统时间log_timestamps =0时 日志中是UTC时区log_timestamps=1时 日志中是SYSTEM时区默认是log_timestamps=0set global log_timestamps = 1;innodb_spin_wait_delay 控制轮训间隔 默认6秒innodb_read_io_threads读io进程数 默认是4 改成8innodb_write_io_threads写io进程数 默认是4 改成8innodb_io_capacity磁盘IO吞吐，具体为缓冲区落地的时候，可以刷脏页的数量，默认200，由于使用了SSD硬盘，所以推荐设置到3000-5000。innodb_max_dirty_pages_pct争议比较大，一般来说都是在75-90之间，主要控制BP中的脏数据刷盘的时机，如果太小会频繁刷盘造成IO上升，如果太大会导致MySQL正常关闭的时候需要很长的时间才能normal shutdown，具体需要看实际场景，个人推荐90。innodb_force_recovery 1-6 启用故障恢复模式1.即便发现损坏的页 实例也正常运行2.避免innodb master 已经purge线程的运行3.不回滚事务4.避免 insert buffer 的合并5.不扫描undo日志6.不做redo前滚 会导致部分数据丢失（主库崩溃 从库没有这时候 从库比主库的可信度要高）show engine innodb status\Ginnodb_undo_tablespaces=3 #设置undo个数innodb_undo_logs 设置undo表空间个数当undo超过innodb_max_undo_log_size时 会出发truncate工作purge执行innodb_purge_rseg-truncate_frequency次后也会触发truncate工作建议使用独立undo表空间innodb_page_size<5.6版本 不可调整5.6版本时 8kb 4kb 但不能调大5.7以上 32KB 64KB全局选项 无法在运行过程中动态修改innodb_buffer_pool_instances管理innodb buffer pool一个instances 管理8到16g内存instances*buffer poolinnodb_buffer_pool_load_at_startupmysql启动 自动加载之前保存的buffer poolinnodb_buffer_pool_dump_at_shutdown是否在关闭实例的时候保存 buffer pool的内容innodb_flush_method=O_DIRECTinnodb flush 操作文件方法innodb_old_blocks_pct设置老数据块占用 buffer pool的比例relay_log_recovery当slave从库宕机 relay-log损坏自动放弃未执行的relay-log 并且重新从master 获取日志保证了relay-log的完整innodb_fill_factorinnodb_flush_log_at_trx_commit0 事务提交时不将redo log buffer写入磁盘1事务提交时将redo log buffer写入磁盘2 事务提交时 仅将redo log buffer写入操作系统缓存通常设置为1 并且设置sync_binlog=1 以保证数据可靠性innodb_log_buffer通常8-32M 足够了innodb_log_file_size一般设置为512M-4Ginnodb_log_fles_in_group至少2个文件比较暴力 只有在需要干净重启时才需要innodb_fast_shutdown=0,1,2扫描列表 并找到邻居页面（innodb_flush_neighbors=1 机械硬盘适用 ssd盘可关闭）一起刷新重点参数innodb_buffer_pool_size最大内存块 建议为物理内存的50%-80%innodb_max_dirty_pages_pctbuufer pool中dirty page 最大占比 建议不超过50%innodb_old_blocks_pctbuffer pool 中old block sublist 最大占比 默认3/8innodb_change_bufferingchange buffer 类型 all或其他innodb_log_buffer_sizeredo log buffer 能缓存5秒左右产生的redo log 能够32MB基本管够innodb_sort_buffer_sizeinnodb 表新创建索引时 批量加载更新索引 以及online ddl时将当前发生dml记录到临时log中innodb_data_file_path共享表空间初始大小 至少1G以上innodb_log_file_sizeredo log 大小 加大有助于减少checkpoint 频率 高tpsinnodb_flush_log_at_trx_commitredo log 刷新机制 1最安全 0性能最高innodb_io_capacityinnodb 后台线程最大iops上限innodb_flush_method刷新innodb date file和log file使用的方式推荐O_DIRECTinnodb_stats_on_metadata=0执行 show table status show index时是否更新统计信息innodb_autoinc_lock_mode=1auto-inc锁模式 推荐是1innodb_file_per_table=1是否独立表空间innodb_fast_shutdown=0/1是否快速关闭 推荐1 需要版本升级或者机器重启 设置为0innodb_force_recovery =0恢复级别 可选0-6 从最小开始尝试起关闭CPU节能，设定为最大性能模式关闭NUMA C-states C1Ebios和os 都要关闭os里面关闭numaCENTOE7GRUB_CMDLINE_LINUX="rhgb quiet numa=off"grub2-mkconfig -o /boot/grub2/grub.cfg #重新生成CENTOS6/etc/grub.conf的kernel行最后添加numa=off，如下所示：阵列卡策略 机械盘好用wb sshd 用wt模式 根据实际情况自己做测试wbwrite back 先写缓冲在写硬盘 机器盘 wbwt write through 直接写硬盘 ssd用wt（ssd 从wb改成wt后 iops没什么变化 但是 io的%util上升了很多 ）正常 wb模式至少比wt时iops高一倍以上测试工具机械盘时，所有盘所组成RAID10陈列SSD盘可以只有二块组成RAID1 或三块做RAID5 最好不用同一个批次的ssdXFS +deadine/noop/sys/block/sda/queue/scheduleranticipatory：猜想时间（适合大文件，如果数据连续猜想时间小为好，如果数据不太连续，时间大较好）deadline：最终期限（对小数据优）cfq：完全公平noop：不调整，在iosched里面没有任何调优参数（适合ssd硬盘和单独的存储）[root@localhost queue]# sysctl -a | grep swapvm.swappiness = 30 #系统优先使用swap和内存[root@localhost queue]# sysctl -a | grep ratiovm.dirty_ratio = 30 #脏页占整个内存的比例，开始刷新vm.dirty_background_ratio = 10 #脏页占程序的百分比 ,开始刷新net.ipv4.tcp_tw_recycle = 0net.ipv4.tcp_tw_reuse = 0#设置为1 减少TIME_WAIT 提高TCP效率建议值vm.swappiness <=10vm.dirty_ratio <=5vm.dirty_background_ratio <= 10#mysql 服务器要不要swap 取决于 你希望mysql半死不活，还是快速重启mount 选项 noatime nodiratime nobarrier选项mysql安装规范basedir datadir backupdir binlogdirmysql参数innodb_flush_log_at_trx_commit & sync_binlog =1 #数据的安全性每次事务提交mysql都会把log buffer 的数据写入log file ，并且flush（刷新到磁盘）中#0 每秒钟刷出一次log，避免性能问题#1在事务提交的时候 强制必须刷出所有的log才算提交成功sync_binlog =1二进制日志同步到磁盘的频率。 binary log 每次写入sync_binlog 1次后 刷写到磁盘innodb_file_per_table =1/oninnodb 独立表空间innodb_data_file_path 至少是1G#innodb 表空间llong_query_time <=0.5#开启慢查询日志lower_case_table_names#表的大小写是否敏感0区分大小写1不区分大小写max_connection（最大连接数）、max_connection_error（最大错误数，建议设置为10万以上，open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connection的大小# 不使用 qcquery_cache_size =0query_cache_type =0qc是查询缓存用于缓存select查询语句，并在下次使用的时候不去查询直接走返回的结果。如果一张表里面有 update insert delect语音 他就重新计算hash值 然后在计算缓存值qc的使用场景 一张表里面 99.999%都是读业务redisvm.overcommit_memory = 10， 表示内核将检查是否有足够的可用内存供应用进程使用；如果有足够的可用内存，内存申请允许；否则，内存申请失败，并把错误返回给应用进程。1， 表示内核允许分配所有的物理内存，而不管当前的内存状态如何。2， 表示内核允许分配超过所有物理内存和交换空间总和的内存学习方法手册基础知识 事务 锁 b+树/sys/block/sda/queue/scheduleranticipatory：猜想时间（适合大文件，如果数据连续猜想时间小为好，如果数据不太连续，时间大较好）deadline：最终期限（对小数据优）cfq：完全公平noop：不调整，在iosched里面没有任何调优参数（适合ssd硬盘和单独的存储）[root@localhost queue]# sysctl -a | grep swapvm.swappiness = 30 #系统优先使用swap和内存[root@localhost queue]# sysctl -a | grep ratiovm.dirty_ratio = 30 #脏页占整个内存的比例，开始刷新vm.dirty_background_ratio = 10 #脏页占程序的百分比 ,开始刷新net.ipv4.tcp_tw_recycle = 0net.ipv4.tcp_tw_reuse = 0#设置为1 减少TIME_WAIT 提高TCP效率建议值vm.swappiness <=10vm.dirty_ratio <=5vm.dirty_background_ratio <= 10#mysql 服务器要不要swap 取决于 你希望mysql半死不活，还是快速重启mount 选项 noatime nodiratime nobarrier选项在mount的时候加上 noatime属性
若有收获，就点个赞吧
0 人点赞
