InnoDB导入性能

Question

我很难大量导入一个相当大的InnoDB表，该表由大约1000万行(或7GB)组成(对我来说，这是我迄今为止使用过的最大的表)。

我做了一些研究如何提高Inno的进口速度，目前我的设置如下：

/etc/mysql/my.cnf/
[...]
innodb_buffer_pool_size = 7446915072 # ~90% of memory
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_thread_concurrency=0
innodb_doublewrite = 0
innodb_log_file_size = 1G
log-bin = ""
innodb_autoinc_lock_mode = 2
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_instances=8


import is done via bash script, here is the mysql code:
SET GLOBAL sync_binlog = 1;
SET sql_log_bin = 0;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
SET SESSION tx_isolation='READ-UNCOMMITTED';
LOAD DATA LOCAL INFILE '$filepath' INTO TABLE monster
COMMIT;

数据在CSV文件中提供。

目前，我用200万，300万，…的较小的“测试转储”测试我的设置行每一行，并使用time import_script.sh比较性能。

缺点是我只有一个整体的运行时间，所以我必须等待完整的导入完成才能得到结果。

到目前为止，我的结果：

• 10 000行：<1秒
• 10万行:10秒
• 30万行:40秒
• 200万行: 18分钟
• 300万行: 26分钟
• 400万行：(2小时后取消)

似乎没有“食谱”解决方案，必须自己找出最佳的设置组合。

除了建议修改设置之外，我还想了解更多的信息，如何更好地对导入过程进行基准测试/获得更多的洞察力，了解正在发生的事情，以及瓶颈可能在哪里。

我试着阅读我正在更改的设置的文档，但是我仍然不知道任何副作用，如果我可能会降低性能，并且选择得很差。

目前，我想尝试一下chat的一个建议，在导入和更改表引擎期间使用MyISAM。

我想尝试一下，但目前我的DROP TABLE查询也需要几个小时才能完成。(这似乎是另一个指标，我的设置不如最优)。

其他信息：

我目前使用的机器有8GB的RAM和一个固态混合硬盘驱动器w/ 5400 RAM。

虽然我们还希望从表中删除过时的数据，但我仍然需要快速导入到

( a)在开发和开发时测试automatic data cleanup feature

b)如果我们的服务器崩溃，我们希望使用我们的第二台服务器作为替换(这需要最新的数据，最后的导入需要超过24小时)。

mysql> SHOW CREATE TABLE monster\G
*************************** 1. row ***************************
       Table: monster
Create Table: CREATE TABLE `monster` (
  `monster_id` int(11) NOT NULL AUTO_INCREMENT,
  `ext_monster_id` int(11) NOT NULL DEFAULT '0',
  `some_id` int(11) NOT NULL DEFAULT '0',
  `email` varchar(250) NOT NULL,
  `name` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  `postcode` varchar(20) NOT NULL,
  `city` varchar(100) NOT NULL,
  `country` int(11) NOT NULL DEFAULT '0',
  `address_hash` varchar(250) NOT NULL,
  `lon` float(10,6) NOT NULL,
  `lat` float(10,6) NOT NULL,
  `ip_address` varchar(40) NOT NULL,
  `cookie` int(11) NOT NULL DEFAULT '0',
  `party_id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '2',
  `creation_date` datetime NOT NULL,
  `someflag` tinyint(1) NOT NULL DEFAULT '0',
  `someflag2` tinyint(4) NOT NULL,
  `upload_id` int(11) NOT NULL DEFAULT '0',
  `news1` tinyint(4) NOT NULL DEFAULT '0',
  `news2` tinyint(4) NOT NULL,
  `someother_id` int(11) NOT NULL DEFAULT '0',
  `note` varchar(2500) NOT NULL,
  `referer` text NOT NULL,
  `subscription` int(11) DEFAULT '0',
  `hash` varchar(32) DEFAULT NULL,
  `thumbs1` int(11) NOT NULL DEFAULT '0',
  `thumbs2` int(11) NOT NULL DEFAULT '0',
  `thumbs3` int(11) NOT NULL DEFAULT '0',
  `neighbours` tinyint(4) NOT NULL DEFAULT '0',
  `relevance` int(11) NOT NULL,
  PRIMARY KEY (`monster_id`),
  KEY `party_id` (`party_id`),
  KEY `creation_date` (`creation_date`),
  KEY `email` (`email`(4)),
  KEY `hash` (`hash`(8)),
  KEY `address_hash` (`address_hash`(8)),
  KEY `thumbs3` (`thumbs3`),
  KEY `ext_monster_id` (`ext_monster_id`),
  KEY `status` (`status`),
  KEY `note` (`note`(4)),
  KEY `postcode` (`postcode`),
  KEY `some_id` (`some_id`),
  KEY `cookie` (`cookie`),
  KEY `party_id_2` (`party_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=13763891 DEFAULT CHARSET=utf8

Answer 1

首先，当您将数百万行放入一个InnoDB表中时，您需要知道您要对InnoDB做什么。让我们来看看InnoDB体系结构。

在左上角，有一个InnoDB缓冲池的插图。注意，其中有一部分专门用于插入缓冲区。那有什么用？将对辅助索引的更改从缓冲池迁移到系统表空间(a.k.a )中的插入缓冲区。ibdata1)。默认情况下，诺姆b_变化_缓冲器_最大值_大小设置为25。这意味着多达25%的缓冲池可用于处理次要索引。

在您的示例中，InnoDB缓冲池的容量为6.935 GB。处理辅助索引最多将使用1.734 GB。

现在看看你的桌子。你有13个二级索引。您处理的每一行都必须生成一个辅助索引条目，并将其与行的主键耦合，并将它们作为一对从缓冲池中的插入缓冲区发送到ibdata1中的插入缓冲区。每一行发生13次。再乘以1千万，你几乎就会感觉到瓶颈的到来。

不要忘记，在单个事务中导入1,000万行将将所有内容堆积到一个回滚段中，并在ibdata1中填充撤消空间。

建议

建议#1

我关于导入这个相当大的表的第一个建议是

• 删除所有非唯一索引。
• 导入数据
• 创建所有非唯一索引。

建议#2

去掉重复的索引。在你的情况下，你

KEY `party_id` (`party_id`),
KEY `party_id_2` (`party_id`,`status`)

这两个索引都以party_id开头，您可以增加至少7.6 %的二级索引处理，去除13个索引中的一个。

ALTER TABLE monster DROP INDEX party_id;

建议#3

去掉不使用的索引。查看应用程序代码，看看查询是否使用所有索引。您可能需要查看pt-索引-使用，让它显示哪些索引未被使用。

建议#4

您应该将诺姆b_日志_缓冲器_大小提高到64M，因为缺省值是8M。更大的日志缓冲区可能会提高InnoDB的写入I/O性能。

尾声

将前两项建议付诸实施，做以下工作：

• 删除13个非唯一索引。
• 导入数据
• 创建除party_id索引之外的所有非唯一索引。

也许以下几点可能会有所帮助

CREATE TABLE monster_new LIKE monster;
ALTER TABLE monster_new
  DROP INDEX `party_id`,
  DROP INDEX `creation_date`,
  DROP INDEX `email`,
  DROP INDEX `hash`,
  DROP INDEX `address_hash`,
  DROP INDEX `thumbs3`,
  DROP INDEX `ext_monster_id`,
  DROP INDEX `status`,
  DROP INDEX `note`,
  DROP INDEX `postcode`,
  DROP INDEX `some_id`,
  DROP INDEX `cookie`,
  DROP INDEX `party_id_2`;
ALTER TABLE monster RENAME monster_old;
ALTER TABLE monster_new RENAME monster;

将数据导入monster。然后，运行这个

ALTER TABLE monster
  ADD INDEX `creation_date`,
  ADD INDEX `email` (`email`(4)),
  ADD INDEX `hash` (`hash`(8)),
  ADD INDEX `address_hash` (`address_hash`(8)),
  ADD INDEX `thumbs3` (`thumbs3`),
  ADD INDEX `ext_monster_id` (`ext_monster_id`),
  ADD INDEX `status` (`status`),
  ADD INDEX `note` (`note`(4)),
  ADD INDEX `postcode` (`postcode`),
  ADD INDEX `some_id` (`some_id`),
  ADD INDEX `cookie` (`cookie`),
  ADD INDEX `party_id_2` (`party_id`,`status`);

试试看！

替代

您可以创建一个名为monster_csv的表，作为一个没有索引的MyISAM表，并执行以下操作：

CREATE TABLE monster_csv ENGINE=MyISAM AS SELECT * FROM monster WHERE 1=2;
ALTER TABLE monster RENAME monster_old;
CREATE TABLE monster LIKE monster_old;
ALTER TABLE monster DROP INDEX `party_id`;

将数据导入monster_csv。然后，使用mysqldump创建另一个导入。

mysqldump -t -uroot -p mydb monster_csv | sed 's/monster_csv/monster/g' > data.sql

mysqldump文件data.sql将扩展INSERT命令，一次导入10,000至20,000行。

现在，只需加载mysqldump

mysql -uroot -p mydb < data.sql

最后，去掉MyISAM表

DROP TABLE monster_csv;

Answer 2

我想写一个评论(因为这不是一个明确的答案)，但它变得太长了：

我将给你几个广泛的建议，如果你愿意的话，我们可以详细介绍每一个建议：

• 降低耐久性(你已经做了一些)。最新版本甚至允许做更多。您甚至可以禁用双写缓冲区，因为损坏对导入来说不是问题。
• 增加缓冲:增加事务日志大小，并增加可用缓冲池大小。监视事务日志文件的使用情况和检查点。不要害怕输入大量的日志。
• 避免大的事务-你的回滚会变成满是不必要的数据。这可能是你最大的问题。
• SQL将成为瓶颈，避免SQL开销(handlersocket，memcached)和/或同时以多个线程的并发方式加载SQL开销。并发必须达到一个甜蜜的点，不要太多，不要太少。
• 按主键顺序分段加载数据可以是isse。
• 测试InnoDB压缩，如果IO是瓶颈，而CPU和内存不会使其慢下来
• 尝试创建您的辅助键之后(在某些情况下更快)，不要加载索引数据-禁用键不影响InnoDB。如果没有，则监视插入缓冲区(可能超过缓冲池的一半)。
• 改变或禁用校验和算法-不可能是你的问题，但它成为一个瓶颈的高端闪存卡。
• 最后一招:监视您的服务器，找出您当前的瓶颈，并尝试缓解(InnoDB在这方面非常灵活)。

请记住，其中有些是不安全的，也不适合非导入(正常操作)。

Answer 3

到目前为止，大多数好的建议都已经给出了，但是对于最好的建议却没有很多的解释。我会给出更多细节。

首先，延迟创建索引是一个很好的方法，在其他响应中有足够的细节。我不会再回来了。

一个更大的InnoDB日志文件将对您有很大的帮助(如果您正在使用MySQL 5.6，因为在MySQL 5.5中不可能增加它)。如果要插入7 GB的数据，我建议总日志大小至少为8 GB (将innodb_log_files_in_group保持在其默认值(2)，并将innodb_log_file_size设置为4GB)。这8GB并不准确:它至少应该是重做日志中的导入大小，并且可能是这个大小的两倍或四倍。InnoDB日志大小背后的原因增加了，当日志几乎满时，InnoDB将开始积极地将其缓冲池刷新到磁盘，以避免填充日志(当日志已满时，InnoDB无法执行任何数据库写入，直到将缓冲池的某些页写入磁盘)。

一个更大的InnoDB日志文件将帮助您，但您也应该按主键顺序插入(插入之前对文件进行排序)。如果您按主键顺序插入，InnoDB将填充一页，然后填充另一页，依此类推。如果不按主键顺序插入，则下一次插入可能会在一个已满的页面中结束，并将导致“页面拆分”。此页面分割对于InnoDB来说将是昂贵的，并且会减慢您的导入速度。

您已经有一个内存允许大小的缓冲池，如果您的表不适合它，除了购买更多的RAM之外，没有什么可以做的。但是您的表适合缓冲池，但大于75%的缓冲池，您可以尝试在导入期间将innodb_max_dirty_pages_pct提高到85或95 (默认值为75)。此配置参数告诉InnoDB，当脏页的百分比达到此限制时，应开始积极地刷新缓冲池。通过增加这个参数(如果您在数据大小上幸运的话)，您可能会在导入过程中避免咄咄逼人的IO，并将那些IO延迟到以后。

也许(这是猜测)在许多小事务中导入您的数据将对您有所帮助。我不知道重做日志是如何构建的，但是如果在事务进行过程中在内存中缓冲(以及在需要过多RAM的情况下使用磁盘)，您可能会得到不必要的IOs。您可以尝试这样做:一旦您的文件被排序，将其分割成许多块(尝试使用16 MB和其他大小)并逐一导入它们。这也将允许您控制导入的进度。如果在导入时不希望数据对其他读者部分可见，则可以使用不同的表名导入，稍后创建索引，然后重命名表。

关于您的混合SSD/5400 how磁盘，我不知道这些和如何优化这一点。对于数据库来说，5400 for看起来很慢，但也许SSD正在避免这种情况。可能您正在用顺序写入重做日志的方式填充磁盘的SSD部分，而SSD正在损害性能。我不知道。

您不应该尝试(或小心处理)的一个坏提示是:不要使用多线程:在InnoDB中很难进行优化以避免分页。如果要使用多线程，请在不同的表(或同一表的不同分区)中插入。

如果您正在考虑多线程，也许您有一个多套接字(NUMA)计算机.在这种情况下，请确保避免使用MySQL互换中的疯狂问题。

如果您使用的是MySQL 5.5，请升级到MySQL 5.6:它可以增加重做日志大小，并且有更好的缓冲池刷新算法。

祝你的进口好运。