☰

如何调整 MySQL 以从 MySQL 转储中进行恢复？

tag-icon • tag-icon performance mysql mysqldump performance-tuning
如何调整 MySQL 以从 MySQL 转储中进行恢复？

我的 5GB 小型数据库通过 mysqldump 转储只需 5 分钟，恢复则需要 9 个小时。幸运的是我是在测试运行期间发现这个问题的，而不是实际的紧急情况。

优化哪些参数可以加快这一速度？

我在具有 2GB RAM 的服务器上尝试了以下设置：

innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=50M
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=1G
innodb_log_buffer_size=1G

奇怪的是，即使有了这些激进的设置，也top只显示 mysqld 几乎只使用了分配内存的一小部分：

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
4421 mysql     20   0  247m  76m 5992 S   91  3.7   4:09.33 mysqld

答案1

你应该能够计算这些数字在 mysqldump 之前。

关于你在问题中给出的设置，

innodb_log_file_size=1G

这个设置太大了！！！

这innodb_log_file_size应该是innodb_buffer_pool_size

innodb_log_file_size=128M

一旦设置了此项/etc/my.cnf，您必须执行以下操作来调整 InnoDB 日志文件的大小：

  1. service mysql stop
  2. rm -f /var/lib/mysql/ib_logfile[01]
  3. service mysql start

至于其他设置

innodb_log_buffer_size=1G

您永远都不想在将大量数据发送到 InnoDB 日志文件之前将其缓存在这里，尤其是对于 mysqldump 重新加载或繁重的事务提交。应该小一个数量级。

innodb_log_buffer_size=32M

顺便说一句，您应该在重新加载之前禁用二进制日志记录。否则，所有数据都会进入二进制日志。请执行以下操作之一：

  1. 将此设为SET SQL_LOG_BIN=0;mysqldump 文件的第一行。
  2. 从 MySQL 命令行SET SQL_LOG_BIN=0;运行source < mysqldumpfile >
  3. 注释掉log-bin/etc/my.cnf并重新启动MySQL 5.1，加载mysqldump文件，取消注释log-bin，然后重新启动MySQL。

更新时间 2011-07-24 20:30

如果你有 mysqldump 文件/root/MyData.sql，你仍然可以运行如下命令

SET SQL_LOG_BIN=0;
source /root/MyData.sql

这属于选项 2。

答案2

我遇到过类似的情况，转储恢复耗时太长。在我的案例中，我通过推迟 MySQL 索引创建将恢复速度提高了 3-10 倍。

简而言之，每次插入时都会重新计算索引。因此，最好一次性计算整个表的索引。

以下是 MySQL 如何导出数据的示例：

CREATE TABLE `SOME_TABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SOME_COLUMN` varchar(255) NOT NULL,
  `OTHER_COLUMN` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_SOME_COLUMN` (`SOME_COLUMN`),
  KEY `IDX_OTHER_COLUMN` (`OTHER_COLUMN`)
);

INSERT INTO `SOME_TABLE` (`id`, `SOME_COLUMN`, `OTHER_COLUMN`)
VALUES (...), (...), ... ;

如果表中有数百万行且带有索引，则恢复时间会更长，就像在插入后应用索引一样。

如果您在插入后移动索引创建语句，您将获得更高的导入速度：

CREATE TABLE `SOME_TABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SOME_COLUMN` varchar(255) NOT NULL,
  `OTHER_COLUMN` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `SOME_TABLE` (`id`, `SOME_COLUMN`, `OTHER_COLUMN`)
VALUES (...), (...), ... ;

ALTER TABLE `SOME_TABLE`
ADD UNIQUE KEY `UK_SOME_COLUMN` (`SOME_COLUMN`),
ADD KEY `IDX_OTHER_COLUMN` (`OTHER_COLUMN`);

查看我的存储库对于一个简单的 Python 脚本，它可以执行上述转换。

答案3

您的问题在于 I/O，而不是内存。如果您对磁盘进行性能分析，您会发现它在读写时非​​常不稳定，尤其是当它与备份位于同一磁盘上时。

我会将文件移动到不同的物理驱动器，看看是否有帮助。

相关内容