pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedToo many .history file in pg_xlog takes lots of space
7+ messages / 5 participants
[nested] [flat]
* Too many .history file in pg_xlog takes lots of space
@ 2018-03-14 02:31 彭昱傑 <k872892@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: 彭昱傑 @ 2018-03-14 02:31 UTC (permalink / raw)
To: pgsql-performance
Hi team,
My postgre version is 9.4.9, and I face a space issue.
Every time I restart postgre server, it generates a new history file:
0000156A.history => 0000156B.history
Now it takes a lot of space about 800MB (5787 history file):
-rw------- 1 pgsql pgsql 247K Mar 13 14:49 00001568.history
-rw------- 1 pgsql pgsql 247K Mar 13 14:51 00001569.history
-rw------- 1 pgsql pgsql 247K Mar 13 14:52 0000156A.history
-rw------- 1 pgsql pgsql 247K Mar 13 15:03 0000156B.history
-rw------- 1 pgsql pgsql 247K Mar 13 15:56 0000156C.history
-rw------- 1 pgsql pgsql 247K Mar 13 16:06 0000156D.history
-rw------- 1 pgsql pgsql 247K Mar 13 16:06 0000156E.history
-rw------- 1 pgsql pgsql 248K Mar 13 17:13 0000156F.history
-rw------- 1 pgsql pgsql 16M Mar 13 17:13 0000156F00000024000000F2
-rw------- 1 pgsql pgsql 248K Mar 13 17:13 00001570.history
-rw------- 1 pgsql pgsql 16M Mar 14 10:11 0000157000000024000000F2
Is file 00001570.history important?
When I do diff to these file I found new file just have a new line compared
to previous history file :
command:
diff 0000156F.history 00001570.history
result
10971a10972,10973
>
> 5487 24/F2000090 reached consistency
Is there a safety way to clean .history file?
1. Just save latest one?
2. Or remove history file which is marked as done
in pg_xlog/archive_status like:
-rw------- 1 pgsql pgsql 0 Mar 13 14:52 0000156A.history.done
-rw------- 1 pgsql pgsql 0 Mar 13 15:03 0000156B.history.done
-rw------- 1 pgsql pgsql 0 Mar 13 15:56 0000156C.history.done
-rw------- 1 pgsql pgsql 0 Mar 13 16:06 0000156D.history.done
-rw------- 1 pgsql pgsql 0 Mar 13 16:06 0000156E.history.done
-rw------- 1 pgsql pgsql 0 Mar 13 17:13 0000156F.history.done
-rw------- 1 pgsql pgsql 0 Mar 13 17:13 0000156F00000024000000F2.done
-rw------- 1 pgsql pgsql 0 Mar 13 17:13 00001570.history.done
Thanks.
By Pin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Too many .history file in pg_xlog takes lots of space
@ 2018-03-14 05:56 Laurenz Albe <laurenz.albe@cybertec.at>
parent: 彭昱傑 <k872892@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2018-03-14 05:56 UTC (permalink / raw)
To: 彭昱傑 <k872892@gmail.com>; pgsql-performance
彭昱傑 wrote:
> My postgre version is 9.4.9, and I face a space issue.
>
> Every time I restart postgre server, it generates a new history file:
> 0000156A.history => 0000156B.history
>
> Now it takes a lot of space about 800MB (5787 history file):
> [...]
> Is file 00001570.history important?
A new history file is created when a new timeline is opened,
which happens after point-in-time-recovery or promotion of
a physical standby server.
There must be something weird in the way you start PostgreSQL.
Examine the start script, maybe you can fix the problem.
These files are only necessary for point-in-time-recovery,
so you don't have to retain them any longer than you retain
your WAL archives.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Too many .history file in pg_xlog takes lots of space
@ 2018-03-14 06:12 彭昱傑 <k872892@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: 彭昱傑 @ 2018-03-14 06:12 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-performance
Thank you.
It's useful information for me.
I will examine my restart script, and study point-in-time-recovery.
Also remove unused history file.
2018-03-14 13:56 GMT+08:00 Laurenz Albe <laurenz.albe@cybertec.at>:
> 彭昱傑 wrote:
> > My postgre version is 9.4.9, and I face a space issue.
> >
> > Every time I restart postgre server, it generates a new history file:
> > 0000156A.history => 0000156B.history
> >
> > Now it takes a lot of space about 800MB (5787 history file):
> > [...]
> > Is file 00001570.history important?
>
> A new history file is created when a new timeline is opened,
> which happens after point-in-time-recovery or promotion of
> a physical standby server.
>
> There must be something weird in the way you start PostgreSQL.
> Examine the start script, maybe you can fix the problem.
>
> These files are only necessary for point-in-time-recovery,
> so you don't have to retain them any longer than you retain
> your WAL archives.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Too many .history file in pg_xlog takes lots of space
@ 2018-03-14 07:04 Michael Paquier <michael@paquier.xyz>
parent: 彭昱傑 <k872892@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Paquier @ 2018-03-14 07:04 UTC (permalink / raw)
To: 彭昱傑 <k872892@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
On Wed, Mar 14, 2018 at 02:12:47PM +0800, 彭昱傑 wrote:
> It's useful information for me.
Once archived, there is no need to keep them in the data folder as if
needed at recovery the startup process would look for timeline history
files where necessary if it needs to do a timeline jump.
> I will examine my restart script, and study point-in-time-recovery.
> Also remove unused history file.
At the same time, the backend makes little effort to remove past
timeline history files, and those are just a couple of bytes, which
accumulate, so after a couple of hundreds of failovers you could bloat
the data folder. Why not making their removal more aggressive at each
restart point created? You don't need any history files older than the
current timeline recovery is processing, so we could make the removal
policy more aggressive.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20180314070443.GE16179@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Too many .history file in pg_xlog takes lots of space
@ 2018-03-14 13:49 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: 彭昱傑 <k872892@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2018-03-14 13:49 UTC (permalink / raw)
To: 彭昱傑 <k872892@gmail.com>; +Cc: pgsql-performance
彭昱傑 wrote:
> My postgre version is 9.4.9, and I face a space issue.
Latest in 9.4 is 9.4.17, so you're missing about two years of bug fixes.
> Every time I restart postgre server, it generates a new history file:
That's strange -- it shouldn't happen ... sounds like you're causing a
crash each time you restart. Are you using immediate mode in shutdown
maybe? If so, don't; use fast mode instead.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Too many .history file in pg_xlog takes lots of space
@ 2018-03-14 15:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-03-14 15:29 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: 彭昱傑 <k872892@gmail.com>; pgsql-performance
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> 彭昱傑 wrote:
>> Every time I restart postgre server, it generates a new history file:
> That's strange -- it shouldn't happen ... sounds like you're causing a
> crash each time you restart. Are you using immediate mode in shutdown
> maybe? If so, don't; use fast mode instead.
I'm confused by this report too. Plain crashes shouldn't result in
forking a new timeline. To check, I tried "-m immediate", as well as
"kill -9 postmaster", and neither of those resulted in a new .history file
on restart. I wonder if the OP's restart process involves calling
pg_resetxlog or something like that (which would be risky as heck).
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Too many .history file in pg_xlog takes lots of space
@ 2018-03-15 10:22 彭昱傑 <k872892@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: 彭昱傑 @ 2018-03-15 10:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-performance
Hi Michael, Alvaro, Tom:
Really appreciate yours help, this is an invalid report, and I'm sorry for
that.
After I examine restart script, I found we generate recovery.conf every
time, and this cause lost of timeline.
Thanks.
2018-03-14 23:29 GMT+08:00 Tom Lane <tgl@sss.pgh.pa.us>:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > 彭昱傑 wrote:
> >> Every time I restart postgre server, it generates a new history file:
>
> > That's strange -- it shouldn't happen ... sounds like you're causing a
> > crash each time you restart. Are you using immediate mode in shutdown
> > maybe? If so, don't; use fast mode instead.
>
> I'm confused by this report too. Plain crashes shouldn't result in
> forking a new timeline. To check, I tried "-m immediate", as well as
> "kill -9 postmaster", and neither of those resulted in a new .history file
> on restart. I wonder if the OP's restart process involves calling
> pg_resetxlog or something like that (which would be risky as heck).
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-03-15 10:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-14 02:31 Too many .history file in pg_xlog takes lots of space 彭昱傑 <k872892@gmail.com>
2018-03-14 05:56 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-14 06:12 ` 彭昱傑 <k872892@gmail.com>
2018-03-14 07:04 ` Michael Paquier <michael@paquier.xyz>
2018-03-14 13:49 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2018-03-14 15:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-03-15 10:22 ` 彭昱傑 <k872892@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox