特定MySQL大容量插入性能调优

Question

我知道这个问题已经被问了一遍又一遍。但是，对于非常特定的场景，这是一个非常特定的问题。希望你能帮助我。

我运行了一个日志数据库，大约有10个表。存储实际日志条目的主表有大约30个字段，其中5个字段是可搜索的。我要说的是，数据库最近变得中等大小，因为我们在该表中达到了2亿个条目。其他表存储公共数据，其中最大的一个表有4个字段，都是可搜索的，几乎有100万个条目。所有其他表包含的记录都少于10万条。

插入物以尖峰形式出现。我每天凌晨2点从csv文件中获取前一天的日志(格式相当差)，直到上午8点我才能将它们(大约20个文件，每个10万行)插入数据库。然后，我在工作日得到的选择很少(可能一天有1000个)。然后冲洗一下，重复一遍。

SELECT查询非常简单，因为它们大多由一个或两个连接和一个或两个GROUP BY语句组成。搜索这个数据库的人想要立即得到结果，所以我在主表中有5个多列索引，这有助于我进行精确的搜索，目前，SELECT性能相当好。到目前为止，没有查询耗时超过0.1秒。有一些报告，但这些报告需要大约10秒才能生成，这是可以接受的。

目前，我编写了一个C程序，用于从CSV文件中读取数据，对其进行清理，然后在每个插入查询中以1000行为一批插入数据。这些插入并不完全是愚蠢的，因为我需要获取公共数据，查看它是否已经存在于其他表中，如果没有则插入它，如果已经存在则缓存它。它还以每秒插入多少条记录的形式向我提供性能数据。这个程序非常快，并且不需要将数据发送到数据库中，我每秒可以获得大约100,000行。当然，这个程序和数据库位于同一台物理计算机上。

现在，我每天获得的数据呈线性增长，而插入的性能呈对数下降。昨天的数据插入花费了5个半小时，大约每秒400行插入。

通过将具有不同配置的前100万行插入到一个空数据库中，我获得了一些基准测试数据，这就是我得到的结果：

MyISAM表:从每秒1500行开始，在插入第1,000行InnoDB表时，以对数方式减少到每秒700行左右:与MyISAM相同，只有大约每秒100行，在禁用主表上的所有索引的情况下，InnoDB速度更快:从每秒2100行开始，减少到每秒1000行。带有索引的InnoDB，文件系统安装了数据写回(ext3)：与InnoDB相同，只是稍微快了一点，但几乎没有明显的提高。

innodb_buffer_pool_size设置为1000MB

避免创建索引不是一种选择，但很明显，它对性能有很大的影响。然而，我需要更快的插入。正如数据显示的那样，随着数据库的增长，插入需要更长的时间，因此，随着我每天获得的数据量越来越大，我需要在插入性能上有一个巨大的飞跃。如果我能达到每秒10000次或更多的插入次数，那就太好了。

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

要实现这一点，可以在几天内重新创建数据库，然后从读卡器应用程序进行热插拔，可以更改操作系统和MySQL中的任何设置，也可以根据需要添加内存。如有必要，甚至可以更改数据库结构。

因此，我对这里的想法持开放态度。有谁知道什么能帮到我吗？

编辑:我目前正在考虑在内存表中插入新行，然后在实表中执行SELECT操作。希望在插入所有行之后，它只更新和刷新索引一次。我周一试一下这个。以前有没有人尝试过这样的东西？

Answer 1

6,5小时两百万行？

您要存储的数据集有多大？

我使用下面的粗略计算得出一个比较有用的数字：

假设1每秒吞噬35 mb的磁盘很糟糕，那么您应该能够在该时间段内写入(35 * 6,5 * 3600) = ~800 gb。向后计算(800 gb /2 mrows)，得出的平均行大小为400 kb。

如果这些数字看起来是正确的，那么您需要增强您的硬件以提高速度。如果它们完全关闭，则可能存在其他问题。

此外，还可以查看ServerFault上的comparisons of disk i/o for a dedicated MySQL server，以获得一种度量I/O的方法。

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

如果大多数csv数据最终被存储，请考虑将其放入中间表中，并使用基于集合的processing.

• If处理数据库内的数据。如果大多数数据被丢弃，请考虑将引用表移出/缓存到数据库之外，以便能够过滤C代码中的csv数据。

• MySQL没有散列连接，而是依赖于索引循环。确保其他表具有适当的索引

• 尝试对数据库外部的数据进行预排序，以匹配进程中使用的某些其他表的索引(以增加相关数据不会从缓存中清除的可能性)

• 查看partitioning，并查看是否可以使用智能分区方案替换某些索引，而不是维护所有这些索引。

编辑的

已更正计算(400kb)

Answer 2

在做了一整天的小事之后，我做了一件大事情。归根结底，我将插入性能提高了8倍，达到每秒近10000条记录。

下面是我做的事情：

1. 重写加载程序。我说它是用C语言写的，但实际上是用C++写的。将string更改为char*，使用mmap更改fstream，以及其他类似操作，我几乎将性能提高了一倍。(很多人仍然声称C++和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内的复杂表达式进行批量插入(每个insert命令有几行)，而不是对每一行执行SELECT/INSERT。这也带来了非常好的性能提升。这还需要对重新创建数据库的表structures.
4. When进行一些修改，重新创建已经超过20亿行的数据库，创建在不带索引的INFILE插入中获得加载数据的表，并在完成时重新创建它们。我的所有基准测试都表明，插入没有索引的时间，加上创建索引的时间，比插入有索引的表的时间要短。差别不是很大，但很明显(大约快了1.2倍)。我假设B树也会以这种方式得到更好的平衡。
5. 使用MyISAM。我之前的基准测试并不是那么有说服力，但是当使用LOAD DATA INFILE时，InnoDB每次都会失败。在本地测试中，我在MyISAM/no indexes中获得了大约16000条记录，在MyISAM/indexes中获得了12000条记录，在InnoDB/no indexes中获得了9000条记录，在InnoDB/indexes中获得了大约7500条记录。MySQL版本为5.1.47。
6. 对于用于加载数据INFILE的文件，请在装载了tmpfs的分区中创建它们。这也是一个巨大的性能提升，特别是因为您需要写写文件并将其刷新到磁盘，这样MySQL就可以读取它。如果这个tmpfs是不可能的，应该可以使用命名管道来完成。

学到的教训:当MySQL很慢的时候，你很可能可以通过改变你的代码来做更多的事情，而不是得到更强大的硬件。

祝你好运，感谢你们的帮助。

Answer 3

当提到磁盘I/O时，你会遇到这个问题。如果你的磁盘被插入的东西填满了，除非你升级，否则你不会有更快的速度。您没有提到是否可以进行磁盘升级，但我会考虑使用基于SCSI或闪存的磁盘。即使您没有达到SATA的总线限制，您的磁盘肯定是瓶颈。