Re: [BUGS] BUG #7801: Streaming failover checkpoints much slower than master, pg_xlog space problems during db load
On 1/8/2013 2:48 PM, Simon Riggs wrote: > On 8 January 2013 19:24, wrote: > >> Simply stated, pg_xlog grows out of control on a streaming-replication >> backup server with a high volume of writes on the master server. This occurs >> only with checkpoint_completion_target>0 and very large (eg. 8GB) >> shared_buffers. pg_xlog on the master stays a fixed size (1.2G for me). > All of this appears to be working as designed. > > It will issue a restartpoint every checkpoint_timeout seconds on the standby. > > checkpoint_segments is ignored on standby. The documentation does not seem to agree with the last point. "In standby mode, a restartpoint is also triggered if checkpoint_segments log segments have been replayed since last restartpoint and at least one checkpoint record has been replayed." This is precisely the problem. The failover should not go checkpoint_timeout*checkpoint_completion_target seconds without executing a restartpoint, in spite of the fact that thousands of WAL segments are stacking up in pg_xlog. With checkpoint_completion_target=0, the standby server will happily execute restartpoints much faster than checkpoint_timeout if it is necessary. Once checkpoint_completion_target>0, no attention is paid to the backlog of WAL data. I honestly do not understand postgresql well enough to understand why large vs. small shared_buffers changes this behavior, but it does. If shared_buffers is not extremely large, it seems postgresql is forced to execute restartpoints more frequently? In general it seems like it should be safe to use the same postgresql.conf on the master and the standby server, but this would clearly be an exception. One wouldn't expect a 10GB pg_xlog on a standby where the master has no such problem. Thank you for your assistance. Brian -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7800: Welcome email with login ifnormation NOT received
On Tue, Jan 8, 2013 at 2:47 PM, Scott Mead wrote: > Chrystel, > >I just verified, you never need to register in order to download > postgres from the OpenSCG site. > > If you hit this link (from the postgres download site: > http://www.openscg.com/se/postgresql/packages.jsp). You can click > through and download without ever registering. > Just to be specific, the postgres download page never requires registration for download. ( We don't check for what site you are coming from ). --Scott > > --Scott > > > > On Tue, Jan 8, 2013 at 12:43 PM, wrote: > >> The following bug has been logged on the website: >> >> Bug reference: 7800 >> Logged by: Brunet-Cambus >> Email address: camb...@slb.com >> PostgreSQL version: 9.2.2 >> Operating system: PostgreSQL >> Description: >> >> >> I subscribed to be able to login to OpenSCG portal and so to download >> PostgreSQL. Web site state that: "Your email is successfully subscribed. >> If >> you are a new subscriber, you will receive a welcome email with >> instructions >> on how to login to OpenSCG portal." BUT I never received something and so >> cannot download PostgreSQL! >> >> Could you please help me? >> Many thanks in advance for your help. >> >> Best regards, >> Chrystel >> >> >> >> >> -- >> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs >> > >
Re: [BUGS] BUG #7801: Streaming failover checkpoints much slower than master, pg_xlog space problems during db load
On 8 January 2013 19:24, wrote: > Simply stated, pg_xlog grows out of control on a streaming-replication > backup server with a high volume of writes on the master server. This occurs > only with checkpoint_completion_target>0 and very large (eg. 8GB) > shared_buffers. pg_xlog on the master stays a fixed size (1.2G for me). All of this appears to be working as designed. It will issue a restartpoint every checkpoint_timeout seconds on the standby. checkpoint_segments is ignored on standby. > Detail: I have two new/fast servers using streaming replication, with 9.2.2 > from the PostgreSQL yum repository. The servers are connected via 10G > network, so the failover receives data as fast as it is created. > > It appears that to trigger this problem you need large shared_buffers (mine > is set to 8GB) and checkpoint_completion_target > 0. (.8 or .9 will do). I > have checkpoint_timeout=5min. (though setting this to 30s *does not* > alleviate the problem). The failover machine doesn't seem to be concerned > with checkpoint_timeout or checkpoint_segments. > > When doing large writes (like pg_restore) the failover machine's pg_xlog > does not clear files. The reason appears to be that it is not > checkpointing(verified by log_checkpoints=on and examining the log). If I > run 'checkpoint;' manually on the failover in psql(hot_standby=on), it > immediately cleans up pg_xlog(after a pause to actually perform the > checkpoint). > > To generate the data,you can just run > pgbench -i -s1000 > on the master server, then watch pg_xlog grow on the failover. > > This produces a ~10GB of data, then creates indices. If I run du on the > failover after the data is loaded, I see: > > # du -s base pg_xlog | sort -n > 10928276pg_xlog > 13144536base > > I have a 10GB pg_xlog. The load completes in less than 5 minutes, which may > be relevant here, since eventually the failover will perform a checkpoint. > On the primary server, pg_xlog caps at 1.2GB. > > If checkpoint_completion_target=0, or shared_buffers=256MB, pg_xlog grows to > around 1GB and stays there. > > This guess may be off target, but it appears that the database only > checkpoints every approximately checkpoint_completion_target* > checkpoint_timeout seconds. So, for a .9 completion target and the default > 5min timeout, the failover will go for 4+ minutes without executing a single > checkpoint, regardless of how much data arrives (verified with > log_checkpoints=on). > > All the while the master server is spitting out 'checkpoints are occurring > too frequently' messages, which I assume is to be expected during a reload. > > This means that the failover machine needs 2x the database size to not crash > during a database reload. We run VMs with limited disk allocation where > this is not the case. We periodically need to dump/restore, and I'm > concerned this simple operation will crash our failover machines. > > I can avoid the problem by setting checkpoint_completion_target=0, which I > will do for now. > > Here are settings that differ from the default initdb install: > > shared_buffers = 8GB > work_mem = 64MB > maintenance_work_mem = 256MB > max_stack_depth = 8MB > wal_level = hot_standby > synchronous_commit = off > wal_buffers = 64MB > checkpoint_segments = 10 > checkpoint_completion_target = 0 > max_wal_senders = 2 > hot_standby = on > effective_cache_size = 8GB > log_checkpoints = on -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7800: Welcome email with login ifnormation NOT received
Chrystel, I just verified, you never need to register in order to download postgres from the OpenSCG site. If you hit this link (from the postgres download site: http://www.openscg.com/se/postgresql/packages.jsp). You can click through and download without ever registering. --Scott On Tue, Jan 8, 2013 at 12:43 PM, wrote: > The following bug has been logged on the website: > > Bug reference: 7800 > Logged by: Brunet-Cambus > Email address: camb...@slb.com > PostgreSQL version: 9.2.2 > Operating system: PostgreSQL > Description: > > > I subscribed to be able to login to OpenSCG portal and so to download > PostgreSQL. Web site state that: "Your email is successfully subscribed. If > you are a new subscriber, you will receive a welcome email with > instructions > on how to login to OpenSCG portal." BUT I never received something and so > cannot download PostgreSQL! > > Could you please help me? > Many thanks in advance for your help. > > Best regards, > Chrystel > > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
[BUGS] BUG #7801: Streaming failover checkpoints much slower than master, pg_xlog space problems during db load
The following bug has been logged on the website: Bug reference: 7801 Logged by: Brian Krznarich Email address: bri...@openroadtech.com PostgreSQL version: 9.2.2 Operating system: RHEL6 Description: I was told to resubmit this- the original submission () may have been lost. Simply stated, pg_xlog grows out of control on a streaming-replication backup server with a high volume of writes on the master server. This occurs only with checkpoint_completion_target>0 and very large (eg. 8GB) shared_buffers. pg_xlog on the master stays a fixed size (1.2G for me). Detail: I have two new/fast servers using streaming replication, with 9.2.2 from the PostgreSQL yum repository. The servers are connected via 10G network, so the failover receives data as fast as it is created. It appears that to trigger this problem you need large shared_buffers (mine is set to 8GB) and checkpoint_completion_target > 0. (.8 or .9 will do). I have checkpoint_timeout=5min. (though setting this to 30s *does not* alleviate the problem). The failover machine doesn't seem to be concerned with checkpoint_timeout or checkpoint_segments. When doing large writes (like pg_restore) the failover machine's pg_xlog does not clear files. The reason appears to be that it is not checkpointing(verified by log_checkpoints=on and examining the log). If I run 'checkpoint;' manually on the failover in psql(hot_standby=on), it immediately cleans up pg_xlog(after a pause to actually perform the checkpoint). To generate the data,you can just run pgbench -i -s1000 on the master server, then watch pg_xlog grow on the failover. This produces a ~10GB of data, then creates indices. If I run du on the failover after the data is loaded, I see: # du -s base pg_xlog | sort -n 10928276pg_xlog 13144536base I have a 10GB pg_xlog. The load completes in less than 5 minutes, which may be relevant here, since eventually the failover will perform a checkpoint. On the primary server, pg_xlog caps at 1.2GB. If checkpoint_completion_target=0, or shared_buffers=256MB, pg_xlog grows to around 1GB and stays there. This guess may be off target, but it appears that the database only checkpoints every approximately checkpoint_completion_target* checkpoint_timeout seconds. So, for a .9 completion target and the default 5min timeout, the failover will go for 4+ minutes without executing a single checkpoint, regardless of how much data arrives (verified with log_checkpoints=on). All the while the master server is spitting out 'checkpoints are occurring too frequently' messages, which I assume is to be expected during a reload. This means that the failover machine needs 2x the database size to not crash during a database reload. We run VMs with limited disk allocation where this is not the case. We periodically need to dump/restore, and I'm concerned this simple operation will crash our failover machines. I can avoid the problem by setting checkpoint_completion_target=0, which I will do for now. Here are settings that differ from the default initdb install: shared_buffers = 8GB work_mem = 64MB maintenance_work_mem = 256MB max_stack_depth = 8MB wal_level = hot_standby synchronous_commit = off wal_buffers = 64MB checkpoint_segments = 10 checkpoint_completion_target = 0 max_wal_senders = 2 hot_standby = on effective_cache_size = 8GB log_checkpoints = on -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7800: Welcome email with login ifnormation NOT received
The following bug has been logged on the website: Bug reference: 7800 Logged by: Brunet-Cambus Email address: camb...@slb.com PostgreSQL version: 9.2.2 Operating system: PostgreSQL Description: I subscribed to be able to login to OpenSCG portal and so to download PostgreSQL. Web site state that: "Your email is successfully subscribed. If you are a new subscriber, you will receive a welcome email with instructions on how to login to OpenSCG portal." BUT I never received something and so cannot download PostgreSQL! Could you please help me? Many thanks in advance for your help. Best regards, Chrystel -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs