mysql - 维基百科转储表页面链接的问题

Question

我从 dumps.wikimedia.org/enwiki/latest/ 下载了 enwiki-latest-pagelinks.sql.gz 转储。

我把文件打包了，解压后大小为37G。

表结构是这样的:

SHOW CREATE TABLE wp_dump.pagelinks;

CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

我将表导入到一个新的空数据库中:

mysql -D wp_dump -u root -p < enwiki-latest-pagelinks.sql

我运行任务的计算机有 16G RAM，mysql 数据库位于 SSD 上，所以我假设尽管表很大，导入也不会花费太长时间。

但是，该任务已经运行了一天多，并且仍在运行。没有其他进程访问mysql，计算机上没有工作量。

数据库文件本身现在有 79G 大。

ls -lh

-rw-r----- 1 mysql mysql   65 May 11 17:40 db.opt
-rw-r----- 1 mysql mysql 8,6K May 12 07:06 pagelinks.frm
-rw-r----- 1 mysql mysql  79G May 13 16:59 pagelinks.ibd

该表现在有超过 5 亿行。

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'wp_dump';

+------------+------------+
| table_name | table_rows |
+------------+------------+
| pagelinks  |  520919860 |
+------------+------------+

我在想:

enwiki-latest-pagelinks.sql真的超过79G了吗？

pagelinks 真的包含超过 5 亿行吗？

导入pagelinks表真的需要那么长时间吗？

请问您能否提供一些指标，例如预期的表格大小和行数？

更新:2017 年 5 月 14 日:

insert 仍在运行； pagelinks.ibd文件现在130G；行数现在接近 7 亿

更新:2017 年 5 月 16 日:

insert 仍在运行； pagelinks.ibd文件现在204G；行数现在超过 12 亿

我计算了过去两天每秒插入的行数:

行数/秒 = 3236

并且:在 sql 脚本中每个插入语句有数千次插入 (head -41 enwiki-latest-pagelinks.sql | tail -1 | grep -o "("| wc -l是 30471)

所以，我的后续/修改的问题:

给定 37G 的 sql 文件大小和表结构(如上所列)，行数和 idb 文件大小是否符合预期？

rows/sek = 3236 是否是一个好的值(意味着插入表格需要几天时间)？

限制速度因素可能是什么/我怎样才能加快导入速度？

• 禁用索引(并在插入后计算它们)？
• 优化事务(提交(脚本中未设置任何内容)/autocommit(现在启用))？
• 优化变量设置(例如 innodb_buffer_pool_size，现在为 134217728)？

Answer 1

@Sim Betren:我目前正在导入同一张表，我可以获得大约 7700 行/秒。这意味着每天大约有 600.000.000 行。可能最重要的是在 InnoDB 上进行正确的设置:

https://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

这些设置效果很好。根据我的阅读和尝试，InnoDB 喜欢高内存设置。理想情况下，可以使用 16Gb 甚至 32Gb 的机器，然后进一步增加这些设置。但是我在一个适度的设置下得到了 7700 行/秒，这已经有将近 10 年的历史了:

• 英特尔 Q6700 四核
• 8 Gb DDR2 内存

我将那个 10 年前的硬件与 2017 年型号的 500Gb SSD 组合在一起，它专用于这项工作并处理读取和写入。使用旧硬件的原因是 SSD 是设置中最重要的部分(因为 IOPS)。另外，通过使用旧硬件，我节省了一些钱。但是，硬件仅限于 8Gb 的 DDR2。我认为具有 32Gb 或 64Gb 内部存储器的较新的专用机器真的可以飞起来。

软件设置:

• Linux Mint 64 位
• 适用于 Ubuntu 的 MySQL 服务器 5.7.18
• 用于导入的 MySQL Workbench

我也在 Windows 10 上尝试过，两者的速度几乎相同。所以你也可以试试Windows。

注意:我确实尝试将引擎更改为 MyISAM。 MyISAM 可以非常快，也大约 8000 行/秒或更多。但是由于某种原因，导入总是被损坏。所以我会坚持使用 InnoDB

2017 年 6 月 17 日更新:

完成导入。 “pagelinks”表大约有 214Gb，有 12 亿行。大约 112Gb 是原始数据，102Gb 是索引。原始未压缩文件大约为 37Gb。

导入大约需要 2 天 6 小时。平均速度 = 5350 行/秒。使用高端设备(大内存，最好是 64Gb 或更多)和最佳设置，它可能会更快地完成。但我让它在专用机器上 24/7 全天候运行，而且我并不着急，所以 2 天似乎没问题。

2017 年 6 月 18 日更新:

还导入了“page.sql”，因为它包含与 ID 相关的名称。解压文件约5Gb，导入耗时1小时。这看起来很快:页面链接文件大约 37Gb，比“page.sql”大 7 倍。但导入时间要长 50 倍。因此，“pagelinks”花费这么长时间的原因有几个:(A)可能是因为它不适合内存(B)表结构，每次插入很多数据(C)设置。但最有可能的是内存。

结论:尝试使用 32Gb 或 64Gb 内存的 PC。也许更多。并使用能够跟上该内存(500Gb 或更多)的 SSD。 SSD 比内存更重要，因此请先尝试。