SEO

I am recently started to manage a MySQL database with about 20GB in
size, (10GB Data and 10GB index), with both InnoDB tables and MYISAM
table inside.

From time to time, we need to restore the database to different
servers for testing and development purpose. We use mysqldump to make
backups, backup usually take about 30 minutes, which is exceptable.
However, when comes to restore, it is extremely slow, it always took
more than 30 hours to restore the 20GB database. (I had worked with
Oracle, Informix, and SQL Server, I could backup and restore a Tera
bytes database several times in such long time).

As our server is very a powerful Sun sparc machine, which has 8 CPU
and 16GB memory, runs on solaris 10, 64bit, and Hard drive is very
high speed RAID 10 configuration.

We also tested on Linux environment on X86 Servers, with different
MySQL Enterprise 5.0.XX versions, also tried increase
bulk_inser_buffer_size, sort_bufer_size, myisam_sort_buffer_size, etc,
but don't see any acceptable improvement.

Could anyone tell me, is this the nature of MySQL? or am I missing
something important tunning with MySQL?

Thanks,

yeekli@gmail.com writes:

> I am recently started to manage a MySQL database with about 20GB in
> size, (10GB Data and 10GB index), with both InnoDB tables and MYISAM
> table inside.
>
> From time to time, we need to restore the database to different
> servers for testing and development purpose. We use mysqldump to make
> backups, backup usually take about 30 minutes, which is exceptable.
> However, when comes to restore, it is extremely slow, it always took
> more than 30 hours to restore the 20GB database. (I had worked with
> Oracle, Informix, and SQL Server, I could backup and restore a Tera
> bytes database several times in such long time).
>
I have never used this approach, but I have seen it in
MySQL documentation. I assume your database tables probably have
indexes and I believe if you do just the default mysqldump/restore
you could up recalculating the indexes after every insert (a real
drag!) I thought there was a command option, did a quick look and
can't find anything.

In the worst case, just go into your dump file and remove
the index creation statements. Then put the indexes in after you
have restored.

Hopefully someone else will be able to give a better
reference.

Best regards!
--
John
__________________________________________________ _________________
John Murtari Software Workshop Inc.
jmurtari@following domain 315.635-1968(x-211) "TheBook.Com" (TM)
http://thebook.com/

== Quote from John Murtari (jmurtari@thebook.com)'s article
> yeekli@gmail.com writes:
> > I am recently started to manage a MySQL database with about 20GB in
> > size, (10GB Data and 10GB index), with both InnoDB tables and MYISAM
> > table inside.
> >
> > From time to time, we need to restore the database to different
> > servers for testing and development purpose. We use mysqldump to make
> > backups, backup usually take about 30 minutes, which is exceptable.
> > However, when comes to restore, it is extremely slow, it always took
> > more than 30 hours to restore the 20GB database. (I had worked with
> > Oracle, Informix, and SQL Server, I could backup and restore a Tera
> > bytes database several times in such long time).
> >
> I have never used this approach, but I have seen it in
> MySQL documentation. I assume your database tables probably have
> indexes and I believe if you do just the default mysqldump/restore
> you could up recalculating the indexes after every insert (a real
> drag!) I thought there was a command option, did a quick look and
> can't find anything.
> In the worst case, just go into your dump file and remove
> the index creation statements. Then put the indexes in after you
> have restored.
> Hopefully someone else will be able to give a better
> reference.
> Best regards!

have you tried something like this:

% mysqldump --opt whateverdb | mysql --compress -h foo.bar.net whateverdb

you will also have to pass login/passwod to both programs.




--
POST BY: lark with PHP News Reader ;o)