MySQL 5.6 在 Linux 和 Windows 系统下的性能对比

Question

以下命令在运行Php 5.4的Linux MySQL 5.6服务器上需要2-3秒钟。

exec("mysql --host=$db_host --user=$db_user --password=$db_password $db_name < $sql_file"); 

在类似配置的Windows上，这需要10-15秒钟。 Windows机器具有更多的RAM（16GB）和类似的硬盘。 我安装了MySQL 5.6，并没有进行任何配置更改。 这是在Windows Server 2012上运行的。

我可以更改哪些配置来解决这个问题？

数据库文件创建了大约40个InnoDB表，其中非常少量插入。

编辑：这是我正在运行的文件：

https://www.dropbox.com/s/uguzgbbnyghok0o/database_14.4.sql?dl=0

更新：在Windows 8和7上，它只需要3秒。但在Windows Server 2012上则需要15秒以上。我已禁用System Center 2012，但没有任何改善。

更新2：

我几乎杀死了除mysql和IIS之外的所有服务，但它仍然运行缓慢。是Windows Server 2012中有某些原因导致速度变慢吗？

更新3：

我尝试禁用写缓存缓冲区刷新，现在性能非常好。在我测试的其他机器上不需要这样做。这是否表明磁盘设置存在瓶颈？

https://social.technet.microsoft.com/Forums/windows/en-US/282ea0fc-fba7-4474-83d5-f9bbce0e52ea/major-disk-speed-improvement-disable-write-cache-buffer-flushing?forum=w7itproperf

- Chris Muench

---

可能是基本的问题，如在Windows上创建文件比在Linux上花费更长的时间。如果设置了innodb_file_per_table。尽管创建40个文件需要10-15秒听起来有点多。 - Bjorn Munch

我认为这个问题可能更适合向超级用户或者是数据库stackexchange网站提问。这似乎并不像一个编程问题。 - Spencer Wieczorek

将这个问题标记为 PHP 问题还是有些值得商榷的。你正在运行一些未公开的 SQL 命令，并想知道它们为什么表现不佳。你应该认识到这完全取决于你的 SQL 命令是什么。 - Álvaro González

我认为这里有一个共识，即文件存储器不是展示代码的适当方式，因为它们需要人们获取东西来帮助你，并且链接会断掉，这可能会使问题在你完成后对未来读者的有用性降低。你能把那个文件转移到你的问题中吗？ - halfer

你尝试过在Windows服务器上直接运行mysql < sqlfile.sql吗？ - Murali Mopuru

Answer 1

这就是为什么我们称之为 LAMP stack，也无疑是它如此受欢迎的原因mysql on windows vs Linux。但这更多与稳定性和安全性有关。就性能而言，差异应该是微不足道的。虽然Microsoft专业人员可以通过启用和禁用服务来明确地为MySQL调整Windows Server，但我们更希望看到您的my.ini配置。那么关于在MySQL上使用Windows时应考虑哪些因素呢？

1. 由于各种限制和保护措施，Windows中的服务和策略有时会对性能造成很大的阻碍。

2. 我们还应考虑Apache(httpd.conf)和PHP(php.ini)的配置，因为MySQL与它们紧密耦合。

3. 杀毒软件：在进行有关性能的基准测试时最好禁用它

4. 必须考虑这些参数在my.ini 中，因为您在这里有40个Innodb表

   innodb_buffer_pool_size，innodb_flush_log_at_trx_commit，query_cache_size，innodb_flush_method，innodb_log_file_size，innodb_file_per_table

例如：如果ib_logfile0的文件大小= 524288000，则 524288000 /1048576 = 500，因此innodb_log_file_size应为500M。

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT 

https://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html

5. 在将数据导入InnoDB时，请确保MySQL没有启用自动提交模式，因为这需要每次插入都进行日志刷新到磁盘 SET autocommit=0;

6. 最重要的是innodb_flush_log_at_trx_commit，在这种情况下，它是关于导入数据库的。 将其从“1”（默认）更改为“2”可以在数据导入期间特别是在每个事务提交时将日志缓冲区刷新到操作系统文件缓存中，从而大大提高性能。

参考文献：

https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

https://dba.stackexchange.com/a/72766/60318

http://kvz.io/blog/2009/03/31/improve-mysql-insert-performance/

---

最后，基于此：

mysql --host=$db_host --user=$db_user --password=$db_password $db_name < $sql_file

如果mysqldump（.sql）文件未驻留在导入的同一台主机上，则性能将变慢。请考虑将（.sql）文件复制到需要导入数据库的服务器中，然后尝试在不使用--host选项的情况下进行导入。

- koustuv

---

尝试将innodb_flush_log_at_trx_commit从1更改为2，也尝试了0，但仍然很慢。我开始觉得机器出了问题。 - Chris Muench

项目5（自动提交）-- 如果您正在导入 msyqldump --opt 的输出，则每个 INSERT 将具有数百或数千行，因此 日志 刷新非常好地分摊了。DDLs 总是强制执行 COMMIT。 - Rick James

（复制.sql vs --host）--除非您的网络速度较慢，否则通过远程执行导入操作可能会加快速度。这是因为您将避免从要写入的磁盘中读取.sql文件。（因为“读取”来自网络。） - Rick James

瓶颈与您的网络速度有关，永远会有点长。如果远程执行，则在重负载下也可能出现“转储表时丢失与MySQL服务器的连接”的情况。 - mysqlrockstar

MySql与PHP/Apache完全没有任何关系。 - daniel.gindi

Answer 2

Windows在创建文件方面较慢，这是事实。 40个InnoDB表需要40或80次文件创建。由于它们是小型的InnoDB表，因此在进行CREATE操作之前，最好将innodb_file_per_table = OFF设置为OFF，从而只需要40次文件创建。

MySQL中的良好实践是仅创建一次表，而不经常创建/删除表。如果您的应用程序旨在执行大量CREATE操作，则应重点关注此问题。（请注意，即使在Linux上，表创建时间也不是微不足道的。）

如果这些是临时表...5.7版本将有重大变化，将改善此领域（无论是在哪个操作系统上）的性能。 5.7即将推出。

（在这种情况下，RAM大小无关紧要。）

- Rick James

---

我已经发布了SQL文件。如果有使用Windows电脑的人可以尝试同样的操作，也许这只是一个Windows问题。 - Chris Muench

innodb_file_per_table = OFF 使它实际上变得有点慢了。 - Chris Muench

Windows创建文件速度较慢？你有证据吗？ - DarkBee

@ChrisMuench 我正在运行 Windows 8 x64，使用 WAMP/Apache/2.4.9 (Win32) PHP/5.5.12。在 phpmyadmin 4.1.14 中导入大约需要 3 秒钟。 - DarkBee

@DarkBee 这也是我在 Windows 8 上得到的结果（常规安装）。Windows Server 有什么问题吗？ - Chris Muench