Hacker News Reader

Comment by taspeotis | Hacker News Reader

At work we have an MS SQL Server instance managing ~1TB of data spread among various databases.

No one database is > 200GB so it's not "web scale" by any means but sometimes you need to pull out tricks like indexed/materialised views.

I use Transact-SQL directly via ADO in C++ and ADO .NET in C# and indirectly via EF and NHibernate.

It's easy to manage schema with SQL Server Data Tools and diagnosing a poorly performing query is straightforward with graphical execution plans.

Quite frankly, I don't care that there are alternative RDBMS' or alternatives to traditional RDBMS.

But ... I keep hearing about problem after problem with MySQL. What's the trick to using it successfully? Is the barrier to entry too low, and problems are from legions of rank amateurs? i.e. is it as simple as constructing your schema thoughtfully, with tables that are "well-it's-almost-3NF" and making some educated guesses about which indexes might be needed in advance?

View on HN

MySQL is pretty good in practice, I think many complaints are indeed just user error. That said:

- Much like MongoDB, the defaults sacrifice safety for performance, e.g. MyISAM tables (which everyone everywhere will tell you never to use, but are IIRC still the default) ignore things like foreign key constraints

- Lots of "error" conditions silently complete as best as they can, rather than failing with an error. E.g. trying to store the wrong type of value, or trying to make a table using a disabled storage engine, or trying to store a string in an encoding that can't represent all its characters. A lot of this is legacy that is slowly being improved on, but reputation naturally lags slightly behind

- The query planner is a bit simplistic, so if you want performance you do need to put a little more thought into tables so that you're not doing 5-way joins all the time

- Some more advanced "features" of other databases like triggers just don't exist. IMO they are bad features and shouldn't be used, but their absence provides an easy thing to criticize mysql for if you're that way inclined

It's perfectly possible to make a good product using MySQL, quite possibly better than you would with other databases. But you do need to be very scrupulous about checking for warnings after every statement.

MySQL has strict mode flags that can turn those silent "best guess" inserts into errors.

Triggers have existed since 5.0.2 in MySQL.

The query planner has been greatly improved with MySQL 5.6 and the MariaDB fork.

InnoDB, not MyISAM, has been the default storage engine since 5.5 which was released nearly 3 years ago.

MySQL is a good database for basing a CMS on. (for a loose definition of database)

PostgreSQL is a good SQL database.

And don't take me wrong, I'll take MySQL instead of PostgreSQL for simple and medium projects.

"Is the barrier to entry too low, and problems are from legions of rank amateurs?"

Maybe. Well, "low barrier to entry" is not a defect.

MySQL is a good database for basing a CMS on. (for a loose definition of database)

It seems to me that the success of MySQL can be tied directly to people using "database" for things where what they really need is "filesystem".

Seems like. That's also what this comment on Reddit says:

http://www.reddit.com/r/programming/comments/1niheb/mongodb_...

How easy is it to put a lock on a file while writing on it? What happens in an environment where you have multiple frontend servers that need to write to the same file? Are you going to have like one server that does the reading/writing from a file and have like a protocol with which other servers can send commands?

Great, you've just invented a half-assed DBMS.

> Great, you've just invented a half-assed DBMS.

My first thought is "Yeah, we already have one of those, it's called MySQL"

Agree, except I'd amend it as

The success of MySQL can be tied directly to people using "database" for things where what they really need is "network filesystem".

The success of SQLite can be tied directly to people using "database" for things where what they really need is "filesystem".

Interesting that it's the same dynamic at play.

> And don't take me wrong, I'll take MySQL instead of PostgreSQL for simple and medium projects.

Why? I can't see one feature that MySQL delivers better than Pgsql...

It's really funny when I get asked that question, really:

PostgreSQL is a pain to setup, the SQL parser is very finnicky and for the past times I spent too much time trying to work around every idiosyncrasy of Postgres when I could have a similar MySQL instance running in a shorter time

So, MySQL doesn't waste my time with stupid crap.

Now please call me ignorant and downvote me since PostgreSQL fanboys can't take a critique and think their loved database is perfect.

Not calling you ignorant, but for workloads that a simple, untuned mysql installation is sufficient you can just build yourself a simple, default postgresql installation. Use chef, puppet, terraform,... to make that repeatable and the setup pain goes away.

True, Chef and Puppet kind of take the pain away between different linux distributions (which is good). But this would be aiming at a deployment.

However, one common use case (for me) is having to have a one-off install, which unfortunately kind of prevents that.

If my job needed frequently installs of DBs I would have probably gone this way, but it isn't, so today it's PostgreSQL 9.1 on Mac OS, tomorrow (more likely, a year or two from now) it's MySQL on Fedora, etc

Chef and Puppet can deploy just fine into a development machine - either a VM (i'd recommend this) or even on your host machine. I can't imagine any workload that's simple enough to be handled by a vanilla, untuned mysql instance and at the same time not simple enough to be handled by a scripted postgres installation. Things get different once you're getting to more complex workloads, but the effort of tuning a mysql instance to handle those is in the same ballpark as the effort of tuning a postgres instance.

The odd thing is that my experience has been the opposite. Perhaps only because I started using PostgreSQL earlier, but nonetheless.

My experience has been running on Slackware/RedHat/Ubuntu, though, and never on any Windows version, if that matters.

I really like how all values in the SQL in PostgreSQL can be replaced with arbitrary expressions, including stored functions. YMMV.

PostgreSQL is not user friendly.

What is an hba file? How am I supposed to know what it does?

PostgreSQL has a "createuser" command. Great, but how am I supposed to know this is a PSQL command? (not to mention potential collision with other filenames)

RTFM? This is an acceptable answer, but it disregards completely the user experience.

MySQL is more intuitive. Authentication? User/password. None of this "ident" or "trust" (really?) schemes. Yes, pg_hba is more flexible, but if you have only one DB user... Yes, it's convenient to have a local "trust" connection. Also a little bit more insecure as well.

And there are convenient mysql* commands, you can tell they are mysql commands.

An hba file is one of the configuration files. The converse question would be: What is a my.cnf file? How am I supposed to know what it does? The answer is the same for both: RTFM

The CREATE USER issue is just bad faith on your part. It is a de-facto standard, used by Oracle, Transact-SQL and ironically MySQL.

In the end, your complain boils down to:

1. Having to edit pg_hba replacing "ident" with "password"

2. Unfamiliarity with pgsql opposed to familiarity with mysql

A developer that chooses tools primarily based on familiarity is on the slippery slope to dinosaur land. Not that it's bad -- Cobol development still pays well to this day.

I'm not talking about "CREATE USER", I'm talking about /usr/sbin/createuser (or whatever it is installed in your system)

"A developer that chooses tools primarily based on familiarity "

Yes, let me waste time to learn how to use the latest fad on HN, sure (not talking about PostgreSQL here)

And PostgreSQL is made of unicorn blood and my projects will be 200% better if I use it instead of MySQL, sure.

It's a DB. There's a narrow range where it would make a difference to use it instead of scaling to more servers, for example. And you can "always" convert later. (Unless you're FB apparently)

> What is an hba file? How am I supposed to know what it does?

From the section in the docs titled "the pg_hba.conf file", which explain it quite clearly? [1]

> PostgreSQL has a "createuser" command. Great, but how am I supposed to know this is a PSQL command?

Its not a psql command, its a client application. Which you are expected to know by reading either the documentation section on client applications [2] or the documentation on the administrative function for which that particular application is used [3].

Of course, you don't need the program for the function, you can just use SQL. Which, presumably, you are going to be reading the documentation on for any RDBMS, since even the ones that support the standard the best (e.g., between PostgreSQL and MySQL, PostgreSQL by far) still have some implementation specifics you need to be aware of.

> MySQL is more intuitive. Authentication? User/password.

PostgreSQL supports that, and configures it by default in the project-supplied installers (IIRC, for local connections only by default.) You only need to configure authentication if you are using different options, or want to support remote logins.

> Yes, pg_hba is more flexible, but if you have only one DB user...

You don't need a multiuser RDBMS at all?

> And there are convenient mysql* commands, you can tell they are mysql commands.

And how do you know that they exist or what they do without reading the docs?

[1] http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.h...

[2] http://www.postgresql.org/docs/9.3/static/reference-client.h...

[3] http://www.postgresql.org/docs/9.3/static/database-roles.htm...

Beyond the RTFM part of your post, it's fun that people are so defensive and don't understand what I'm saying.

"(createuser) not a psql command, its a client application."

Yeah, so postgresql is the only program that creates users for itself? Createuser is a generic name.

(Some) Postgresql client appplications have no prefix. MySQL apps have.

So that if I see a file called mysqladmin I know it's from mysql. If I see a file called createuser I don't have that information. Who says it's from pgsql and not somebody else?

> And how do you know that they exist or what they do without reading the docs?

mysql <TAB>, then man or --help?

Your point seems to be "I'm willing to read the mysql introductory documentation, but not the postgres introductory documentation".

I will definitely grant you that mysql is easier to use than postgres if you're only willing to read the docs for one.

Honestly, this is not my experience at all, and I'm not a DBA. I install postgres, I fiddle with setting up a user & pg_hba.conf to allow connections, and after that it's basically game on.

> SQL parser is very finnicky

In what way is it finicky?

http://www.merriam-webster.com/dictionary/finnicky

For example, MySQL accepts both ' and " as string delimiters. It's very fun trying to get around PGSQL not understanding what you want to do while MySQL understands.

It doesn't require ';' to mark the end of a command

> For example, MySQL accepts both ' and " as string delimiters.

That is, in its default mode, MYSQL does not accept ANSI-standard quoted identifiers (supporting only non-standard use of backquotes for quoted identifiers), and instead treats double quotes as equivalent to single quotes for strings. (Of course, MYSQL has a non-default option to support the standard.)

PostgreSQL, OTOH, supports the standard out of the box, and treates double quoted material as quoted identifiers.

Neither behavior is more finnicky, one is just standards-compliant.

> It's very fun trying to get around PGSQL not understanding what you want to do while MySQL understands

This would appear to only be an issue if your only RDBMS experience is with MySQL and you are adapted to its quirks, since if you have experience with other DBs which adhere to the ANSI standards for basic behavior, MySQL will, by default, be more likely to not understand what you mean than PostgreSQL.

PostgreSQL setup is as easy as yum install postgresql. You may have to tweak pg_hba.conf to get password authentication in some distros, but this is it setup-wise.

As for the parser being finnicky, that is a feature. Hint: an empty string in a timestamp does not mean midnight 1/1/1970. This is one area where mysql did more harm than good: getting developers hooked in sloppy SQL.

> PostgreSQL is a pain to setup

I've installed it on Linux and Windows and, no, its not. Its pretty much the same work as MySQL to setup, except the install usually wants information to create or use a system user for the DB server in the install, which is a trivial amount of extra work.

I understand this used to be an issue many years ago (IIRC, most of the streamlining of the setup happened in the first couple of major 8.x releases, in 2005-2006, about the same time as official native Windows support was added.)

> the SQL parser is very finnicky

How so?

upserts?

Consider SQLite instead.

That's a joke, right? SQLite isn't even in the same ballpark as MySQL. It has a thousand uses, but a replacement for MySQL it is not.

SQLite is a great replacement for MySQL for many of the same reasons and in many of the same circumstances where MySQL is a decent replacement for a non-MySQL RDBMS. Its lighter, simpler, and less overhead for simple tasks.

SQLite is a replacement for the uses of MySQL that aren't better replaced with postgres.

Answer to your last 3 questions :

- Replace MySQL with MariaDB (hopefully your tables are InnoDB and the transition will be "relatively" seamless). You can continue using the rest of your app as-is.

- Yes. There are many by the simple fact that a lot of people are unaware of what they're really doing and what impact certain indexes, queries and even to some extent, compile-time options will introduce.

- Schema is everything for an RDBMS. If people just take the time to calmly and carefully consider what it is they're trying to do and what options they'll truly need vs. just guessing what future "growth" would be like, they will have fewer actual (rather than imagined) growing pains later.

Of course, most of these problems could be mitigated if new projects started off with Postgres or MariaDB, if they're taking the RDBMS route.

I keep hearing people recommend MariaDB, which is fine. I'd add in Percona as well. I have/had a decent run with it, but MySQL limitations with querying make me look towards Postgres. SQL engines are hard to do right, no wonder most of MPPs are built on top of Postgres.

Except that I have coworkers that have no clue whatsoever and ran into deadlocks. Hmmm.. Deadlocks? That's probably a transaction problem. MS SQL is buggy, right? We just don't use transations anymore and pass on the wisdom that transactions are slow and don't work. The _transactions_ cause deadlocks. Evil transactions..

SQL works well, if you invest time understanding the theory and the limitations. If you care about the implementation you're going to use (whether it's MySQL, MS SQL, PostgreSQL or whatnot).

Unfortunately my rather sad experience is that people give a damn about it and will use the trial&error approach, until they cannot reproduce deadlocks/critical errors in their local 'runs on my Thinkpad' VM.

(I really need to quit)

The MsSQL locking problems is just because snapshot isolation is disabled per default: http://use-the-index-luke.com/blog/2011-06-24/dear-database-...

"I keep hearing about problem after problem with MySQL. What's the trick to using it successfully?"

Switching to PostgreSQL.

What are you actually trying to say? Not everything in your post is a full truth.

MS's graphical query plan tool is actually pretty awful and hasn't been updated since forever, quite often hiding the most important information, like estimated rows vs. actual rows. It takes quite a while to learn how to actually read it unless someone shows you. Like months. And it's really just a wrapper around SHOW PLAN, which MySQL has with EXPLAIN.

I'm not convinced that SQL Server Data Tools easily manages schemas, I've only been using it a few months but it seems a massive hack to me which relies on constant schema compares.

MS also constantly push you to use the graphical tools, but if you play in the tools and then try to make something repeatable it's a right PITA. Your only option is to turn on the incredibly annoying 'Autogenerate change scripts' whereas in MySQL you can just look at the history tab in Workbench which shows every command you've run. Imagine that. Everything as SQL scripts you can modify and re-run!

Finally, SQL Server Management studio is an absolute dog performance wise, I actually still miss Query Analyser which was much better IMO.

And you can't actually use SQL Server with something like Ruby or Python without quite a massive amount of effort. I know because I've tried, there's no decent, easy to install, equivalent of ADO.Net without mucking around with ODBC connections. At least a year ago there wasn't.

As for the problems with MySQL, he mentions in the post the problem with MySQL is the lack of certain types of operations.

Also there's a bunch of gotchas, sub-selects perform terribly in MySQL, which makes the EF unusable on it.

Still, I've used both and SQL Server is much better if you're developing with C#.

I work with SQL Server and its parts (SSIS, SSAS, etc.) pretty much exclusively at my day job, and I occasionally find myself hacking up Python scripts to automate various tasks.

I don't know about a year ago, but several months ago I found pyodbc [1] and it's been wonderful. Easy to set up, works a lot like the mysql functions in PHP.

[1] https://code.google.com/p/pyodbc/

Oh this looks like a modern solution

But really, Python + MSSQL = Bag of hurt.

It's a real pity, but I see the "market" for that being very small...

It's easy to manage schema with SQL Server Data Tools and diagnosing a poorly performing query is straightforward with graphical execution plans.

I wouldn't put too much stock in the graphical execution plans. Outside of the problem that it simply fails for complex queries, it lies about costings (costings are always based upon estimates, which means that when you have super-fast storage, as many new installs do, the query analyzer becomes a giant bag of lies), it can't "see into" table-defined functions (they sit there innocuously with negligible cost when often they sit in front of a curse of poor performance).

SQL Server is a great database, but it is very far from perfect. I mentioned above about the graphical execution plans lying about storage costs, and the same goes for the query planner -- it will choose horrific query plans under the assumption that you have one spinning disc of rust, falling apart when you're really on some FusionIO SLC. This can manifest in queries that run excellently one day falling completely to shit the next because it passed a threshold and has decided to switch join patterns. One would think that it would actively actually monitor storage/memory and CPU to make all of these weights, but it doesn't whatsoever.

The other major issue being lock escalation that can destroy SQL Server databases under heavy contention.

Reply on news.ycombinator.com