Issues with wikipedia dump table pagelinks

Question

I downloaded the enwiki-latest-pagelinks.sql.gz dump from dumps.wikimedia.org/enwiki/latest/.

I upacked the file, its uncompressed size is 37G.

The table structure is this:

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

I imported the table into a new, empty database:

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

The computer I am running the task on has 16G of RAM and the mysql database is located on a SSD, so I was assuming that despite the table's size the import would not take too long.

However, the task is running since over a day and still running. There are no other processes accessing mysql and there is no workload on the computer.

The database file itself now is 79G large.

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

The table now has over 500 million rows.

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

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

I am wondering:

Is the enwiki-latest-pagelinks.sql really over 79G large?

Does pagelinks really contain over 500 million rows?

Does it really take that long to import the pagelinks table?

Can you provide some metrics the expected table size and the row amount, please?

Update: 14th may, 2017:

insert still running; pagelinks.ibdfile now 130G; number of rows now almost 700 million

Update: 16th may, 2017:

insert still running; pagelinks.ibdfile now 204G; number of rows now over 1.2 billion

I calculated the rows inserted per second over the last two days:

rows/sek = 3236

And: It is many thousand inserts per insert statement in the sql script (head -41 enwiki-latest-pagelinks.sql | tail -1 | grep -o "(" | wc -l is 30471)

So, my follow-up / modified questions:

Is the number of rows and the idb file size to be expected given the sql file size of 37G and the table structure (as listed above)?

Is rows/sek = 3236 a good value (meaning that it takes a few days to insert the table)?

What may be the limiting speed factor / how can I speed up the import?

Disable the indexes (and calculate them after the insert)?
Optimize transactions (commit (nothing set in script) / autocommit (now ON))?
Optimize variable settings (e.g. innodb_buffer_pool_size, now 134217728)?

Issues with wikipedia dump table pagelinks

3 Answers