"Strong sides of MySQL" talk from PgDay16Russia, translated

Following Uber's case discussion, I found this talk by Alexey Kopytov to be
really interesting:
http://kaamos.me/talks/pgday16/strongmysql/strongmysql.html (online html,
in Russian)

I translated it to English:

https://www.dropbox.com/s/t6a15s66jxg50tg/mysqlstrong_pgday16russia.pdf?dl=0
(pdf)

The slides deck contains a lot of details. The author claims that during
recent years, MySQL made a lot of progress in defending and advancing its
position as a "most popular database for the web", he provides detailed
reasoning for that, and then concludes that PostgreSQL will need years and
maybe even decades to close gaps in the certain fields which are very
sensitive for large companies:
- replication
- storage engines / compression / direct IO / etc
- partitioning,
etc.

Of course this information is biased (Alexey works at Percona) but IMO it's
much more detailed, qualitative and useful analysis compared to the Uber's
recent article.

Great translation.

BTW, is there any opposite information, i.e. showing the limitation of
MySQL comparing with PostgreSQL? I'm not familiar with MySQL, but
occasionally hearing surprising (as a PostgreSQL user) limitation of
MySQL and wondering if there's any summary of the info.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jul 29, 2016 at 4:39 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:

Great translation.

BTW, is there any opposite information, i.e. showing the limitation of
MySQL comparing with PostgreSQL? I'm not familiar with MySQL, but
occasionally hearing surprising (as a PostgreSQL user) limitation of
MySQL and wondering if there's any summary of the info.

Sorry cannot help with that :-) I stopped using MySQL in 2005, when
discovered that we speak different languages (I learned standard ISO/ANSI
SQL in university, and then easily communicated with Oracle, SQL Server,
but failed to do so with MySQL; that's why I switched to Postgres).

During last years, all the focus of "let's compare Postres to ..." activity
was switched from MySQL to MongoDB and Oracle.
Maybe it's time to refresh the data -- for those who works with both
Postgres and MySQL.

On Thu, Jul 28, 2016 at 8:39 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:

BTW, is there any opposite information, i.e. showing the
limitation of MySQL comparing with PostgreSQL?

I'm not aware of a general list on the topic, but in reviewing
academic papers regarding transaction isolation I did find (and
confirm) that MySQL InnoDB relaxes the "strict" aspect of the
Strict 2 Phase Locking they use for implementing serializable
transactions. "For performance reasons" they drop the locks
acquired during the transaction *before* ensuring crash/recovery
persistence. This is more-or-less equivalent to always running
with synchronous_commit = off as well as allowing a small window
for serialization anomalies in corner cases. The PostgreSQL
synchronous_commit option allows a similar performance benefit
(where the trade-off is deemed justified) without risking data
integrity in the same way.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/29/16 8:17 AM, Kevin Grittner wrote:

On Thu, Jul 28, 2016 at 8:39 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:

BTW, is there any opposite information, i.e. showing the
limitation of MySQL comparing with PostgreSQL?

I'm not aware of a general list on the topic, but in reviewing
academic papers regarding transaction isolation I did find (and
confirm) that MySQL InnoDB relaxes the "strict" aspect of the
Strict 2 Phase Locking they use for implementing serializable
transactions. "For performance reasons" they drop the locks

...

The way I sum up MySQL vs PG for people that ask is to recount how they
"fixed" the Feb. 31st bug when they released strict mode (something that
they actually called out in the release PR). With strict mode enabled,
Feb. 30th and 31st would give you an error. Feb 35th was still silently
converted to March whatever. *That was the MySQL mentality: data quality
doesn't matter compared to "ease of use".*

They've done this throughout their history... when presented with a hard
problem, they skip around it or plaster over it, and then they promote
that their solution is the only right way to solve the problem. (Their
docs actually used to say that anything other that table-level locking
was a bad idea.)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jul 29, 2016 at 5:28 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

The way I sum up MySQL vs PG for people that ask is to recount how they
"fixed" the Feb. 31st bug when they released strict mode (something that
they actually called out in the release PR). With strict mode enabled, Feb.
30th and 31st would give you an error. Feb 35th was still silently
converted to March whatever. *That was the MySQL mentality: data quality
doesn't matter compared to "ease of use".*

They've done this throughout their history... when presented with a hard
problem, they skip around it or plaster over it, and then they promote that
their solution is the only right way to solve the problem. (Their docs
actually used to say that anything other that table-level locking was a bad
idea.)

This is exactly what I mean saying MySQL speaks different language than I
know, and that's why I simply cannot use it:

(mysql 5.7.12)

mysql> select cast('2016-99-99' as date);
+----------------------------+
| cast('2016-99-99' as date) |
+----------------------------+
| NULL |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

In Postgres:

test=# select cast('2016-99-99' as date);
ERROR: date/time field value out of range: "2016-99-99"
LINE 1: select cast('2016-99-99' as date);
^

On Fri, Jul 29, 2016 at 09:20:12PM +0300, Nikolay Samokhvalov wrote:

On Fri, Jul 29, 2016 at 5:28 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

The way I sum up MySQL vs PG for people that ask is to recount how they
"fixed" the Feb. 31st bug when they released strict mode (something that
they actually called out in the release PR). With strict mode enabled, Feb.
30th and 31st would give you an error. Feb 35th was still silently
converted to March whatever. *That was the MySQL mentality: data quality
doesn't matter compared to "ease of use".*

They've done this throughout their history... when presented with a hard
problem, they skip around it or plaster over it, and then they promote that
their solution is the only right way to solve the problem. (Their docs
actually used to say that anything other that table-level locking was a bad
idea.)

This is exactly what I mean saying MySQL speaks different language than I
know, and that's why I simply cannot use it:

(mysql 5.7.12)

mysql> select cast('2016-99-99' as date);
+----------------------------+
| cast('2016-99-99' as date) |
+----------------------------+
| NULL |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

In Postgres:

test=# select cast('2016-99-99' as date);
ERROR: date/time field value out of range: "2016-99-99"
LINE 1: select cast('2016-99-99' as date);
^

I expect this kind of blather from MySQL, but you've brought up
something that's been bothering me for awhile. PostgreSQL's response
should look more like this:

ERROR: month field value out of range: "2016-99-99"
LINE 1: select cast('2016-99-99' as date);
^
Any idea how much effort that would be?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/29/16 3:13 PM, David Fetter wrote:

I expect this kind of blather from MySQL, but you've brought up
something that's been bothering me for awhile. PostgreSQL's response
should look more like this:

ERROR: month field value out of range: "2016-99-99"
LINE 1: select cast('2016-99-99' as date);
^
Any idea how much effort that would be?

This particular case is probably not hard, but the problem is that that
would raise the bar about error pointer precision, and you then should
also update a bunch of other places to give similar precision. That
could be a lot of work.

I am, however, of the opinion, that these kinds of things can never be
helpful enough. The latest trend is start and end pointers, which would
be nice.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers