6 ms·

So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona... 1. It's reliable/durable 2. I

by taf2 12y ago

So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona... 1. It's reliable/durable 2. I know the tools (mysql command line is friendly to me) 3. It's performance is predictable. 99% of issues with performance are easily solved with the right index 4. I don't find myself needing many SQL features beyond the basics. Also, I'm a happy user of other tools to compliant mysql. Including redis and elasticsearch. Both of these tools IMO compliant MySQL. I'm no fan of Oracle by any means but at least MySQL is still an open source database. Perhaps when I have free time before the next startup I'll give Postgres a good look. For now I'll probably try it out with AWS redshift...

Thaxll 12y ago
Well it's HN, outside of Postgres / Redis everything sucks. And yes you're right on MySQL it's reliable. Honest question here, is there an equivalent of Percona tools for Postregres?
pilif 12y ago
>1. It's reliable/durable I would personally contest that. I've seen MySQL corrupt tables before and the insanely unhelpful defaults which make MySQL accept and alter invalid data really don't strike me as neither reliable nor durable. Your other points are valid reasons for sticking with MySQL though. If you're happy and the tool does what you need, that's fine. But once you've seen what you can do as you gain access to the more advanced SQL features and once you've burned by MySQL not starting up because of data corruption, then you might want to investigate other options. Postgres happens to be a very good alternative with good tools, predictable performance, very advanced SQL features, but also really good reliability and durability.
- morgo 12y ago
  In the context of this post (MySQL 5.7) it does not accept invalid data :) The default sql mode has changed.
  - tsuraan 12y ago
    So, MySQL, as of 5.7, no longer accepts dates with year 0, and it also stopped converting unrecognized date strings into the date 00-00-0000? That would be a very positive change.
    - FireBeyond 12y ago
      It has "not supported" them for a long time. But it wasn't the default, due to backward compatibility (of course the decision that lead to this in the first place is no less suspect).
    - morgo 12y ago
      Yes. See slide 43 for examples of statements that now produce errors by default: http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57 http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57 This will break some applications that are upgrading. I have a whitelist/blacklist suggestion on how to transition on my blog: http://www.tocker.ca/2014/09/01/suggestions-for-transitioning-into-strict-sql-mode.html http://www.tocker.ca/2014/09/01/suggestions-for-transitionin...
  - equoid 12y ago
    "The default sql mode has changed." So long as it possible to redefine the mode at will on a per connection basis or on startup then the integrity of data held within MySQL cannot be guaranteed.
    - morgo 12y ago
      Being able to change it on a per connection basis is required for earlier applications. If we use Wordpress as an example, upon initial connection it will set the SQL mode to what it is compatible with (magento, drupal, others also do similar). Integrity can also not be upheld if applications lie. i.e. I always enter the same incorrect birthday if an application asks me for this, but I don't think they have a reason for it.
- kyrra 12y ago
  Corruption issues are fairly true with MyISAM engine. InnoDB did a lot to help reliability of tables (it can auto-correct them in cases where you had a hard poweroff or other related issues). No more needing to manually run table scans to detect corruption. What "advanced SQL features" do other DB products have that you cannot do with MySQL?
  - pilif 12y ago
    CTEs, range types, window functions, custom aggregates, functional indexes, conditional indexes, proper utf-8 support and once you bring Postgres with its unique features into the mix: kick-ass JSON support (including the usage of indexes), array support (any my best friend array_agg()) Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) Now I can totally accept that this might have been a one-time fluke, but I personally really have trouble trusting a DBMS once it has lost data due to causes other than user-error or hardware faults.
    - kbenson 12y ago
      It's not pretty, but you can get the equivalent of range types using variables[1]. Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) It's not safe until it's backed up. I don't care if it's a $100k oracle installation, It's not safe until it's backed up. Even then it's suspect... 1: http://stackoverflow.com/questions/17147920/postgres-window-function-lag-equivalent-query-in-mysql http://stackoverflow.com/questions/17147920/postgres-window-...
      - ldng 12y ago
        I would add, as Dimitri Fontaine says : it is not safe until backup is tested and automatically recoverable ! https://fosdem.org/2015/schedule/event/youd_better_have_tested_backups/ https://fosdem.org/2015/schedule/event/youd_better_have_test...
- FireBeyond 12y ago
  Though "you shouldn't have to", my first recommendation for anyone setting up a MySQL installation is to go to the Percona Configuration Wizard and build a much more appropriate configuration file (with strict and enforcing defaults, better performance and a whole bunch of other things).
- IndianAstronaut 12y ago
  I have had terrible luck with MySQL on my macbook. The db gets corrupted often and I lose my data on a regular basis.
benmorris 12y ago
I have a similar opinion. Not to mention I have a many clients/projects already built on MySql databases. Moving to a different DB is expensive and unnecessary.
brendoncrawford 12y ago
Redshift is great for some use cases, and completely unsuitable for others. If testing the waters with Postgres is your objective, Redshift will not be a good representation.
morgo 12y ago
Under your point (3), it is important to note that this has been a major focus area of MySQL. I have two comments to make on it: 1) Peak throughput is not as important as consistent throughput (i.e. less variance between response times). This makes it a great fit for front-facing web apps, and we have Percona/Facebook/Google in particular to thank for their contributions in improving this. 2) Often users do not get the performance they are entitled to, because they lack the visibility. MySQL 5.6 and above (and particularly mysql 5.7) have performance_schema to be able to track down and diagnose issues. Memory/transactions/stages/replication/prepared statements/statements/mutexes.. these things are all instrumented by Performance schema. Just today I saw a new query written to show a breakdown of latency per schema/per statement type: http://dba.stackexchange.com/questions/94723/getting-database-request-statistics-on-mysql/94846 http://dba.stackexchange.com/questions/94723/getting-databas...
IgorPartola 12y ago
MySQL is a fine DB for most cases. There are some specific features it lacks that do make it annoying (and make Postgres a better choice): 1. Indexes are too simplistic. You can't index the output of a function. 2. InnoDB lacks full text search. MyISAM has it, but MyISAM is bad since it doesn't support transactions. 3. Unicode support by default does not support all Unicode characters. That's right, even though it says UTF-8, not everything is supported, and you have to specifically enable support for some character subsets for your DB. 4. MySQL replication is... how should I put it? Delicate. There is no integrity checking, and since by default it's just replaying an SQL log you can easily get inconsistencies between the master and the slave. There are lots of ways to confuse replication, such as `INSERT INTO my_table (foo) VALUES (RAND())`. There are no built-in tools for integrity checking the slave, and the third party tools that exist have to resort to some really crazy things, like re-inserting tables. I could go on about replication, and its issues for a while, but I want to move on to the other points. 5. No transactional DDL. This really sucks when using with something like Django migrations. 6. No point in time backups. You either use mysqldump with a transaction (you aren't using MyISAM, right?), or you stop the database server. 7. Logs suck. No, really, have you tried debugging an issue with your queries, deadlocks, configuration errors, etc.? MySQL's server logs (not query logs), are not very verbose, and what the do write is mostly useless. 8. Row level locking semantics are at times doing unexpected things. Last time I used MySQL for complex real time write-heavy stuff, I actually moved to advisory locks instead (the support for which is not exactly great in MySQL and could be expanded). This invites contention, deadlocks, and other nastiness where it isn't necessary. 9. No IPv6 support. 10. Can't index Archive engine tables. 11. Can't partition tables by any arbitrary value. It has to be only specific types which makes it too restrictive. 12. GIS support is not really there. Lots of basic features aren't supported. 13. No materialized views. You can simulate it with triggers, but that's not nearly as convenient. 14. No async drivers. Note that these are very specific features. If you don't use them, good for you. No reason to switch away from MySQL just because. It has some advantages over Postgres as well: 1. Simple user management. 2. Simple database management. No schemas, objects, etc., just tables and views here. 3. INSERT IGNORE and REPLACE! You don't need to create triggers for these. 4. Decent performance out of the box, and lots of knobs to turn when tuning. 5. Generally, doesn't require a ton of setup time. Install it, create user and DB and code away (Postgres and lots of others have this too, but it is a good feature). 6. Large amount of community brain share, so you won't be braving a new world here. So, basically, go ahead and use it if it works, but I'd say Postgres deserves a try too and your 4 points apply to it equally as well.
sarciszewski 12y ago
I made the switch from MySQL to PostgreSQL last year. The learning curve between the two is small and you'll probably find yourself liking PG more. Even if you don't, give it a chance.
kidmenot 12y ago
>4. I don't find myself needing many SQL features beyond the basics. That's probably why you still like it. As it stands, MySQL has so many shortcomings it's not even funny. Version 5.7 is the first one to support multiple triggers per event. Beyond that, and just to name a few, you can't have subselects in views, you don't have CTEs (good luck storing hierarchical data in an adjacency list), no materialized views, CHECK constraints are parsed but ignored. I'm glad it works for you, but the minute you need to do something slightly more complex, MySQL gets in your way. I hope the situation will improve, though.
- Buttons840 12y ago
  There have been several times in my career where I have missed full SQL support in MySQL databases other people have setup and left for me to maintain. Choosing MySQL isn't the worst thing one can do when building a new system, but just because the initial designers don't intend to use advanced SQL features doesn't mean future use cases wont benefit from full SQL support.
  - kidmenot 12y ago
    Indeed. Just these days the lack of materialized view has been giving me troubles. It would make a certain process about 50 times faster without touching code at all, and it would take me all of three queries. Granted, the real problem is a poor design of the system, but materialized views would enable me to improve what others have left behind.
JohnBooty 12y ago
> 3. It's performance is predictable. 99% of issues > with performance are easily solved with the right index This is one of my least favorite things about MySQL. The "explain" feature is much less informative than that offered by MSSQL or Postgres, so figuring out what indexes or tuning to apply is harder than it needs to be. And then when you do have everything indexed correctly, MySQL is significantly dumber (edit: looks like I'm significantly dumber! see correction below) about using those indexes than MSSQL or Postgres. For example: those two can do index-only SELECTS. If table "foo" has columns A, B, C, D, E, and F and I've created an index on A and B, why can't "select A,B from foo" be served directly from the index? Postgres and MSSQL do it, and it's a huge performance boost in those two. Postgres also lets you do some amazing things with partial indexes, indexes on computed values, etc. MySQL might not be horrible here, but out of the 3 major relational databases I've used, it's clearly the weakest at this so it's funny to hear it touted as a strength. Edit: Good news - I was wrong; MySQL does support index-only selects (covering indexes). User morgo also pointed out the improved JSON format for explain as well.
- morgo 12y ago
  If you do not like explain, may I suggest using `EXPLAIN FORMAT=JSON` - it is new to MySQL 5.6, and contains additional meta data. There is also optimizer trace, which provides significantly more meta data. Some good examples in these slides: http://www.slideshare.net/oysteing/how-to-analyze-and-tune-sql-queries-for-better-performance-webinar http://www.slideshare.net/oysteing/how-to-analyze-and-tune-s... I believe your example might be http://mysql-nordic.blogspot.ca/2015/01/with-latest-verions-of-mysql-575.html http://mysql-nordic.blogspot.ca/2015/01/with-latest-verions-... - Fixed in 5.7.5. There has been a lot of work in improving the optimizer recently. One project that I find particularly interesting, is the focus on improving the cost model: http://mysqlserverteam.com/the-mysql-optimizer-cost-model-project/ http://mysqlserverteam.com/the-mysql-optimizer-cost-model-pr...
  - JohnBooty 12y ago
    That's great to see. Thank you for the correction. I did not know that information was available. I wonder if there are others like me who didn't know this because they've been doing the plain-vanilla EXPLAIN like always.
dschiptsov 12y ago
Try this. Download mysql sources. Install clang 3.6. Try to compile with all sanitizers. Last time I did it (it was percona code) it found a leak somewhere in SQL parsing code. No kidding. It is my habit from old days to compile everything.

by taf2 12y ago

Thaxll 12y ago

Well it's HN, outside of Postgres / Redis everything sucks. And yes you're right on MySQL it's reliable. Honest question here, is there an equivalent of Percona tools for Postregres?

pilif 12y ago

>1. It's reliable/durable I would personally contest that. I've seen MySQL corrupt tables before and the insanely unhelpful defaults which make MySQL accept and alter invalid data really don't strike me as neither reliable nor durable. Your other points are valid reasons for sticking with MySQL though. If you're happy and the tool does what you need, that's fine. But once you've seen what you can do as you gain access to the more advanced SQL features and once you've burned by MySQL not starting up because of data corruption, then you might want to investigate other options. Postgres happens to be a very good alternative with good tools, predictable performance, very advanced SQL features, but also really good reliability and durability.

morgo 12y ago
In the context of this post (MySQL 5.7) it does not accept invalid data :) The default sql mode has changed.
- tsuraan 12y ago
  So, MySQL, as of 5.7, no longer accepts dates with year 0, and it also stopped converting unrecognized date strings into the date 00-00-0000? That would be a very positive change.
  - FireBeyond 12y ago
    It has "not supported" them for a long time. But it wasn't the default, due to backward compatibility (of course the decision that lead to this in the first place is no less suspect).
  - morgo 12y ago
    Yes. See slide 43 for examples of statements that now produce errors by default: http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57 http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57 This will break some applications that are upgrading. I have a whitelist/blacklist suggestion on how to transition on my blog: http://www.tocker.ca/2014/09/01/suggestions-for-transitioning-into-strict-sql-mode.html http://www.tocker.ca/2014/09/01/suggestions-for-transitionin...
- equoid 12y ago
  "The default sql mode has changed." So long as it possible to redefine the mode at will on a per connection basis or on startup then the integrity of data held within MySQL cannot be guaranteed.
  - morgo 12y ago
    Being able to change it on a per connection basis is required for earlier applications. If we use Wordpress as an example, upon initial connection it will set the SQL mode to what it is compatible with (magento, drupal, others also do similar). Integrity can also not be upheld if applications lie. i.e. I always enter the same incorrect birthday if an application asks me for this, but I don't think they have a reason for it.
kyrra 12y ago
Corruption issues are fairly true with MyISAM engine. InnoDB did a lot to help reliability of tables (it can auto-correct them in cases where you had a hard poweroff or other related issues). No more needing to manually run table scans to detect corruption. What "advanced SQL features" do other DB products have that you cannot do with MySQL?
- pilif 12y ago
  CTEs, range types, window functions, custom aggregates, functional indexes, conditional indexes, proper utf-8 support and once you bring Postgres with its unique features into the mix: kick-ass JSON support (including the usage of indexes), array support (any my best friend array_agg()) Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) Now I can totally accept that this might have been a one-time fluke, but I personally really have trouble trusting a DBMS once it has lost data due to causes other than user-error or hardware faults.
  - kbenson 12y ago
    It's not pretty, but you can get the equivalent of range types using variables[1]. Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) It's not safe until it's backed up. I don't care if it's a $100k oracle installation, It's not safe until it's backed up. Even then it's suspect... 1: http://stackoverflow.com/questions/17147920/postgres-window-function-lag-equivalent-query-in-mysql http://stackoverflow.com/questions/17147920/postgres-window-...
    - ldng 12y ago
      I would add, as Dimitri Fontaine says : it is not safe until backup is tested and automatically recoverable ! https://fosdem.org/2015/schedule/event/youd_better_have_tested_backups/ https://fosdem.org/2015/schedule/event/youd_better_have_test...
