我知道这个问题已经被一遍又一遍地问了。但是,对于非常特定的情况,这是一个非常特定的问题。希望你能帮助我。
我运行一个约有10个表的日志数据库。存储实际日志条目的主表有大约30个字段,其中5个是可搜索的。我要说的是,数据库的大小最近有所增加,因为我们在该表中达到了2亿个条目。其他表存储公共(public)数据,其中最大的一个有4个字段,所有字段均可搜索,几乎有100万个条目。所有其他表均包含少于10万条记录。
嵌件有尖刺。我每天凌晨2点从前一天的日志(格式很差)的csv文件中获取日志,直到凌晨8点,我才将它们(大约20个文件,每行10万行)插入到数据库中。然后,在工作日中,我得到的选择很少(也许一天大约有1000个)。然后冲洗并重复。
SELECT查询非常简单,因为它们主要由一个或两个联接以及一个或两个GROUP BY语句组成。搜索该数据库的人员需要立即获得结果,因此我在主表中有5个多列索引,这些索引可以帮助我进行精确的搜索,并且目前SELECT性能非常好。到目前为止,没有任何查询花费超过0.1秒的时间。有一些报告,但是这些报告大约需要10秒钟才能生成,这是可以接受的。
当前,我编写了一个C程序来读取CSV文件中的数据,对其进行清理,然后按每个INSERT查询以1000行的批数进行插入。这些INSERT并不完全是愚蠢的,因为我需要获取公共(public)数据,查看它是否已经在其他表上,如果不是,则将其插入,如果是,则对其进行缓存。它还以每秒插入多少条记录的形式提供了性能数据。这个程序非常快,并且没有将数据发送到数据库中,我每秒获得约10万行。当然,该程序和数据库位于同一台物理计算机上。
现在,我每天获取的数据呈线性增长,而INSERT的性能呈对数下降。昨天的数据花了5个半小时才能插入,每秒插入大约400行。
通过将具有不同配置的前一百万行插入一个空数据库中,我得到了一些基准数据,这几乎就是我得到的:
MyISAM表:从每秒1500行开始,到插入第1百万行时,对数减少到每秒约700行
InnoDB表:与MyISAM相同,每秒仅快约100行
在主表上禁用所有索引的InnoDB:从每秒2100行开始,减少到每秒1000行。
InnoDB带索引,文件系统安装有数据回写(ext3):与InnoDB相同,只是速度稍快,但几乎没有明显的提高。
innodb_buffer_pool_size设置为1000MB
避免创建索引不是一种选择,但是很明显,它对性能有很大的影响。但是,我需要更快的插入速度。如数据所示,随着数据库的增长,插入将花费更长的时间,因此,随着我每天获得的数据越来越大,我需要在插入性能方面取得巨大飞跃。如果我能达到每秒10000次或以上的插入次数,那就太好了。
系统监视器告诉我,我的主要资源消耗是磁盘I/O,在插入时几乎达到100%。因此,我需要一种超快速的方式来插入数据。我的理论极限是SATA总线的极限,但距离仍然很远。内存使用率似乎没有达到20%的高水平(或者MySQL未正确使用内存)
为此,可以在几天的过程中重新创建数据库,然后从读取器应用程序进行热插拔,可以更改OS和MySQL中的任何设置,可以根据需要添加内存。如有必要,甚至可以更改数据库结构。
所以我真的很愿意在这里提出想法。有人知道有什么可以帮助我的吗?
编辑:我目前正在考虑将新的行插入到内存表中,然后在实际表中执行SELECT INTO。希望它仅在插入所有行后更新和刷新索引一次。我星期一试试。有人尝试过这样的东西吗?
最佳答案
6,5小时内有200万行?
您存储的数据集有多大?
我使用下面的信封计算得出一个有用的数字:
假设ozt_code单个cr脚的磁盘每秒吞下1
mb,那么您应该能够在该时间范围内写入(35 * 6,5 * 3600)=〜35
。向后计算(800 gb/2行),得出的平均行大小为800 gb
kb。
如果这些数字看似正确,则需要增强硬件以提高速度。如果它们完全关闭,则可能还有其他问题。
另外,请查看ServerFault上的comparisons of disk i/o for a dedicated MySQL server,以了解一种测量I/O的方法。
以下是一些随机建议(以防您怀疑其他问题)
编辑
更正的计算(400kb)