☰

MySQL 使用大量内存来导入 1-2GB 的 SQL 文件

tag-icon • tag-icon mysql docker memory sql mysqldump
MySQL 使用大量内存来导入 1-2GB 的 SQL 文件

我有一个通过 Docker 在 Ubuntu 18.04 机器上运行的 MySQL 服务器。该机器有 32 GB 的 RAM。

我有大约 300 个 1-2GB 的 SQL 文件需要导入到服务器上的数据库中，并导入到单个表中。

数据库本身完全是空的，总共只有一张表。

当我尝试导入某些文件时，我可以看到内存使用量飙升至 32 GB（100％），并且在分配 100 GB 的交换内存后，我看到的最高内存使用量为 60 GB（60 GB + 32 GB = 92 GB！！！）

考虑到 MySQL 正尝试使用 92 GB 的 RAM 将 1 GB 的 SQL 文件导入空数据库上的单个表中，它可能在做什么？这似乎不是内存泄漏，因为一旦文件导入完成，内存就会变为未分配状态。

我可能应该提到，MySQL 正在使用主机可以直接访问文件的 Docker 卷来存储其数据。

我尝试了许多不同的配置来解决这个问题，此外，有时我会收到一个MySQL server has gone away错误。

我尝试了以下方法：

  • 将表从 InnoDB 更改为 MyISAM 并运行ALTER TABLE tbl_name DISABLE KEYS
  • 环境autocommit=0，unique_checks=0，foreign_key_checks=0
  • max_allowed_packet=999999999G将相关超时变量设置为类似的值
  • 使用mysqltuner.pl生成一些最佳的 InnoDB 配置选项（如下所示）

SQL 文件本身实际上是一个包含数千行的 INSERT 语句。

我该怎么办？我曾考虑将 INSERT 语句分块为多个不同的 INSERT，但由于生成 SQL 文件的程序的多处理流程，这需要进行大量代码重构。

我的cnf：

[mysqld]
max_allowed_packet = 9999999G
wait_timeout = 99999999999
key_buffer_size=10M
innodb_buffer_pool_size=21G
innodb_log_file_size=2G
innodb_buffer_pool_instances=21
innodb_file_per_table
net_read_timeout=999999999999
net_write_timeout=999999999999

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/

即使在添加和调整变量之后，问题仍然存在innodb_*。

显示全局变量：

https://pastebin.com/raw/pXK4MgFb

非常感谢

答案1

文明世界中没有任何数量的 RAM 或磁盘空间可以容纳max_allowed_packet = 9999999G想要分配的内容！将其恢复为默认值，或最多256M。

SQL 文件本身实际上是一个包含数千行的 INSERT 语句。

这是最佳的。

那么，总表大小大约是半兆兆字节？写入这么多磁盘所需的时间是几个小时，具体取决于磁盘类型。

不是不是使用交换空间；这只会减慢 MySQL 的速度。非常慢。但是，您的设置似乎实际上没有使用交换空间。buffer_pool 的 21G 应该可以让 RAM 使用率远低于 32G 的物理大小。但是，您说它飙升到 100%？我想我漏掉了什么。如果有任何交换，MySQL 就会变慢，因此请稍微降低 buffer_pool_size 以避免交换。

autocommit=0使用 InnoDB 效率不高 -- 如果没有COMMIT，则插入数据，然后回滚。如果有COMMIT，则必须做大量工作来准备回滚。设置为ON。

是不是要禁用并重新启用“键”300次？这意味着索引要重建300次。在执行这300次时，文件的运行速度是否越来越慢？

答案2

根据我的经验，我想说：

  • 在导入期间设置：
    • max_allowed_packet=10G（使内存可供使用INSERT）
    • innodb_buffer_pool_size=10G（或更少）来释放服务器内存，您可以在以后使用服务器进行查询时增加此内存，但对于插入来说，这几乎没用
    • innodb_flush_log_at_trx_commit = 0以获得更好的 I/O 性能（对于生产用途，绝对应该删除或设置为 1 或 2！）

INSERT此外，语句对于一个事务来说太大也存在问题。基本上，如果您的事务对于重做日志来说太大，它就会失败。一定要设置AUTOCOMMIT=1，从转储文件中删除所有START TRANSACTION行，如果这没有帮助，请增加日志文件大小：https://dba.stackexchange.com/a/1265/12685

此外，我意识到您正在以编程方式生成这些 SQL 文件。将这些插入逐个发送到数据库服务器会更有效率，尤其是使用上述设置时，而且如果您使用 ，速度会更快INSERT DELAYED。

相关内容