I'm having troubles to import one of the SQl dumps of Wikipedia into a local database. I've downloaded the categorylinks.sql from https://dumps.wikimedia.org/enwiki/latest/ which is around 17GB when unpacked and I tried to load it into a local MySql database via the command line with mysql -uroot > path/to/file I monitor the progress my using pipeviewer in front of the command and it begins with ok speed (1-2MB/s) but it gets slower and slower over time, currently sitting at 18KiB/s after a weekend of running and it gives me an ETA of 4 days and that ETA is rather rising then going down.

The first few INSERT statements run at this speed:

Query OK, 7352 rows affected, 64 warnings (0.48 sec)
Records: 7352  Duplicates: 0  Warnings: 64

Query OK, 7145 rows affected, 64 warnings (0.71 sec)
Records: 7145  Duplicates: 0  Warnings: 64

Query OK, 7139 rows affected, 64 warnings (0.66 sec)
Records: 7139  Duplicates: 0  Warnings: 64`

And currently, after 3 days it slows down to:

Query OK, 7166 rows affected, 64 warnings (5 min 25.89 sec)
Records: 7166  Duplicates: 0  Warnings: 64

Query OK, 7013 rows affected, 64 warnings (5 min 8.13 sec)
Records: 7013  Duplicates: 0  Warnings: 64

The warnings are all just "Invalid utf8mb4 character string", so those should not be the problem, right? And the duplicate checks are turned of by setting unique_checks to 0.

I've already tried to tweak many options, most importantly setting autocommit, unique_checks, and foreign_key_checks to 0. Also have set innodb_flush_log_at_trx_commit = 2 as well as playing around with the values of innodb_buffer_pool_size, innodb_log_buffer_size, innodb_log_file_size, innodb_write_io_threads.

Neither RAM (16GB), nor I/O write speed are a problem as the process does not use nearly the available resources according to htop and iotop. I've read of other people importing >60GB of data in less than 4 hours, so it should be possible to speed it up somehow?

Do you have any recommendations on what other options to change?

Would it be an option to split the SQL file into many smaller files and import them separately? Can you do that parallel?

Thank you very much in advance!

  • Remove indices - import .SQL - recreate indices is usually more fast... the more data and indices - the greater the difference.
    – Akina
    May 28 '18 at 11:37
  • The SQL statement already contains the line /*!40000 ALTER TABLE categorylinks` DISABLE KEYS */;` before the INSERT statements which, according to my understanding already does that?
    – Drukob
    May 28 '18 at 13:17
  • DISABLE KEYS affects only on MyISAM tables and prevents update (as Reference Manual asserts) for non-unique indexes only. It differs from DROP INDEX for all indices... slightly...
    – Akina
    May 28 '18 at 13:23
  • Ok, so I commented out the lines PRIMARY KEY (cl_from,cl_to), KEY cl_timestamp (cl_to,cl_timestamp), KEY cl_sortkey (cl_to,cl_type,cl_sortkey,cl_from), KEY cl_collation_ext (cl_collation,cl_to,cl_type,cl_from) and so far it looks a lot faster, will report back tomorrow if it worked out. Have I understood it correctly that the keys are mainly used to speed up queries on the db?
    – Drukob
    May 29 '18 at 13:32
  • Have I understood it correctly that the keys are mainly used to speed up queries on the db? That's right.
    – Akina
    May 29 '18 at 13:47

Just in case someone else comes looking for the same type of information. I decided that I'd like to import only a subset of wikipedia pages (the most popular subset) and built a couple of tools that build the list of most popular wikipedia pages (from the monthly logs) and filter the sqldump data files before import.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.