3 Replies - 1035 Views - Last Post: 15 August 2019 - 08:03 AM Rate Topic: -----

#1 bioplanet   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 15-August 19

How to speed up upload of data from file Mysql

Posted 15 August 2019 - 03:16 AM

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!

Is This A Good Question/Topic? 0
  • +

Replies To: How to speed up upload of data from file Mysql

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,898
  • Joined: 12-June 08

Re: How to speed up upload of data from file Mysql

Posted 15 August 2019 - 07:10 AM

That seems excessive, but with four indexes I can see that being a pill. You could always try and not have it do indexing and apply that after the fact..

It might be worth running this to see what's processing.

SHOW PROCESSLIST;
Was This Post Helpful? 0
  • +
  • -

#3 bioplanet   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 15-August 19

Re: How to speed up upload of data from file Mysql

Posted 15 August 2019 - 07:55 AM

Thank you for your answer!

It seems that it is still inserting lines into the DB.. The
ibdat1
file is not growing in size, but it's timestamp is constantly being updated...I am suspecting that the problem is that it is re-creating the indexes every time it is inserting a line. Is that possible?
Also, is it possible that I can kill the process, turn indexing off, restore the DB from the dump, turn indexing back on and re-create the indexes?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,898
  • Joined: 12-June 08

Re: How to speed up upload of data from file Mysql

Posted 15 August 2019 - 08:03 AM

Yeah I think you can do that. DROP INDEX and 'kill #'.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1