在MySQL中执行很大(20 GB).sql文件的更高速度选项 [英] Higher speed options for executing very large (20 GB) .sql file in MySQL
问题描述
我的公司收到了超过20 GB的.sql文件，以响应政府的数据请求.我没有很多选择来获取不同格式的数据，因此我需要一些选项来选择如何在合理的时间内导入数据.我正在使用Navicat的批处理执行工具在高端服务器(Win 2008 64位，MySQL 5.1)上运行它.它已经运行了14个小时，没有迹象表明它即将完成.
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?
谢谢
推荐答案
我猜你的意思是这是mysqldump生成的文件，作为数据库的备份，因此它主要包含CREATE TABLE和INSERT语句.
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.
(但严格来说，SQL脚本可以包含任何内容，例如长时间运行的存储过程的定义和执行，导致死锁的查询等.我假设这不是 情况.)
(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:
-
禁用外键检查:
SET FOREIGN_KEY_CHECKS=0(记住要重新启用 然后).也禁用唯一检查:SET UNIQUE_CHECKS=0
Disable foreign key checks:
SET FOREIGN_KEY_CHECKS=0(remember to re-enable afterwards). Disable unique checks too:SET UNIQUE_CHECKS=0
确保您的 <如果使用MyISAM表，则将c5> 设置为尽可能大.默认值为8MB，最大为4GB.我会尝试1GB.
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.
这些首要提示来自Baron Schwartz的帖子: http://lists.mysql.com/mysql/206866
These first tips come from a post by Baron Schwartz: http://lists.mysql.com/mysql/206866
确保您的 innodb_buffer_pool_size如果您使用InnoDB表，则设置为尽可能大.默认值为8MB，最大为4GB.我会尝试1GB.
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.
设置 innodb_flush_log_at_trx_commit = 2 .
Set innodb_flush_log_at_trx_commit = 2 during the restore if you use InnoDB tables.
@Mark B在在还原过程中禁用密钥.这是您的操作方式:
@Mark B adds a good suggestion below to disable keys during a restore. This is how you do it:
ALTER TABLE <table-name> DISABLE KEYS;
...run your restore...
ALTER TABLE <table-name> 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.
此外，如果包含您的还原的SQL脚本删除并重新创建表，则这将避免禁用密钥.在创建表之后和插入行之前，您将必须找到某种方法来插入命令以禁用键.您可能需要先使用sed进行创意，然后才能将SQL脚本提供给mysql客户端.
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.
这篇关于在MySQL中执行很大(20 GB).sql文件的更高速度选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
