代码之家 › 专栏 › 技术社区 › drdaeman

有很多更新和PostgreSQL的流

sql-update optimization postgresql sql

drdaeman · 技术社区 · 15 年前

我对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)
)

随着会计数据的流动,这个表会收到很多更新,比如:

-- 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会计的例子都是为了简化解释。真正的问题是另一个系统,它的结构很难解释。

谢谢你的建议!

2 回复 | 直到 13 年前

Ants Aasma 15 年前

数据库确实不是收集大量小更新的最佳工具,但是由于我不知道您的查询能力和ACID要求,所以我不能真正推荐其他东西。如果这是一种可接受的方法,那么zzzeek建议的应用程序端更新聚合可以帮助显著降低更新负载。

有一种类似的方法,可以让您以某种性能成本来查询更新鲜的数据,并提供持久性和能力。创建一个缓冲区表,该表可以收集对需要更新的值所做的更改,并将更改插入其中。在事务中定期将表重命名为其他表,并创建一个新表来代替它。然后在事务中汇总所有更改,对主表进行相应的更新并截断缓冲区表。这样,如果您需要任何数据的一致和新的快照,您可以从主表中进行选择,并从活动和重命名的缓冲区表中加入所有更改。

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

要优化更新,请确保PostgreSQL可以使用 heap-only tuples 存储行的更新版本。为此,请确保频繁更新的列上没有索引,并更改 fillfactor 低于默认值100%。您需要自己确定一个合适的填充因子,因为它在很大程度上取决于工作负载和运行它的机器的详细信息。填充因子必须足够低,以便在Autovacuum有机会清除旧的不可见版本之前,所有更新都适合同一数据库页。您可以调整自动真空设置以在数据库密度和真空开销之间进行权衡。另外,考虑到任何长事务(包括统计查询)都将保留在事务启动后已更改的元组上。见 pg_stat_user_表 查看要调整的内容,尤其是 NuToupHythuPUD 到 更新(U) 和 活生生的 到 N-Dealth-Toup .

大量更新还将产生大量的提前写日志(wal)负载。调整Wal行为( docs for the settings )有助于降低这一点。特别是,更高的检查点段数和更高的检查点超时可以通过允许在内存中进行更多更新来显著降低IO负载。查看pg_stat_bgwriter中检查点时间与检查点要求的关系,查看由于达到任一限制而发生的检查点数量。提高共享缓冲区,使工作集适合内存也会有所帮助。检查buffers_checkpoint与buffers_clean+buffers_backend,看看有多少是为了满足检查点的要求而写的,而不是刚刚耗尽内存。

zzzeek 15 年前

当统计更新发生在某种类型的内存队列中时,您希望将它们组合起来,或者如果您更雄心勃勃,可以将它们组合到消息总线上。然后,一个接收过程会定期汇总这些统计更新——可以是每5秒到每小时——这取决于您想要什么。计数 bytes_received 和 bytes_sent 然后更新,计数可能表示许多单独的“更新”消息汇总在一起。此外,您应该将多个ID的update语句批处理到一个事务中,确保update语句以与主键相同的相对顺序发出,以防止可能执行相同操作的其他事务出现死锁。

通过这种方式,您可以将活动“批处理”成更大的块来控制pg数据库的负载量,还可以将许多并发活动序列化为单个流(或多个流,具体取决于发布更新的线程/进程的数量)。根据“period”调整的权衡是,有多少新鲜度与多少更新负载。