Обсуждение: "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:
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.
> 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
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
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
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
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