Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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).

share|improve this answer
    
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? –  BradStevenson Mar 5 at 15:07
    
If this is the cause then the pauses will be shorter when it starts on a new table. –  symcbean Mar 5 at 17:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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