1

I'm importing a large .sql file into my servers MySQL Database,

However the import has only so far imported a few tables and is currently on record ~17mill of one table. However the import pauses after it does every couple thousand record and these pauses are getting longer the more records imported into the table. It's basically going to grind to a halt very soon.

I'm thinking this could be to do with some memory constrictions?

however running iotop shows that jbd2 process is using a huge amount of disk I/O at any time.

showing jbd2 high usage

could this be a cause? and if so are there any methods to disable the journaling until import is completed?

1 Answer 1

1

It doesn't just copy the data - the DBMS has to write the data into a table and maintain indexes. To avoid fragmenting the indexes and to increase overall throughput, it will add a whole load of records at a time and amend the indexes in chunks too - meaning that there will be periods when it's doing high volumes of writes the database file(s) and low volumes of reads from the import file. As the indexes get larger it takes more work to add records.

Turning off the filesystem journalling will help with throughput. (probably a bigger benefit from tuning the DBMS - but this requires a lot more effort, trial and error - which won't be worthwhile if your restores are infrequent).

2
  • Thought something like that could be the issue, would it be expected then that when the import moves to importing records from the next table will the length of the pauses become quicker again or does it depend on the number of records from the overall import? Mar 5, 2014 at 15:07
  • If this is the cause then the pauses will be shorter when it starts on a new table.
    – symcbean
    Mar 5, 2014 at 17:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .