提高向有索引的表插入数据时的性能

文章分类:

　　最近有一个项目，想用PostgreSQL，先做了做实验。

　　项目里有一个非常巨大的表，会有上亿的纪录，并且还要每3分钟插入10万条左右的数据。为了查询效率，表上还建有几个索引。

　　为了排除很多因素的影响，实验中扩大shared_buffers到300M，增大checkpoint_segments到9。每次实验时都vacuum full analyze，并且所有的插入都在一个事务中进行。

　　在做插入实验的时候，发现了这样的问题：

　　当表中已经有大量纪录的时候，插入速度就会非常慢，以至于完全不能接受。

　　如果删除掉索引，那么插入速度就变得非常快了。加上随后的创建索引的时间，

　　比前一种方式快几十倍。

　　我们知道维护一个索引是需要时间的，但维护索引的花费不应该比重建索引花费大这么多啊?

　　经过观察，我发现，最主要的原因是这样的，使用top监视系统资源，当表上有索引的时候，插入过程中，cpu有大约一半的时间是在wait上 (系统是双cpu)，另外一半是idle。而如果表上没有索引，那么几乎一半的时间是在user上，另外一半是idle

　　不干活，光等待，当然慢了。

　　按理说，这两种情况的IO应该差不多，为什么一个会发生大量的wait，而另外一种却很顺利?

　　猜测一下，可能会是这种情况，当一个表上有索引的时候，表和索引是由不同的文件存放的，并且这几个文件都比较大。当再大量插入数据的时候，PostgreSQL需要频繁的向这几个文件写数据，结构导致磁头频繁移动，

　　于是IO效率急剧下降。

　　考虑到这种情况，如果使用表空间，将这几个索引和表分别存放在不同的物理磁盘，效果会好一些。