主页 / dba / 问题 / 300855
Accepted
Googlebot
Asked: 2021-10-09 03:15:14 +0800 CST

为写入密集型机器调整 innoDB

  • 772

我的桌面上有 MariaDB 10.5，有多个磁盘（SSD 和 HDD）用于写入密集型项目。写入单个表的速度很快，并且脏页的百分比保持接近于零1000-3000 writes/s。

但是，当我同时主动写入多个表时，脏页的百分比迅速上升。问题是刷新到磁盘的水平下降到100 writes/s并保持在该水平。

在重新启动之前，此行为将一直存在。

我认为这个问题与Percona 10 年前确定的问题有某种关系（不完全是）。

有什么技巧可以保持冲水的速度吗？

key_buffer_size     = 20M
max_allowed_packet  = 5G
thread_stack        = 256K
thread_cache_size       = 8
innodb_buffer_pool_size = 70G
innodb_log_buffer_size = 512M
innodb_log_file_size = 20G
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 0
innodb_compression_level = 6
innodb_io_capacity=2000
innodb_io_capacity_max=30000
innodb_max_dirty_pages_pct=0
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth=128
innodb_purge_threads=8
innodb_purge_batch_size=600
innodb_flush_neighbors=0
innodb_change_buffer_max_size=50
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb-ft-result-cache-limit=4G
innodb_fatal_semaphore_wait_threshold=7200
innodb_compression_default=ON
innodb_random_read_ahead=1

更新：可能的解决方案

我不发布这个，因为我不确定它是否是真正的解决方案。经过大量实验，我发现问题在于自适应冲洗。我解决了这个问题

innodb_adaptive_flushing=0
innodb_adaptive_flushing_lwm=70

显然，当触发自适应刷新以避免高 I/O 时，它会停留很长时间。

UPDATE2：页面与列压缩

我发现问题是

innodb_compression_default=ON

根据Rick James的建议，我使用列压缩而不是页面压缩创建了类似的表。两种方法的压缩率大约为 300%（页面压缩比页面压缩好 10-20%，适用于整个表而不是选择性列），但在 HDD 上的性能明显不同。

我认为问题在于在 HDD 上同时写入由页面压缩创建的多个稀疏文件（这在 SSD 上应该不是问题）。

我需要重新创建所有表来确定，这个过程非常耗时。

mysql innodb

4 个回答

  1. 我怀疑这innodb_io_capacity_max=30000太大了。试试5000。

    innodb_max_dirty_pages_pct=0-- 脏页好；不要试图用“0”来避免它们。默认值为 75（百分比）；MariaDB 10.5.7 决定一个更好的默认值是 90。试试其中一个。请注意，该设置是GLOBAL动态的，因此不需要重新启动。

    通过不积极刷新“脏”页面，您提供了在实际需要将块（“页面”）写入磁盘之前多次写入的可能性。

    什么工具告诉你“100 writes/s”？

    如需深入了解，请提供全局状态和变量：http: //mysql.rjweb.org/doc.php/mysql_analysis#tuning

    • 1

  2. 每秒 100 次磁盘写入——这听起来像是 HDD 的最大速度。

    每秒写入 100 行——这听起来是一种非常低效的做法INSERT（或UPDATE）。

    autocommit = ON而不是 inside BEGIN..COMMIT， plus innodb_flush_log_at_trx_commit = 1and/or sync_binlog = 1-- 这听起来像是每条语句都会刷新日志。

    向我们展示“写”语句。让我们讨论如何“批处理” ，从而避免每一行INSERT的刷新/同步。

    • 1
  3. Best Answer

    对于 MariaDB 和“行通常具有较长的中文本字段”，请考虑使用列压缩。

    • 1

  4. InnoDB 中针对脏页、重做日志、页面刷新、io 容量等的所有优化都是在假设高写入流量是间歇性的情况下设计的。也就是说，这取决于存在低写入流量的时期，因此所有延迟的刷新都可以“赶上”。

    如果你有一个非常高的连续写入率，而你的 InnoDB 引擎跟不上，那么你就不能继续在单个磁盘设备上抛出更多的写入流量。任何单个磁盘可以处理的 I/O 量都有一个硬上限。

    因此，如果采用其他优化手段，那么您的选择可能是：

    • 迁移到更高端的 I/O 系统，例如 RAID0 或 RAID10。即使你有一个 SSD 磁盘，它也不是无限可扩展的。SSD 磁盘的条带阵列优于单个 SSD 磁盘。

    • 将写入分发到多个 MySQL 实例，即使它们共享相同的存储。您可能拥有世界上最强大的 I/O 阵列，因此最终您的瓶颈将是 InnoDB 本身。

    • 将写入分发到多个服务器主机。通过使用更多服务器，您可以获得更多并行 I/O 容量。我支持一些需要将写入分散到数百个分片上的应用程序，这些应用程序在 docker 容器中运行，每个物理主机使用 2 到 8 个容器。不惜一切代价获得足够的并行化和 I/O 容量来处理写入流量。

    如果您需要进一步扩展，您可能会认为 MySQL 旨在优化 OLTP 流量，并不是实现高写入率的最佳技术。您可能想要评估 RocksDB 或 ScyllaDB 或其他专门的日志结构化数据服务。

    • 1

相关问题