本博客参考了我们之前的“Innodb性能优化基础”。2007而且2013．尽管从那时起已经有许多关于调整MySQL变量以获得更好性能的博客，但我认为这个主题值得更新，因为上一次更新是在十年前，MySQL 5.7和8.0已经发布，并进行了一些重大更改。
这些指导原则适用于广泛的应用程序，当然，最佳设置取决于工作负载。
硬件
内存
根据数据库的大小和公司的特定需求，为数据库服务器提供的RAM数量可能有很大差异。有些服务器可能需要几gb的RAM，而其他服务器可能需要数百gb甚至tb的RAM。影响数据库服务器所需RAM数量的因素包括数据库的总大小、并发用户数量和数据库查询的复杂性。随着数据集的规模不断增长，存储和处理这些数据集所需的RAM数量也在增加。通过缓存热数据集、索引和正在进行的更改，InnoDB可以提供更快的响应时间，并以更优化的方式利用磁盘IO。
CPU
从CPU的角度来看，具有多核的更快的处理器可以提供更好的吞吐量。32/64核的cpu仍然很常见，我们看到一些96核的大型客户端，最新的MySQL版本可以比以前更好地利用它们。然而，值得注意的是，仅仅增加更多的CPU内核并不总是能提高性能。CPU核心的使用还取决于应用程序的特定工作负载，例如并发用户的数量或正在运行的查询的复杂性。
存储
数据库服务器使用的存储和磁盘类型对性能和可靠性有很大影响。目前，对于数据库服务器来说，固态硬盘(ssd)或非易失性内存驱动器(NVMe)比传统硬盘驱动器(hdd)更受欢迎，因为它们具有更快的读写速度、更低的延迟和更好的可靠性。虽然NVMe或ssd通常比hdd更贵，但它们提供的更高的性能和可靠性使它们成为需要快速访问数据和最小化停机时间的数据库服务器的经济实惠的选择。对于大多数工作负载，RAID 10仍然是推荐的级别，但是要确保您的RAID控制器可以利用SSD驱动器的性能，而不会成为实际的瓶颈。
操作系统
Linux是高性能MySQL服务器最常用的操作系统。确保使用现代的文件系统，如Linux上的EXT4、XFS或ZFS，并结合最新的内核。它们都有自己的限制和优势:例如，XFS在删除大文件方面速度很快，而EXT4可以在快速SSD驱动器上提供更好的性能Linux下的ZFS取得了很大的进步。在你决定之前进行基准测试。
对于数据库服务器，我们通常建议客户:
- 为MySQL安装并启用了Jemalloc。
- 禁用透明大页面(THP)。
- 通常建议将swappiness设置为1，以降低交换的倾向。
- 设置oom_score_adj为-800。
云
不同的云提供商提供了一系列实例类型和大小，每种实例都具有不同数量的CPU、内存和存储。一些云提供商还为数据库工作负载提供专门的实例，这可能为性能和可伸缩性提供额外的特性和优化。基于云的数据库服务器的好处之一是能够根据需要向上或向下扩展资源。重要的是要考虑扩展的潜在需求，并选择一个实例类型和大小来适应未来的增长。一些云提供商还提供自动伸缩功能，可以根据工作负载需求自动调整实例数量。
MySQL InnoDB设置
(动态)-不需要MySQL重新启动更改。
(静态)-需要MySQL重新启动更改。
通过innodb_buffer_pool_size(动态)——InnoDB非常依赖缓冲池，应该正确设置。通常，较好的值是可用内存的70%-80%。另外，参考下面提到的innodb_buffer_pool_chunk_size。
innodb_buffer_pool_instances(静态)——在高并发工作负载中启用此功能非常有用，因为它可以减少全局互斥对象的争用。最优值可以在测试多个设置后确定，从8开始是一个不错的选择。
innodb_buffer_pool_chunk_size(静态)-定义缓冲池放大或缩小的块大小。这个变量不是动态的，如果配置错误，可能会导致不希望出现的情况。指InnoDB Buffer Pool Resizing: Chunk Change有关配置的详细信息。
innodb_log_file_size(静态)——足够大的InnoDB事务日志对于良好稳定的写性能至关重要。但是更大的日志文件也意味着在崩溃的情况下恢复过程会更慢。但是，该变量自8.0.30起已弃用。参考下面的innodb_redo_log_capacity。
innodb_redo_log_capacity(动态)-在8.0.30中引入，它定义了重做日志文件占用的磁盘空间量。该变量取代了innodb_log_files_in_group和innodb_log_file_size变量。当这个设置被定义时，innodb_log_files_in_group和innodb_log_file_size设置将被忽略(这两个变量从8.0.30开始就被弃用了)。
innodb_log_buffer_size(动态)- InnoDB将更改的数据记录写入其日志缓冲区，保存在内存中，并且它为大型事务保存磁盘I/O，因为它不需要在事务提交前将更改日志写入磁盘。如果事务更新、插入或删除许多行，那么增大日志缓冲区可以节省磁盘I/O。
innodb_flush_log_at_trx_commit(动态)-默认值' 1 '提供了最大的持久性(ACID遵从性)，代价是增加文件系统写/同步。将该值设置为“0”或“2”将提供更高的性能但更低的持久性。至少每秒刷新一次事务。
innodb_thread_concurrency(动态)-随着InnoDB引擎的改进，建议允许引擎通过保持默认值(即零)来控制并发性。如果发现并发性问题，可以调优此变量。建议设置为cpu数+硬盘数的2倍。
innodb_flush_method(静态)-将此设置为O_DIRECT将避免双缓冲的性能损失;这意味着InnoDB绕过操作系统的文件缓存，直接将数据写入磁盘(减少所需的I/O操作数量)。
innodb_online_alter_log_max_size(动态)- InnoDB表在线DDL操作期间使用的临时日志文件大小的上限(以字节为单位)。如果临时日志文件超过了最大大小限制，那么ALTER TABLE操作将失败，所有未提交的并发DML操作将回滚。因此，该选项的大值允许在在线DDL操作期间发生更多的DML，但也延长了DDL操作结束时锁定表以应用日志中的数据的时间。
innodb_numa_interleave(静态)-对于具有大量内存(即> 128GB)的“NUMA启用系统”，我们建议打开NUMA交叉。启用该参数将内存分配配置为在各种cpu -内存通道之间“交错”。这有助于“平衡”内存分配，这样一个CPU就不会成为内存瓶颈。
innodb_buffer_pool_dump_at_shutdown/innodb_buffer_pool_load_at_startup(分别为动态/静态)-这些变量允许你在关闭时将InnoDB缓冲池的内容转储到磁盘，并在启动时将其加载回来，这将预热缓冲池，这样你就不必在重启后使用冷缓冲池启动。
innodb_buffer_pool_dump_pct(动态)-该选项定义转储最近使用的缓冲池页面的百分比。默认情况下，MySQL只保存最活跃访问页面的25%，这对于大多数用例来说都是合理的，它可以比尝试加载缓冲池中的每个页面(100%)更快地加载，其中许多页面对于一般工作负载来说可能不是必需的。如果用例需要，您可以增加这个百分比。
Innodb_io_capacity(动态)-它定义了每秒可用于InnoDB后台任务的I/O操作数(IOPS)，例如从缓冲池中刷新页面和从更改缓冲区中合并数据。理想情况下，保持低的设置为实际，但不是太低，背景活动落后。指这有关配置的更多信息。
Innodb_io_capacity_max(动态)-如果刷新活动落后，InnoDB可以以比innodb_io_capacity更高的速度刷新。innodb_io_capacity_max定义了InnoDB后台任务在这种情况下的最大IOPS。指爱你的ssd -减少innodb_io_capacity_max!有关配置的更多信息。
innodb_autoinc_lock_mode(静态)-将值设置为' 2 '(交错模式)可以消除对auto-inc锁的需求(在表级别)，并且可以在使用多行插入语句将值插入具有自动递增主键的表时提高性能。注意，这需要ROW或MIXED binlog格式。(MySQL 8.0的默认设置是2)
innodb_temp_data_file_path(静态)-定义InnoDB临时表空间数据文件的相对路径、名称、大小和属性。如果没有为innodb_temp_data_file_path指定值，默认行为是在MySQL数据目录中创建一个名为ibtmp1的自动扩展数据文件。对于5.7，建议设置一个max值，以避免由于大量或糟糕的查询而导致datadir分区被填满的风险。8.0引入的会话临时表空间、临时表或内部优化器表不再使用' ibtmp1 '。
innodb_stats_on_metadata(动态)-默认设置“OFF”可以避免不必要的InnoDB统计数据更新，并可以极大地提高读取速度。
innodb_page_cleaners(静态)——InnoDB支持多个page cleaner线程，用于从缓冲池实例中清除脏页。如果在将脏页从缓冲池实例刷新到数据文件时，工作负载受写- io限制，并且如果系统硬件有可用容量，则增加页清理线程的数量可能有助于提高写- io吞吐量。
innodb_deadlock_detect(动态)-此选项可用于禁用死锁检测。在高并发系统上，当多个线程等待同一个锁时，死锁检测可能会导致速度减慢。
InnoDB的应用程序调优
确保您的应用程序已准备好处理可能发生的死锁。回顾一下你的表结构，看看你如何利用InnoDB属性——通过主键聚类，在所有索引中都有一个主键(所以主键要短)，以及通过主键快速查找(试着在连接中使用它)。
结论
还有许多其他选项你可能想要调优，但在这里我们已经介绍了重要的InnoDB参数，与操作系统相关的调整，以及优化MySQL服务器性能的硬件。我希望这能有所帮助!
如果所有这些看起来难以理解或难以理解，我们的世界级MySQL培雷竞技公司电话训是来帮忙的。我们为期两天的密集的伸缩和优化MySQL类带你通过什么你需要知道优化你的MySQL性能。
雷竞技下载官网Percona Distribution for MySQL是最完整、稳定、可扩展和安全的开源MySQL解决方案，为您最关键的业务应用程序提供企业级数据库环境……而且它是免费使用的!
现在就试雷竞技下载官网试MySQL的Percona发行版吧!
参考文献
MySQL 8.0.21和MariaDB 10.5.4在SATA SSD和NVMe存储上io绑定场景下的表现如何
在Percona服务器上启用和禁用Jemalloc雷竞技下载官网
