使用大量UPDATE和PostgreSQL进行流式处理

我是PostgreSQL优化的新手,并且选择适当的工作,不管是什么.因此,我想知道每当我尝试使用PostgreSQL进行不适当的工作时,或者它适合它,我应该正确设置一切.

无论如何,我需要一个包含大量经常变化的数据的数据库.

例如,想象一个ISP,拥有大量客户端,每个客户端都有一个会话(PPP/VPN /无论如何),有两个自我描述的经常更新的属性bytes_received和bytes_sent.有一个表格,每个会话由一个具有唯一ID的行表示:

CREATE TABLE sessions(
    id BIGSERIAL NOT NULL,
    username CHARACTER VARYING(32) NOT NULL,
    some_connection_data BYTEA NOT NULL,
    bytes_received BIGINT NOT NULL,
    bytes_sent BIGINT NOT NULL,
    CONSTRAINT sessions_pkey PRIMARY KEY (id)
)

并且随着会计数据的流动,此表会收到许多UPDATE,如下所示:

-- There are *lots* of such queries!
UPDATE sessions SET bytes_received = bytes_received + 53554,
                    bytes_sent = bytes_sent + 30676
                WHERE id = 42

当我们收到一个永无止境的流,有很多(比如每秒1-2次)更新一个有很多(如几千个)会话的表,可能要归功于MVCC,这使得PostgreSQL 非常繁忙.有没有办法加速一切,或者Postgres不完全适合这个任务,我最好认为它不适合这个工作,把这些计数器放到另一个存储器,如memcachedb,使用Postgres仅用于相当静态的数据？但我会错过不经常查询这些数据的能力,例如找到TOP10下载器,这不是很好.

不幸的是,数据量不能降低太多.ISP会计示例全部用于简化说明.真正的问题在于另一个系统,这种结构在某种程度上难以解释.

谢谢你的建议!

数据库确实不是收集大量小更新的最佳工具,但由于我不知道您的可查询性和ACID要求,因此我无法真正推荐其他内容.如果这是一种可接受的方法,zzzeek建议的应用程序端更新聚合可以帮助显着降低更新负载.

有一种类似的方法可以为您提供耐用性和以一些性能成本查询更新数据的能力.创建一个缓冲表,可以收集需要更新的值的更改并在其中插入更改.在事务中定期将表重命名为其他内容并创建一个新表来代替它.然后在事务中聚合所有更改,对主表执行相应的更新并截断缓冲表.这样,如果您需要从主表中选择的任何数据的一致且全新的快照,并加入来自活动和重命名的缓冲区表的所有更改.

但是,如果两者都不可接受,您还可以调整数据库以更好地处理大量更新负载.

要优化更新,请确保PostgreSQL可以使用仅限堆的元组来存储行的更新版本.为此,请确保经常更新的列上没有索引,并将fillfactor更改为 低于默认值100%的值.您需要自己确定合适的填充因子,因为它很大程度上取决于工作负载及其运行的计算机的详细信息.fillfactor需要足够低,以至于在autovacuum有机会清理旧的不可见版本之前,所有更新都适合同一数据库页面.您可以调整autovacuum设置以在数据库密度和真空开销之间进行权衡.此外,请考虑任何长事务(包括统计查询)将保留在事务开始后已更改的元组.请参阅pg_stat_user_tables视图以查看要调整的内容,尤其是n_tup_hot_upd与n_tup_upd和n_live_tup与n_dead_tup的关系.

大量更新还会产生大量写入日志(WAL)负载.调整WAL行为(设置的文档)将有助于降低这一点.特别是,更高的checkpoint_segments数和更高的checkpoint_timeout可以通过允许在内存中进行更多更新来显着降低IO负载.请参阅pg_stat_bgwriter中checkpoints_timed与checkpoints_req的关系,以查看有多少检查点发生,因为已达到任一限制.提升你的shared_buffers以使工作集适合内存也会有所帮助.检查buffers_checkpoint与buffers_clean + buffers_backend以查看为满足检查点要求而不是内存耗尽而编写的数量.

您希望将统计更新组合到某种内存中的队列中,或者如果您更加雄心勃勃,则可以将它们组合到消息总线上.接收过程然后定期聚合这些统计更新 - 可以是从每5秒到每小时的任何地方 - 取决于您想要什么.的计数bytes_received和bytes_sent随后的更新,可能代表相加许多个人"更新"的消息计数.此外,您应该将多个ID的更新语句批处理到单个事务中,确保更新语句以与主键相同的相对顺序发出,以防止对可能执行相同操作的其他事务发生死锁.

通过这种方式,您可以将活动"批处理"到更大的块中,以控制PG数据库上的负载量,并将许多并发活动序列化为单个流(或多个,具体取决于发布更新的线程/进程数).根据"周期"调整的权衡是,新增量与更新负载量之间的关系.