FireBeyond 12y ago
Though "you shouldn't have to", my first recommendation for anyone setting up a MySQL installation is to go to the Percona Configuration Wizard and build a much more appropriate configuration file (with strict and enforcing defaults, better performance and a whole bunch of other things).
IndianAstronaut 12y ago
I have had terrible luck with MySQL on my macbook. The db gets corrupted often and I lose my data on a regular basis.

benmorris 12y ago

I have a similar opinion. Not to mention I have a many clients/projects already built on MySql databases. Moving to a different DB is expensive and unnecessary.

brendoncrawford 12y ago

Redshift is great for some use cases, and completely unsuitable for others. If testing the waters with Postgres is your objective, Redshift will not be a good representation.

morgo 12y ago

Under your point (3), it is important to note that this has been a major focus area of MySQL. I have two comments to make on it: 1) Peak throughput is not as important as consistent throughput (i.e. less variance between response times). This makes it a great fit for front-facing web apps, and we have Percona/Facebook/Google in particular to thank for their contributions in improving this. 2) Often users do not get the performance they are entitled to, because they lack the visibility. MySQL 5.6 and above (and particularly mysql 5.7) have performance_schema to be able to track down and diagnose issues. Memory/transactions/stages/replication/prepared statements/statements/mutexes.. these things are all instrumented by Performance schema. Just today I saw a new query written to show a breakdown of latency per schema/per statement type: http://dba.stackexchange.com/questions/94723/getting-database-request-statistics-on-mysql/94846 http://dba.stackexchange.com/questions/94723/getting-databas...

IgorPartola 12y ago

