Search Postgresql Archives

Re: Help estimating database and WAL size

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

 



Am 2012-10-15 23:13, schrieb John R Pierce:
On 10/15/12 2:03 PM, Daniel Serodio (lists) wrote:
John R Pierce wrote:
On 10/08/12 1:39 PM, Daniel Serodio (lists) wrote:
3) Estimate the size of the transaction log
    ** We've got no idea how to estimate this, need advice **

postgres doesn't have a 'transaction log', it has the WAL (Write-Ahead Logs). These are typically 16MB each. on databases with a really heavy write load, I might bump the checkpoint_segments as high as 60, which seems to result in about 120 of them being created, 2GB total. these files get reused, unless you are archiving them to implement a continuous realtime backup system (which enables "PITR", Point in Time Recovery)
Thanks, I was using the term "transaction log" as a synonym for WAL. We're planning on enabling PITR; how can we calculate the WAL size and the WAL archive size in this case?


its based on how much data you're writing to the database.   Wheen
you write tuples (rows) to the database, they are stored in 8K
pages/blocks which are written to the current WAL file as they are
committed, when that WAL file fills up, or the checkpoint_timeout is
reached (the default is 30 seconds, I believe) , the WAL file is
written to the archive.

To be able to utilize PITR, you need a complete base backup of the
file system, and /all/ the archived WAL files since that base backup
was taken.

In huge number of cases you will also write these files to some kind of network storage via e.g. CIFS or NFS so you have access to them via your warm-standby-machines. I want to say: this is taken some storage but can be reviewed kind of independent from database itself.

Cheers,
Frank





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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux