Re: BUG #7521: Cannot disable WAL log while using pg_dump

On Fri, Sep 7, 2012 at 12:15 PM, Gezeala M. Bacuño II <gezeala(at)gmail(dot)com> wrote:

>
> From pg_controldata right after starting the instance:
> Latest checkpoint location: 4441/5E681F38
> Prior checkpoint location: 4441/5E67D140
> Latest checkpoint's REDO location: 4441/5E681F38
> Latest checkpoint's TimeLineID: 1
> Latest checkpoint's NextXID: 0/419543166
> Latest checkpoint's NextOID: 653512568
> Latest checkpoint's NextMultiXactId: 107873
> Latest checkpoint's NextMultiOffset: 219841
>
> From pg_controldata after pg_dump:
> Latest checkpoint location: 4450/7A14F280
> Prior checkpoint location: 4450/7A14E018
> Latest checkpoint's REDO location: 4450/7A14F280
> Latest checkpoint's TimeLineID: 1
> Latest checkpoint's NextXID: 0/419543166
> Latest checkpoint's NextOID: 653512568
> Latest checkpoint's NextMultiXactId: 107873
> Latest checkpoint's NextMultiOffset: 219841
>
> Background:
> The cluster used for the backup was cloned from a zfs snapshot (taken
> while the db is running without issuing pg_start/stop_backup). WALs
> are replayed and cluster starts up usually in a minute or so. After
> pg_dump, the clone's zfs USED property value is 285G -- that's how
> huge block changes have grown for the entirety of the pg_dump process.
> Yesterday's backup clone was 280G.
>
> Aside from pg_dump, a cron job issues a 'select * from
> pg_stat_activity' every 9mins. The instance is cloned solely for
> backup purposes with zero modifications and autovacuum disabled.

Hmm.. So there is definitely large number of WALs being written but no
transaction activity as shown by the constant NextXID. As someone
mentioned upthread, HOT prune can cause WAL activity even for what is
otherwise a read-only transaction. Given that pg_dump would be
touching each and every page in every relation, its not entirely
unlikely that HOT might be acting on many pages. But that should
happen only once. So if you take another dump of the cluster, you
should not see more WAL activity.

Does your primary database (which you cloned) get significant
UPDATE/DELETE activities ? Further, does it have autovacuum disabled
or have long running transactions ?

BTW, the following query returns ~60GB. Thats the amount of WAL
written after the server was started and at the end of pg_dump (I
don't think pg_xlog_location_diff() is available in the older
releases).

postgres=# select pg_xlog_location_diff('4450/7A14F280',
'4441/5E681F38')/(2^30);
?column?
------------------
60.1980484202504

Thanks,
Pavan