MySQL is a fine DB for most cases. There are some specific features it lacks that do make it annoying (and make Postgres a better choice): 1. Indexes are too simplistic. You can't index the output of a function. 2. InnoDB lacks full text search. MyISAM has it, but MyISAM is bad since it doesn't support transactions. 3. Unicode support by default does not support all Unicode characters. That's right, even though it says UTF-8, not everything is supported, and you have to specifically enable support for some character subsets for your DB. 4. MySQL replication is... how should I put it? Delicate. There is no integrity checking, and since by default it's just replaying an SQL log you can easily get inconsistencies between the master and the slave. There are lots of ways to confuse replication, such as `INSERT INTO my_table (foo) VALUES (RAND())`. There are no built-in tools for integrity checking the slave, and the third party tools that exist have to resort to some really crazy things, like re-inserting tables. I could go on about replication, and its issues for a while, but I want to move on to the other points. 5. No transactional DDL. This really sucks when using with something like Django migrations. 6. No point in time backups. You either use mysqldump with a transaction (you aren't using MyISAM, right?), or you stop the database server. 7. Logs suck. No, really, have you tried debugging an issue with your queries, deadlocks, configuration errors, etc.? MySQL's server logs (not query logs), are not very verbose, and what the do write is mostly useless. 8. Row level locking semantics are at times doing unexpected things. Last time I used MySQL for complex real time write-heavy stuff, I actually moved to advisory locks instead (the support for which is not exactly great in MySQL and could be expanded). This invites contention, deadlocks, and other nastiness where it isn't necessary. 9. No IPv6 support. 10. Can't index Archive engine tables. 11. Can't partition tables by any arbitrary value. It has to be only specific types which makes it too restrictive. 12. GIS support is not really there. Lots of basic features aren't supported. 13. No materialized views. You can simulate it with triggers, but that's not nearly as convenient. 14. No async drivers. Note that these are very specific features. If you don't use them, good for you. No reason to switch away from MySQL just because. It has some advantages over Postgres as well: 1. Simple user management. 2. Simple database management. No schemas, objects, etc., just tables and views here. 3. INSERT IGNORE and REPLACE! You don't need to create triggers for these. 4. Decent performance out of the box, and lots of knobs to turn when tuning. 5. Generally, doesn't require a ton of setup time. Install it, create user and DB and code away (Postgres and lots of others have this too, but it is a good feature). 6. Large amount of community brain share, so you won't be braving a new world here. So, basically, go ahead and use it if it works, but I'd say Postgres deserves a try too and your 4 points apply to it equally as well.

sarciszewski 12y ago

I made the switch from MySQL to PostgreSQL last year. The learning curve between the two is small and you'll probably find yourself liking PG more. Even if you don't, give it a chance.

kidmenot 12y ago

>4. I don't find myself needing many SQL features beyond the basics. That's probably why you still like it. As it stands, MySQL has so many shortcomings it's not even funny. Version 5.7 is the first one to support multiple triggers per event. Beyond that, and just to name a few, you can't have subselects in views, you don't have CTEs (good luck storing hierarchical data in an adjacency list), no materialized views, CHECK constraints are parsed but ignored. I'm glad it works for you, but the minute you need to do something slightly more complex, MySQL gets in your way. I hope the situation will improve, though.

Buttons840 12y ago
There have been several times in my career where I have missed full SQL support in MySQL databases other people have setup and left for me to maintain. Choosing MySQL isn't the worst thing one can do when building a new system, but just because the initial designers don't intend to use advanced SQL features doesn't mean future use cases wont benefit from full SQL support.
- kidmenot 12y ago
  Indeed. Just these days the lack of materialized view has been giving me troubles. It would make a certain process about 50 times faster without touching code at all, and it would take me all of three queries. Granted, the real problem is a poor design of the system, but materialized views would enable me to improve what others have left behind.

JohnBooty 12y ago

> 3. It's performance is predictable. 99% of issues > with performance are easily solved with the right index This is one of my least favorite things about MySQL. The "explain" feature is much less informative than that offered by MSSQL or Postgres, so figuring out what indexes or tuning to apply is harder than it needs to be. And then when you do have everything indexed correctly, MySQL is significantly dumber (edit: looks like I'm significantly dumber! see correction below) about using those indexes than MSSQL or Postgres. For example: those two can do index-only SELECTS. If table "foo" has columns A, B, C, D, E, and F and I've created an index on A and B, why can't "select A,B from foo" be served directly from the index? Postgres and MSSQL do it, and it's a huge performance boost in those two. Postgres also lets you do some amazing things with partial indexes, indexes on computed values, etc. MySQL might not be horrible here, but out of the 3 major relational databases I've used, it's clearly the weakest at this so it's funny to hear it touted as a strength. Edit: Good news - I was wrong; MySQL does support index-only selects (covering indexes). User morgo also pointed out the improved JSON format for explain as well.

morgo 12y ago
If you do not like explain, may I suggest using `EXPLAIN FORMAT=JSON` - it is new to MySQL 5.6, and contains additional meta data. There is also optimizer trace, which provides significantly more meta data. Some good examples in these slides: http://www.slideshare.net/oysteing/how-to-analyze-and-tune-sql-queries-for-better-performance-webinar http://www.slideshare.net/oysteing/how-to-analyze-and-tune-s... I believe your example might be http://mysql-nordic.blogspot.ca/2015/01/with-latest-verions-of-mysql-575.html http://mysql-nordic.blogspot.ca/2015/01/with-latest-verions-... - Fixed in 5.7.5. There has been a lot of work in improving the optimizer recently. One project that I find particularly interesting, is the focus on improving the cost model: http://mysqlserverteam.com/the-mysql-optimizer-cost-model-project/ http://mysqlserverteam.com/the-mysql-optimizer-cost-model-pr...
- JohnBooty 12y ago
  That's great to see. Thank you for the correction. I did not know that information was available. I wonder if there are others like me who didn't know this because they've been doing the plain-vanilla EXPLAIN like always.

dschiptsov 12y ago

Try this. Download mysql sources. Install clang 3.6. Try to compile with all sanitizers. Last time I did it (it was percona code) it found a leak somewhere in SQL parsing code. No kidding. It is my habit from old days to compile everything.