我的桌面上有 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 上应该不是问题)。
我需要重新创建所有表来确定,这个过程非常耗时。
我怀疑这
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
每秒 100 次磁盘写入——这听起来像是 HDD 的最大速度。
每秒写入 100 行——这听起来是一种非常低效的做法
INSERT
(或UPDATE
)。autocommit = ON
而不是 insideBEGIN..COMMIT
, plusinnodb_flush_log_at_trx_commit = 1
and/orsync_binlog = 1
-- 这听起来像是每条语句都会刷新日志。向我们展示“写”语句。让我们讨论如何“批处理” ,从而避免每一行
INSERT
的刷新/同步。对于 MariaDB 和“行通常具有较长的中文本字段”,请考虑使用列压缩。
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 或其他专门的日志结构化数据服务。