InnoDB核心参数的介绍

最新推荐文章于 2023-07-28 17:23:14 发布
原创 最新推荐文章于 2023-07-28 17:23:14 发布 · 252 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细解析了MySQL数据库中InnoDB存储引擎的写盘机制，包括默认设置、表空间模式、共享表空间文件个数和大小、redo日志设置、脏页刷写策略等关键参数的解释。探讨了不同配置下数据和redo日志的写入方式，以及如何平衡性能与安全性。

#存储引擎默认设置
default_storage_engine=innodb

#表空间模式
innodb_file_per_table=1
# 共享表空间文件个数和大小
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
# "双一" 标准的其中一个 ******
innodb_flush_log_at_trx_commit=1 
-------
1 每个事物提交的时候,写入OS buffer并且必须写入磁盘 1 默认模式
2 每秒写入 0 
3 每个事物提交的时候只写入OS buffer 2 并不实时写入磁盘
-------

每次事务提交都立即刷写binlog到磁盘  "双一" 标准的其中一个

sync_binlog=1    

 

Innodb_flush_method=(O_DIRECT, fsync) *****

作用: 控制的是 Redo buffer  和 buffer pool
fsync    :                       数据方向buffer pool 先写入OS buffer 在刷新到磁盘  redo日志方向 先写入OS buffer 在刷新到磁盘
O_DIRECT : 建议模式              数据方向直接写入磁盘  redo日志方向 先写入OS buffer 在刷新到磁盘
O_DSYNC  :                       数据方向 先写入OS buffer 在刷新到磁盘  日志方向直接写入磁盘:

最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT

最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync


redo日志设置有关的
innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3

脏页刷写策略
innodb_max_dirty_pages_pct=75
还有哪些机制会触发写磁盘?
CSR 
redo满了

