PostgreSQL调优数据仓库的最佳实践

 

我发现了大量关于如何调整和优化Postgres for OLTP应用程序性能的在线和打印指南，但是我还没有发现任何特定于数据仓库应用程序的排序。 由于工作负载类型之间存在如此多的差异，我相信在数据库的管理和调整方面必须有一些差异。

我自己的一些：

  • 我从DDL方面发现，我使用的索引更加宽松，因为我通常只担心每天插入一次，并且可以使用索引重建进行批量插入。

  • 我通常会使用整数代理键来获取通常具有多个自然键的数据，以便更快地进行联接

  • 我通常会定义和维护一个非常全面的日期表，它具有预先编制的日期操作（会计日期而不是日历日期，会计年度 - 月，每周的开始日期等），并且在选择语句中使用它而非自由地使用它并在哪里发言。 这通常有助于在CPU绑定的聚合查询中。

    • 我希望能够找到关于内存管理和其他数据库设置的一些信息，但是我很乐意听到任何针对基于Postgres的数据仓库的有用的最佳实践。

    我的经验（当然涉及数据仓库时的规模相当小）：

  • 就像你提到的那样，预先汇总数据很容易是最重要的事情，因为它减少了需要以很多数量级读取的数据量。
  • 避免写短交易，子事务和保存点。 这包括PL / pgSQL中的异常处理。 这些会快速消耗可用的“事务ID”空间，并导致需要重写整个表的昂贵的“环绕”真空。
  • 我发现，如果您需要执行任何操作，分区表使得每个分区可以单独适合内核的缓存，这对于维护和迁移非常有用。 这意味着您可以在磁盘上仅使用1 seq扫描来重新创建分区上的所有索引，而不是为每个索引扫描一次。
  • 像克里斯已经提到的那样，对work_mem和maintenance_work_mem表示慷慨; 如果您的工作负载不适合RAM，那么在内存中保留更多临时数据可以节省I / O和CPU时间，因为更智能的查询计划（最重要的是HashAggregate）。
  • 如果你需要做大量的工作，它可以帮助购买专用的SSD来存储临时文件。

    • 从内存管理的角度来看，你最大的不同之处在于，你通常可以希望将正在工作的OLTP集保留在内存中，而OLAP环境并不是这种情况。 另外很多时候你的加入的组合更大。 这意味着更高的work_mem设置可能非常有用，并且在表格非规范化的情况下，这意味着可以将work_mem推高一点。 我不确定我对shared_buffers的建议是否会发生变化（我倾向于从低开始增加，并在每个步骤测试性能），但如果您正在报告任意大小的集合，则work_mem肯定需要增加。

    链接地址: http://www.djcxy.com/p/11181.html

    上一篇: PostgreSQL tuning best practices for data warehousing

    下一篇: How to extract relationship from text in NLTK