我有一个 Percona 8 MySQL 服务器,通过 Docker 运行,充当频繁使用的服务的后备数据存储。整点时,将运行一个脚本,从大约 170 万行的虚拟列中读取一个值(表大小估计为 2.3 GiB),并将该特定值和关联的关键数据插入到系统仅读取的另一个表中。虚拟列是 JSON 查找json_extract(jsonData, '$.root.interestingValue')
并具有GENERATED
标志。其背后的想法是,当用户只对一些特定值感兴趣并且不需要检索绝对最新值时,对被视为实时且定期更新的表施加较小的压力。
查询如下(表/列已重命名)
CREATE TEMPORARY TABLE t1_cache_temp
SELECT
t2.id as uid,
t3.displayText as dt,
t2.virtualColumn as interestingValue
FROM liveTable t2
JOIN otherLiveTable t3 on t2.id = t3.id;
TRUNCATE TABLE t1_cache;
INSERT INTO t1_cache
SELECT uid, dt, interestingValue FROM t1_cache_temp;
DROP TEMPORARY TABLE t1_cache_temp;
该脚本花费的总时间为 47 秒。
虽然从 InnoDB 表读取数据并写入临时表没问题,但将其写入另一个 InnoDB 表会导致数据库上的所有其他操作在前几秒后挂起。INSERT INTO t1_cache...
我通过单独执行每个语句来将范围缩小到该语句。
我还尝试写入新的和完全未使用的表(也使用 InnoDB),这给出了相同的结果。在执行最后一次测试时,只有允许的最大连接数的 2% 正在使用中。此外,只有 48% 的 InnoDB 缓冲池正在使用。
如果我将表切换到 MyISAM,一切都会正常工作,不会出现中断或挂起的情况。此外,如果我删除临时表,无论 InnoDB 还是 MyISAM 引擎,都会导致相同的问题。
虽然使用 MyISAM 可能是更好的选择,但什么可能导致这种情况呢?在配置级别我可以做些什么吗?
你说你的写入量是2.3GiB,但是InnoDB重做日志大小是48MiB。我估计这样的一次写入会填满重做日志 49 次以上。
InnoDB重做日志有固定的大小。写入不会追加或使其变大,而是使其环绕并覆盖日志文件。每次执行此操作时,InnoDB 都必须暂停 SQL,并将脏页从缓冲池刷新到磁盘,直到不需要重做日志的大部分。这不一定是整个重做日志,它可能足以让 MySQL 认为它可以允许更多写入。因此,一次 2.3GiB 写入可能会产生数百个检查点。
我建议阅读这篇博文,以便更好地解释日志文件大小如何影响吞吐量:https://www.percona.com/blog/what-is-a-big-innodb_log_file_size/
默认的 InnoDB 重做日志大小为 48MiB。这仅对于写入流量非常低的应用程序来说就足够了。例如,WordPress 博客主要是只读的,当发生写入时,它们很小。
如果有足够的存储空间,如果您想允许偶尔的 2.3GiB 写入更轻松地吞吐量,我会将重做日志增加到 4GiB。
以适当的余量调整日志文件的大小是一个优点,因为当日志已满 3/4 时会触发检查点。有关详细信息,请参阅https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/ 。
PS 如果可以避免的话我不会使用MyISAM。出于原因请参阅我的答案:https://stackoverflow.com/questions/20148/myisam-versus-innodb/17706717#17706717
该脚本执行的工作量是所需工作量的两倍。改成