Speed up DB restore from file (mysql) I am running 5.5.60-MariaDB and I need to upload a DB on the server. The .sql file is located on an SSD drive (locally) and it contains ~700 million rows, with 4 columns indexed. Everything is included in the dump file.

My question is, is it normal that it is taking already 24h and still the DB is not up? The server is not busy doing other things, has 16 cores and 125 GB of RAM.

The command I am using is:

mysql -u root myDB &lt; database_dump.sql


My configuration file is as follows:

[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/var/lib/mysql/mysql.sock
innodb_buffer_pool_size=4GB

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory
!includedir /etc/my.cnf.d


Both the tmp and data directory are on the same SSD disk, and the sql dump as well. I do not know if this a normal time to expect, or I should change something in my settings, that it why I reach out. The engine of the DB to upload is InnoDB.

Thanks!
If you are unsure anything is happening during the restore here are a few ways to check for activity:

Using Top

From the command line run top. It will show you what's happening right now on the system.  You can then filter the list to look for MySQL by pressing o and typing COMMAND=mysql

If RAM and CPU usage looks fairly low it may indicate the bottleneck being your system's IO being the slowest part while you finish the restore. In the top header you'll see the wa value, which is the time the CPU spends waiting for I/O to complete.

You could interrogate the IO subsystem more by using the linux iostat command. You may be hitting IO bottlenecks but, unless you are planning on doing this regularly, this import task isn't going to be typical system usage so don't worry too much about IO performance just from this. You're importing a 157GB file - that's a lot of data to read, insert and build indexes for!

Mysql client

Log into the server using mysql. Run the command show processlist to see a list of currently running queries. You should see the inserts taking place and changing on each execution of the query.

Filesystem

take a look in the /home/ssd/mysql_datadir directory to see if the physical database files are growing as the data is being inserted. This will show you the file size every two seconds. They'll be growing nicely as the data imports.

watch -n 2 ls -la /home/ssd/mysql_datadir/


ibdata1 will be the data for your database. ib_logfile0 and ib_logfile0 is used by MySQL\MariaDB to help ensure data consistency after an unclean shutdown.

It's a bit too late to restart your import but in future you can use a small utility called pipe viewer to see where in the backup file the import has got to by piping the import through it.

pv database_dump.sql | mysql -u root myDB


It will give you a fancy progress bar and show you the read rate of the file and give an estimated completion time. This is only measuring the reading of the file though, not how long MariaDB is taking to process things.

I hope that helps!