Обсуждение: Re: Curiosity: what is PostgreSQL doing with data when "nothing" is happening?
Re: Curiosity: what is PostgreSQL doing with data when "nothing" is happening?
От
"Kevin Grittner"
Дата:
Gavan Schneider wrote: > Therefore I wonder if I should just go down to 4MB, no > archive_timeout, and accept the (max) 4MB of updated pages as my > (worst case) loss if everything should go wrong at once? And, > maybe do the segment downsize when a new version needs > installing :) You might want to consider grabbing the source for pg_clearxlogtail from pgfoundry and piping through that followed by gzip in your archive script. An "empty" WAL file tends to be about 16KB that way, rather than (for us) ranging between 4MB and 8MB. That way you can keep the archive_timeout fairly short. -Kevin
Re: Curiosity: what is PostgreSQL doing with data when "nothing" is happening?
От
Gavan Schneider
Дата:
Tom & Kevin Thank you both for your inputs On Sunday, November 25, 2012 at 06:36, Tom Lane wrote: >If you're concerned about minimizing WAL traffic at idle, you might want >to think about setting up a streaming replication arrangement instead of >relying on archive segment switches. archive_timeout is really kind >of a hack. On Sunday, November 25, 2012 at 04:27, Kevin Grittner wrote: >You might want to consider grabbing the source for pg_clearxlogtail >from pgfoundry and piping through that followed by gzip in your >archive script. An "empty" WAL file tends to be about 16KB that >way, rather than (for us) ranging between 4MB and 8MB. That way you >can keep the archive_timeout fairly short. > 3096 -rw-------+ 1 postgres _postgres 1583556 Nov 26 13:46 0000000100000001000001B9.bz2 1216 -rw-------+ 1 postgres _postgres 619973 Nov 26 13:56 0000000100000001000001BA.bz2 736 -rw-------+ 1 postgres _postgres 375341 Nov 26 14:06 0000000100000001000001BB.bz2 80 -rw-------+ 1 postgres _postgres 37941 Nov 26 14:16 0000000100000001000001BC.bz2 88 -rw-------+ 1 postgres _postgres 42718 Nov 26 14:26 0000000100000001000001BD.bz2 48 -rw-------+ 1 postgres _postgres 21046 Nov 26 14:36 0000000100000001000001BE.bz2 584 -rw-------+ 1 postgres _postgres 298291 Nov 26 14:46 0000000100000001000001BF.bz2 240 -rw-------+ 1 postgres _postgres 120852 Nov 26 14:56 0000000100000001000001C0.bz2 3096 -rw-------+ 1 postgres _postgres 1583557 Nov 26 15:06 0000000100000001000001C1.bz2 8 -rw-------+ 1 postgres _postgres 161 Nov 26 15:26 0000000100000001000001C2.bz2 8 -rw-------+ 1 postgres _postgres 158 Nov 26 15:26 0000000100000001000001C3.bz2 8 -rw-------+ 1 postgres _postgres 162 Nov 26 15:36 0000000100000001000001C4.bz2 8 -rw-------+ 1 postgres _postgres 157 Nov 26 15:46 0000000100000001000001C5.bz2 8 -rw-------+ 1 postgres _postgres 163 Nov 26 15:56 0000000100000001000001C6.bz2 8 -rw-------+ 1 postgres _postgres 162 Nov 26 16:06 0000000100000001000001C7.bz2 8 -rw-------+ 1 postgres _postgres 165 Nov 26 16:16 0000000100000001000001C8.bz2 8 -rw-------+ 1 postgres _postgres 165 Nov 26 16:26 0000000100000001000001C9.bz2 PostgreSQL server idle throughout, and spitting out a WAL file for archive every 10 minutes: archive_timeout = 600 # force a logfile segment switch after this Impressive what happens when pg_clearxlogtail gets into the mix halfway through! :) Basically this resolves my original question about WAL output when the system is idle... there isn't any. And Kevin's WAL tail docker nicely isolates the good data from the rubbish. I can now move forward exploring options for incremental uploads to an internet (i.e., well off site) data repository, since the data traffic will only carry stuff that actually matters. Getting philosophical... .... and taking Tom at his word that "archive_timeout is really kind of a hack" .... and noting Kevin's pg_clearxlogtail is something of the counter hack, Would the universe as we know it be upset if there was a postgresql.conf option such as: archive_zero_fill_on_segment_switch = on|off # default off This would achieve the idle compression result much more elegantly (I know, it's still a hack) for those who have the need, without, as far as I can tell, breaking anything else. Regards Gavan Schneider