Postgresql 9加速索引插入(JPA)

Question

我有一个应用程序，它生成大量需要快速插入的数据(大约1300万条记录)。我在Postgres 9.1中使用了JPA2.0/Hibernate，并且我成功地实现了相当好的性能(每秒大约25k个插入)，每几千个插入的多线程和批处理，在大约8分钟内完成整个运行。

但是，我注意到我有几个外键丢失了一个索引，我真的希望从分析的角度来深入挖掘数据，并删除数据到特定的运行。不幸的是，当我将这3个索引添加到获得最多插入量的表中时，性能急剧下降到每秒3k左右。

有什么办法可以避免这种表现慢下来吗？我知道有一个选项是在运行之前删除索引并在最后重新创建它们。另一个笨拙的选择是生成文件中最大表的数据，然后使用COPY。我想我只能在关系中最大的表上做这件事，因为我需要知道外键值(通过序列生成)。

这两种选择似乎都是黑客行为。有没有其他的解决方案，也许对应用程序的侵扰会稍微少一点呢？让postgres推迟索引之类的设置？

任何想法都欢迎。

Answer 1

延迟索引很好，但目前不支持。

添加索引具有成本写入性能。他们是一种交换。

如果索引维护是主要问题，COPY就不会有多大帮助。

最简单的解决方案是删除索引，并在导入完成后重新创建它们。

由于如果DB崩溃，您可以忍受丢失所有数据，因此您有许多选项可以进一步提高性能，包括：

• 未登录表
• fsync=off
• 托管DB的驱动器上的非持久磁盘写入缓存。

如果出现问题，使用上述任何一种方法都会吃掉您的数据。最后一个选项可能也会吃掉文件系统。

我在https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing上写了更多关于这个的文章。

Answer 2

除了Craig的建议，我建议您检查受影响表的存储参数。

我现在的情况和你的差不多。我的系统中最大的表包含了大约2亿条记录，而且性能真的很差。

调优表和索引的存储参数

除了向数据库添加几个索引之外，我还更改了一些表的存储参数，并为表本身的填充因子和索引指定了一个自定义值。

为填充因子设置一个自定义值，可以让您指示PostgreSQL应该为进一步更新保留多少页空间。这同样适用于索引。

有关详细信息，请参阅关于创建表的文档和可用存储参数的说明。

监视您的基础设施

监视和分析您的基础设施。PostgreSQL维基列出了许多有用的工具。

查找长时间运行的语句

通过更改postgresql.conf文件中的下列值来启用语句日志记录：

• log_min_duration_statement=x记录运行时间较长的x毫秒的所有状态
• log_min_messages=level可以帮助您理解JPA生成的语句。

详细信息，请参阅对运行时日志配置的描述

安装pgFounine可以轻松地分析PostgreSQL日志文件。

是挑剔的

除了改变存储参数之外，我还通过优化所有频繁执行的语句获得了很多性能。在各部分中，每次执行只获得100或50毫秒，但对于复杂操作，总共获得了超过5秒的时间。