Thread: replication optimization: page writes only at the slave

replication optimization: page writes only at the slave

From
Xin Pan
Date:
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




Re: replication optimization: page writes only at the slave

From
Hannu Krosing
Date:
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
>
>
>




Re: replication optimization: page writes only at the slave

From
Robert Haas
Date:
On Mon, Dec 10, 2012 at 10:56 AM, Xin Pan <panxin0718@gmail.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