Re: [HACKERS] max_standby_delay considered harmful
Robert Haas writes: > On Sun, May 9, 2010 at 6:58 PM, Andres Freund wrote: >> The difference is that in HS you have to wait for a moment where *no >> exclusive >> lock at all* exist, possibly without contending for any of them, while on the >> master you might not even blocked by the existence of any of those locks. >> >> If you have two sessions which in overlapping transactions lock different >> tables exlusively you have no problem shutting the master down, but you will >> never reach a point where no exclusive lock is taken on the slave. > > A possible solution to this in the shutdown case is to kill anyone > waiting on a lock held by the startup process at the same time we kill > the startup process, and to kill anyone who subsequently waits for > such a lock as soon as they attempt to take it. I'm not sure if this > would also make sense in the pause case. Well, wait, I'm getting lost here. It seems to me that no query on the slave is granted to take AEL, not matter what. The only case is a query waiting for the replay to release its locks. The only consequence of pause not waiting for any lock to get released from the replay is that those backends will be, well, paused. But that applies the same to any backend started after we pause. Waiting for replay to release all its locks before to pause would mean that there's a possibility that the activity on the master is such that you never reach a pause in the WAL stream. Let's assume we want any new code we throw in at this stage to be a magic wand making every use happy at once. So we'd need a pause function taking either 1 or 2 arguments, first is to say we pause now even if we know the replay is holding some locks that might pause the reporting queries too, the other is to wait until the locks are not held anymore, with a timeout (default 1min?). Ok, that's designing the API we're missing, and we should not be in the process of doing any design at this stage. But we are. > [good summary of current positions] > I can't presume to extract a consensus from that; I don't think there > is one. All we know for sure is that Tom does not want to release as-is, and he rightfully insists on several objectives as far as the editing is concerned: - no addition of code we might want to throw away later - avoid having to deprecate released behavior, it's too hard - minimal change set, possibly with no new features. One more, pausing the replay is *already* in the code base, it's exactly what happens under the hood if you favor queries rather than replay, to the point I don't understand why the pause design needs to happen now. We're only talking about having an *explicit* version of it. Regards, -- dim I too am growing tired of insisting this much. I only continue because I really can't get to understand why-o-why considering a new API over existing feature is not possible at this stage. I'm hitting my head on the wal, so to say… -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Robert Haas wrote: > On Sun, May 9, 2010 at 6:58 PM, Andres Freund wrote: >> On Monday 10 May 2010 00:25:44 Florian Pflug wrote: >>> On May 9, 2010, at 22:01 , Robert Haas wrote: On Sun, May 9, 2010 at 3:09 PM, Dimitri Fontaine >> wrote: Seems like it could take FOREVER on a busy system. Surely that's not OK. The fact that Hot Standby has to take exclusive locks that can't be released until WAL replay has progressed to a certain point seems like a fairly serious wart. >>> If this is a serious wart then it's not one of hot standby, but one of >>> postgres proper. AccessExclusiveLocks (SELECT-blocking locks that is, as >>> opposed to UPDATE/DELETE-blocking locks) are never necessary from a >>> correctness POV, they're only there for implementation reasons. >>> >>> Getting rid of them doesn't seem completely insurmountable either - just as >>> multiple row versions remove the need to block SELECTs dues to concurrent >>> UPDATEs, multiple datafile versions could remove the need to block SELECTs >>> due to concurrent ALTERs. But people seem to live with them quite well, >>> judged from the amount of work put into getting rid of them (zero). I >>> therefore fail to see why they should pose a significant problem in HS >>> setups. >> The difference is that in HS you have to wait for a moment where *no >> exclusive >> lock at all* exist, possibly without contending for any of them, while on the >> master you might not even blocked by the existence of any of those locks. >> >> If you have two sessions which in overlapping transactions lock different >> tables exlusively you have no problem shutting the master down, but you will >> never reach a point where no exclusive lock is taken on the slave. > > A possible solution to this in the shutdown case is to kill anyone > waiting on a lock held by the startup process at the same time we kill > the startup process, and to kill anyone who subsequently waits for > such a lock as soon as they attempt to take it. If you're not going to apply any more WAL records before shutdown, you could also just release all the AccessExclusiveLocks held by the startup process. Whatever the transaction was doing with the locked relation, if we're not going to replay any more WAL records before shutdown, we will not see the transaction committing or doing anything else with the relation, so we should be safe. Whatever state the data on disk is in, it must be valid, or we would have a problem with crash recovery recovering up to this WAL record and then starting up too. I'm not 100% clear if that reasoning applies to AccessExclusiveLocks taken explicitly with LOCK TABLE. It's not clear what the application would use the lock for. Nevertheless, maybe killing the transactions that wait for the locks would be more intuitive anyway. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Robert Haas wrote: > On Thu, May 6, 2010 at 2:47 PM, Josh Berkus wrote: >>> Now that I've realized what the real problem is with max_standby_delay >>> (namely, that inactivity on the master can use up the delay), I think >>> we should do what Tom originally suggested here. It's not as good as >>> a really working max_standby_delay, but we're not going to have that >>> for 9.0, and it's clearly better than a boolean. >> I guess I'm not clear on how what Tom proposed is fundamentally >> different from max_standby_delay = -1. If there's enough concurrent >> queries, recovery would never catch up. > > If your workload is that the standby server is getting pounded with > queries like crazy, then it's probably not that different: it will > fall progressively further behind. But I suspect many people will set > up standby servers where most of the activity happens on the primary, > but they run some reporting queries on the standby. If you expect > your reporting queries to finish in <10s, you could set the max delay > to say 60s. In the event that something gets wedged, recovery will > eventually kill it and move on rather than just getting stuck forever. > If the volume of queries is known not to be too high, it's reasonable > to expect that a few good whacks will be enough to get things back on > track. Yeah, I could live with that. A problem with using the name "max_standby_delay" for Tom's suggestion is that it sounds like a hard limit, which it isn't. But if we name it something like: # -1 = no timeout # 0 = kill conflicting queries immediately # > 0 wait for N seconds, then kill query standby_conflict_timeout = -1 it's more clear that the setting is a timeout for each *conflict*, and it's less surprising that the standby can fall indefinitely behind in the worst case. If we name the setting along those lines, I could live with that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On May 10, 2010, at 11:43 , Heikki Linnakangas wrote: > If you're not going to apply any more WAL records before shutdown, you > could also just release all the AccessExclusiveLocks held by the startup > process. Whatever the transaction was doing with the locked relation, if > we're not going to replay any more WAL records before shutdown, we will > not see the transaction committing or doing anything else with the > relation, so we should be safe. Whatever state the data on disk is in, > it must be valid, or we would have a problem with crash recovery > recovering up to this WAL record and then starting up too. Sounds plausible. But wouldn't this imply that HS could *always* postpone the acquisition of an AccessExclusiveLocks until right before the corresponding commit record is replayed? If fail to see a case where this would fail, yet recovery in case of an intermediate crash would be correct. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] make install fails due to "/bin/mkdir: missing operand"
Hello. This is my first mail to "pgsql-hackers@postgresql.org". When I install PostgreSQL,I get an error. So I'd like to send a report . PostgreSQL version: postgresql-9.0beta1 Operating system: CentOS release 5.4 (Final) Reproduce case: #prefix and with-pgport are not important ./configure --enable-nls='UFT_JP' --prefix=/home/p900/posgrehome --with-pgport=1900 make && make install make install log--- : : make[3]: Leaving directory `/home/p900/postgresql-9.0beta1/src/timezone' /bin/mkdir: missing operand Try `/bin/mkdir --help' for more information. make[2]: *** [installdirs-po] Error 1 make[2]: Leaving directory `/home/p900/postgresql-9.0beta1/src/backend' make[1]: *** [install] Error 2 make[1]: Leaving directory `/home/p900/postgresql-9.0beta1/src' make: *** [install] Error 2 -- First of all,I have a mistake. "enable-nls" allows only 'de es fr ja pt_BR tr'. I can work around to fix this. But we can install PostgreSQL with the configure command in 8.3.10 or before version. We get an error following line of the Makefile. -- postgresql-9.0beta1/src/nls-global.mk 74 installdirs-po: 75 $(MKDIR_P) $(foreach lang, $(LANGUAGES), '$(DESTDIR)$(localedir)'/$(lang)/LC_MESSAGES) -- In 8.3.10 or before version, we use "mkinstalldirs" and this allow "miss operand" -- ##mkinstalldirs test [p8...@postgres01 config]$ ./mkinstalldirs [p8...@postgres01 config]$ echo $? 0 ##MKDIR_P test [p...@postgres01 src]$ mkdir -p mkdir: missing operand [p...@postgres01 src]$ echo $? 1 # I also checked following archives # http://archives.postgresql.org/pgsql-committers/2009-08/msg00277.php -- In the first place,I must fix my configure option. But I also think that configure command have to check wrong config parameter, because I could not install postgresql-9.0beta1 for some days. We can work around to make mkinstalldirs, but I feel it is not good becasuse it casts away the change of "pgsql-committers/2009-08/msg00277.php". Thank you for reading. Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
* Heikki Linnakangas [100510 06:03]: > A problem with using the name "max_standby_delay" for Tom's suggestion > is that it sounds like a hard limit, which it isn't. But if we name it > something like: I'ld still rather an "if your killing something, make sure you kill enough to get all the way current" behaviour, but that's just me I'm want to run my standbys in a always current mode... But if I decide to play with a lagged HR, I really want to make sure there is some mechanism to cap the lag, and the "cap" is something I can understand and use to make a reasonable estimate as to when data I know is live on the primary will be seen on the standby... bonus points if it works similarly for archive recovery ;-) a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] max_standby_delay considered harmful
On Mon, May 10, 2010 at 2:27 AM, Simon Riggs wrote: > I already explained that killing the startup process first is a bad idea > for many reasons when shutdown was discussed. Can't remember who added > the new standby shutdown code recently, but it sounds like their design > was pretty poor if it didn't include shutting down properly with HS. I > hope they fix the bug they have introduced. HS was never designed to > work that way, so there is no flaw there; it certainly worked when > committed. The patch was written by Fujii Masao and committed, after review, by me. Prior to that patch, smart shutdown never worked; now it works, or so I believe, unless recovery is stalled holding a lock upon which a regular back-end is blocking. Clearly that is both better and not all that good. If you have any ideas to improve the situation further, I'm all ears. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On Mon, May 10, 2010 at 6:03 AM, Heikki Linnakangas wrote: > Yeah, I could live with that. > > A problem with using the name "max_standby_delay" for Tom's suggestion > is that it sounds like a hard limit, which it isn't. But if we name it > something like: > > # -1 = no timeout > # 0 = kill conflicting queries immediately > # > 0 wait for N seconds, then kill query > standby_conflict_timeout = -1 > > it's more clear that the setting is a timeout for each *conflict*, and > it's less surprising that the standby can fall indefinitely behind in > the worst case. If we name the setting along those lines, I could live > with that. Yeah, if we do it that way, +1 for changing the name, and your suggestion seems good. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On Mon, May 10, 2010 at 6:13 AM, Florian Pflug wrote: > On May 10, 2010, at 11:43 , Heikki Linnakangas wrote: >> If you're not going to apply any more WAL records before shutdown, you >> could also just release all the AccessExclusiveLocks held by the startup >> process. Whatever the transaction was doing with the locked relation, if >> we're not going to replay any more WAL records before shutdown, we will >> not see the transaction committing or doing anything else with the >> relation, so we should be safe. Whatever state the data on disk is in, >> it must be valid, or we would have a problem with crash recovery >> recovering up to this WAL record and then starting up too. > > Sounds plausible. But wouldn't this imply that HS could *always* postpone the > acquisition of an AccessExclusiveLocks until right before the corresponding > commit record is replayed? If fail to see a case where this would fail, yet > recovery in case of an intermediate crash would be correct. Yeah, I'd like to understand this, too. I don't have a clear understanding of when HS needs to take locks here in the first place. [removing Josh Berkus's persistently bouncing email from the CC line] -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Florian Pflug wrote: > On May 10, 2010, at 11:43 , Heikki Linnakangas wrote: >> If you're not going to apply any more WAL records before shutdown, you >> could also just release all the AccessExclusiveLocks held by the startup >> process. Whatever the transaction was doing with the locked relation, if >> we're not going to replay any more WAL records before shutdown, we will >> not see the transaction committing or doing anything else with the >> relation, so we should be safe. Whatever state the data on disk is in, >> it must be valid, or we would have a problem with crash recovery >> recovering up to this WAL record and then starting up too. > > Sounds plausible. But wouldn't this imply that HS could *always* postpone the > acquisition of an AccessExclusiveLocks until right before the corresponding > commit record is replayed? If fail to see a case where this would fail, yet > recovery in case of an intermediate crash would be correct. I guess it could in some situations, but for example the AccessExclusiveLock taken at the end of lazy vacuum to truncate the relation must be held during the truncation, or concurrent readers will get upset. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On Monday 10 May 2010 14:00:45 Heikki Linnakangas wrote: > Florian Pflug wrote: > > On May 10, 2010, at 11:43 , Heikki Linnakangas wrote: > >> If you're not going to apply any more WAL records before shutdown, you > >> could also just release all the AccessExclusiveLocks held by the startup > >> process. Whatever the transaction was doing with the locked relation, if > >> we're not going to replay any more WAL records before shutdown, we will > >> not see the transaction committing or doing anything else with the > >> relation, so we should be safe. Whatever state the data on disk is in, > >> it must be valid, or we would have a problem with crash recovery > >> recovering up to this WAL record and then starting up too. > > > > Sounds plausible. But wouldn't this imply that HS could *always* postpone > > the acquisition of an AccessExclusiveLocks until right before the > > corresponding commit record is replayed? If fail to see a case where > > this would fail, yet recovery in case of an intermediate crash would be > > correct. > > I guess it could in some situations, but for example the > AccessExclusiveLock taken at the end of lazy vacuum to truncate the > relation must be held during the truncation, or concurrent readers will > get upset. Actually all the locks that do not need to be taken on the slave would not need to be an ACCESS EXCLUSIVE but a EXCLUSIVE on the master, right? That should be "fixed" on the master, not hacked up on the slave and is by far out of scope of 9.0. Thats an area where I definitely would like to improve pg in the future... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
On 05/08/2010 04:07 AM, Craig Ringer wrote: It's probably worth mentioning that people who want to turn off fsync to gain a performance boost should instead look at a RAID controller with a BBU so they can safely enable write-back caching, getting most of the benefits of fsync=off safely. Which options specifically should be set if a BBU is in use? Obviously fsync should be on always, but can full_page_writes be disabled? Are there other tweaks that can be done? It would be great to see some practical hints in the documentation while the fsync part is getting changed. -- m. tharp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Simon Riggs wrote: > Bruce has used the word crippleware for the current state. Raising a > problem and then blocking solutions is the best way I know to cripple a > release. It should be clear that I've done my best to avoid this FYI, it was Robert Haas who used the term "crippleware" to describe a boolean value for max_standby_delay, and I was just repeating his term, and disputing it would be crippleware. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Robert Haas wrote: > Wultsch (who doesn't ever want to kill queries and therefore would be > happy with a boolean), Yeb Havinga (who never wants to stall recovery > and therefore would also be happy with a boolean), and Florian Pflug > (who points out that pause/resume is actually a nontrivial feature). > Apologies if I've left anyone out or misrepresented their position. > > Overall I would say opinion is about evenly split between: > > - leave it as-is > - make it a Boolean > - change it in some way but to something more expressive than a Boolean > > I can't presume to extract a consensus from that; I don't think there > is one. You could say "the majority of people want to change > something" and that would be true; you could also say "the majority of > people don't want a Boolean" and that would also be true. Yep, this is where we are. Discussion had stopped, so it seemed like time for a decision, and with no one agreeing on what to do, feature removal seemed like the best approach. Suggesting we will fix it later in beta is not a solution. Now, if everyone agrees we should do X, and X in simple, lets do X, but I am stil not seeing that. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
Michael Tharp wrote: > On 05/08/2010 04:07 AM, Craig Ringer wrote: > > It's probably worth mentioning that people who want to turn off fsync to > > gain a performance boost should instead look at a RAID controller with a > > BBU so they can safely enable write-back caching, getting most of the > > benefits of fsync=off safely. > > Which options specifically should be set if a BBU is in use? Obviously > fsync should be on always, but can full_page_writes be disabled? Are > there other tweaks that can be done? > > It would be great to see some practical hints in the documentation while > the fsync part is getting changed. Uh, our docs have: Turning this parameter off speeds normal operation, but might lead to a corrupt database after an operating system crash or power failure. The risks are similar to turning off fsync, though smaller. It might be safe to turn off this parameter if you have hardware (such as a battery-backed disk controller) or file-system software that reduces the risk of partial page writes to an acceptably low level (e.g., ZFS). -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On Mon, May 10, 2010 at 6:03 AM, Heikki Linnakangas wrote: > Robert Haas wrote: >> On Thu, May 6, 2010 at 2:47 PM, Josh Berkus wrote: Now that I've realized what the real problem is with max_standby_delay (namely, that inactivity on the master can use up the delay), I think we should do what Tom originally suggested here. It's not as good as a really working max_standby_delay, but we're not going to have that for 9.0, and it's clearly better than a boolean. >>> I guess I'm not clear on how what Tom proposed is fundamentally >>> different from max_standby_delay = -1. If there's enough concurrent >>> queries, recovery would never catch up. >> >> If your workload is that the standby server is getting pounded with >> queries like crazy, then it's probably not that different: it will >> fall progressively further behind. But I suspect many people will set >> up standby servers where most of the activity happens on the primary, >> but they run some reporting queries on the standby. If you expect >> your reporting queries to finish in <10s, you could set the max delay >> to say 60s. In the event that something gets wedged, recovery will >> eventually kill it and move on rather than just getting stuck forever. >> If the volume of queries is known not to be too high, it's reasonable >> to expect that a few good whacks will be enough to get things back on >> track. > > Yeah, I could live with that. > > A problem with using the name "max_standby_delay" for Tom's suggestion > is that it sounds like a hard limit, which it isn't. But if we name it > something like: > > # -1 = no timeout > # 0 = kill conflicting queries immediately > # > 0 wait for N seconds, then kill query > standby_conflict_timeout = -1 > > it's more clear that the setting is a timeout for each *conflict*, and > it's less surprising that the standby can fall indefinitely behind in > the worst case. If we name the setting along those lines, I could live > with that. +1 from the peanut gallery. -- Mike Rylander | VP, Research and Design | Equinox Software, Inc. / The Evergreen Experts | phone: 1-877-OPEN-ILS (673-6457) | email: mi...@esilibrary.com | web: http://www.esilibrary.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Bruce Momjian wrote: > Robert Haas wrote: >> Overall I would say opinion is about evenly split between: >> >> - leave it as-is >> - make it a Boolean >> - change it in some way but to something more expressive than a >> Boolean I think a boolean would limit the environments in which HS would be useful. Personally, I think how far the replica is behind the source is a more useful metric, even with anomalies on the transition from idle to active; but a blocking duration would be much better than no finer control than the boolean. So my "instant runoff second choice" would be for the block duration knob. > time for a decision, and with no one agreeing on what to do, > feature removal seemed like the best approach. I keep wondering at the assertion that once a GUC is present (especially a tuning GUC like this) that we're stuck with it. I know that's true of SQL code constructs, but postgresql.conf files? How about redirect_stderr, max_fsm_*, sort_mem, etc.? This argument seems tenuous. > Suggesting we will fix it later in beta is not a solution. I'm with you there, 100% -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] make install fails due to "/bin/mkdir: missing operand"
Excerpts from Kenichiro Tanaka's message of lun may 10 07:07:27 -0400 2010: > Reproduce case: > #prefix and with-pgport are not important > ./configure --enable-nls='UFT_JP' --prefix=/home/p900/posgrehome > --with-pgport=1900 > make && make install I think this is pilot error, in the sense that it doesn't fail if you don't pass an invalid language name. Maybe the bug is that we allow --enable-nls to pass down junk down to the install Makefile, instead of erroring out right there. The failure was introduced in this patch: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=2b30e0ad2f198505c584792e0694a65e10cabe39 -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
On Mon, May 10, 2010 at 11:12 AM, Bruce Momjian wrote: > Michael Tharp wrote: >> On 05/08/2010 04:07 AM, Craig Ringer wrote: >> > It's probably worth mentioning that people who want to turn off fsync to >> > gain a performance boost should instead look at a RAID controller with a >> > BBU so they can safely enable write-back caching, getting most of the >> > benefits of fsync=off safely. >> >> Which options specifically should be set if a BBU is in use? Obviously >> fsync should be on always, but can full_page_writes be disabled? Are >> there other tweaks that can be done? >> >> It would be great to see some practical hints in the documentation while >> the fsync part is getting changed. > > Uh, our docs have: > > Turning this parameter off speeds normal operation, but might > lead to a corrupt database after an operating system crash or > power failure. The risks are similar to turning off > fsync, though smaller. It might be safe to turn > off this parameter if you have hardware (such as a battery-backed > disk controller) or file-system software that reduces the risk > of partial page writes to an acceptably low level (e.g., ZFS). "It might be safe" is a bit of a waffle. It would be nice if we could provide some more clear guidance as to whether it is or is not, or how someone could go about testing their hardware to find out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
Robert Haas wrote: > On Mon, May 10, 2010 at 11:12 AM, Bruce Momjian wrote: > > Michael Tharp wrote: > >> On 05/08/2010 04:07 AM, Craig Ringer wrote: > >> > It's probably worth mentioning that people who want to turn off fsync to > >> > gain a performance boost should instead look at a RAID controller with a > >> > BBU so they can safely enable write-back caching, getting most of the > >> > benefits of fsync=off safely. > >> > >> Which options specifically should be set if a BBU is in use? Obviously > >> fsync should be on always, but can full_page_writes be disabled? Are > >> there other tweaks that can be done? > >> > >> It would be great to see some practical hints in the documentation while > >> the fsync part is getting changed. > > > > Uh, our docs have: > > > > ? ? ? ?Turning this parameter off speeds normal operation, but might > > ? ? ? ?lead to a corrupt database after an operating system crash or > > ? ? ? ?power failure. The risks are similar to turning off > > ? ? ? ?fsync, though smaller. ?It might be safe to turn > > ? ? ? ?off this parameter if you have hardware (such as a battery-backed > > ? ? ? ?disk controller) or file-system software that reduces the risk > > ? ? ? ?of partial page writes to an acceptably low level (e.g., ZFS). > > "It might be safe" is a bit of a waffle. It would be nice if we could > provide some more clear guidance as to whether it is or is not, or how > someone could go about testing their hardware to find out. Agreed. It is "safe" for us to be definitive here? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
Robert Haas wrote: > "It might be safe" is a bit of a waffle. It would be nice if we > could provide some more clear guidance as to whether it is or is > not, or how someone could go about testing their hardware to find > out. I think that the issue is that you could have corruption if some, but not all, disk sectors from a page were written from OS cache to controller cache when a failure occurred. The window would be small for a RAM-to-RAM write, but it wouldn't be entirely *safe* unless there's some OS/driver environment where you could count on all the sectors making it or none of them making it for every single page. Does such an environment exist? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
* Aidan Van Dyk (ai...@highrise.ca) wrote: > * Heikki Linnakangas [100510 06:03]: > > A problem with using the name "max_standby_delay" for Tom's suggestion > > is that it sounds like a hard limit, which it isn't. But if we name it > > something like: > > I'ld still rather an "if your killing something, make sure you kill > enough to get all the way current" behaviour, but that's just me I agree with that comment, and it's more like what max_standby_delay was. That's what I had thought Tom was proposing initially, since it makes a heck of alot more sense to me than "just keep waiting, just keep waiting..". Now, if it's possible to have things queue up behind the recovery process, such that the recovery process will only wait up to timeout * # of locks held when recovery started, that might be alright, but that's not the impression I've gotten about how this will work. Of course, I also want to be able to have a Nagios hook that checks how far behind the slave has gotten, and a way to tell the slave "oook, you're too far behind, just forcibly catch up right *now*". If I could use reload to change max_standby_delay (or whatever) and I can figure out how long the delay is (even if I have to update a table on the master and then see what it says on the slave..), I'd be happy. That being said, I do think it makes more sense to wait until we've got a conflict to start the timer, and I rather like avoiding the uncertainty of time sync between master and slave by using WAL arrival time on the slave. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] max_standby_delay considered harmful
Kevin Grittner wrote: > Bruce Momjian wrote: > > Robert Haas wrote: > > >> Overall I would say opinion is about evenly split between: > >> > >> - leave it as-is > >> - make it a Boolean > >> - change it in some way but to something more expressive than a > >> Boolean > > I think a boolean would limit the environments in which HS would be > useful. Personally, I think how far the replica is behind the > source is a more useful metric, even with anomalies on the > transition from idle to active; but a blocking duration would be > much better than no finer control than the boolean. So my "instant > runoff second choice" would be for the block duration knob. > > > time for a decision, and with no one agreeing on what to do, > > feature removal seemed like the best approach. > > I keep wondering at the assertion that once a GUC is present > (especially a tuning GUC like this) that we're stuck with it. I > know that's true of SQL code constructs, but postgresql.conf files? > How about redirect_stderr, max_fsm_*, sort_mem, etc.? This argument > seems tenuous. You are right that we are much more flexible about changing administrative configuration parameters (like this one) than SQL. In the past, we even renamed logging parameters to be more consistent, and I think that proves the bar is quite low for GUC administrative parameter change. :-) The concern about 'max_standby_delay' is that it controls a lot of new code and affects the behavior of HS/SR in ways that might cause a poor user experience, expecially for non-expert users. I admit that expert users can use the setting, but we are coding for a general user base, and we might have to field many questions about 'max_standby_delay' from general users that will make us look bad. "The setting is total useless" is something we have heard about other partial solutions we have released in the past. We try to avoid that. ;-) Labeling something "experimental" also makes our code look sloppy. And if we decide the problem is unsolvable using this approach, we should remove it now rather than later. We don't like to carry around a wart for a small segment of our userbase. I realize many of you have not been around to see some of our less-than-perfect solutions and to see the pain they cause. Once something gets it, we have to fight to remove it. In fact, there is no way we would add 'max_standby_delay' into our codebase now, knowing its limitations, but people are having to fight hard for its removal, if necessary. Now that discussion has restarted again, let's keep going to see if can reach some kind of simple solution. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] make install fails due to "/bin/mkdir: missing operand"
Alvaro Herrera writes: > Excerpts from Kenichiro Tanaka's message of lun may 10 07:07:27 -0400 2010: >> ./configure --enable-nls='UFT_JP' --prefix=/home/p900/posgrehome > I think this is pilot error, in the sense that it doesn't fail if you > don't pass an invalid language name. Maybe the bug is that we allow > --enable-nls to pass down junk down to the install Makefile, instead of > erroring out right there. Yeah, I don't think this is the makefile's fault. If we wanted to do something to make it more user-friendly, we'd try to validate the value of --enable-nls at configure time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On Mon, May 10, 2010 at 5:20 PM, Bruce Momjian wrote: > You are right that we are much more flexible about changing > administrative configuration parameters (like this one) than SQL. In the > past, we even renamed logging parameters to be more consistent, and I > think that proves the bar is quite low for GUC administrative parameter > change. :-) > > The concern about 'max_standby_delay' is that it controls a lot of new > code and affects the behavior of HS/SR in ways that might cause a poor > user experience, expecially for non-expert users. I would like to propose that we do the following: 1) Replace max_standby_delay with a boolean as per heikki's suggestion 2) Add an explicitly experimental option like max_standby_delay or recovery_conflict_timeout which is only effective if you've chosen recovery_conflict="pause recovery" option and is explicitly documented as being scheduled to be replaced with a more complete system in future versions. My thinking is that when we do replace max_standby_delay we would keep the recovery_conflict parameter with the same semantics. It's just the additional experimental option which would change. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
On Mon, May 10, 2010 at 4:55 PM, Kevin Grittner wrote: > Robert Haas wrote: > >> "It might be safe" is a bit of a waffle. It would be nice if we >> could provide some more clear guidance as to whether it is or is >> not, or how someone could go about testing their hardware to find >> out. > > I think that the issue is that you could have corruption if some, > but not all, disk sectors from a page were written from OS cache to > controller cache when a failure occurred. The window would be small > for a RAM-to-RAM write, but it wouldn't be entirely *safe* unless > there's some OS/driver environment where you could count on all the > sectors making it or none of them making it for every single page. > Does such an environment exist? The reason for the waffle is that the following sentence describes a whole set of environments based the following description: > > ? ? ? ?if you have hardware (such as a battery-backed > > ? ? ? ?disk controller) or file-system software that reduces the risk > > ? ? ? ?of partial page writes to an acceptably low level Depending on which set of hardware and how low the risk is it might be safe. I think with WAFL or ZFS it's entirely safe. There may be other filesystems with similar guarantees. With a BBU the risk might be very low -- but it might not, it would be hard to determine without a detailed analysis of the entire stack from the buffer cache, filesystem, lvm, hardware drivers, BBU design, etc. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] "SET search_path" clause ignored during function creation
Takahiro Itagaki writes: > Thanks for the report! Please check whether the attached patch > is the correct fix. An additional regression test is included. That's going to provoke "uninitialized variable" compiler warnings, but otherwise it seems reasonably sane. I don't particularly see the point of the added regression test. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
On Mon, 2010-05-10 at 18:46 +0100, Greg Stark wrote: > On Mon, May 10, 2010 at 4:55 PM, Kevin Grittner > wrote: > > Robert Haas wrote: > > > >> "It might be safe" is a bit of a waffle. It would be nice if we > >> could provide some more clear guidance as to whether it is or is > >> not, or how someone could go about testing their hardware to find > >> out. > > > > I think that the issue is that you could have corruption if some, > > but not all, disk sectors from a page were written from OS cache to > > controller cache when a failure occurred. The window would be small > > for a RAM-to-RAM write, but it wouldn't be entirely *safe* unless > > there's some OS/driver environment where you could count on all the > > sectors making it or none of them making it for every single page. > > Does such an environment exist? > > The reason for the waffle is that the following sentence describes a > whole set of environments based the following description: > > > > ? ? ? ?if you have hardware (such as a battery-backed > > > ? ? ? ?disk controller) or file-system software that reduces the risk > > > ? ? ? ?of partial page writes to an acceptably low level > > Depending on which set of hardware and how low the risk is it might be safe. > > I think with WAFL or ZFS it's entirely safe. There may be other > filesystems with similar guarantees. With a BBU the risk might be very > low -- but it might not, it would be hard to determine without a > detailed analysis of the entire stack from the buffer cache, > filesystem, lvm, hardware drivers, BBU design, etc. > The answer to this is: PostgreSQL.org recommends that this setting be left on at all times. Turning it off, may lead to data corruption. Anything else is circumstantial and based on knowledge and facts we don't have about environmental factors. Joshua D. Drake > -- > greg > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
"Joshua D. Drake" wrote: > The answer to this is: > > PostgreSQL.org recommends that this setting be left on at all > times. Turning it off, may lead to data corruption. > > Anything else is circumstantial and based on knowledge and facts > we don't have about environmental factors. Perhaps Josh's language for fsync could be modified to work here (we're now talking about full_page_writes, for anyone who's lost track): | it is only advisable to turn off fsync if you can easily recreate | your entire database from external data. That covers bulk loads to an empty or just-backed-up database and entirely redundant databases. Saying it should never be turned off would tend to make one wonder why we have the setting at all. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
"Kevin Grittner" writes: > Perhaps Josh's language for fsync could be modified to work here > (we're now talking about full_page_writes, for anyone who's lost > track): > | it is only advisable to turn off fsync if you can easily recreate > | your entire database from external data. > That covers bulk loads to an empty or just-backed-up database and > entirely redundant databases. Saying it should never be turned off > would tend to make one wonder why we have the setting at all. +1. Perhaps for both of them, we should specify that the intended use-case is for improving performance during initial database load and similar cases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
2010/5/8 Bernd Helmle : > > > --On 7. Mai 2010 09:48:53 -0500 Kevin Grittner > wrote: > >> I think it goes beyond "tweaking" -- I think we should have a bald >> statement like "don't turn this off unless you're OK with losing the >> entire contents of the database cluster." A brief listing of some >> cases where that is OK might be illustrative. >> > > +1 > >> I never meant to suggest any statement in that section is factually >> wrong; it's just all too rosy, leading people to believe it's no big >> deal to turn it off. > > I think one mistake in this paragraph is the passing mention of > "performance". I've seen installations in the past with fsync=off only > because the admin was pressured to get instantly "more speed" out of the > database (think of "fast_mode=on"). In my opinion, phrases like "performance > penalty" are misleading, if you need that setting in 99% of all use cases > for reliable operation. > > I've recently even started to wonder if the performance gain with fsync=off > is still that large on modern hardware. While testing large migration > procedures to a new version some time ago (on an admitedly fast storage) i > forgot here and then to turn it off, without a significant degradation in > performance. On a recent pg_restore -j 32, with perc 6i with BBU, RAID10 8 hd, results were not so bas with fsync turn on. (XFS with nobarrier su and sw) -- deactivate fsync time pg_restore -U postgres -d foodb -j 32 foo.psql real170m0.527s user43m12.914s sys 1m56.499s -- activate fsync time pg_restore -U postgres -d foodb -j 32 foo.psql real177m0.121s user42m54.581s sys 2m0.452s > > > -- > Thanks > > Bernd > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Cédric Villemain -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
All, Updated docs based on tracking this discussion. fsync through full page writes recorded below. fsync configuration parameter fsync (boolean) If this parameter is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods (see ). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash. While turning off fsync is often a performance benefit, this can result in unrecoverable data corruption in the event of an unexpected system shutdown or crash. Thus it is only advisable to turn off fsync if you can easily recreate your entire database from external data. Examples of safe times to turn off fsync would be when initially loading a new database from a backup file, on a database which is only used for processing statistics on an hourly basis and is then deleted, or on a reporting read-only clone of your database which gets recreated very night and is not used for failover. High quality hardware alone is not a sufficient justification for turning off fsync. In many situations, turning off for noncritical transactions can provide much of the potential performance benefit of turning off fsync, without the attendant risks of data corruption. fsync can only be set in the postgresql.conf file or on the server command line. If you turn this parameter off, also consider turning off . synchronous_commit (boolean) synchronous_commit configuration parameter Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a success indication to the client. The default, and safe, setting is on. When off, there can be a delay between when success is reported to the client and when the transaction is really guaranteed to be safe against a server crash. (The maximum delay is three times .) Unlike , setting this parameter to off does not create any risk of database inconsistency: a crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion see . This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously. For example, to make a single multi-statement transaction commit asynchronously when the default is the opposite, issue SET LOCAL synchronous_commit TO OFF within the transaction. wal_sync_method (enum) wal_sync_method configuration parameter Method used for forcing WAL updates out to disk. If fsync is off then this setting is irrelevant, since WAL file updates will not be forced out at all. Possible values are: open_datasync (write WAL files with open() option O_DSYNC) fdatasync (call fdatasync() at each commit) fsync_writethrough (call fsync() at each commit, forcing write-through of any disk write cache) fsync (call fsync() at each commit) open_sync (write WAL files with open() option O_SYNC) Not all of these choices are available on all platforms. The default is the first method in the above list that is supported by the platform. The open_* options also use O_DIRECT if available. The utility src/tools/fsync in the PostgreSQL source tree can do performance testing of various fsync methods. This parameter can only be set in the postgresql.conf file or on the server command line. full_page_writes configuration parameter full_page_writes (boolean) When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the
Re: [HACKERS] max_standby_delay considered harmful
> 1) Replace max_standby_delay with a boolean as per heikki's suggestion > > 2) Add an explicitly experimental option like max_standby_delay or > recovery_conflict_timeout which is only effective if you've chosen > recovery_conflict="pause recovery" > option and is explicitly documented as being scheduled to be replaced > with a more complete system in future versions. +1 As far as I can tell, the current delay *works*. It just doesn't necessarily work the way most people expect it to to work. Kind of like, hmmm, shared_buffers? Or effective_cache_size? Or effective_io_concurrency? And I still think that having this kind of a delay option will give us invaluable use feedback on how the option *should* work in 9.1, which we won't get if we don't have an option. I think we will be overhauling it for 9.1, but I don't think that overhaul will benefit from a lack of data. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
Cédric Villemain wrote: > On a recent pg_restore -j 32, with perc 6i with BBU, RAID10 8 hd, > results were not so bas with fsync turn on. (XFS with nobarrier su > and sw) > -- deactivate fsync > time pg_restore -U postgres -d foodb -j 32 foo.psql > real170m0.527s > user43m12.914s > sys 1m56.499s > -- activate fsync > time pg_restore -U postgres -d foodb -j 32 foo.psql > real177m0.121s > user42m54.581s > sys 2m0.452s Wow. In a situation where you save seven minutes (4%), it's hardly worth turning off. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
> Wow. In a situation where you save seven minutes (4%), it's hardly > worth turning off. I've had it be much higher, especially for really large databases. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
On Mon, May 10, 2010 at 01:35:32PM -0700, Josh Berkus wrote: > deleted, > or on a reporting read-only clone of your database which gets > recreated very > night and is not used for failover. High quality hardware alone s/very/every/ or s/very night/periodically/ Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer & Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On May 10, 2010, at 17:39 , Kevin Grittner wrote: > Bruce Momjian wrote: >> Robert Haas wrote: > >>> Overall I would say opinion is about evenly split between: >>> >>> - leave it as-is >>> - make it a Boolean >>> - change it in some way but to something more expressive than a >>> Boolean > > I think a boolean would limit the environments in which HS would be > useful. Personally, I think how far the replica is behind the > source is a more useful metric, even with anomalies on the > transition from idle to active; but a blocking duration would be > much better than no finer control than the boolean. So my "instant > runoff second choice" would be for the block duration knob. You could always toggle that boolean automatically, based on some measurement of the replication lag (Assuming the boolean would be settable at runtime). That'd give you much more flexibility than any built-on knob could provide, and even more so than a built-in knob with known deficiencies. My preference is hence to make it a boolean, but in a way that allows more advanced behavior to be implemented on top of it. In the simplest case by allowing the boolean to be flipped at runtime and ensuring that the system reacts in a sane way. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
Josh Berkus wrote: Wow. In a situation where you save seven minutes (4%), it's hardly worth turning off. I've had it be much higher, especially for really large databases. Cedric's system had a non-volatile write cache in it. In that case, a few percentage points of improvement is normal--the overhead of fsync is very low. In the case where you don't have one of those, and the write cache on the drives are turned off for safety too, I've seen turning fsync off be a 40X speedup--100 inserts/second jumping to 4000TPS. (This was before synchronous_commit). The real question is how much of a speed-up fsync provides compared to the same workload with synchronous_commit disabled. The only case for fsync=off is one where that number is much faster. That's the case on some low-level operations (I seem to recall there is no async commit speedup for CREATE DATABASE for example). But for most of what people want to speed, just killing sync commit while keeping fsync is on is good enough. I suspect there are still some bulk-load workloads where fsync=off helps beyond just going for async commit, but they're tougher to find and the difference isn't huge relative to total load times. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
> The real question is how much of a speed-up fsync provides compared to > the same workload with synchronous_commit disabled. The only case for > fsync=off is one where that number is much faster. I can't say I've tested this. Most of my head-to-heads on fsync were before asych existed. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
On 5/10/10 2:21 PM, Ross J. Reedstrom wrote: > On Mon, May 10, 2010 at 01:35:32PM -0700, Josh Berkus wrote: >> deleted, >> or on a reporting read-only clone of your database which gets >> recreated very >> night and is not used for failover. High quality hardware alone > > s/very/every/ > or > s/very night/periodically/ "frequently" I think. Periodically could mean once a year. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
Josh Berkus wrote: The real question is how much of a speed-up fsync provides compared to the same workload with synchronous_commit disabled. The only case for fsync=off is one where that number is much faster. I can't say I've tested this. Most of my head-to-heads on fsync were before asych existed. Ditto for me. Curious about that, and I'd like to help work on improving this chunk of the docs too. I don't know about you guys, but I'm swamped until after PGCon though. I have some hardware testing stuff planned anyway later this month, can check exactly where this situation truly stands on a couple of common pieces of hardware (next system has one of the LSI controllers Dell rebrands too). I'll have the systems setup for something similar anyway--can certainly see fsync differences with pgbench--easy to throw this test into the mix too. With that report, we should have the info needed to really nail this down accurately. I can make my own proofreading pass of what Josh has already been doing that also reflects the new data, and then we can commit something that's good and well reviewed for 9.0 here. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Make archiver check for SIGHUP more often?
There's a complaint over here
http://archives.postgresql.org/pgsql-general/2010-05/msg00365.php
about the archiver process not being very swift to adopt a new
value of archive_command. This is because it only reacts to SIGHUP
once per outer loop, ie, only after completing an archiving cycle.
This is unhelpful in the example case, since the point of changing
the command is to get it to finish archiving faster.
How do people feel about adding
/* Check for config update */
if (got_SIGHUP)
{
got_SIGHUP = false;
ProcessConfigFile(PGC_SIGHUP);
}
to the inner loop in pgarch_ArchiverCopyLoop? This would allow
a new archive_command value to be adopted immediately for the next
copy attempt. (Hm, I guess we'd need to recheck XLogArchiveCommandSet
as well...)
The only objection I can see to this is that someone might have an
archive command that depends on the identical command being issued
for all files copied in a given archiving cycle. However, it's tough
to see how such a dependency could arise, considering that the archive
command isn't told about the start or finish of an archiving cycle.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Invitation to connect on LinkedIn
LinkedIn Carlos Jordão souhaite se connecter à vous sur LinkedIn : -- Amine, I'd like to add you to my professional network on LinkedIn. - Carlos Accepter l'invitation de Carlos Jordão http://www.linkedin.com/e/VWCdriNHrcXMfYpwEG6pcCKmodzjIoHmhgnzwrRR5NC/blk/I59447601_6/6lColZJrmZznQNdhjRQnOpBtn9QfmhBt71BoSd1p65Lr6lOfPpvcj0SdPgQejl9bSN7gQJIdktzbP0Pe3kQejAUcz4LrCBxbOYWrSlI/EML_comm_afe/ Voir l'invitation de Carlos Jordão http://www.linkedin.com/e/VWCdriNHrcXMfYpwEG6pcCKmodzjIoHmhgnzwrRR5NC/blk/I59447601_6/dBYNc3oTd3gVdkALqnpPbOYWrSlI/svi/ -- SAVEZ-VOUS que LinkedIn peut trouver une réponse aux questions les plus complexes ? Publiez ces questions dans la rubrique Réponses de LinkedIn pour profiter de l'expertise des plus grands experts internationaux : http://www.linkedin.com/e/ask/inv-23/ -- (c) 2010, LinkedIn Corporation
Re: [HACKERS] [GENERAL] Archiver not picking up changes to archive_command
On Tue, May 11, 2010 at 9:50 AM, Tom Lane wrote: > bricklen writes: >> Due to some heavy processing today, we have been falling behind on >> shipping log files (by about a 1000 logs or so), so wanted to up our >> bwlimit like so: > >> rsync -a %p postg...@192.168.80.174:/WAL_Archive/ && rsync >> --bwlimit=1875 -az %p postg...@14.121.70.98:/WAL_Archive/ > >> The db is showing the change. >> SHOW archive_command: >> rsync -a %p postg...@192.168.80.174:/WAL_Archive/ && rsync >> --bwlimit=1875 -az %p postg...@14.121.70.98:/WAL_Archive/ > >> Yet, the running processes never get above the original bwlimit of >> 1250. Have I missed a step? Would "kill -HUP " help? >> (I'm leery of trying that untested though) > > A look at the code shows that the archiver only notices SIGHUP once > per outer loop, so the change would only take effect once you catch up, > which is not going to help much in this case. Possibly we should change > it to check for SIGHUP after each archive_command execution. +1 Here is the simple patch to do so. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center pgarch_check_sighup_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make archiver check for SIGHUP more often?
On Tue, May 11, 2010 at 10:13 AM, Tom Lane wrote:
> There's a complaint over here
> http://archives.postgresql.org/pgsql-general/2010-05/msg00365.php
> about the archiver process not being very swift to adopt a new
> value of archive_command. This is because it only reacts to SIGHUP
> once per outer loop, ie, only after completing an archiving cycle.
> This is unhelpful in the example case, since the point of changing
> the command is to get it to finish archiving faster.
>
> How do people feel about adding
>
> /* Check for config update */
> if (got_SIGHUP)
> {
> got_SIGHUP = false;
> ProcessConfigFile(PGC_SIGHUP);
> }
>
> to the inner loop in pgarch_ArchiverCopyLoop? This would allow
> a new archive_command value to be adopted immediately for the next
> copy attempt. (Hm, I guess we'd need to recheck XLogArchiveCommandSet
> as well...)
Yeah, go for it.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] "SET search_path" clause ignored during function creation
Tom Lane wrote: > Takahiro Itagaki writes: > > Thanks for the report! Please check whether the attached patch > > is the correct fix. An additional regression test is included. > > That's going to provoke "uninitialized variable" compiler warnings, > but otherwise it seems reasonably sane. I applied a revised version that can surpress compiler warnings to 9.0beta, 8.4 and 8.3. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On Mon, May 10, 2010 at 3:27 PM, Simon Riggs wrote: > I already explained that killing the startup process first is a bad idea > for many reasons when shutdown was discussed. Can't remember who added > the new standby shutdown code recently, but it sounds like their design > was pretty poor if it didn't include shutting down properly with HS. I > hope they fix the bug they have introduced. HS was never designed to > work that way, so there is no flaw there; it certainly worked when > committed. New smart shutdown during recovery doesn't kill the startup process until all of the read only backends have gone away. So it works fine with HS. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
> The referential integrity triggers contain some extra magic that isn't > easily simulatable in userland, and that is necessary to make the > foreign key constraints airtight. We've discussed this previously but > I don't remember which thread it was or the details of when things > blow up. I think it's something like this: the parent has a tuple > that is not referenced by any child. Transaction 1 begins, deletes > the parent tuple (checking that it has no children), and pauses. > Transaction 2 begins, adds a child tuple that references the parent > tuple (checking that the parent exists, which it does), and commits. > Transaction 1 commits. Will SELECT ... FOR SHARE not help? Regargs, Dmitry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
