Re: [gentoo-user] PostgreSQL Vs MySQL @Uber

[james]

On 08/01/2016 11:49 AM, J. Roeleveld wrote:
> On Monday, August 01, 2016 08:43:49 AM james wrote:
>> On 08/01/2016 02:16 AM, J. Roeleveld wrote:
>>> On Saturday, July 30, 2016 06:38:01 AM Rich Freeman wrote:
>>>> On Sat, Jul 30, 2016 at 6:24 AM, Alan McKinnon <alan.mckinnon@×××××.com>
>>>
>>> wrote:
>>>>> On 29/07/2016 22:58, Mick wrote:
>>>>>> Interesting article explaining why Uber are moving away from
>>>>>> PostgreSQL.
>>>>>> I am
>>>>>> running both DBs on different desktop PCs for akonadi and I'm also
>>>>>> running
>>>>>> MySQL on a number of websites.  Let's which one goes sideways first.
>>>>>> :p
>>>>>>
>>>>>>  https://eng.uber.com/mysql-migration/
>>>>>
>>>>> I don't think your akonadi and some web sites compares in any way to
>>>>> Uber
>>>>> and what they do.
>>>>>
>>>>> FWIW, my Dev colleagues support and entire large corporate ISP's
>>>>> operational and customer data on PostgreSQL-9.3. With clustering. With
>>>>> no
>>>>> db-related issues :-)
>>>>
>>>> Agree, you'd need to be fairly large-scale to have their issues,
>>>
>>> And also have to design your database by people who think MySQL actually
>>> follows common SQL standards.
>>>
>>>> but I
>>>> think the article was something anybody interested in databases should
>>>> read.  If nothing else it is a really easy to follow explanation of
>>>> the underlying architectures.
>>>
>>> Check the link posted by Douglas.
>>> Ubers article has some misunderstandings about the architecture with
>>> conclusions drawn that are, at least also, caused by their database design
>>> and usage.
>>>
>>>> I'll probably post this to my LUG mailing list.  I think one of the
>>>> Postgres devs lurks there so I'm curious to his impressions.
>>>>
>>>> I was a bit surprised to hear about the data corruption bug.  I've
>>>> always considered Postgres to have a better reputation for data
>>>> integrity.
>>>
>>> They do.
>>>
>>>> And of course almost any FOSS project could have a bug.  I
>>>> don't know if either project does the kind of regression testing to
>>>> reliably detect this sort of issue.
>>>
>>> Not sure either, I do think PostgreSQL does a lot with regression tests.
>>>
>>>> I'd think that it is more likely
>>>> that the likes of Oracle would (for their flagship DB (not for MySQL),
>>>
>>> Never worked with Oracle (or other big software vendors), have you? :)
>>>
>>>> and they'd probably be more likely to send out an engineer to beg
>>>> forgiveness while they fix your database).
>>>
>>> Only if you're a big (as in, spend a lot of money with them) customer.
>>>
>>>> Of course, if you're Uber
>>>> the hit you'd take from downtime/etc isn't made up for entirely by
>>>> having somebody take a few days to get everything fixed.
>>>
>>> --
>>> Joost
>>
>> I certainly respect your skills and posts on Databases, Joost, as
>> everything you have posted, in the past is 'spot on'.
>
> Comes with a keen interest and long-term (think decades) of working with
> different databases.
>
>> Granted, I'm no database expert, far from it.
>
> Not many people are, nor do they need to be.
>
>> But I want to share a few thing with you,
>> and hope you  (and others) will 'chime in' on these comments.
>>
>> Way back, when the earth was cooling and we all had dinosaurs for pets,
>> some of us hacked on AT&T "3B2" unix systems. They were know for their
>> 'roll back and recovery', triplicated (or more) transaction processes
>> and 'voters' system to ferret out if a transaction was complete and
>> correct. There was no ACID, the current 'gold standard' if you believe
>> what Douglas and other write about concerning databases.
>>
>> In essence, (from crusted up memories) a basic (SS7) transaction related
>> to the local telephone switch, was ran  on 3 machines. The results were
>> compared. If they matched, the transaction went forward as valid. If 2/3
>> matched,
>
> And what in the likely case when only 1 was correct?

1/3 was a failure, in fact X<1 could be defined (parameter setting) as a 
failure depending on the need.

> Have you seen the movie "minority report"?
> If yes, think back to why Tom Cruise was found 'guilty' when he wasn't and how
> often this actually occured.

Apples to Oranges. The (3) "pre-cons" were  not equal, ableit the voted, 
most of the time all three in agreement, but the dominant pre-con was 
always on the correct side of the issue. But that is make-believe. 
Comparing results of codes run on 3 different processors or separate 
machines for agreement withing tolerances, is quite different.  The very 
essence of using voting where there a result less that 1.0 (that is 
n-1/n or n-x/n  was requisite on identical (replicated) processes all 
returning the same result ( expecting either a 0 or 1) returned. Results 
being logical or within rounding error of acceptance. Surely we need not 
split hairs. I was merely pointing out that the basis telecom systems 
formed the early and of widespread transaction processing industries and 
is the grand daddy of the ACID model/norms/constructs of modern 
transaction processing. And Douglas is
dead wrong that those sorts of (ACID) transactions cannot be made to fly 
on clusters versus a single machine. For massively parallel needs,
distributed processing rules, but it is not trivial and hence Uber, with 
mostly a bunch of kids, seems to be struggling and have made bad 
decisions. Prolly, there mid managers and software architects are the 
weak link, or they did get expert guidance that was not inhouse, or poor 
decisions to get some code running quickly etc etc. I do not really care 
about UBER. My singular issue is Douglas was completely dead wrong 
(which nicely promoted himself as a postgress expert and his business 
credentals, and just barely saved his credibility by stating what UBER 
is now doing that is superior to a grade ACID, dB solution.

Another point, there are single big GPUs that can be run as thousands of 
different processors on either FPGA or GPU, granted using SIMD/MIMD 
style processors and thing like 'systolic algorithms' but that sort of 
this is out of scope here. (Vulcan might change that, in an open source
kind of way, maybe). Furthermore, GPU resources combined with DDR-5 can 
blur the line and may actually be more cost effective for many forms of 
transaction processing, but clusters, in their current forms are very 
much general purpose machines. My point:: Douglas is dead wrong about 
ACID being dominated by Databases, for technical reasons, particularly 
for advanced teams of experts. Surely most MBA, HR and Finance types of 
idiots running these new startups would know know a coder from an 
architect, and that is very sad, because a good consultant could have 
probably designed several robust systems in a week or two. Grant few 
consultants has that sort of unbiased integrity, because we all have 
bills to pay and much is getting outsourced... Integrity has always been 
the rarest of qualities, particularly with humanoids......


>
>> and the switch was was configured, then the code would
>> essentially 'vote' and majority ruled. This is what led to phone calls
>> (switched phone calls) having variable delays, often in the order of
>> seconds, mis-connections and other problems we all encountered during
>> periods of excessive demand.
>
> Not sure if that was the cause in the past, but these days it can also still
> take a few seconds before the other end rings. This is due to the phone-system
> (all PBXs in the path) needing to setup the routing between both end-points
> prior to the ring-tone actually starting.
> When the system is busy, these lookups will take time and can even time-out.
> (Try wishing everyone you know a happy new year using a wired phone and you'll
> see what I mean. Mobile phones have a seperate problem at that time)

I did not intend to argue about the minutia of how a particular Baby 
Bell implemented their SS7 switching systems on unix systems. My point 
was the 'transaction processing' grew out the early telephone network, 
the way I remember it:: ymmv. Banks did dual entry accounting by hand 
and had clerks manually load data sets, then double entry accounting 
became automated and ACID style transaction processing added later. So 
what sql folks refer to as ACID properties, comes from the North 
American  switching heritage and eventually the worlds telecom networks, 
eons ago.

>> That scenario was at the heart of how old, crappy AT&T unix (SVR?) could
>> perform so well and therefore established the gold standard for RT
>> transaction processing, aka the "five  9s" 99.999% of up-time (about 5
>> minutes per year of downtime).
>
> "Unscheduled" downtime. Regular maintenance will require more than 5 minutes
> per year.

Yes but the redundancy of 3b2 and other computers (Sequent, Sequoia and 
Tandem to name a few) meant that the "phone switching" fabric, at any 
given Central Office (the local building where the copper, Rf and fiber 
lines are muxed)(was, on average up and available 99.999% of the time.
Ironically gentoo now has a 'sys/fabric group :: 
/usr/portage/sys-fabric, thanks to some forward thinking cluster folk.

>
>> Sure this part is only related to
>> transaction processing as there was much more to the "five 9s" legacy,
>> but imho, that is the heart of what was the precursor to ACID property's
>> now so greatly espoused in SQL codes that Douglas refers to.
>>
>> Do folks concur or disagree at this point?
>
> ACID is about data integrity. The "best 2 out of 3" voting was, in my opinion,
> a work-around for unreliable hardware.

Absolute true. But the fact that a High Reliability in computer 
processing (including the billing) could be replicated performed 
elsewhere and then 'recombined', proves that the need of any ACID 
function can be split up and ran on clusters and achieve ACID standards 
or even better. So my point, is that the cluster, if used wisely,
will beat the 'dog shit' out of any Oracle fancy-pants database 
maneuvers. Evidence:: Snoracle is now snapping up billion dollar 
companies in the cluster space, cause their days of extortion are 
winding down rather rapidly, imho.

Also, just because the kids are writing the codes, have not figured all 
of this out, does not mean that SQL and any abstraction is better that 
parallel processing. No way in hell. Cheaper and quicker to set up, 
surely true, but never superior to a well design properly coded 
distributed solution. That's my point. Hence, Douglas is full of 
stuffing, except he alludes to the fact that UBER is doing something 
much better, beyond what Oracle has an interest in doing, at the last 
possible moment in his critique. This is back up by Oracles lethargic 
reaction to the data processing market just leaving Oracle to become the 
next IBM.... (ymmv).

> It is based on a clever idea, but when
> 2 computers having the same data and logic come up with 2 different answers, I
> wouldn't trust either of them.

Yep, That the QA of  Transactions is rejected and must be resubmitted, 
modified or any number of remedies, is quite common in many forms of 
software. Voting does not correct errors, except maybe a fractional 
rounding up to 1(pass) or down to zero (failure). It does help to 
achieve the ACI of ACID

Since billions and billions of these (complex) transactions are 
occurring, it is usually just repeated. If it keeps failing then 
engineers/coders take a deeper look. Rare statistical anomalies are 
auto-scrutinized (that would be replications and voting) and the pushed 
to a logical zero or logical one.

>
>> The reason this is important to me (and others?), is that, if this idea
>> (granted there is much more detail to it) is still valid, then it can
>> form  the basis for building up superior-ACID processes, that meet or
>> exceed, the properties of an expensive (think Oracle) transaction
>> process on distributed (parallel) or clustered systems, to a degree of
>> accuracy only limited by the limit of the number of odd numbered voter
>> codes involve in the distributed and replicated parts of the
>> transaction. I even added some code where replicated routines were
>> written in different languages, and the results compared to add an
>> additional layer of verification before the voter step. (gotta love
>> assembler?).
>
> You have seen how "democracies" work, right? :)

Yes I need to shed some light on  telecom processing. I never intend to 
suggest that voting corrected errors; althoght error correction codes 
are usually part of the overall stack. I  tried to suggest that all 
transactions on phone switches are already (Atomic (pass or fail-redo;
Consistent (replications pass on different hardware pathways to 
satisfaction metrics; Isolated via multiple hardware pathways; Durable
passing a voter check scheme and (five nines still is the gold standard 
for a system (even mil-spec).

So the old telecom systems are indeed and infact  the heritage for 
modern ACID transactions.



> The more voters involved, the longer it takes for all the votes to be counted.

Wrong! Voters are all run in parallel. For this level of redundancy (to 
achieve a QA result of 99.999% system pristine, it is more expensive, 
analogous to encryption versus clear text. Nobody, but a business major 
would use an excessive number of voters in their switching fabric. 
Telecom incompetences, in my experiences, has been the domain of mid 
manager too weak to educate upper management on poor ideas many of them 
have had and continue to have (Verizon comes to mind, too often).

> With a small number, it might actually still scale, but when you pass a magic
> number (no clue what this would be), the counting time starts to exceed any
> time you might have gained by adding more voters.

Nope the larger the number, the more expensive. The number of voters 
rarely goes above 5, but it could for some sorts of physics problems 
(think quantum mechanics and logic not bound to [0 1] whole numbers.
Often logic circuits (constructs for programmers, have "dont care" 
states that can be handled in a variety of ways (filters, transforms, 
counters etc etc).

> Also, this, to me, seems to counteract the whole reason for using clusters:
> Have different nodes handle a different part of the problem.

That also occurs. But my point is properly design code for the cluster 
can replace ACID functions, offered by Oracle and other over priced 
solutions, on standard cluster hardware. The problem with todays 
clusters is the vendors that employ the kid-coders, are making things 
far more complicated that necessary, so the average linux hacker just 
outsources via the cloud. DUMB, insecure and not a wise choice for many 
industries. And sooner or later folks are going to get wise can build 
their own clusters that just solve the problems they have. Surely hybrid 
clusters will domiant where the owner of the codes does outsource peak 
loads and mundance collects of ordinary (non-critical) data. Vendors 
know this and have started another 'smoke and mirrors' campaign called 
(brace yourself) 'Unikernels'.....  Problem with that approach is they 
should just be using minized (focused) gentoo on striped and optimize 
linux kernels; but that is another lost art from the linux collection

>
> Clusters of multiple compute-nodes is a quick and "simple" way of increasing
> the amount of computational cores to throw at problems that can be broken down
> in a lot of individual steps with minimal inter-dependencies.

And surpass the ACID features of either postgresql or Oracle, and spend 
less money (maybe not with you and postgresql on their team)!


> I say "simple" because I think designing a 1,000 core chip is more difficult
> than building a 1,000-node cluster using single-core, single cpu boxes.

Today, you are correct. Tomorrow you will be wrong. [1]. Besides once 
that chip or VHDL code or whatever is designed, it can be replicated and 
resused endlessly. Think ASIC designers, folks to take a fpga project to 
completing, An EE can codes on large arrays of DSPs, or a GPU
(think Khronos group) using Vulcan.


>
> I would still consider the cluster to be a single "machine".

Thats the goal.

>
>> I guess my point is 'Douglas' is full of stuffing, OR that is what folks
>> are doing when they 'role their own solution specifically customized to
>> their specific needs' as he alludes to near the end of his commentary?
>
> The response Douglas linked to is closer to what seems to work when dealing
> with large amounts of data.
>
>> (I'd like your opinion of this and maybe some links to current schemes
>> how to have ACID/99.999% accurate transactions on clusters of various
>> architectures.)  Douglas, like yourself, writes of these things in a
>> very lucid fashion, so that is why I'm asking you for your thoughts.
>
> The way Uber created the cluster is useful when having 1 node handle all the
> updates and multiple nodes providing read-only access while also providing
> failover functionality.

SIMD solution, mimic on a cluster? Cool.
>
>> Robustness of transactions, in a distributed (clustered) environment is
>> fundamental to the usefulness of most codes that are trying to migrate
>> to a cluster based processes in (VM/container/HPC) environments.
>
> Whereas I do consider clusters to be very useful, not all work-loads can be
> redesigned to scale properly.

Today, correct. Tomorrow, I think you are going to be wrong. It's like 
the single core, multicore. Granted many old decreped codes had to be 
redesigned and coded anew with threads and other modern constructs to 
take advantage of newer processing platforms. Sure the same is true with 
distributed, but it's far closer than ever. The largest problem with 
cluster, is Vendors with agendas, are making things more complicated 
than necessary and completely ignoring many fundamental issues, like 
kernel stripping and optimizations under the bloated OS they are using.

>
>> I do
>> not have the old articles handy but, I'm sure that many/most of those
>> types of inherent processes can be formulated in the algebraic domain,
>> normalized and used to solve decisions often where other forms of
>> advanced logic failed (not that I'm taking a cheap shot at modern
>> programming languages) (wink wink nudge nudge); or at least that's how
>> we did it.... as young whipper_snappers bask in the day...
>
> If you know what you are doing, the language is just a tool. Sometimes a
> hammer is sufficient, other times one might need to use a screwdriver.
>
>> --an_old_farts_logic
>
> Thinking back on how long I've been playing with computers, I wonder how long
> it will be until I am in the "old fart" category?

Stay young! I run full court hoops all the time with young college 
punks; it's one of my greatest joys in life, run with the young 
stallions, hacking, pushing, shoving, slicing and taunting other 
athletes. Old farts clubs is not something to be proud of, I just like 
to share too much......

> Joost

Thanks !

James