From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Joseph Marlin <jmarlin(at)saucontech(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: WAL tuning advice |
Date: | 2012-07-30 23:15:42 |
Message-ID: | 8537.1343690142@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Joseph Marlin <jmarlin(at)saucontech(dot)com> writes:
> I'm hoping someone can help me figure out how to better tune my WAL parameters. Right now I am seeing WAL activity that is faster than I think it should be - we average about 30 write queries a second at our peak, and yet we're getting a new WAL file every 15-20 seconds.
You would be better off asking about this on pgsql-performance, but yes,
the first thing you should try is boosting checkpoint_segments and
checkpoint_timeout. The longer the interval between checkpoints, the lower
the WAL volume will be, because of reduction of full-page-image updates.
The limiting factor of course is how long a replay you can stand after
a database crash.
> 1) Due to high ratio of checkpoints_req to checkpoints_timed, I should increase checkpoints_segments, maybe from 10 to 15?
Right now, at one segment every 15 seconds, "10" is forcing a checkpoint
every 150 seconds. You probably want at least four or five times that
interval if you want to make a serious dent in the WAL volume. And
checkpoint_timeout has to increase enough to allow that, of course.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-07-31 00:59:22 | Re: very slow update query |
Previous Message | Kevin Grittner | 2012-07-30 22:43:13 | Re: Index slow down insertions... |