MySQL InnoDB 常用优化参数

内存相关

  • innodb_buffer_pool_size 缓冲池,会缓冲索引页、数据页、undo页、插入缓冲、自适应哈希索引、innodb存储的锁信息、数字字典信息等
  • innodb_buffer_pool_instances 允许多个缓冲池实例,每页根据哈希平均分配到不同缓冲池实例中,减少数据库内部资源竞争,增加数据库并发处理能力
  • innodb_old_blocks_pct 确定modpoint位置,默认37,modpoint指新读取到的页放入LRU(最近最少使用算法)列表中的位置,modpoint之后的列表称为old列表,之前的称为new列表
  • innodb_old_blocks_time 表示页读入mid位置后需要等待多久才会被加入到LRU列表的热端
  • innodb_purge_batch_size 控制每次full purge回收的undo页的数量
  • innodb_change_buffer_max_size 控制change buffer最大使用内存数量

IO相关

  • MySQL 5.6 开始支持 Multi-Range Read (MRR),减少磁盘随机访问,将随机访问转化为较为顺序的访问,适用于 range, ref, eq_ref 类型的查询

    • 查看是否开启 show variables like '%optimizer_switch%' mrr=on,mrr_cost_based=off(off为不判断,总是开启)'
    • read_rnd_buffer_size控制键值的缓冲区大小,当大于该值时,执行器对已经缓存的数据根据RowID进行排序,并通过RowID来取得行数据,默认256K
  • 5.6 开始支持Index Condition Pushdown(ICP),取出索引的同时,判断是否可以进行条件过滤,过滤后再去获取记录,可以大大减少上层SQL对记录的索取,支持range,ref,eq_ref,ref_or_null类型的查询。
  • innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略
    • 默认值为1,事务提交时必须调用一次fsync,将日志刷新到磁盘
    • 0,事务提交时不写入重做日志,这个操作仅在master thread中完成,master thread每1秒会进行一次重做日志文件的fsync操作
    • 2,事务提交时将重做日志写入重做日志文件,但仅写入文件系统的缓存中,不进行fsync操作
    • 优缺点,#1数据有保障但是依赖于磁盘的性能;#2在操作系统宕机时会造成事务丢失。#0&#2的设置都会使事务丧失ACID特性
  • innodb_purge_batch_size
  • innodb_max_purge_lag & innodb_max_purge_lag_delay
  • binlog_max_flush_queue_time
  • innodb_read_io_threads & innodb_write_io_threads,多核cpu可以通过这两个参数更有效的利用cpu性能
  • innodb_io_capacity 可以充分利用固态硬盘带来的高IOPS特性
  • innodb_purge_threads 将purge线程从master线程分离出来,提高cpu使用率提升存储引擎性能,innodb1.2之后可以设置多个purge线程
  • innodb_flush_neighbors 刷新邻接页,对于高iops的磁盘,建议关闭此特性

系统相关

  • innodb_fast_shutdown 0表示完成所有full purge和merge insert buffer,并将所有脏页刷新回磁盘;1表示不需要完成full purge和merge insert buffer,但要刷新脏页;2表示不执行以上所有操作,但将日志全部写入日志文件,下次启动时需要恢复(recovery)
  • innodb_force_recovery

SLOW QUERY 查询相关

  • long_query_time
  • log_slow_queries
  • log_queries_not_useing_indexes 开启后,没有用到索引的查询也会记录到slowlog里
  • log_throttle_queries_not_using_indexes 5.6.5新增,表示每分钟允许记录到slow log的未使用索引的sql语句次数
  • 可以使用mysqldumpslow命令分析slow log
  • log_output,有FILE和TABLE两种,动态修改且是全局的

数据安全性相关

  • sync_binlog=[N]表示每写缓冲多少次就同步到磁盘,1表示同步写磁盘的方式写二进制日志。默认为0,由操作系统决定同步
  • innodb_support_xa=1,可以保证二进制日志和InnoDB存储引擎数据文件的同步
  • innodb_log_file_size 指每个重做日志文件的大小,innodb1.2之前必须小于4G,之后扩大到512G
  • innodb_log_files_in_group 指定了日志文件组中重做日志文件的数量,默认为2
  • innodb_mirrored_log_groups 指定了日志镜像文件组的数量,默认为1

在线DDL相关

  • innodb_online_alter_log_max_size Online DDL 原理是在执行创建或删除操作的同时,将INSERT、UPDATE、DELETE 这类 DML 操作日志写入到一个缓存中,此参数控制缓存大小,默认128M
时间: 2024-11-02 05:49:04

MySQL InnoDB 常用优化参数的相关文章

MYSQL INNODB innodb_thread_concurrency相关参数理解

原创水平有限请谅解 虽然这几个参数我以前也有学习过,但是一直没有在源码级别进行证明,所以一直也没有写,但是今天群里有 朋友问到,所以先按照官方手册的加上我自己的理解进行一下解释,以后一定要在源码级别进行下补充 使用MYSQL版本:5.7.14 OS平台: CentOS release 6.5 (Final) 64bit 一.理论基础 首先要理解几个参数我们必须要先知道下面的内容,注意下面内容并不深入,而且只是我自己的理解 1.什么是多线程 实际上MYSQL中的多线程就是POSIX那一套,比如也就

MySQL数据库性能优化之缓存参数优化

在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助. 数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作.而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级.所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO.本文先从

MySQL 数据库性能优化之缓存参数优化详解

数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作.而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级.所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO.本文先从 MySQL 数据库IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化: query_cache_size/query_cache_type (global) Query cache 作用于整个 MySQL

mysql数据库的sql语句常用优化方法

mysql数据库的sql语句常用优化方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where

MYSQL性能优化-安装时优化参数配置提高服务性能

安装时优化参数配置提高服务性能 在Linux下安装Mysql采用默认配置安装的Mysql却未必是工作在最佳性能状态的,需要对其进行优化.一般认为在 Mysql的配置文件中,下列系统参数是比较关键的: (1) interactive_timeout : 服务器在关闭它前在一个交互连接上等待行动的秒数.一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户. 默认数值是28800,我把它改为7200. (2) back_log : 要

加速MySQL SQL语句优化的一些命令方法

引言 优化SQL,是DBA常见的工作之一.如何高效.快速地优化一条语句,是每个DBA经常要面对的一个问题.在日常的优化工作中,我发现有很多操作是在优化过程中必不可少的步骤.然而这些步骤重复性的执行,又会耗费DBA很多精力.于是萌发了自己编写小工具,提高优化效率的想法. 那选择何种语言来开发工具呢? 对于一名DBA来说,掌握一门语言配合自己的工作是非常必要的.相对于shell的简单.perl的飘逸,Python是一种严谨的高级语言.其具备上手快.语法简单.扩展丰富.跨平台等多种优点.很多人把它称为

如何用一款小工具大大加速MySQL SQL语句优化(附源码)

作者介绍 韩锋,宜信技术研发中心数据库架构师.精通多种关系型数据库,曾任职于当当网.TOM在线等公司,曾任多家公司首席DBA.数据库架构师等职,多年一线数据库架构.设计.开发经验.著有<SQL优化最佳实践>一书.   引言   优化SQL,是DBA常见的工作之一.如何高效.快速地优化一条语句,是每个DBA经常要面对的一个问题.在日常的优化工作中,我发现有很多操作是在优化过程中必不可少的步骤.然而这些步骤重复性的执行,又会耗费DBA很多精力.于是萌发了自己编写小工具,提高优化效率的想法.   那

Mysql数据库性能优化一些经验分享

一,性能检测与瓶颈分析 1性能检测常用命令 show status 显示状态信息,参考:Mysql show status命令详解 show processlist 查看当前SQL执行,包括执行状态.是否锁表等,参考:Mysql show processlist命令详解 show variables 显示系统变量,参考:Mysql show variables命令详解 2瓶颈分析常用命令 获取mysql用户下的进程总数 ps -ef | awk '{print $1}' | grep "mysq

MySQL大表优化方案

MySQL大表优化方案 mysql   manong 2016年08月03日发布 当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化: 单表优化 除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑.部署.运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的.而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量: 字段 尽量使用TINYINT.SMALLINT.MEDIU