CODEWITHSUNDEEP
mysqldatabase-replicationmaster-slave
StackOverflowed
StackOverflowed

Reputation: 5975

Import large MySQL file without replication lagging

I'm about to import a 5 GB table on the command line:

mysql -u dbuser -p customersdb < transactions.sql

Previously I had imported a 2GB file and that caused replication to lag for long periods of time. Is there anyway to avoid that here? Somehow adding a timeout after every few thousand imports would seem ideal in my mind.

I've tried googling it but it doesn't seem like this use case comes up often.

Edit: Additionally, is there anyway to monitor the progress of an import?

Upvotes: 1

Views: 177

Answers (1)

johannes
johannes

Reputation: 15969

The issue causing the lag is that the slave thread is single threaded by default. All operations - both from your import and from other operations - happen in a single queue.

Starting with MySQL 5.6 you can use multi threading there by setting the slave_parallel_workers option. With MySQL 5.6 this will distribute operations from different schemas, with 5.7 it can also parallize within a single schema.

See https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#sysvar_slave_parallel_workers

Upvotes: 1

Related Questions