Restore of a MYSQL Backup is very slow

Question

I am restoring a 26GB mysql dump file on a 16 GB RAM MySQL Running on MacOS.

First I tried to restore the mysql backup like this

mysql -ufoo -pbar foo < foo.dump

This crashed mySQL because the foo.dump contain lots of very funny international characters and the above command was not taking care of the encodings.

So I tried

mysql -uroot -p --default-character-set=utf8 foo
mysql> SET names 'utf8'
mysql> SOURCE foo.dump

This worked and the restore process didn't crash because I guess the funny international characters were handled correctly.

But now the restore process is very slow. For 26 GB file, it is running throughout the night (one table which has 40 million rows is the culprit). I can see that it is restoring approximately 3000 rows every 15 seconds. But at this rate the restore process is going to take forever.

So is there a way to restore the dump file fast and not to mess up the encodings?

Solution

Your process is slow due to heavy disk I/O activity for SQL operations. You need to optimize innodb for intensive operations. Modify your mysql configuration file to have these lines:

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16

innodb_buffer_pool_size: the memory area where InnoDB caches table and index data. When table data is cached in the InnoDB buffer pool, it can be accessed repeatedly by queries without requiring any disk I/O. Data changes are cached rather than immediately written to disk.

A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory else use 50 to 75 percent of system memory. The default buffer pool size is 128MB

innodb_log_buffer_size: The size in bytes of the buffer that InnoDB uses to write to the log files on disk.

A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O.

innodb_log_file_size: The size in bytes of each log file in a log group. A large size ensures that the server can smooth out peaks and troughs in workload activity, which often means that there is enough redo log space to handle more than an hour of write activity. The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O

Innodb_write_io_threads: The number of I/O threads for write operations in InnoDB. The default value is 4. InnoDB uses background threads to service various types of I/O requests. . You can configure the number of background threads that service read and write I/O on data pages using the innodb_read_io_threads and innodb_write_io_threads configuration parameters.

These parameters signify the number of background threads used for read and write requests, respectively. Each background thread can handle up to 256 pending I/O requests.