Welcome to WebmasterWorld Guest from 34.204.189.171

Forum Moderators: open

Mysql speed DB upload

     
9:46 am on Aug 15, 2019 (gmt 0)

New User

joined:Aug 15, 2019
posts:1
votes: 0


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 < 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!
5:38 pm on Aug 15, 2019 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:5040
votes: 57


Is the primary key in the dump in order? Also IIRC indexing all secondary indexes after import is quicker.

There are a few optimizations, if no one else is reading or writing you should be able to import roughly at the speed of the underlying hardware, or at least a decent fraction of it. You can then add indexes afterwards.
11:09 pm on Aug 16, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member Top Contributors Of The Month

joined:Nov 13, 2016
posts:1194
votes: 285


This shouldn't take that long, no matter of the nature of the data.

Why are you mentioning "uploading" ? Apparently, all is already on the server, before you run the importation.

Look at your resources usage, CPU, RAM, I/O to see if there is something not normal. With SSD, so much CPU power and RAM, this should be rather smooth.

Check for log files, to see if there are errors, or warnings.

Is the DB effectively growing as data are imported?
11:21 pm on Aug 16, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11842
votes: 242


Also IIRC indexing all secondary indexes after import is quicker.

i would start here.
2:15 am on Aug 17, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:10458
votes: 1091


Check your ISP for upload v download rates. They are always different, and the upload side is always slower than then download. This could be what you are seeing.

Also, i the db compacted before uploading? All extraneous deleted/compacted?

With 700 million rows that indicates much activity/data and inserts deletes during creation of the db does not mean non-visible rows are actually deleted.

I suspect your slowdown is related to your isp connection more than anything... and your db might be larger than it needs to be.
6:38 am on Aug 17, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11842
votes: 242


I suspect your slowdown is related to your isp connection more than anything

ummm...
The .sql file is located on an SSD drive (locally)
7:51 am on Aug 17, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member Top Contributors Of The Month

joined:Nov 13, 2016
posts:1194
votes: 285


Yes, there is something odd about the use of the word "upload" by the OP. May be he meant "import" .
7:57 am on Aug 17, 2019 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:5040
votes: 57


Looks like the OP asked this in 3 different forums and got what he needed...
[google.com...]
10:32 am on Aug 17, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:10458
votes: 1091


upload a DB on the server

Can only go by what's been asked. :)

Words "local" and "server" have different meanings.
11:38 am on Aug 17, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11842
votes: 242


Can only go by what's been asked. :)

Words "local" and "server" have different meanings.

fair enough.
perhaps you missed this:
Both the
tmp
and
data
directory are on the same SSD disk, and the sql dump as well.
11:40 am on Aug 17, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11842
votes: 242


Looks like the OP asked this in 3 different forums and got what he needed...

and likely won't return.
closing the thread...