在PostgreSQL中生成大量归档日志

Question

我们在生产服务器中使用PostgreSQL 9.1。在过去的一个月中，我们的数据库每天生成近35 GB的归档日志。为此，我们已经监控了在生成归档日志时正在运行的所有查询。然后我们在整个数据库上运行vacuum(冻结，分析)。但它似乎对归档日志的生成没有影响。我们怀疑有一张桌子是这样的。每隔一小时的第9分钟和第39分钟，运行相同的delete语句。每次执行时，它都会删除整个表。出于测试目的，我们对语句运行了EXPLAIN(ANALYZE，BUFFERS)。我们发现读=576MB，写=328MB。但是，在我们的生产服务器中，shared_buffers =24MB。因此，每次它都会将24MB的数据块从磁盘转移到共享缓冲区。然后它将刷新到磁盘，并再次将24MB的数据块放入共享缓冲区。

那么，生成归档日志的原因是不是因为频繁刷新数据块？我们是否需要在生产服务器中增加shared_buffers，以消除繁重的归档日志生成？在我们的生产服务器中，work_mem =1MB以下是EXPLAIN(ANALYZE，BUFFERS)的输出，供您参考：

                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Delete on table_a  (cost=83.35..171.85 rows=2060 width=12) (actual time=11949.929..11949.929 rows=0 loops=1)
   Buffers: shared hit=375963 read=73999 written=42030
   ->  Hash Semi Join  (cost=83.35..171.85 rows=2060 width=12) (actual time=1.028..12.570 rows=2060 loops=1)
         Hash Cond: (public.table_a.id = public.table_a.id)
         Buffers: shared hit=46 read=30 written=18
         ->  Seq Scan on table_a  (cost=0.00..57.60 rows=2060 width=10) (actual time=0.007..5.009 rows=2060 loops=1)
               Buffers: shared hit=7 read=30 written=18
         ->  Hash  (cost=57.60..57.60 rows=2060 width=10) (actual time=0.973..0.973 rows=2060 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 89kB
               Buffers: shared hit=37
               ->  Seq Scan on table_a  (cost=0.00..57.60 rows=2060 width=10) (actual time=0.002..0.463 rows=2060 loops=1)
                     Buffers: shared hit=37
 Total runtime: 11950.028 ms

Answer 1

https://www.postgresql.org/docs/9.1/static/wal-intro.html

简而言之，WAL的核心概念是，对数据文件(表和索引所在的位置)的更改必须在这些更改被记录到之后才能写入，即，在描述这些更改的日志记录被刷新到永久存储之后。如果我们遵循此过程，则不需要在每次事务提交时将数据页刷新到磁盘，因为我们知道，在发生崩溃的情况下，我们可以使用日志恢复数据库:任何未应用于数据页的更改都可以从日志记录中重做。(这是前滚恢复，也称为重做。)

所以简而言之，INSERT, UPDATE, DELETE, VACUUM, CLUSTER, ALTER.. SET TABLESPACE (和其他类似的)产生WAL。随着更多的WAL被创建，更多的块被重写。shared_buffers不会影响它。