why is mysql restore so slow (when mysqldump is so fast....)

[terry...@yahoo.com]

I have a database with 80 tables (mysql on Solaris)

Of these tables - there are 2 that are particular massive (one has
2,000,000 records approx)

The tables themselves are of a simple structure (3 fields of INT(1)
and one field of type VARCHAR(20))

Creating the dump with mysqldump is a matter of minutes ....... see
command below
/opt/mysql/bin/mysqldump -Q --opt -u root
-pmypassword MYDATABASE "$@" | gzip > MYDATABASE.sql.gz

BUT when restoring the dump can take almost 24hours (????) and it
always gets stuck at these 2 above-mentioned massive tables, see
restore command below

gzcat /export/home/MYDATABASE.sql.gz | /opt/mysql/mysql/bin/
mysql -u root -p
MYDATABASE

I have tried invoking mysqldump with the '-K' flag to diable indexes,
but this made no difference whatsoever

I have no clean efficient solution for backing up and restoring
mydatanbase and its a major concern - any help or advice greatly
appreciated

thanks

[fabrice régnier]

Hi,

I don't answer to your question about slow restore.
For mysql backup, i don't use mysqldump but i stop the mysqld then i
tar&gzip all the mysql directory.
If i need to restore a table or even the whole database, the restore
only last the moment of the cp command.

ps: i use the MyISAM engine.

regards,

f.

Le 23/01/2011 12:07, terry...@yahoo.com a �crit :

[Axel Schwenke]

"terry...@yahoo.com" <terry...@yahoo.com> wrote:
> I have a database with 80 tables (mysql on Solaris)
>
> Of these tables - there are 2 that are particular massive (one has
> 2,000,000 records approx)

2 million rows is pretty far from "massive"

> The tables themselves are of a simple structure (3 fields of INT(1)
> and one field of type VARCHAR(20))
>
> Creating the dump with mysqldump is a matter of minutes

> BUT when restoring the dump can take almost 24hours (????) and it
> always gets stuck at these 2 above-mentioned massive tables

Not enough information.

What storage engine is it? What's the *exact* table definition?
(remember: indexes are created at restore time!)

What is happening when the restore ist running? SHOW GLOBAL STATUS?
SHOW ENGINE INNODB STATUS? (few samples every ~1 min ).

What says iostat? vmstat?

How is MySQL configured? What MySQL version is it anyway?

Since it is Solaris: running on ZFS? Using an appropriate ZFS block
size? Did you RTFM?

The default configuration for i.e. InnoDB is far from being suitable
for bulk loads. An enabled binlog (with sync_binlog=1?) adds another
penalty. And there are tons more of possible pitfalls.

> I have no clean efficient solution for backing up and restoring
> mydatanbase and its a major concern - any help or advice greatly
> appreciated

R.T.F.M!

If restore performance is an issue (isn't it always?) then physical
backup is a must have. Use a snapshot. Or Enterprise Backup.

XL

[Bodo]

Hi,

for fast restore of your database i would use

innodb_flush_log_at_trx_commit = 0 in my.cnf

and then switch it back to 1

You have to stop/start MySQL each time for this to take effect,

kind regards,

Toni

PS: You are using innobase engine?