# MySQL dump takes too long to restore



## birender (Feb 1, 2018)

I have a mysql dump nearly 1GB ,When I try to restore it takes a very long time.
The OS is installed on virtual box

OS- FreeBSD 10
DB- MySQL 5.5

The table has nearly 2 million entries.
What can I do to speed up the restoring process?


----------



## SirDice (Feb 1, 2018)

birender said:


> What can I do to speed up the restoring process?


There's nothing much to do about that actually. It's a single thread so large databases are going to take a very long time to restore.

One of the databases at a client of mine takes a little over 2 days to restore from scratch.


----------



## birender (Feb 1, 2018)

SirDice said:


> There's nothing much to do about that actually. It's a single thread so large databases are going to take a very long time to restore.
> 
> One of the databases at a client of mine takes a little over 2 days to restore from scratch.



On some forum it said that adjusting in my.cnf can speed up the process.But honestly saying I don't know what I should change there.


----------



## SirDice (Feb 1, 2018)

birender said:


> On some forum it said that adjusting in my.cnf can speed up the process.


To a certain extend. It's still going to be a slow and tedious process, even if you tuned it perfectly. 


birender said:


> But honestly saying I don't know what I should change there.


In that case I recommend not changing anything. You can tune MySQL quite a lot but if you don't know what you're doing you will likely "over" tune things or tune the wrong things. This will only make it worse, not better.

That said, I often use databases/mysqltuner and databases/tuning-primer to get some base values. But none of them are going to be helpful during a restore, both scripts are more for tuning 'regular' database usage.


----------



## birender (Feb 1, 2018)

OK I understand what you mean.
But I will try this databases/mysqltuner and databases/tuning-primer to see the performance of mysql.


----------



## azathoth (Feb 1, 2018)

my.cnf:
look at the mysql or mariadb docs

the innodb buffer is the most important variable

https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_buffer_pool_size

There is another one to make like 8 x 1g segments for the buffer pool!
https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances


You might have to look up the things mysqltuner.pl tells you.

I did
ez
There is one about number of read threads thats a good one to get to 4 or 5..
How many cpu on box and disk setup ?

But inndb buffer is the big one!


----------



## azathoth (Feb 1, 2018)

Ps switch to postgresql.org and use the online pgtuner website


----------



## Datapanic (Feb 1, 2018)

Also check out databases/percona56-server.  I switched over to this based on performance reviews and it's a lot faster than the default Oracle MySQL version in the ports.   There is also a 5.5 and 5.7 version.  It's a drop-in replacement for MySQL.


----------



## azathoth (Feb 1, 2018)

http://mariadb.org/   is like improved mysql


----------



## azathoth (Feb 1, 2018)

I hear from most the www.postgresql.org is best


----------



## birender (Feb 4, 2018)

I have 5 database ,the total size of them is more than 1 GB.
So as solution I made a sh script to take backup of the database as tar file.Now the size of the tar file is 100 mb and it becomes easy to restore now.
But tables with InnoDB engine are corrupted so I converted to myisam engine.


----------



## linux->bsd (Feb 4, 2018)

Try to optimize your tables to see if you can reduce your database's size and get rid of bloat:


```
OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space
and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the
storage engine used by that table.
```


----------



## azathoth (Feb 5, 2018)

birender said:


> I have 5 database ,the total size of them is more than 1 GB.
> So as solution I made a sh script to take backup of the database as tar file.Now the size of the tar file is 100 mb and it becomes easy to restore now.
> But tables with InnoDB engine are corrupted so I converted to myisam engine.




Sounds like you need to slow down.
Innodb is better table type and has row level locking.....
If guna use mysql should use mariadb.
Check out the mysql tutorial in the manual.
Configuring the innodb cache will give giant performance gain.


----------

