Re: [HACKERS] Why we lost Uber as a user

On Wed, Aug 17, 2016 at 5:18 PM, Jim Nasby wrote: > On 8/17/16 2:51 PM, Simon Riggs wrote: >> On 17 August 2016 at 12:19, Greg Stark wrote: >>> Yes, this is exactly what it should be doing and exactly why it's >>> useful. Physical replication accurately replicates the data from the >>> master inc

Re: [HACKERS] Why we lost Uber as a user

On 8/17/16 2:51 PM, Simon Riggs wrote: On 17 August 2016 at 12:19, Greg Stark wrote: On Wed, Aug 17, 2016 at 1:36 AM, Jim Nasby wrote: Something I didn't see mentioned that I think is a critical point: last I looked, HOT standby (and presumably SR) replays full page writes. That means that *a

Re: [HACKERS] Why we lost Uber as a user

On 17 August 2016 at 12:19, Greg Stark wrote: > On Wed, Aug 17, 2016 at 1:36 AM, Jim Nasby wrote: >> Something I didn't see mentioned that I think is a critical point: last I >> looked, HOT standby (and presumably SR) replays full page writes. That means >> that *any* kind of corruption on the ma

Re: [HACKERS] Why we lost Uber as a user

On 17 August 2016 at 21:35, Bruce Momjian wrote: > > > I saw from the Uber article that they weren't going to per-row logical > > replication but _statement_ replication, which is very hard to do > > because typical SQL doesn't record what concurrent transactions > > committed before a new state

Re: [HACKERS] Why we lost Uber as a user

On Wed, Aug 17, 2016 at 01:27:18PM +0800, Craig Ringer wrote: > It's really bugging me that people are talking about "statement based" > replication in MySQL as if it's just sending SQL text around. MySQL's > statemnet > based replication is a lot smarter than that, and in the > actually-works-pro

Re: [HACKERS] Why we lost Uber as a user

On Wed, Aug 17, 2016 at 1:36 AM, Jim Nasby wrote: > Something I didn't see mentioned that I think is a critical point: last I > looked, HOT standby (and presumably SR) replays full page writes. That means > that *any* kind of corruption on the master is *guaranteed* to replicate to > the slave the

Re: [HACKERS] Why we lost Uber as a user

On 17 August 2016 at 08:36, Jim Nasby wrote: > Something I didn't see mentioned that I think is a critical point: last I > looked, HOT standby (and presumably SR) replays full page writes. Yes, that's right, all WAL-based physical replication replays FPWs. We could, at the cost of increased WA

Re: [HACKERS] Why we lost Uber as a user

Something I didn't see mentioned that I think is a critical point: last I looked, HOT standby (and presumably SR) replays full page writes. That means that *any* kind of corruption on the master is *guaranteed* to replicate to the slave the next time that block is touched. That's completely the

Re: [HACKERS] Why we lost Uber as a user

On 8/3/16 3:29 AM, Greg Stark wrote: Honestly the take-away I see in the Uber story is that they apparently had nobody on staff that was on -hackers or apparently even -general and tried to go it alone rather than involve experts from outside their company. As a result they misdiagnosed their

Re: [HACKERS] Why we lost Uber as a user

On 8/2/16 10:02 PM, Mark Kirkwood wrote: On 03/08/16 02:27, Robert Haas wrote: Personally, I think that incremental surgery on our current heap format to try to fix this is not going to get very far. If you look at the history of this, 8.3 was a huge release for timely cleanup of dead tuple.

Re: [HACKERS] Why we lost Uber as a user

On 8/4/16 2:00 AM, Torsten Zuehlsdorff wrote: On 03.08.2016 21:05, Robert Haas wrote: On Wed, Aug 3, 2016 at 2:23 PM, Tom Lane wrote: Robert Haas writes: I don't think they are saying that logical replication is more reliable than physical replication, nor do I believe that to be true. I

