5 ms·

What's MySQL like as a database these days? I get the impression that it used to be a bit of a mess but it's improved a lot and has some advantages over Postgre

What's MySQL like as a database these days? I get the impression that it used to be a bit of a mess but it's improved a lot and has some advantages over Postgres. Did anyone choose it over pg, and if so, why?

epberry 5y ago
I think to think of it as an interface, https://dbdb.io/browse?compatible=mysql https://dbdb.io/browse?compatible=mysql These days you have shiny companies like PlanetScale and SingleStore implementing the MySQL wire protocol.
skanga 5y ago
In terms of popularity, it sure looks like that ... https://db-engines.com/en/ranking_trend https://db-engines.com/en/ranking_trend
noaheverett 5y ago
I haven’t used PG in production so I’m probably biased, naive or both, so I know this isn’t a direct response to your question, but… MySQL has been great for every project I’ve used it in and haven’t had any issues. Largest app we used it in was Twitpic which handled high query traffic and along with the built-in replication, it was easy to use. We had replication lag on spinning disks (this was 2009) but switching to SSDs fixed it. Backups with xtrabackup from Percona is a key piece though for taking incremental or full DB backups. I think back-in-the-day, PG didn’t have replication built-in and that always kept me using MySQL, but I’ve honestly never understood the bad rap MySQL got in years previous as I’ve never run into any of the described issues and it has been rock solid for me in apps of all sizes.
jorams 5y ago
It's mostly fine and certainly has some advantages, but it also continues to have weird behavior that can randomly creep up. My recent favorite is[1]: > MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. Because of course it's very reasonable to accept a foreign key specification but completely ignore it. [1]: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-ke...
jeffbee 5y ago
It's terrifyingly bad and its global buffer pool mutex that's designed to look smart on benchmarks falls to pieces if you have a few dozen queries running at the same time. The only places I've had to suffer through it were ones that made the fatal decision early in the life of the company when they didn't have anyone on staff qualified to make that decision, but then were stuck with it forever.
- std_reply 5y ago
  I think you need to update your knowledge about MySQL and not bang on about v5.1 and earlier.
  - deleted 5y ago
    [deleted]
- zmxz 5y ago
  This comment looks like an attempt at implying some kind of deep knowledge, but you're demonstrating a terrible ability to convey information. "Global buffer pool mutex that's designed to look smart" does not fall apart if you have a few dozen queries running at the same time. Source: the place I work at has a multiple of several thousand of what you wrote, and surprisingly - nothing falls apart, and we've been using MySQL for decades. Like any software, it has its negatives and positives. Our job is to be aware of shortcomings and work around it. This rule applies to any software, any language. I'm sorry that using MySQL has made you so mad, but deliberate spreading false knowledge simply because you're angry helps no one.
kijin 5y ago
We gave the geospatial features a try with a real estate dataset that contains ~50M rows. It's crappy to say the least. Half-baked features, poor performance, random crashes, barely documented gotchas with no good reason. At least the MariaDB folks try to fix the problems that are passed down to them. MySQL just seems content to tick the box, "Yeah we have that feature too, please don't switch to PostgreSQL!" and leave it at that. MySQL is okay for typical CRUD workloads with old-fashioned scalar types, but don't expect much with respect to the newer, fancier features.
- datalopers 5y ago
  MySQL’s spatial indexes work just fine and yield extremely low-latency queries when used correctly.
  - kijin 5y ago
    Well, the last time we tried to insert a bunch of complex polygons (hundreds of points, multiple parts, etc.) into an indexed column using several concurrent transactions, MySQL crashed.
- evanelias 5y ago
  The list of companies using MySQL as their primary database is absolutely massive. It's a sizeable chunk of the world's economy and it's not just used for CRUD workloads. InnoDB is admittedly much better at OLTP than OLAP, but OLTP encompasses a lot more than simple CRUD workloads. > At least the MariaDB folks try to fix the problems that are passed down to them This assertion simply does not make sense. MariaDB hard-forked its codebase from MySQL's a full decade ago. They were able to use/port some open source MySQL code through ~5.7 but that seems to have stopped in recent years with MySQL 8, probably due to its major architectural changes. These days MySQL and MariaDB each have some nice features that the other one lacks. With respect to what is "passed down", by whom and to whom: the creator of MySQL is literally the chief architect at MariaDB, and many other early MySQL AB engineers work at MariaDB. Meanwhile most of MySQL's poor reputation today is a lingering effect of its early versions, largely before the Oracle acquisition of Sun. In this context, I am continually confused as to why some Postgres users have a higher opinion of MariaDB than MySQL. Edit to add: I failed to mention that I do absolutely concede that for a new application with heavy geospatial component, Postgres+PostGIS is absolutely the correct choice. InnoDB's geospatial support is comparatively much newer and less widely used.
willcipriano 5y ago
I've used both professionally. I only have a mild preference for PG, MySQL has been perfectly serviceable for me.
jamesfinlayson 5y ago
I'm using MySQL 5.6 and 5.7 and the error messages for syntax errors in queries are next to useless.
oofabz 5y ago
I use MySQL (and increasingly, MariaDB) because I prefer the tooling. I use software like PHPMyAdmin and mycli, and when I need to use Postgres or MSSQL the tools available do not live up to the same high standards.
- ultrarunner 5y ago
  What does PHPMyAdmin offer over, say, Postico? I avoid MySQL because of its history of being "a bit of a mess" and its lack of tooling.
  - ikiris 5y ago
    I’ve heard of it for a starter… Tryin to find Postgres tooling like that hasn’t been very productive. Thanks for the tip just for this one.
linsomniac 5y ago
I'm a long-time (~1995) Postgres fan here, and my opinion is that MySQL today doesn't have all the weird restrictions and limitations that it used to have in the past. I don't get super deep into advanced features, but when I've used it lately it has had all the features I wanted. Though I was looking for a JSON data type earlier today and that didn't seem to be in there. I did pick MySQL over Postgres for a old project that I'm currently working on again, the primary reason was easy multi-master replication via galera. I know that Postgres has long had many cluster stories, but I always struggled to even figure out which one was the right choice to make. Most of the clusters I've deployed ended up using DRBD and heartbeat rather than one of the Postgres native options. As far as advance features, quite a few years ago I was doing this exercise to generate reports on some sample payroll dataset. The expectation was clearly that you would run a query, massage that data in some code, then run a bunch of other queries in that code to further correlate the data. I forget the exact problem that was posed. I'm not super strong in database advanced features, but in the end I was able to come up with some fairly simple SQL, using materialized views or CTEs or similar, that produced the report entirely using SQL, in a fairly short runtime. This was my first real experience with using MySQL beyond the basic features it had in decades past, and I was quite impressed with it.
- mmerlin 5y ago
  CitusDB shards PostgreSQL nicely for both data ingestion bandwidth and load balancing + HA
- DeathArrow 5y ago
  >I did pick MySQL over Postgres for a old project that I'm currently working on again, the primary reason was easy multi-master replication via galera. I plan to use CockroachDB as a distributed data store since it's protocol compatible with Postgres. Still in testing phase, but the deployment is very easy.
  - linsomniac 5y ago
    Agreed, the deployment was very easy, that was one of the reasons I wanted to switch to Cockroach from MySQL+Galera. It took me less than a day to get ansible roles to set up a Cockroach cluster, but around a week for the MySQL Galera cluster, to get it completely right. And that was starting with somebody else's Maria+Galera Ansible role. My blocker was that I'm using it in part for Postfix maps, and the Postfix pgsql module can't be configured to us anything but LATIN-1 encoding, and Cockroach only supports UTF-8.
kmos17 5y ago
MySQL been rock solid for us, handling a million+ queries per hour on a fairly large database, running continually for over 10 years (other than occasional updates), but we're not using any advanced features, just run of the mill relational db with some full text searching. The tooling (phpmyadmin, percona xtrabackup) and knowledge available online are also a big plus.

epberry 5y ago

I think to think of it as an interface, https://dbdb.io/browse?compatible=mysql https://dbdb.io/browse?compatible=mysql These days you have shiny companies like PlanetScale and SingleStore implementing the MySQL wire protocol.

skanga 5y ago

In terms of popularity, it sure looks like that ... https://db-engines.com/en/ranking_trend https://db-engines.com/en/ranking_trend

noaheverett 5y ago

I haven’t used PG in production so I’m probably biased, naive or both, so I know this isn’t a direct response to your question, but… MySQL has been great for every project I’ve used it in and haven’t had any issues. Largest app we used it in was Twitpic which handled high query traffic and along with the built-in replication, it was easy to use. We had replication lag on spinning disks (this was 2009) but switching to SSDs fixed it. Backups with xtrabackup from Percona is a key piece though for taking incremental or full DB backups. I think back-in-the-day, PG didn’t have replication built-in and that always kept me using MySQL, but I’ve honestly never understood the bad rap MySQL got in years previous as I’ve never run into any of the described issues and it has been rock solid for me in apps of all sizes.

jorams 5y ago

It's mostly fine and certainly has some advantages, but it also continues to have weird behavior that can randomly creep up. My recent favorite is[1]: > MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. Because of course it's very reasonable to accept a foreign key specification but completely ignore it. [1]: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-ke...

jeffbee 5y ago

It's terrifyingly bad and its global buffer pool mutex that's designed to look smart on benchmarks falls to pieces if you have a few dozen queries running at the same time. The only places I've had to suffer through it were ones that made the fatal decision early in the life of the company when they didn't have anyone on staff qualified to make that decision, but then were stuck with it forever.

std_reply 5y ago
I think you need to update your knowledge about MySQL and not bang on about v5.1 and earlier.
- deleted 5y ago
  [deleted]
zmxz 5y ago
This comment looks like an attempt at implying some kind of deep knowledge, but you're demonstrating a terrible ability to convey information. "Global buffer pool mutex that's designed to look smart" does not fall apart if you have a few dozen queries running at the same time. Source: the place I work at has a multiple of several thousand of what you wrote, and surprisingly - nothing falls apart, and we've been using MySQL for decades. Like any software, it has its negatives and positives. Our job is to be aware of shortcomings and work around it. This rule applies to any software, any language. I'm sorry that using MySQL has made you so mad, but deliberate spreading false knowledge simply because you're angry helps no one.

kijin 5y ago

We gave the geospatial features a try with a real estate dataset that contains ~50M rows. It's crappy to say the least. Half-baked features, poor performance, random crashes, barely documented gotchas with no good reason. At least the MariaDB folks try to fix the problems that are passed down to them. MySQL just seems content to tick the box, "Yeah we have that feature too, please don't switch to PostgreSQL!" and leave it at that. MySQL is okay for typical CRUD workloads with old-fashioned scalar types, but don't expect much with respect to the newer, fancier features.

datalopers 5y ago
MySQL’s spatial indexes work just fine and yield extremely low-latency queries when used correctly.
- kijin 5y ago
  Well, the last time we tried to insert a bunch of complex polygons (hundreds of points, multiple parts, etc.) into an indexed column using several concurrent transactions, MySQL crashed.
evanelias 5y ago
The list of companies using MySQL as their primary database is absolutely massive. It's a sizeable chunk of the world's economy and it's not just used for CRUD workloads. InnoDB is admittedly much better at OLTP than OLAP, but OLTP encompasses a lot more than simple CRUD workloads. > At least the MariaDB folks try to fix the problems that are passed down to them This assertion simply does not make sense. MariaDB hard-forked its codebase from MySQL's a full decade ago. They were able to use/port some open source MySQL code through ~5.7 but that seems to have stopped in recent years with MySQL 8, probably due to its major architectural changes. These days MySQL and MariaDB each have some nice features that the other one lacks. With respect to what is "passed down", by whom and to whom: the creator of MySQL is literally the chief architect at MariaDB, and many other early MySQL AB engineers work at MariaDB. Meanwhile most of MySQL's poor reputation today is a lingering effect of its early versions, largely before the Oracle acquisition of Sun. In this context, I am continually confused as to why some Postgres users have a higher opinion of MariaDB than MySQL. Edit to add: I failed to mention that I do absolutely concede that for a new application with heavy geospatial component, Postgres+PostGIS is absolutely the correct choice. InnoDB's geospatial support is comparatively much newer and less widely used.

willcipriano 5y ago

I've used both professionally. I only have a mild preference for PG, MySQL has been perfectly serviceable for me.

jamesfinlayson 5y ago

I'm using MySQL 5.6 and 5.7 and the error messages for syntax errors in queries are next to useless.

oofabz 5y ago

I use MySQL (and increasingly, MariaDB) because I prefer the tooling. I use software like PHPMyAdmin and mycli, and when I need to use Postgres or MSSQL the tools available do not live up to the same high standards.

ultrarunner 5y ago
What does PHPMyAdmin offer over, say, Postico? I avoid MySQL because of its history of being "a bit of a mess" and its lack of tooling.
- ikiris 5y ago
  I’ve heard of it for a starter… Tryin to find Postgres tooling like that hasn’t been very productive. Thanks for the tip just for this one.

linsomniac 5y ago

I'm a long-time (~1995) Postgres fan here, and my opinion is that MySQL today doesn't have all the weird restrictions and limitations that it used to have in the past. I don't get super deep into advanced features, but when I've used it lately it has had all the features I wanted. Though I was looking for a JSON data type earlier today and that didn't seem to be in there. I did pick MySQL over Postgres for a old project that I'm currently working on again, the primary reason was easy multi-master replication via galera. I know that Postgres has long had many cluster stories, but I always struggled to even figure out which one was the right choice to make. Most of the clusters I've deployed ended up using DRBD and heartbeat rather than one of the Postgres native options. As far as advance features, quite a few years ago I was doing this exercise to generate reports on some sample payroll dataset. The expectation was clearly that you would run a query, massage that data in some code, then run a bunch of other queries in that code to further correlate the data. I forget the exact problem that was posed. I'm not super strong in database advanced features, but in the end I was able to come up with some fairly simple SQL, using materialized views or CTEs or similar, that produced the report entirely using SQL, in a fairly short runtime. This was my first real experience with using MySQL beyond the basic features it had in decades past, and I was quite impressed with it.

mmerlin 5y ago
CitusDB shards PostgreSQL nicely for both data ingestion bandwidth and load balancing + HA
DeathArrow 5y ago
>I did pick MySQL over Postgres for a old project that I'm currently working on again, the primary reason was easy multi-master replication via galera. I plan to use CockroachDB as a distributed data store since it's protocol compatible with Postgres. Still in testing phase, but the deployment is very easy.
- linsomniac 5y ago
  Agreed, the deployment was very easy, that was one of the reasons I wanted to switch to Cockroach from MySQL+Galera. It took me less than a day to get ansible roles to set up a Cockroach cluster, but around a week for the MySQL Galera cluster, to get it completely right. And that was starting with somebody else's Maria+Galera Ansible role. My blocker was that I'm using it in part for Postfix maps, and the Postfix pgsql module can't be configured to us anything but LATIN-1 encoding, and Cockroach only supports UTF-8.

kmos17 5y ago

MySQL been rock solid for us, handling a million+ queries per hour on a fairly large database, running continually for over 10 years (other than occasional updates), but we're not using any advanced features, just run of the mill relational db with some full text searching. The tooling (phpmyadmin, percona xtrabackup) and knowledge available online are also a big plus.