Re: speedup mysql restore
The best way to speed up restores is to not use mysqldump at all. Instead take a snapshot of the filesystem with something like mylvmbackup. Then restore is as fast as copying the files over. Also mysqldump adds disable keys and disable unique checks to the output by default when using the --opt flag. -Eric On 5/20/07, Ray Cauchi [EMAIL PROTECTED] wrote: Have you tried using the use db source /path/to/dumpfile.sql command via the mysql command line client? At 08:28 AM 5/20/2007, Vitaliy Okulov wrote: Здравствуйте, mysql. How i can speedup restore of mysql DB from file created by mysqldump? MySQL is quite fast at reloading data, but if you don't have enough memory or if key_buffer_size is not set high enough, then it can take days to re-index the data. You need to stuff as much RAM as possible in your machine and set key_buffer_size to at least 30% of your machine RAM. I've had indexed take 2+ days to rebuild and adding more RAM and tweaking key_buffer_size allowed the same keys to be rebuilt in under an hour. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com
speedup mysql restore
Здравствуйте, mysql. How i can speedup restore of mysql DB from file created by mysqldump? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speedup mysql restore
--disable-keys, -K For each table, surround the INSERT statements with /*!4 ALTER TABLE tbl_name DISABLE KEYS */; and /*!4 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for non-unique indexes of MyISAM tables. On Sun, May 20, 2007 16:28, Vitaliy Okulov wrote: Здравствуйте, mysql. How i can speedup restore of mysql DB from file created by mysqldump? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: speedup mysql restore
Здравствуйте, Mogens. Вы писали 20 мая 2007 г., 18:47:33: --disable-keys, -K For each table, surround the INSERT statements with /*!4 ALTER TABLE tbl_name DISABLE KEYS */; and /*!4 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for non-unique indexes of MyISAM tables. On Sun, May 20, 2007 16:28, Vitaliy Okulov wrote: Здравствуйте, mysql. How i can speedup restore of mysql DB from file created by mysqldump? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 Ok, but for innodb? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speedup mysql restore
Vitaliy Okulov wrote: How i can speedup restore of mysql DB from file created by mysqldump? [snip] Ok, but for innodb? 1) Disable foreign key checks: SET FOREIGN_KEY_CHECKS=0 (remember to re-enable afterwards). Disable unique checks too: SET UNIQUE_CHECKS=0 2) Insert the rows in primary key order. 3) Make sure your buffer pool is set as large as possible. I can't think of anything else at the moment, though there may be some other things you can do. The foreign key and unique checks are probably done for you already; look at the output of mysqldump. Otherwise, try to make sure the restore is the only thing happening in the database, or even try to ensure the entire machine is not being used for anything else. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: speedup mysql restore
On Sun, May 20, 2007 17:34, Vitaliy Okulov wrote: Здравствуйте, Mogens. --disable-keys, -K On Sun, May 20, 2007 16:28, Vitaliy Okulov wrote: Здравствуйте, mysql. How i can speedup restore of mysql DB from file created by mysqldump? Ok, but for innodb? Well, you might be able to gain some speed, fiddeling with buffer sizes. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speedup mysql restore
At 08:28 AM 5/20/2007, Vitaliy Okulov wrote: Çäðàâñòâóéòå, mysql. How i can speedup restore of mysql DB from file created by mysqldump? MySQL is quite fast at reloading data, but if you don't have enough memory or if key_buffer_size is not set high enough, then it can take days to re-index the data. You need to stuff as much RAM as possible in your machine and set key_buffer_size to at least 30% of your machine RAM. I've had indexed take 2+ days to rebuild and adding more RAM and tweaking key_buffer_size allowed the same keys to be rebuilt in under an hour. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speedup mysql restore
Have you tried using the use db source /path/to/dumpfile.sql command via the mysql command line client? At 08:28 AM 5/20/2007, Vitaliy Okulov wrote: Çäðàâñòâóéòå, mysql. How i can speedup restore of mysql DB from file created by mysqldump? MySQL is quite fast at reloading data, but if you don't have enough memory or if key_buffer_size is not set high enough, then it can take days to re-index the data. You need to stuff as much RAM as possible in your machine and set key_buffer_size to at least 30% of your machine RAM. I've had indexed take 2+ days to rebuild and adding more RAM and tweaking key_buffer_size allowed the same keys to be rebuilt in under an hour. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
