12 个提升 PostgreSQL_TSDB 插入性能的建议

• 作者：滴普科技 2048 实验室 北枫

本文将分别从以下两个方面给出提升插入性能的建议:

一提升 PostgreSQL 性能

二选择并配置 TSDB 以获得更好的插入性能

其中一些方法可能会让您感到比较新奇，但所有十二种方法都将帮助您在使用 PostgreSQL 和 TSDB 时提升写入性能。

写入性能对于许多常见的 PostgreSQL 使用案例至关重要，包括应用程序监控、应用程序分析、物联网监控等。虽然数据库早就应用于时间序列领域，但与这些用例使用数据类型有一个关键区别：与标准的关系“业务”数据不同，更多的场景数据更新被视为插入，而不是覆盖(换句话说，每个新值都成为数据库中的一个新行，而不是用最新值替换该行的先前值)。

如果您在一个需要保留所有数据的场景中运行，而不是覆盖过去的值，优化数据库写入性能变得至关重要。

以下，我概述了一些对改进 PostgreSQL 性能非常有用的技巧。之后，概述了一些特定于 TSDB 插入性能的相关建议。

一. 提升 PostgreSQL 性能

下面是一些在 PostgreSQL 中提升数据插入性能的测试结论：

1.合理使用索引

拥有合适的索引可以加快查询速度，但也不是绝对的。以增量方式维护每一新行的索引需要额外的工作。检查您在表上定义的索引数（使用 psql 命令\d table），并确定索引带来的性能提升是否大于存储和插入所用开销。由于每个系统都是不同的，因此对于索引的规则没有绝对的统一标准——只要合理就行。

2.慎重使用外键约束

有时需要从一个表到其他关系表构建外键（FK）。当存在外键约束时，每个 INSERT 通常都需要从引用的表中读取数据，这会降低性能。考虑一下是否可以对数据进行非规范化处理——我们有时会看到约束的使用非常泛滥，应该要合理地使用。

3.避免使用不必要的唯一约束

开发人员通常都习惯在数据库表中指定主键，许多 orm 框架也都支持使用这些主键。然而，许多场景（包括常见的监控或时间序列应用程序）并不需要它们，因为每个事件或设备读取都可以简单地记录为一个单独的事件，方法是在写入时将其插入超表当前块的尾部。

如果另外定义了唯一约束，则该插入可能需要索引查找来确定行是否已经存在，这将增加额外的开销，影响写入的速度。

4.对预写日志(WAL)和数据使用单独的磁盘

虽然有时候这是一个并不总是需要的更高级的优化，但是如果磁盘成为瓶颈，您可以通过为数据库的预写日志（WAL）和数据使用单独的磁盘（表空间）来进一步提高吞吐量。

5.使用高性能磁盘

有时开发人员会在低性能磁盘的环境中部署数据库，无论是由于性能较差的 HDD、远程 SAN 还是其他类型的存储配置。因为在插入行时，数据会在事务完成之前持久化地存储到预写日志（write-ahead log，WAL）中，磁盘速度慢会影响插入性能。要做的一件事是使用 ioping 命令检查下磁盘 IOPS。

读取测试：

$ ioping -q -c 10 -s 8k .

--- . (hfs /dev/disk1 930.7 GiB) ioping statistics ---

9 requests completed in 208 us, 72 KiB read, 43.3 k iops, 338.0 MiB/s

generated 10 requests in 9.00 s, 80 KiB, 1 iops, 8.88 KiB/s

min/avg/max/mdev = 18 us / 23.1 us / 35 us / 6.17 us

写入测试：

$ ioping -q -c 10 -s 8k -W .

--- . (hfs /dev/disk1 930.7 GiB) ioping statistics ---

9 requests completed in 10.8 ms, 72 KiB written, 830 iops, 6.49 MiB/s

generated 10 requests in 9.00 s, 80 KiB, 1 iops, 8.89 KiB/s

min/avg/max/mdev = 99 us / 1.20 ms / 2.23 ms / 919.3 us

二. 选择并合理配置 TSDB 以获得更好的插入性能

TSDB 经过参数调优后显示提升了写入性能。TSDB 最常见的用途是存储大量数据，用于云基础设施度量、产品分析、web 分析、物联网设备和许多其他时间序列用例。与时间序列数据的典型情况一样，这些场景是以时间为中心的，几乎只附加（大量插入），并且需要在小段时间内快速插入大量数据。

下面有七种使用 TSDB 提高写入性能的建议：

1.采用并行写入。

TSDB 的每个 INSERT 或 COPY 命令（如 PostgreSQL 中的）都作为单个事务执行，因此以单线程方式运行。为了获得更高的接收，您应该并行执行多个 insert 或 COPY 命令。

⭐ 温馨提示：确保你的客户机有足够的内核来并行执行插入任务（在一台 2vcpu 机器上运行 32 个客户机 worker 没有多大帮助-worker 实际上不会并行执行）。

2.批量插入行。

为了获得更高的接收率，您应该在每个 insert 语句中插入包含许多行的数据（或者使用一些批量插入命令，如 COPY）。不要一行一行地插入数据，而是尝试每次插入至少数百（或数千）行。这样数据库在连接管理、事务开销、SQL 解析等方面会花费更少的时间。

3.正确配置共享缓冲区

我们通常推荐 25%的可用 RAM。TSDB 已经自动将共享缓冲区参数配置为非常适合您的硬件规格。

注意：在某些情况下，通常使用虚拟化和受限 cgroups 内存分配，这些自动配置的设置可能并不理想。要检查您的 shared_buffers 是否设置在 25%的范围内，请使用 psql 连接并运行 SHOW shared_buffers

4.以分散的时间顺序写入数据

当区块大小适当时，最新的区块及其相关索引默认会保存在内存中。插入具有最新时间戳的新行将被写入内存中已经存在的这些块和索引中。

如果插入一个过去时间戳的行，则需要从磁盘读入对应于旧块（及其索引）的磁盘页。这将显著增加写入延迟并降低写入吞吐量。

特别是，当您第一次加载数据时，请尝试以排序的、递增的时间戳顺序加载数据。

如果要批量加载有关许多不同服务器、设备等的数据，请注意：

不要按服务器顺序批量插入数据（即，服务器 A、服务器 B、服务器 C 等的所有数据）。这将导致磁盘震荡，因为加载每个服务器将在重新启动之前遍历所有块。

相反，请安排批量加载，以便以更分散的时间戳顺序插入来自所有服务器的数据（例如，第一天并行插入所有服务器，第二天并行插入所有服务器，等等）

5.避免“太大”的区块

为了保持更高的插入效率，您需要将最新的块及其所有相关联的索引保留在内存中，这样对块和索引的写入只会更新内存(写入仍然是持久的，因为在更新数据库页之前，数据会写入磁盘上的 WAL。）

如果块太大，那么对最新块的写入也会开始交换到磁盘。

根据经验，我们建议将最新的块及其所有索引放入数据库的共享缓冲区中。您可以通过chunk_relation_size_pretty方法检查块大小。

=> SELECT chunk_table, table_size, index_size, toast_size, total_sizeFROM chunk_relation_size_pretty('hypertable_name')ORDER BY ranges DESC LIMIT 4;

chunk_table               | table_size | index_size | toast_size | total_size

-----------------------------------------+------------+------------+------------+------------

_tsdb_internal._hyper_1_96_chunk | 200 MB     | 64 MB      | 8192 bytes | 272 MB

_tsdb_internal._hyper_1_95_chunk | 388 MB     | 108 MB     | 8192 bytes | 500 MB

_tsdb_internal._hyper_1_94_chunk | 388 MB     | 108 MB     | 8192 bytes | 500 MB

_tsdb_internal._hyper_1_93_chunk | 388 MB     | 108 MB     | 8192 bytes | 500 MB

如果块太大，可以通过set_chunk_time_interval方法更新以后块的范围。但是，这不会修改现有块的范围（例如，通过将大块重写为多个小块）。

对于单个块比可用内存大得多的配置，我们建议将超表数据转储并重新加载到大小适当的块中。

保留最新的区块适用于所有活动的超表；如果您正在写入两个超表，则两个超表的最新数据块都应该放在 shared_buffers 中。

6.避免太多或太小的块

除非您运行的是多节点 TSDB，否则我们目前不建议使用空间分区。如果您创建 64 个空间分区和时间间隔为每天的块，那么每年将会有 24640 个块。与插入时间相比，这可能会在查询（由于执行计划开销）期间导致更大的性能损失，而且还需要考虑其他一些问题。另一件要注意的事情是：在使用 create_hypertable 创建超表时避免使用不恰当的整数值。

⭐ 温馨提示：

如果时间列使用原生时间戳类型，则任何整数值都应以微秒为单位（因此一天=86400000000）。我们建议使用间隔类型（“1 天”），以避免任何潜在的混淆。

如果时间列是整数或 bigint，请注意合适的范围：如果整数时间戳以秒为单位，请使用 86400；如果 bigint 时间戳以纳秒为单位，则使用 86400000000000。

在这两种情况下，您可以使用 chunk_relation_size_pretty 来确保您的块大小或分区范围合理：

=> SELECT chunk_table, ranges , total_size

FROM chunk_relation_size_pretty('hypertable_name')

ORDER BY ranges DESC LIMIT 4;

chunk_table               |                         ranges                          | total_size

-----------------------------------------+---------------------------------------------------------+------------

_tsdb_internal._hyper_1_96_chunk | {"['2020-02-13 23:00:00+00','2020-02-14 00:00:00+00')"} | 272 MB

_tsdb_internal._hyper_1_95_chunk | {"['2020-02-13 22:00:00+00','2020-02-13 23:00:00+00')"} | 500 MB

_tsdb_internal._hyper_1_94_chunk | {"['2020-02-13 21:30:00+00','2020-02-13 22:00:00+00')"} | 500 MB

_tsdb_internal._hyper_1_93_chunk | {"['2020-02-13 20:00:00+00','2020-02-13 21:00:00+00')"} | 500 MB

7.监视行宽度

插入一个宽行（比如，50、100、250 列）的开销将比插入一个窄行的开销要高得多，因为有更多的网络 I/O、更多的解析和数据处理、对 WAL 的更大的写操作等，因此，如果您有非常宽的行，您可以看到插入率比较低。

如果您考虑使用宽行，且有许多不同类型且互不干扰的列，那么您可能需要尝试使用多个超表–特别是如果您不经常跨这些类型列进行查询的话。

此外，如果很多列都可以采用缺省值的，那么采用 JSONB 类型记录数据是另一个好的选择。也就是说，尽可能对丢失的记录使用空值，而不是默认值，以获得最大的性能增益（空值的存储和查询成本要低得多）。

最后，使用 TSDB 的压缩策略之后，宽行的成本消耗实际上要低得多。行被转换成更多的列压缩形式，可缺省列压缩效果比较好，对于不获取单个列的查询，压缩列不会从磁盘读取。

了解更多 TSDB 产品详情请登录：https://www.deepexi.com/product-new/27