Re: [HACKERS] Why we lost Uber as a user

On 03.08.2016 21:05, Robert Haas wrote: On Wed, Aug 3, 2016 at 2:23 PM, Tom Lane wrote: Robert Haas writes: I don't think they are saying that logical replication is more reliable than physical replication, nor do I believe that to be true. I think they are saying that if logical corruption

Re: [HACKERS] Why we lost Uber as a user

On Wed, Aug 3, 2016 at 2:15 PM, Tom Lane wrote: > "Joshua D. Drake" writes: >> On 08/03/2016 11:23 AM, Tom Lane wrote: >>> I think the realistic answer if you suffer replication-induced corruption >>> is usually going to be "re-clone that slave", and logical rep doesn't >>> really offer much gain

Re: [HACKERS] Why we lost Uber as a user

"Joshua D. Drake" writes: > On 08/03/2016 11:23 AM, Tom Lane wrote: >> I think the realistic answer if you suffer replication-induced corruption >> is usually going to be "re-clone that slave", and logical rep doesn't >> really offer much gain in that. > Yes, it actually does. The ability to unsu

Re: [HACKERS] Why we lost Uber as a user

On Wed, Aug 3, 2016 at 2:23 PM, Tom Lane wrote: > Robert Haas writes: >> I don't think they are saying that logical replication is more >> reliable than physical replication, nor do I believe that to be true. >> I think they are saying that if logical corruption happens, you can >> fix it by typi

Re: [HACKERS] Why we lost Uber as a user

On 08/03/2016 11:23 AM, Tom Lane wrote: Robert Haas writes: I don't think they are saying that logical replication is more reliable than physical replication, nor do I believe that to be true. I think they are saying that if logical corruption happens, you can fix it by typing SQL statements to

Re: [HACKERS] Why we lost Uber as a user

Robert Haas writes: > I don't think they are saying that logical replication is more > reliable than physical replication, nor do I believe that to be true. > I think they are saying that if logical corruption happens, you can > fix it by typing SQL statements to UPDATE, INSERT, or DELETE the > af

Re: [HACKERS] Why we lost Uber as a user

On Tue, Aug 2, 2016 at 5:14 PM, Tom Lane wrote: > Stephen Frost writes: >> With physical replication, there is the concern that a bug in *just* the >> physical (WAL) side of things could cause corruption. > > Right. But with logical replication, there's the same risk that the > master's state co

Re: [HACKERS] Why we lost Uber as a user

On 3 August 2016 at 15:04, Kevin Grittner wrote: > My initial experience with PostgreSQL would have been entirely > different had I not found the community lists and benefited from > the assistance and collective wisdom found on them. The top non-sponsored link on google for "postgres support" ta

Re: [HACKERS] Why we lost Uber as a user

On Wed, Aug 3, 2016 at 8:58 AM, Alfred Perlstein wrote: > On Aug 3, 2016, at 3:29 AM, Greg Stark wrote: > >> Honestly the take-away I see in the Uber story is that they apparently >> had nobody on staff that was on -hackers or apparently even -general >> and tried to go it alone rather than invol

Re: [HACKERS] Why we lost Uber as a user

> On Aug 3, 2016, at 3:29 AM, Greg Stark wrote: > >> > > Honestly the take-away I see in the Uber story is that they apparently > had nobody on staff that was on -hackers or apparently even -general > and tried to go it alone rather than involve experts from outside > their company. As a resu

Re: [HACKERS] Why we lost Uber as a user

On 3 August 2016 at 05:14, Tom Lane wrote: > > In short, I think Uber's position that logical replication is somehow more > reliable than physical is just wishful thinking. If anything, my money > would be on the other way around: there's a lot less mechanism that can go > wrong in physical rep

Re: [HACKERS] Why we lost Uber as a user

On Tue, Aug 2, 2016 at 10:33:15PM -0400, Bruce Momjian wrote: > I saw from the Uber article that they weren't going to per-row logical > replication but _statement_ replication, which is very hard to do > because typical SQL doesn't record what concurrent transactions > committed before a new stat

