Обсуждение: 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@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