MySQL(InnoDB剖析):---Master Thread（MySQL后台核心线程）
m0_46405589的博客
02-21 492
Master Thread 通过前面介绍InnoDB体系架构我们知道，InnoDB存储引擎的主要工作都是在一个单独的后台线程Master Thread中完成的，本文就介绍该线程的具体实现以及该线程可能存在的问题 一、InnoDB 1.0.x版本之前的Master Thread Master Thread具有最高的线程优先级别。其内部由多个循环（loop）组成： 主循环（loop）、后台循环（backgrounp loop）、刷新循环（flush loop）、暂停循环（suspend loop）
mysql优化体系--innodb核心参数配置
yetugeng的专栏
08-28 386
概述 今天主要分享几个关于innodb的参数，以下考虑主要为 Innodb 引擎, key_buffer_size 不考虑。对于实例级别或线程级别参数设置,暂不考虑。 innodb_buffer_pool_size 用于缓存 索引 和 数据的内存大小, 这个当然是越多越好, 数据读写在内存中非常快, 减少了对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。然而内存还...
innodb核心参数
oldboy1999的博客
06-17 314
innodb参数
InnoDB存储引擎的核心参数
cxs_123的博客
03-24 392
"双一"标准 innodb_flush_log_at_commit参数 innodb_flush_log_at_commit参数的作用是控制从 log buffer(如redo buffer、undo buffer) 往磁盘刷写数据的粗略。innodb_flush_log_at_commit参数有三个状态： 为0时：log buffer 中的数据是按照每秒写入到 OS（操作系统缓存）中，然后再每秒刷新一次数据到磁盘中。每次事务的提交不会有任何影响，也就是此状态下 log buffer 的刷新操作和事务提交
常用InnoDB参数介绍
浅谈
07-28 2001
InnoDB状态参数以及常用的调优参数
数据库系列之InnoDB性能有关参数解析
牧羊人的方向
12-25 2611
InnoDB中像sync_binlog这样与事务日志有关的参数设置，对业务性能有着直接的影响，不同的设置是性能和安全的权衡考虑。本文列举了部分参数，并进行测试验证，通过测试结果对比理解不同参数值设置的影响。
mysql数据库innodb引擎的一个重要内核参数swappiness
weixin_33699914的博客
11-12 302
在linux内核中有一个叫 swappiness 的参数。可以调整内存的使用方式。 默认情况下，这个数值为60，这意味着内核会比较多的为文件系统提供内存作为缓存，甚至会将一些程序占据的内存交换到swap中以腾出内存。 这正好与使用innodb引擎的mysql数据库有冲突：innodb缓冲池本来就相当于文件系统的缓存，它会缓存住一部分读取过的数据库...
mysql核心参数_MySQL重要参数介绍
weixin_32774233的博客
02-02 328
1.Skip-External-Locking：即“跳过外部锁定”,据MySQL开发网站的官方解释，External-locking用于多进程条件下为MyISAM数据表进行锁定。如果你有多台服务器使用同一个数据库目录(不建议)，那么每台服务器都必须开启externallocking；当外部锁定(external-locking)起作用时，每个进程若要访问数据表，则必须等待之前的进程完成操作...
mysql的存储核心_关于mysql的2个核心参数
weixin_36219012的博客
01-19 132
以上两个参数是控制MySQL磁盘写入策略以及数据安全性的关键参数。innodb_flush_log_at_trx_commitsync_binlog需要对数据安全性和写入性能根据业务场景进行权衡设置。innodb_flush_log_at_trx_commit参数:0【写性能最好，但当mysqld异常崩溃时会丢失数据】: 由mysql的main_thread每秒将存储引擎log buffer中的r...
innodb技术核心摘录
weixin_38805083的博客
02-17 645
技术内幕-innodb 1Memory 存储引擎默认使用哈希索引，而不是 B+ Tree索引，不支持 TEXT 和 BLOB 列类型。 2MySQL 数据库使用 Memory 存储引擎作为临时表来存放查询的中间结果集。如果中间结果集大于 Memory 存储引擎表的容量设置，又或者中间结果含有 TEXT 或者 BLOB 字段，则 MySQL 数据库会把其转换成 MyISAM 存储引擎表而存在磁盘中。MyISAM 不缓存数据文件，因此这时产生的临时表的性能对于查询会有损失。 3Innodb 内存数据
mysql innodb常用参数介绍
陈小珂
11-09 1140
查看默认的存储引擎是那种 show variables like 'default_storage_engine'; 在 my.cnf加入,重启生效 default_storage_engine=innodb 设置默认存储引擎 innodb初期重要参数设置 show variables like 'innodb_buffer_pool_size'; 1) innodb_buffer_pool_s...
mysql 生产环境下 参数配置_生产环境 MySQL Server 核心参数的配置
weixin_39552286的博客
01-26 390
⑹ innodb_max_dirty_pages_pct● 推荐理由InnoDB较之MyISAM，一个重要特性是InnoDB会在内存中开辟一个Buffer Pool来存储最近访问的数据块/索引块使得下次再次访问这个块时速度能够很快、当InnoDB对需要修改数据块的时候会先记录修改日志，然后直接对Buffer_Pool中的数据块的操作记录日志是顺序写，对数据块的操作是内存操作，这让InnoDB在很多...
MySQL核心参数优化
a82831154的博客
05-05 496
一.数据库服务器配置 CPU：48C 内存：128G DISK：3.2TSSD 二.CPU的优化 innodb_thread_concurrency=32 表示SQL经过解析后，允许同时有32个线程去innodb引擎取数据，如果超过32个，则需要排队； 值太大会产生热点数据，global锁争用严重，影响性能 三.内存的优化 query_cache_type=0 query_ca...
mysql innodb 源码_MySQL源码：深度解析Innodb记录格式源码
weixin_33595380的博客
01-27 640
很长时间又不写东西了，之前已经看过了innodb格式，但现在想研究一下其它方面的东西，突然发现这个又忘了，索性再看看把它写下来。可以通过一个最普遍的插入操作来跟踪Innodb的记录格式，因为在插入时，系统得到的是公共的mysql记录格式record，现在它没有涉及到任何的存储引擎，那么这里不管当前这个表对应的存储引擎是什么，记录格式是一样的，对于插入，mysql函数对应的是ha_write_row...
MySQL常见存储引擎
csrxyr的博客
05-29 265
InnoDB引擎 基本信息：InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。 应用场景：被设计用来处理大量的短期事务 MyISAM引擎 基本信息：在MySQL5.1及之前版本，MyISAM是默认存储引擎。提供了大量的特性，包括全文索引、压缩、空间函数等。但MyISAM不支持事务和行级锁，且有一个严重缺陷是：崩溃之后无法安全恢复。 特性： 加锁与并发：MyISAM...
innodb_data_file_path 参数忘记设置或者重新调整办法
热门推荐
Andes Home 千年的塔 -十年技术，风雨兼程
06-09 1万+
my.cnf文件中，默认配置为 innodb_data_file_path = ibdata1:10M:autoextend 目前该文件已经扩大到了1g多： -rw-r----- 1 mysql mysql 1470103552  4月 16 17:13 ibdata1 修改my.cnf为 innodb_data_file_path = ibdata1:1470M;ibdata2:512
Mysql优化系列 --Innodb重要参数优化
正在编程
05-14 1093
Mysql优化系列 1 --Innodb重要参数优化https://www.cnblogs.com/kevingrace/p/6133818.html 该博客还有大量mysql优化干货：https://www.cnblogs.com/kevingrace/category/796278.html ...
MySQL配置参数
Haibolun的博客
05-08 2425
一.模板 一.基本 user=mysql basedir=/usr/local/mysql datadir=/data/mysql server_id=1 port=3306 socket=/tmp/mysql.sock secure-file-priv=/tmp default_storage_engine=innodb 二.日志相关 log_error=/data/mysql/mysql.err log_timestamps=system innodb_print_all_deadlocks =
优化mysql8 innodb 参数配置
最新发布
02-27
<think>好的，我现在需要帮助用户优化MySQL 8的InnoDB参数配置。首先，我需要回顾一下用户之前的对话。用户之前询问了innodb_buffer_pool_size的值转换，现在他们可能希望更全面地了解如何优化InnoDB的参数。用户可能是在管理自己的数据库服务器，遇到了性能问题，或者想要进行预防性的优化。他们可能对MySQL配置有一定了解，但需要更详细的指导。 接下来，我需要确定用户的具体需求。他们提到“优化mysql8 innodb 参数配置”，所以需要覆盖主要的InnoDB参数，并给出调整建议。考虑到用户之前的问题是关于内存设置的，我应该重点介绍与内存相关的参数，比如缓冲池大小、日志文件等，同时也要包括其他关键参数如并发连接、IO设置等。 我需要整理出常用的优化参数，并解释每个参数的作用、建议值以及调整时的注意事项。例如，innodb_buffer_pool_size是最重要的参数之一，用户已经知道如何计算它的GB值，这里需要建议设置为物理内存的50%-70%。另外，innodb_log_file_size和innodb_log_files_in_group影响事务日志的大小和数量，需要根据写入负载调整。 还要考虑用户的服务器配置可能不同，比如内存大小、存储类型（SSD或HDD）、CPU核心数等，所以需要给出通用的建议，同时提醒用户根据实际情况调整。例如，使用SSD时可以调整innodb_flush_neighbors和innodb_io_capacity。 此外，用户可能没有提到的其他重要参数，如innodb_thread_concurrency、innodb_flush_log_at_trx_commit等，也需要涵盖。需要解释每个参数对性能的影响，比如数据安全性与性能之间的权衡，像innodb_flush_log_at_trx_commit设置为1保证ACID但可能影响性能，而设置为2或0则提高性能但风险增加。 还要提醒用户修改配置的步骤，比如通过my.cnf文件，重启生效，以及调整缓冲池时的逐步增加策略。可能用户不知道如何监控效果，可以建议使用SHOW STATUS和SHOW VARIABLES命令，或者监控工具如Percona Toolkit。 需要避免过于技术化的术语，用简洁明了的语言解释，同时保持专业性。结构要清晰，分点列出每个参数，让用户容易理解和实施。最后，总结关键点，强调根据实际负载测试和监控调整参数的重要性，避免一刀切的配置。</think>### MySQL 8 InnoDB 核心参数优化指南 优化 InnoDB 参数需根据 **硬件配置**（内存/CPU/存储类型）和 **业务场景**（OLTP/OLAP/混合型）调整，以下为关键参数及优化建议： --- #### **一、内存相关优化** 1. **`innodb_buffer_pool_size`** - **作用**：缓存表/索引数据，直接影响查询性能 - **建议值**：物理内存的 **50%-70%** - **示例**： ```ini # 物理内存 32G → 设置为 20G innodb_buffer_pool_size = 20G ``` 2. **`innodb_log_buffer_size`** - **作用**：事务日志缓冲区大小 - **建议值**： - 普通事务：`16M` - 大事务/批量插入：`64M-128M` --- #### **二、I/O 与日志优化** 1. **`innodb_log_file_size`** & **`innodb_log_files_in_group`** - **作用**：控制事务日志文件大小和数量 - **建议值**： - 日志总大小 (`innodb_log_file_size × innodb_log_files_in_group`) 建议为 `1-4小时` 的写入量 - 默认 `2×48M` → 可调整为 `2×1G` 或 `2×2G` ```ini innodb_log_file_size = 2G innodb_log_files_in_group = 2 ``` 2. **`innodb_flush_method`** - **作用**：控制数据文件与日志文件的刷新方式 - **建议值**： - **SSD/NVMe**：`O_DIRECT`（绕过系统缓存） - **HDD**：保持默认或测试不同模式 --- #### **三、并发与线程控制** 1. **`innodb_thread_concurrency`** - **作用**：InnoDB 内部并发线程数 - **建议值**： - **CPU核心数 ≤ 16**：设置为 `0`（自动调整） - **CPU核心数 > 16**：设置为 `2×CPU核心数` 2. **`innodb_read_io_threads`** & **`innodb_write_io_threads`** - **作用**：控制后台 I/O 线程数 - **建议值**： - **SSD**：`8-16` - **HDD**：`4-8` --- #### **四、事务与锁优化** 1. **`innodb_flush_log_at_trx_commit`** - **作用**：事务日志刷新策略（平衡 ACID 与性能） - **选项**： - `1`：每次提交都刷盘（**最高安全，默认值**） - `0`：每秒刷盘（**高性能，可能丢失1秒数据**） - `2`：写入 OS 缓存，依赖系统刷盘（**折中方案**） 2. **`innodb_lock_wait_timeout`** - **作用**：锁等待超时时间（秒） - **建议值**：OLTP 系统建议 `30-60` --- #### **五、其他关键参数** | 参数 | 建议值 | 说明 | | --- | --- | --- | | `innodb_io_capacity` | 2000 (SSD) / 200 (HDD) | 控制后台任务 I/O 吞吐量 | | `innodb_flush_neighbors` | 0 (SSD) / 1 (HDD) | 是否刷新相邻脏页（SSD建议关闭） | | `innodb_buffer_pool_instances` | 8-16 | 缓冲池分区数（减少锁竞争） | | `max_connections` | 500-1000 | 根据实际连接数调整 | --- ### **优化步骤** 1. **监控现状**： ```sql SHOW STATUS LIKE 'Innodb%'; SHOW VARIABLES LIKE 'innodb%'; ``` 2. **逐步调整**：每次修改 1-2 个参数，观察性能变化。 3. **硬件匹配**： - **SSD**：提高 I/O 相关参数（如 `innodb_io_capacity`） - **大内存**：优先分配 `innodb_buffer_pool_size` --- ### **配置文件示例（my.cnf）** ```ini [mysqld] # 内存 innodb_buffer_pool_size = 20G innodb_buffer_pool_instances = 8 # 日志 innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 1 # I/O innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 # 并发 max_connections = 800 innodb_thread_concurrency = 0 ``` --- ### **注意事项** - **测试环境先行**：生产环境调整前需在测试环境验证 - **监控工具**：使用 `Percona Monitoring` 或 `MySQL Enterprise Monitor` 跟踪性能 - **版本差异**：MySQL 8 默认参数已优化，优先调整核心参数（如缓冲池/日志）
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值