I am trying to restore a large dump (~300GB MyISAM) into a Mysql server version 5.6.48, but if using:

mysql < file.sql


mysql> source file.sql

In both cases, after starting the process all memory RAM/SWAP gets used, looks like the memory is not released, or is trying to read all input into memory.

Any idea of how to import/restore the file without consuming all system memory?

What currently I am doing as an ugly solution, is to import by chunks but restarting the server to free memory after every import, something like:

for t in chunks_*
mysql db_name < $t && service mysql-server restart && sleep 2   
  • What were the parameters on mysqldump? Some setting on MySQL may need increasing. – Rick James Sep 1 '20 at 22:22
  • Hi @RickJames, I don't have the dump any more but I think was just a plain mysqldump database > dump.sql, how to know from the dump the options/parameters used? – nbari Sep 1 '20 at 22:25
  • Perhaps max_allowed_packet. – Rick James Sep 1 '20 at 22:28
  • When doing the import was set to max_allowed_packet = 128M – nbari Sep 1 '20 at 22:30
  • 1
    For that much RAM, I recommend key_buffer_size = 20G. This change should significantly decrease I/O, thereby making the load faster. (I cannot predict the improvement amount.) The key_buffer lives in RAM, but nothing else big; I am at a loss to know where the issue is. Please provide a screen shot (preferably in text) showing that it has chewed up all of RAM and some of swap. – Rick James Sep 1 '20 at 23:00

Your Answer

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

Browse other questions tagged or ask your own question.