特定的MySQL批量插入性能调整

我知道这个问题已经被反复询问了。 但是,对于一个非常具体的情况,这是一个非常具体的问题。 希望你能帮助我。

我运行一个日志数据库,大约有10个表。 存储实际日志条目的主表有30个字段,其中5个是可search的。 我想说这个数据库最近已经变得适中了,因为我们在这个表中有2亿个条目。 其他表格存储常用数据,其中最大的有4个字段,全部可search,近100万条。 所有其他表每个包含不到10万条logging。

插入进来尖峰。 我每天凌晨2点从前一天获得日志(格式非常差),每天凌晨2点我都要把这些日志(大约20个文件,每行10万行)插入到数据库中。 然后,我在工作日里得到很less的select(可能每天大约1000)。 然后冲洗并重复。

SELECT查询非常简单,主要由一个或两个包含一个或两个GROUP BY语句的连接组成。 search这个数据库的人希望得到立竿见影的效果,所以我在主表中有5个多列索引,这有助于我进行精确search,而目前的SELECT性能相当不错。 到目前为止,查询已经超过了0.1秒。 有一些报告,但这些需要大约10秒钟生成,这是可以接受的。

目前我有一个C程序,用于读取CSV文件中的数据,将其清理,然后按INSERT查询分批插入1000行。 这些INSERT并不是完全愚蠢的,因为我需要获取公共数据,看看它是否已经在其他表上,如果不是,插入它,如果是,则将其caching。 它还以每秒插入多less条logging的forms提供了性能数据。 这个程序是相当快的,没有把数据发送到数据库,我得到大约每秒10万行。 当然,这个程序和数据库位于同一台物理计算机上。

现在,我每天得到的数据正在线性增长,INSERT的性能呈对数递减。 昨天的数据花了5个半小时的时间插入,每秒大约400行插入。

通过将具有不同configuration的前100万行插入空数据库,我得到了一些基准testing数据,这几乎是我得到的:

MyISAM表格:从1500行开始,在插入第100万行InnoDB表时,对数下降到每秒700行左右:与MyISAM相同,每秒钟只有100行左右,InnoDB的所有索引都禁用表格:以每秒2100行开始,每秒降低到1000行。 InnoDB使用索引,使用数据写回(ext3)挂载的文件系统:与InnoDB相同,只是稍微快一点但几乎不明显。

innodb_buffer_pool_size设置为1000MB

避免创build索引不是一种select,但显然它对性能有很大的影响。 不过,我需要更快的插入。 正如数据显示的那样,随着数据库的增长,插入将花费更长的时间,所以我每天得到的数据都比较大,我需要在插入性能方面有一个巨大的飞跃。 如果我能够达到每秒10000张以上,这将是非常好的。

系统监视器告诉我,我的主要资源消耗是磁盘I / O,插入时几乎达到100%。 因此,我需要一个超快的方式来插入数据。 我的理论上的限制是SATA总线,但这还是相当遥远的。 内存使用率似乎没有那么高,在20%左右(或MySQL没有正确使用内存)

为此,可以在几天内重新创build数据库,然后从阅读器应用程序进行热插拔,可以更改OS和MySQL中的任何设置,如果需要,可以添加内存。 如有必要,更改数据库结构甚至是可以接受的。

所以我对这里的想法非常开放。 任何人都知道什么可以帮助我吗?

编辑:我目前正在考虑将新行插入一个MEMORY表,然后做一个SELECT INTO真正的表。 希望它只会在所有行插入后才更新和刷新索引。 我会在星期一尝试。 有没有人尝试过这样的事情?

6,5小时内有2百万行?
您要存储的数据集有多大?

我使用下面的信封计算来得出一个有用的数字:
假设有一个吞噬每秒35MB的蹩脚磁盘,那么你应该可以在这个时间内写入(35 * 6,5 * 3600)= 800 gb 。 向后计算(800 gb / 2 mrows),得出平均行大小为400 kb。

如果这些数字似乎是正确的,你需要加强硬件来提高速度。 如果完全关闭,可能还有其他一些问题。

另外,请看比较 serverFault上的专用MySQL服务器的磁盘I / O ,以便测量I / O。

这里有一些随机的建议(如果你怀疑有其他问题)

  • 确保在加载过程中消除所有逐行操作
  • 如果大多数csv数据最终被存储,请考虑批量加载到中间表中,并使用基于集合的处理来处理数据库中的数据。
  • 如果大部分数据被丢弃,考虑移动/缓存数据库之外的引用表,以便能够过滤C代码中的csv数据
  • MySQL没有散列连接,但依靠索引循环。 确保其他表具有适当的索引
  • 尝试对数据库之外的数据进行预先排序以匹配进程中使用的其他表的索引(以增加相关数据不会从缓存中清除的可能性)
  • 阅读分区 ,看看是否可以用智能分区方案替换一些索引,而不是维护所有这些索引。

编辑
更正计算(400kb)

经过整整一天做了很多小事情,我建立了一个巨大的事情。 底线是我将插入性能提高了8倍,达到每秒近10000条记录。

这是我做的事情:

  1. 重写加载程序。 我说这是在C中,但实际上是在C ++中。 把字符串改成char *,使用mmap和fstream等等,我的性能几乎翻了一番。 (许多人仍然声称C ++是一样快,或比C更快。我甚至不想在C#/ Java中尝试这个)

  2. 我发现这个网页: http : //kevin.vanzonneveld.net/techblog/article/improve_mysql_insert_performance/这是一个很好的资源(我不隶属于他们),这解释了几乎所有我要去尝试,所有的各种结果。 很多时候,唯一可以提高插入性能的是使用LOAD DATA INFILE。 调整我的表结构,所以我可以插入这样几乎四倍! 我插入的表现。

  3. 我重写了无法使用LOAD DATA INFILE执行的插入,使用ON DUPLICATE KEY UPDATE中的复杂表达式插入(每个插入命令多行),而不是对每行执行SELECT / INSERT。 这也给了很好的性能提升。 这也需要对表格结构进行一些修改。

  4. 当重新创建已经超过20亿行的数据库时,创建获得LOAD DATA INFILE插入的表而不用索引,并在完成时重新创建它们。 我所有的基准测试都表明,不用索引插入的时间加上创建它们的时间比插入到带有索引的表的时间要短。 差异不是很大,但显着(大约快了1.2倍)。 我假设B树也会更好地平衡这种方式。

  5. 使用MyISAM。 我之前的基准并不是那么确定,但是当使用LOAD DATA INFILE时,InnoDB每次都会丢失。 在本地测试中,MyISAM / no索引约为16000条记录,MyISAM /索引约12000条记录,InnoDB / no索引约为9000条记录,InnoDB /索引约为7500条记录。 MySQL版本是5.1.47。

  6. 对于LOAD DATA INFILE的文件,在tmpfs挂载的分区中创建它们。 这也是一个巨大的性能提升,特别是因为你需要编写一个文件并将其刷新到磁盘,所以MySQL可以读取它。 如果这个tmpfs是不可能的,应该可以使用命名管道来做到这一点。

获得的经验:当MySQL速度较慢时,通过更改代码,最有可能通过获得更强大的硬件来做更多的事情。

祝你好运,谢谢大家的帮助。

当提到磁盘I / O时,你可以点击它。 如果你的磁盘插入最大,你不会得到更快,除非你升级。 你没有提到是否可以接受磁盘升级,但我会考虑使用SCSI或基于闪存的磁盘。 即使你没有达到SATA的总线限制,你的磁盘肯定是瓶颈。

我会尝试增加innodb缓冲池大小,看看会发生什么。 对于Innodb,我也会禁用永久刷新innodb_flush_log_at_trx_commit = 0(或= 2)。 默认设置为1,这是写密集型工作负载的瓶颈。 0或2会在两次冲洗之间延迟1秒。 你也可以使用事务来做更大的批量(如果你不明确地使用事务,那么每个插入都是它自己的事务)。

如前所述,预分类输入(通过主键)可以通过消除页面加载中的随机性来帮助减少缓冲池中的数据量。

以上所有都是与innodb相关的。