Re: [HACKERS] Why we lost Uber as a user

On Wed, Aug 3, 2016 at 3:30 AM, Alfred Perlstein wrote: > We may be saying the same thing, but still there is something to be said for > logical replication... also, didnt they show that logical replication was > faster for some use cases at Uber? There is certainly something to be said for logic

Re: [HACKERS] Why we lost Uber as a user

On 29 July 2016 at 15:44, Stephen Frost wrote: > All very hand-wavy, of course, and it'd make sense to make the concept > work for BRIN before we consider anything else, but it seems like there > could be a use-case for allowing indexes other than BRIN to be built in > a way that allows HOT updat

Re: [HACKERS] Why we lost Uber as a user

On 03/08/16 02:27, Robert Haas wrote: Personally, I think that incremental surgery on our current heap format to try to fix this is not going to get very far. If you look at the history of this, 8.3 was a huge release for timely cleanup of dead tuple. There was also significant progress in 8.4

Re: [HACKERS] Why we lost Uber as a user

On Tue, Aug 2, 2016 at 07:30:22PM -0700, Alfred Perlstein wrote: > So for instance, let's say there is a bug in the master's write to disk. > The logical replication acts as a barrier from that bad write going to the > slaves. With bad writes going to slaves then any corruption experienced on >

Re: [HACKERS] Why we lost Uber as a user

On 8/2/16 2:14 PM, Tom Lane wrote: Stephen Frost writes: With physical replication, there is the concern that a bug in *just* the physical (WAL) side of things could cause corruption. Right. But with logical replication, there's the same risk that the master's state could be fine but a repl

Re: [HACKERS] Why we lost Uber as a user

Stephen Frost writes: > With physical replication, there is the concern that a bug in *just* the > physical (WAL) side of things could cause corruption. Right. But with logical replication, there's the same risk that the master's state could be fine but a replication bug creates corruption on th

Re: [HACKERS] Why we lost Uber as a user

* Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Aug 2, 2016 at 3:07 PM, Alfred Perlstein wrote: > > You are quite technical, my feeling is that you will understand it, however > > it will need to be a self learned lesson. > > I don't know what this is supposed to mean, but I think that Ge

Re: [HACKERS] Why we lost Uber as a user

On Tue, Aug 2, 2016 at 3:07 PM, Alfred Perlstein wrote: > You are quite technical, my feeling is that you will understand it, however > it will need to be a self learned lesson. I don't know what this is supposed to mean, but I think that Geoff's point is somewhat valid. No matter how you repli

Re: [HACKERS] Why we lost Uber as a user

> On Aug 2, 2016, at 2:33 AM, Geoff Winkless wrote: > >> On 2 August 2016 at 08:11, Alfred Perlstein wrote: >>> On 7/2/16 4:39 AM, Geoff Winkless wrote: >>> I maintain that this is a nonsense argument. Especially since (as you >>> pointed out and as I missed first time around) the bug actuall

Re: [HACKERS] Why we lost Uber as a user

On 2 August 2016 at 15:27, Robert Haas wrote: > On Tue, Aug 2, 2016 at 5:51 AM, Amit Kapila wrote: >> Why we need to add a record in all indexes if only the key >> corresponding to one of indexes is updated? Basically, if the tuple >> can fit on same page, why can't we consider it as HOT (or HPT

Re: [HACKERS] Why we lost Uber as a user

On Tue, Aug 2, 2016 at 5:51 AM, Amit Kapila wrote: > Why we need to add a record in all indexes if only the key > corresponding to one of indexes is updated? Basically, if the tuple > can fit on same page, why can't we consider it as HOT (or HPT - heap > partial tuple or something like that), unl

Re: [HACKERS] Why we lost Uber as a user

