From: | Sachin Srivastava <ssr(dot)teleatlas(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | pg_xlog Concern |
Date: | 2015-05-18 11:44:26 |
Message-ID: | CAFzqEh+_QtjK-Q4vwJboO26JK7+RZrqhg9ZLxctGiirETcwNpw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have done below changes in my postgresql.conf.
*Checkpoint_segments= 200*
and
*checkpoint_timeout= 5min*
I am also doing archiving and below changes in my postgresql.conf.
*Wal_level=archive*
*archive_command= cp %p /dbkup/momacpp_213_live/%f*
*archive_mode=on*
Achieving is going smoothly in /*dbkup/momacpp_213_live* folder.
But currently my pg_xlog size is 60 GB and there are 3740 WAL file in this
folder and in Last week this was 400 GB(pg_xlog folder) and WAL file were
approx. 30000. Due to archiving pg_xlog folder size is decreasing now but
it’s taking one week to come in normal size.
*I have 2 Question*:
*First:* When I have given *checkpoint segments=200 (As per my knowledge
WAL file should be generated 200*2+3=403 only)* but why it’s generating too
much file. MY each WAL file is 16 MB.
*Second:* Why pg_xlog size is increasing too much it should be only (403*16
MB = 6448 MB) and if Production team is not entering data in bulk and if
normal production is going then it’s size remain same as per logic.
How I handle this case (pg_xlog folder size) when Production people
entering the data in bulk, kindly suggest. I am missing something in my
postgresql.conf and somewhere else.
*Note:* On daily basis Production team is entering the data in bulk
(approx. 30-50 GB) in the database and there are only 2 databases on this
server.
Regards,
*Sachin Srivastava*
Assistant Technical Lead(Oracle/PostgreSQL) | TSG
*Cyient* | www.cyient.com
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2015-05-18 11:48:22 | Re: |
Previous Message | Thomas Kellerer | 2015-05-18 10:32:49 | Re: |