Обсуждение: Oracle vs PG

Well it is Aurora.

https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html

On 10/23/18 12:58 PM, Ravi Krishna wrote:
> Well it is Aurora.
> 
> https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
> 

Since the article was almost content-free I not would use it on either 
side of the argument. The only thing I pulled from it was Amazon changed 
databases and hit the learning curve. That will happen in either direction.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/23/18 1:47 PM, Ravi Krishna wrote:
>>
>> Since the article was almost content-free I not would use it on either 
>> side of the argument. The only thing I pulled from it was Amazon 
>> changed databases and hit the learning curve. That will happen in 
>> either direction.
> 
> I agree but this is the key:
> 
> "Savepoints are an important database tool for tracking and recovering 
> individual transactions. On Prime Day, an excessive number of savepoints 
> was created, and Amazon's Aurora software wasn't able to handle the 
> pressure, slowing down the overall database performance, the report said."
> 
> 

Again, pretty much content-free. For all you know some application was 
creating savepoints, needlessly:

https://www.postgresql.org/docs/10/static/sql-savepoint.html

and not cleaning up after itself.

The content is here:

"Following the Prime Day outage, Amazon engineers filled out a 25-page 
report, which Amazon calls a correction of error. It's a standard 
process that Amazon uses to try to understand why a major incident took 
place and how to keep it from happening in the future."

Not sure if that is publicly available or not, though my hunch is no.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

> On 10/23/18 12:58 PM, Ravi Krishna wrote:
>
>> Well it is Aurora.
>>
>> https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
>>
>
> Since the article was almost content-free I not would use it on either
> side of the argument. The only thing I pulled from it was Amazon
> changed databases and hit the learning curve. That will happen in
> either direction.

Yeah and kudos to them for taking a chance.

I assume what revenue they lost during the incident will be made back
thousands of times over when/if they can avoid paying what it likely an
absurd cost to licence the $big-commercial-db.

FWIW

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

On 10/23/18 12:58 PM, Ravi Krishna wrote:
> Well it is Aurora.
>
> https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
>

Since the article was almost content-free I not would use it on either
side of the argument. The only thing I pulled from it was Amazon changed
databases and hit the learning curve. That will happen in either direction.

On 10/23/18 2:34 PM, Andreas Joseph Krogh wrote:
> På tirsdag 23. oktober 2018 kl. 22:45:36, skrev Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:
> 
>     On 10/23/18 12:58 PM, Ravi Krishna wrote:
>      > Well it is Aurora.
>      >
>      >
>     https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
>      >
> 
>     Since the article was almost content-free I not would use it on either
>     side of the argument. The only thing I pulled from it was Amazon changed
>     databases and hit the learning curve. That will happen in either
>     direction.
> 
> Is it so hard to accept commercial databases have advantages?

That is entirely possible. My point is that the article does not contain 
enough information to make that determination. As with many media 
articles it was written to support the headline, not to actually shed 
light on the issue.


> I find that not one bit surprising.
> I've used PG since 90's and it's no secret the "big guys" beat PG on 
> certain workloads.
> -- 
> Andreas Joseph Krogh
> ​


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/23/18 2:34 PM, Andreas Joseph Krogh wrote:
> På tirsdag 23. oktober 2018 kl. 22:45:36, skrev Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:
>
>     On 10/23/18 12:58 PM, Ravi Krishna wrote:
>      > Well it is Aurora.
>      >
>      >
>     https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
>      >
>
>     Since the article was almost content-free I not would use it on either
>     side of the argument. The only thing I pulled from it was Amazon changed
>     databases and hit the learning curve. That will happen in either
>     direction.
>
> Is it so hard to accept commercial databases have advantages?

That is entirely possible. My point is that the article does not contain
enough information to make that determination. As with many media
articles it was written to support the headline, not to actually shed
light on the issue.

>
> Is it so hard to accept commercial databases have advantages?
> I find that not one bit surprising.
>
> I've used PG since 90's and it's no secret the "big guys" beat PG on certain workloads.
>

In my previous workplace where they tested EDB to replace PG, they found all PL/SQL based codes were running 2x to 3x
slowerthan Oracle. 

e to handle the pressure, slowing down the overall database performance, the report said."
>
> Again, pretty much content-free. For all you know some application was creating savepoints, needlessly:
>
> https://www.postgresql.org/docs/10/static/sql-savepoint.html
>
> and not cleaning up after itself.
>
> The content is here:
>
> "Following the Prime Day outage, Amazon engineers filled out a 25-page report, which Amazon calls a correction of
error.It's a standard process that Amazon uses to try to understand why a major incident took place and how to keep it
fromhappening in the future." 
>
> Not sure if that is publicly available or not, though my hunch is no.

I think PG gurus in this list can expand on the clue as to what could have gone wrong with savepoints in PG under heavy
load.

> Again, pretty much content-free. For all you know some application was 
> creating savepoints, needlessly:

> https://www.postgresql.org/docs/10/static/sql-savepoint.html

I have hardly used savepoints in any application, but if I understand it correctly, isn't it something which is
typicallyused
 
in a persistent connection.  I wonder how it is applicable in a web based stateless application like Amazon.com,
unless
even web based application have database level state.

>I have hardly used savepoints in any application, but if I understand
>it correctly, isn't it something which is typically used
>in a persistent connection.  I wonder how it is applicable in a web
>based stateless application like Amazon.com, unless
>even web based application have database level state.

Doesn't need to be a long running connection, you can trap exceptions, roll back, and do something else all in the SQL
codeor a function from a single call. 


--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

>
> Amazon's web store may be a (mostly) stateless application, that doesn't mean their back end applications are.
>

Oh yes.  There is nothing in that article which suggests that the root cause of the outage was in the web based apps.
As you indicated, their back end may be the source of the issue and web store happens to be the victim.

thanks.

Ravi Krishna <srkrishna1@aol.com> writes:

>> Again, pretty much content-free. For all you know some application was 
>> creating savepoints, needlessly:
>
>> https://www.postgresql.org/docs/10/static/sql-savepoint.html
>
> I have hardly used savepoints in any application, but if I understand it correctly, isn't it something which is
typicallyused
 
> in a persistent connection.  I wonder how it is applicable in a web based stateless application like Amazon.com,
unless
> even web based application have database level state.

No, savepoints and persistent connections are not necessarily related.

Savepoints are really just a way of managing rollback segments. For
example, if you were doing a large number of inserts/updates, things can
become slow if the rollback segment grows really large. One way around
this is to set savepoints, which will allow you to commit more
frequently and prevent the rollback size from growing too large (there
are other benefits as well, such as allowing other transactions to see
partial changes sooner rather than not seeing any change until after a
long running insert/update has completed etc).

I think that article is really just about headline click bait and lacks
any real details. I'm not even convinced that comparison of Oracle and
PG really makes sense anyway - both databases have their pros and cons.

IMO Oracle is a very good database (though most of the 'add ons' are
less beneficial). However, it is extremely expensive, both to license
and to administer. For certain applications, it would be my first choice
(assuming available budget). However, I prefer PG for the majority of
what I need, partially due to the cost, but mainly because it is rock
solid and much, much easier to administer and sufficient for what I
need. As usual, it is more about requirements than brand and choosing
the right tool for the right job.

Tim

-- 
Tim Cross

Ravi Krishna wrote:
> I have hardly used savepoints in any application, but if I understand it correctly, isn't it something which is
typicallyused
 
> in a persistent connection.  I wonder how it is applicable in a web based stateless application like Amazon.com,
unless
> even web based application have database level state.

I have seen people use savepoints in PostgreSQL to emulate Oracle's
"statement rollback" behavior: If a statement fails, only the statement
is undone, but the transaction continues.

If you insert a savepoint before *every* statement in a transaction,
you can get a similar behavior in PostgreSQL, but the performance will
suck.

Perhaps that is what happened in this case.

Of course the correct solution is to redesign and use savepoints only
where you *expect* an error, or at least batch a number of statements
with each savepoint.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Wed, Oct 24, 2018 at 07:31:57AM +0200, Laurenz Albe wrote:
> I have seen people use savepoints in PostgreSQL to emulate Oracle's
> "statement rollback" behavior: If a statement fails, only the statement
> is undone, but the transaction continues.
>
> If you insert a savepoint before *every* statement in a transaction,
> you can get a similar behavior in PostgreSQL, but the performance will
> suck.

The Postgres ODBC driver actually does that, and I have seen
applications actually ready to pay the cost of extra round trips to the
server to be able to get this property, even if that costs performance.
You can issue a query through the driver and rollback at will this way
to the previous state of the transaction.
--
Michael

On 10/23/18 13:45, Adrian Klaver wrote:
> On 10/23/18 12:58 PM, Ravi Krishna wrote:
>> Well it is Aurora.
>>
>> https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
> 
> Since the article was almost content-free I not would use it on either
> side of the argument. The only thing I pulled from it was Amazon changed
> databases and hit the learning curve. That will happen in either direction.

Werner tweeted about this earlier today too, if you didn't see it

https://twitter.com/Werner/status/1054901529478459392

-Jeremy

-- 
Jeremy Schneider
Database Engineer
Amazon Web Services

Michael Paquier <michael@paquier.xyz> writes:

> On Wed, Oct 24, 2018 at 07:31:57AM +0200, Laurenz Albe wrote:
>
>> I have seen people use savepoints in PostgreSQL to emulate Oracle's
>> "statement rollback" behavior: If a statement fails, only the statement
>> is undone, but the transaction continues.
>> 
>> If you insert a savepoint before *every* statement in a transaction,
>> you can get a similar behavior in PostgreSQL, but the performance will
>> suck.
>
> The Postgres ODBC driver actually does that, and I have seen
> applications actually ready to pay the cost of extra round trips to the
> server to be able to get this property, even if that costs performance.
> You can issue a query through the driver and rollback at will this way
> to the previous state of the transaction.

Yep and it's fun watching the txid counter go up so fast too!

> --
> Michael
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800