On Sat, Jul 30, 2016 at 12:06 AM, Stephen Frost wrote: > * Bruce Momjian (br...@momjian.us) wrote: >> On Fri, Jul 29, 2016 at 10:44:29AM -0400, Stephen Frost wrote: >> > Another thought that was kicking around in my head related to that is if >> > we could have indexes that only provide page-level

Re: [HACKERS] Why we lost Uber as a user

On 2 August 2016 at 08:11, Alfred Perlstein wrote: > On 7/2/16 4:39 AM, Geoff Winkless wrote: > > I maintain that this is a nonsense argument. Especially since (as you > > pointed out and as I missed first time around) the bug actually occurred at > > different records on different slaves, so he

Re: [HACKERS] Why we lost Uber as a user

On 7/26/16 9:54 AM, Joshua D. Drake wrote: Hello, The following article is a very good look at some of our limitations and highlights some of the pains many of us have been working "around" since we started using the software. https://eng.uber.com/mysql-migration/ Specifically: * Ineffic

Re: [HACKERS] Why we lost Uber as a user

On 7/28/16 7:08 AM, Merlin Moncure wrote: *) postgres may not be the ideal choice for those who want a thin and simple database This is a huge market, addressing it will bring mindshare and more jobs, code and braintrust to psql. -Alfred -- Sent via pgsql-hackers mailing list (pgsql-hacke

Re: [HACKERS] Why we lost Uber as a user

On 7/28/16 4:39 AM, Geoff Winkless wrote: On 28 Jul 2016 12:19, "Vitaly Burovoy" > wrote: > > On 7/28/16, Geoff Winkless > wrote: > > On 27 July 2016 at 17:04, Bruce Momjian > wrote: > > > >> Well, their bi

Re: [HACKERS] Why we lost Uber as a user

Stephen Frost writes: > * Hannu Krosing (hkros...@gmail.com) wrote: >> Is there any theoretical obstacle which would make it impossible to >> teach VACUUM not to hold back the whole vacuum horizon, but just >> to leave a single transaction alone in case of a long-running >> REPEATABLE READ transac

Re: [HACKERS] Why we lost Uber as a user

* Hannu Krosing (hkros...@gmail.com) wrote: > On 07/27/2016 12:07 AM, Tom Lane wrote: > > > >> 4. Now, update that small table 500 times per second. > >> That's a recipe for runaway table bloat; VACUUM can't do much because > >> there's always some minutes-old transaction hanging around (and SNAPSH

Re: [HACKERS] Why we lost Uber as a user

On 07/27/2016 12:07 AM, Tom Lane wrote: > >> 4. Now, update that small table 500 times per second. >> That's a recipe for runaway table bloat; VACUUM can't do much because >> there's always some minutes-old transaction hanging around (and SNAPSHOT >> TOO OLD doesn't really help, we're talking about

Re: [HACKERS] Why we lost Uber as a user

* Bruce Momjian (br...@momjian.us) wrote: > On Fri, Jul 29, 2016 at 10:44:29AM -0400, Stephen Frost wrote: > > Another thought that was kicking around in my head related to that is if > > we could have indexes that only provide page-level information (similar > > to BRIN, but maybe a btree) and whi

Re: [HACKERS] Why we lost Uber as a user

On Fri, Jul 29, 2016 at 10:44:29AM -0400, Stephen Frost wrote: > Another thought that was kicking around in my head related to that is if > we could have indexes that only provide page-level information (similar > to BRIN, but maybe a btree) and which also would allow HOT updates. > Those indexes w

Re: [HACKERS] Why we lost Uber as a user

* Jim Nasby (jim.na...@bluetreble.com) wrote: > On 7/28/16 10:05 AM, Alex Ignatov wrote: > >>Just curious: what if PostgreSQL supported index that stores "primary > >>key" (or unique key) instead of tids? > > > >You mean IOT like Oracle have? > > IIRC, IOT either stores the table in index order, w

Re: [HACKERS] Why we lost Uber as a user

On 7/28/16 10:05 AM, Alex Ignatov wrote: Just curious: what if PostgreSQL supported index that stores "primary key" (or unique key) instead of tids? You mean IOT like Oracle have? IIRC, IOT either stores the table in index order, which is something different. What Alex is proposing is an i

Re: [HACKERS] Why we lost Uber as a user

On 07/28/2016 03:58 AM, Geoff Winkless wrote: > On 27 July 2016 at 17:04, Bruce Momjian >wrote: > > Well, their big complaint about binary replication is that a bug can > spread from a master to all slaves, which doesn't happen with statement > level replicati

Re: [HACKERS] Why we lost Uber as a user

On 28.07.2016 17:53, Vladimir Sitnikov wrote: >> That's a recipe for runaway table bloat; VACUUM can't do much because >> there's always some minutes-old transaction hanging around (and SNAPSHOT >> TOO OLD doesn't really help, we're talking about minutes here), and >> b

Re: [HACKERS] Why we lost Uber as a user

> > > >> That's a recipe for runaway table bloat; VACUUM can't do much because > >> there's always some minutes-old transaction hanging around (and SNAPSHOT > >> TOO OLD doesn't really help, we're talking about minutes here), and > >> because of all of the indexes HOT isn't effective. > Just curio

Re: [HACKERS] Why we lost Uber as a user

On Thu, Jul 28, 2016 at 8:16 AM, pgwhatever wrote: > Statement-Based replication has a lot of problems with it like indeterminate > UDFs. Here is a link to see them all: > https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages Sure. It's also incr

Re: [HACKERS] Why we lost Uber as a user

Statement-Based replication has a lot of problems with it like indeterminate UDFs. Here is a link to see them all: https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages -- View this message in context: http://postgresql.nabble.com/Why-we-lost-Ub

Re: [HACKERS] Why we lost Uber as a user

On 28 Jul 2016 12:19, "Vitaly Burovoy" wrote: > > On 7/28/16, Geoff Winkless wrote: > > On 27 July 2016 at 17:04, Bruce Momjian wrote: > > > >> Well, their big complaint about binary replication is that a bug can > >> spread from a master to all slaves, which doesn't happen with statement > >> l

Re: [HACKERS] Why we lost Uber as a user

On 7/28/16, Geoff Winkless wrote: > On 27 July 2016 at 17:04, Bruce Momjian wrote: > >> Well, their big complaint about binary replication is that a bug can >> spread from a master to all slaves, which doesn't happen with statement >> level replication. > > ​ > I'm not sure that that makes sense

Re: [HACKERS] Why we lost Uber as a user

On 27 July 2016 at 17:04, Bruce Momjian wrote: > Well, their big complaint about binary replication is that a bug can > spread from a master to all slaves, which doesn't happen with statement > level replication. ​ ​I'm not sure that that makes sense to me. If there's a database bug that occurs

Re: [HACKERS] Why we lost Uber as a user

On 07/26/2016 08:45 PM, Robert Haas wrote: > That's why I found Josh's restatement useful - I am assuming without > proof that his restatement is accurate FWIW, my restatement was based on some other sites rather than Uber. Including folks who didn't abandon Postgres. -- -- Josh Berkus Red H

Re: [HACKERS] Why we lost Uber as a user

On Wed, Jul 27, 2016 at 08:33:52AM -0500, Merlin Moncure wrote: > > Or in short, this seems like an annoyance, not a time-for-a-new-database > > kind of problem. > > Well, the real annoyance as I understand it is the raw volume of bytes > of WAL traffic a single update of a field can cause. They

Re: [HACKERS] Why we lost Uber as a user

On Tue, Jul 26, 2016 at 5:07 PM, Tom Lane wrote: > Josh Berkus writes: >> To explain this in concrete terms, which the blog post does not: > >> 1. Create a small table, but one with enough rows that indexes make >> sense (say 50,000 rows). > >> 2. Make this table used in JOINs all over your datab

Re: [HACKERS] Why we lost Uber as a user

On 27/07/16 05:45, Robert Haas wrote: > On Tue, Jul 26, 2016 at 8:27 PM, Stephen Frost wrote: >> * Joshua D. Drake (j...@commandprompt.com) wrote: >>> Hello, >>> >>> The following article is a very good look at some of our limitations >>> and highlights some of the pains many of us have been worki

Re: [HACKERS] Why we lost Uber as a user

On Tue, Jul 26, 2016 at 8:27 PM, Stephen Frost wrote: > * Joshua D. Drake (j...@commandprompt.com) wrote: >> Hello, >> >> The following article is a very good look at some of our limitations >> and highlights some of the pains many of us have been working >> "around" since we started using the sof

Re: [HACKERS] Why we lost Uber as a user

* Joshua D. Drake (j...@commandprompt.com) wrote: > Hello, > > The following article is a very good look at some of our limitations > and highlights some of the pains many of us have been working > "around" since we started using the software. > > https://eng.uber.com/mysql-migration/ > > Specif

Re: [HACKERS] Why we lost Uber as a user

On Wed, Jul 27, 2016 at 7:19 AM, Robert Haas wrote: > I've seen multiple cases where this kind of thing causes a > sufficiently large performance regression that the system just can't > keep up. Things are OK when the table is freshly-loaded, but as soon > as somebody runs a query on any table in

Re: [HACKERS] Why we lost Uber as a user

On Tue, Jul 26, 2016 at 6:07 PM, Tom Lane wrote: > Josh Berkus writes: >> To explain this in concrete terms, which the blog post does not: > >> 1. Create a small table, but one with enough rows that indexes make >> sense (say 50,000 rows). > >> 2. Make this table used in JOINs all over your datab

Re: [HACKERS] Why we lost Uber as a user

On 07/26/2016 03:07 PM, Tom Lane wrote: > Josh Berkus writes: >> That's a recipe for runaway table bloat; VACUUM can't do much because >> there's always some minutes-old transaction hanging around (and SNAPSHOT >> TOO OLD doesn't really help, we're talking about minutes here), and >> because of a

Re: [HACKERS] Why we lost Uber as a user

Josh Berkus writes: > To explain this in concrete terms, which the blog post does not: > 1. Create a small table, but one with enough rows that indexes make > sense (say 50,000 rows). > 2. Make this table used in JOINs all over your database. > 3. To support these JOINs, index most of the colum

Re: [HACKERS] Why we lost Uber as a user

On Tue, Jul 26, 2016 at 5:26 PM, Josh Berkus wrote: > On 07/26/2016 01:53 PM, Josh Berkus wrote: >> The write amplification issue, and its correllary in VACUUM, certainly >> continues to plague some users, and doesn't have any easy solutions. > > To explain this in concrete terms, which the blog p

Re: [HACKERS] Why we lost Uber as a user

On Tue, Jul 26, 2016 at 02:26:57PM -0700, Josh Berkus wrote: > On 07/26/2016 01:53 PM, Josh Berkus wrote: > > The write amplification issue, and its correllary in VACUUM, certainly > > continues to plague some users, and doesn't have any easy solutions. > > To explain this in concrete terms, which

Re: [HACKERS] Why we lost Uber as a user

On 07/26/2016 01:53 PM, Josh Berkus wrote: > The write amplification issue, and its correllary in VACUUM, certainly > continues to plague some users, and doesn't have any easy solutions. To explain this in concrete terms, which the blog post does not: 1. Create a small table, but one with enough

Re: [HACKERS] Why we lost Uber as a user

On 07/26/2016 09:54 AM, Joshua D. Drake wrote: > Hello, > > The following article is a very good look at some of our limitations and > highlights some of the pains many of us have been working "around" since > we started using the software. They also had other reasons to switch to MySQL, particul