Re: replication optimization: page writes only at the slave

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
>
>
>