如何提高MySQL转储恢复的性能

Question

我们中许多从事家庭或宠物项目并使用数据库存储结构化数据的人在尝试转储/恢复数据时可能会遇到性能问题。坐着等待另一次转储恢复操作几十分钟甚至几个小时都会让人讨厌。

我有相当典型的机器规格-4核心i5 7300，8GB内存，相当快的M2驱动器和Windows10/MySQL5.7。

问题是，要恢复~4.5Gb文件，需要花费更多的，而不是4小时的。这太荒谬了，我想知道mysqld进程是否甚至没有使用一半的系统资源-CPU/内存/磁盘I/O。

一般说来，这个职位涉及到一些相关问题的总结，包括我下面提到的许多其他职位的贷项。

Answer 1

为了更好的转储恢复操作，我用MySQL参数进行了许多实验。

+--------------------------------+---------+---------+-----------------------+---------------------+
|           Parameter            | Default | Changed | Performance (minutes) | Perfomance gain (%) |
+--------------------------------+---------+---------+-----------------------+---------------------+
| All default                    | -       | -       | 259 min               | -                   |
| innodb_buffer_pool_size        | 8M      | 4G      | 32 min                | +709%               |
| innodb_buffer_pool_size        | 4G      | 6G      | 32 min                | ~0%                 |
| innodb_log_file_size           | 48M     | 1G      | 11 min                | +190%               |
| innodb_log_file_size           | 1G      | 2G      | 10 min                | +10%                |
| max_allowed_packet             | 4M      | 128M    | 10 min                | ~0%                 |
| innodb_flush_log_at_trx_commit | 1       | 0       | 9 min 25 sec          | +5%                 |
| innodb_thread_concurrency      | 9       | 0       | 9 min 27 sec          | ~0%                 |
| innodb_double_write            | -       | off     | 8 min 5 sec           | +18%                |
+--------------------------------+---------+---------+-----------------------+---------------------+

摘要(最佳转储恢复性能)

1. 将innodb_buffer_pool_size设置为半数的RAM
2. Set innodb_log_file_size to 1G
3. Set innodb_flush_log_at_trx_commit到0H 212H 113禁用innodb_double_write仅推荐用于最快性能的innodb_double_write，应该在生产中启用。我还发现，更改另一个相关参数innodb_flush_method并没有改变性能。但是，这可能是Windows的一个问题。如果您的结构复杂，有很多外键--例如，您可以尝试大容量加载environment.
4. Restoring表的技巧，如您所见，页面
5. 底部列出了链接，我试图通过将innodb_thread_concurrency设置为0(并将innodb_read_io_threads设置为最大64)来提高CPU利用率，但结果并没有改变--似乎mysqld进程对于仅用于多核InnoDB的数据(没有表结构)也是相当有效的。

我还更改了许多其他参数，但上述参数是迄今为止转储恢复操作中最相关的参数。

似乎很明显，但新手的问题可能是--我在哪里可以找到和设置这些设置？

在Windows中，my.ini文件位于ProgramData/MySQL/MySQL Server <version>/my.ini。您不会在那里找到一些设置(比如innodb_double_write) --没关系，只需添加到文件的末尾即可。

更改设置的最佳方法是使用MySQL工作台(Server > Options file > InnoDB)。

我把我的学分花在了下面的文章(和许多类似的帖子)上，我发现这些帖子非常有用，

https://www.percona.com/blog/2018/02/22/restore-mysql-logical-backup-maximum-speed/ https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/ https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html https://dba.stackexchange.com/questions/86636/when-is-it-safe-to-disable-innodb-doublewrite-buffering https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html