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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers