mysql queries 很大,在MySQL中执行很大(20 GB).sql文件的更高速度选项

My firm was delivered a 20+ GB .sql file in reponse to a request for data from the gov't. I don't have many options for getting the data in a different format, so I need options for how to import it in a reasonable amount of time. I'm running it on a high end server (Win 2008 64bit, MySQL 5.1) using Navicat's batch execution tool. It's been running for 14 hours and shows no signs of being near completion.

Does anyone know of any higher speed options for such a transaction? Or is this what I should expect given the large file size?

Thanks

解决方案

I guess you mean it's a file produced by mysqldump as a backup of a database, so it contains mostly CREATE TABLE and INSERT statements.

(But strictly speaking, an SQL script can contain anything, such as definition and execution of long-running stored procedures, queries that result in deadlocks, etc. I'll assume this is not the case.)

Here are some things you can do to speed up restore, given that you have the backup file and can't change the type of file it is:

Disable foreign key checks: SET FOREIGN_KEY_CHECKS=0 (remember to re-enable

afterwards). Disable unique checks too: SET UNIQUE_CHECKS=0

Make sure your key_buffer_size is set as large as possible if you use MyISAM tables. The default is 8MB, and the max is 4GB. I'd try 1GB.

These first tips come from a post by Baron Schwartz: http://lists.mysql.com/mysql/206866

Make sure your innodb_buffer_pool_size is set as large as possible if you use InnoDB tables. The default is 8MB, and the max is 4GB. I'd try 1GB.

Set innodb_flush_log_at_trx_commit = 2 during the restore if you use InnoDB tables.

@Mark B adds a good suggestion below to disable keys during a restore. This is how you do it:

ALTER TABLE DISABLE KEYS;

...run your restore...

ALTER TABLE ENABLE KEYS;

But that command affects only one table at a time. You'll have to issue a separate command for each table. That said, it's often the case that one table is much larger than the other tables, so you may need to disable keys only for that one large table.

Also, if the SQL script containing your restore drops and recreates tables, this would circumvent disabling keys. You'll have to find some way to insert the commands to disable keys after the table is created and before rows are inserted. You may need to get creative with sed to preprocess the SQL script before feeding it to the mysql client.

Use the Percona Server version of mysqldump, with the --innodb-optimize-keys option.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值