Re: WAL archive space planning?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Tue, Feb 21, 2017 at 12:22 PM, Ray Stell <stellr@xxxxxx> wrote:

On 2/21/17 12:09 AM, Steven Chang wrote:

check this, his 2nd part introduces Stream Replication Implementation and tell you px_log retention and wal archive related parameters.

2017-02-18 2:30 GMT+08:00 Ray Stell <stellr@xxxxxx>:
I was "planning" to turn on WAL archiving on a postgresql 9.4.11 server that currently is running with "wal_level=hot_standby" and streaming to a standby.  I thought there would be a relationship between the rate of pg_xlog files and archive generation.   When I turned up the archive_command/mode I found the scale of the archive target was wrong as I had based it on the pg_xlog file creation rate.

When I turned on the archive command for a few minutes, pg_xlog dir contained these files for the time period:

-rw------- 1 postgres postgres 16777216 Feb 17 06:47 00000001000023AC0000007F
-rw------- 1 postgres postgres 16777216 Feb 17 06:45 00000001000023AC0000007E

The archive command wrote 126, 16MB files:

-rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000056
-rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000055
-rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000054
-rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000053
...

-rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DC
-rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DB
-rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DA
-rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000D9

On servers that are not nearly as busy, I observe a one-to-one relationship between these files/rates.

Is there a good WAL archive space planning guide?

Is the way to collect planning data for this to turn on wal_debug?

TIA!




--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

I'm interested in why I might observed a dramatic difference between pg_xlog files and the archive target files. Other dbs I see have them pretty much one-to-one, but not in this case here.  What might cause the large variation?  I got only a few pg_xlogs files each minute and 42 archive files/min.  


Ray,

Did you see my previous response about the archive_timeout setting?


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux