Re: replication optimization: page writes only at the slave

Assumption: I have enough memory to cache all the database pages.
Goal:
Master never write pages. Slave replays logs from master and writes pages.
Benefits:
Reduce the page IO overhead at master, save money in EC2 cloud.

Question:
Can you give me some comments on this idea?
And I cannot turn of page writes in Postgresql.

I adjust the following parameters:
shared_buffers = 3GB

bgwriter_delay = 2000ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 0 # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 0 # 0-10.0 multipler on buffers
scanned/round

checkpoint_segments = 256 # in logfile segments, min 1,
16MB each
checkpoint_timeout = 1h # range 30s-1h
checkpoint_completion_target = 1.0 # checkpoint target duration,
0.0 - 1.0

However, I still witness large amount of page writes.
Can anyone tell where are the page writes come from?
Can I turn off that part of page writes by configuration?
If not, which part of source code should I adjust to achieve my goal
(turn of page writes)?

Thanks!

Xin

On 12/10/2012 04:56 PM, Xin Pan wrote:
> Assumption: I have enough memory to cache all the database pages.
> Goal:
> Master never write pages. Slave replays logs from master and writes
> pages.
> Benefits:
> Reduce the page IO overhead at master, save money in EC2 cloud.

I have suggested something similar on table-by-table basis but this has
not yet
generated much traction. I'll come back to this in coming weeks

For whole WAL you can achieve this by putting WAL on a large-enough
ramdrive.

Hannu

>
> Question:
> Can you give me some comments on this idea?
> And I cannot turn of page writes in Postgresql.
>
> I adjust the following parameters:
> shared_buffers = 3GB
>
> bgwriter_delay = 2000ms # 10-10000ms between rounds
> bgwriter_lru_maxpages = 0 # 0-1000 max buffers
> written/round
> bgwriter_lru_multiplier = 0 # 0-10.0 multipler on buffers
> scanned/round
>
> checkpoint_segments = 256 # in logfile segments, min 1,
> 16MB each
> checkpoint_timeout = 1h # range 30s-1h
> checkpoint_completion_target = 1.0 # checkpoint target duration,
> 0.0 - 1.0
>
> However, I still witness large amount of page writes.
> Can anyone tell where are the page writes come from?
> Can I turn off that part of page writes by configuration?
> If not, which part of source code should I adjust to achieve my goal
> (turn of page writes)?
>
>
> Thanks!
>
> Xin
>
>
>

On Mon, Dec 10, 2012 at 10:56 AM, Xin Pan <panxin0718(at)gmail(dot)com> wrote:
> However, I still witness large amount of page writes.
> Can anyone tell where are the page writes come from?

Probably not without more details. Things like VACUUM, COPY, and
sequential scans use ring-buffers that are smaller than
shared_buffers, so you often see the cache fill up with your data only
slowly just after starting the database, but if the database really
fits in shared_buffers, you should eventually settle into a rhythm
where dirty buffers are written to disk only once per checkpoint
cycle.

You might want to monitor pg_stat_bgwriter.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company