Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL - Do Not Pass This Way Again

grimoire.ca

Post reply on HN
Print view View original

41–50 of 164 posts

Re: MySQL - Do Not Pass This Way Again

#41

Post by saurik »

rosser wrote: post #23
saurik wrote: post #15

Earlier quoted context omitted.

Many of the complaints I hear people make about the entire concept of an "RDBMS" (often then to motivate why the NoSQL solution they decided to start using is better) are actually MySQL-specific issues that do not affect PostgreSQL (or Oracle, or usually SQL Server; I only mention PostgreSQL, as you wanted a concrete experience); one key example is "if you want to change your schema, it requires locking the entire sy…

To be fair, there are cases where schema changes in PostgreSQL require re-writing the table, too. Like, for example, when you change the data type of an existing column. Otherwise, you're good, though.

Yes, but that case comes up less often and isn't what I see NoSQL people complaining about ;P. (Also, when I've listed that as an explicit caveat recently, I often get corrected that they changed the ramifications of that recently, and it only sometimes has to happen, so I figured I'd just explicitly list the situations where you clearly should never have needed to rewrite the table.)

Generally, though, this is related to be able to do table changes under transaction locks: I have often enjoyed being able to, under an atomic transaction, replace a table with a view over that table, or make modifications to indexes and columns that I then rollback if there's a mistake.

Re: MySQL - Do Not Pass This Way Again

#42

Post by zzzeek »

taligent wrote: post #37
zzzeek wrote: post #32

this is my favorite MySQL "decision", that the GROUP BY keyword by default (that is, unless you turn it off with the late-added magic flag ONLY_FULL_GROUP_BY) will gladly select an essentially "random" (well, the first row based on INSERT order, which in SQL is as good as random) row for you: mysql: create table data (token_a varchar(10), token_b varchar(10)); Query OK, 0 rows affected (0.05 sec) mysql: insert into d…

You say ONLY_FULL_GROUP_BY was late added. But there are posts talking about this setting in 2002.

but only added in version 5 (I was using it back with version 3 in the late 90s):

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#...

> Do not permit queries for which the select list or (as of MySQL 5.0.23) HAVING list refers to nonaggregated columns that are not named in the GROUP BY clause.

Re: MySQL - Do Not Pass This Way Again

#43

Post by josephlord »

viraptor wrote: post #26
rosser wrote: post #20

Earlier quoted context omitted.

"Foreign keys are ignored if you spell them certain, common, ways" - another case of "I want to use the wrong syntax, but still get the right answer" Both forms of declaring foreign keys are SQL-standard compliant syntax. That MySQL silently ignores the "inline" version is the "wrong" here, not the syntax. From MySQL's own docs: "Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as…

I understand that and don't have issue with what he most likely meant - only what he wrote. In short - MySQL does not always comply with the SQL standard. That's unfortunate / silly / annoying / inconsistent. I completely agree. But I don't agree with "Foreign keys are ignored if you spell them certain, common, ways". This is not very specific. Common to what situation? People being used to it in another project? It'…

If it doesn't implement part of the SQL spec that is a shame but why if there is part of the statement that isn't understood or processed there is no excuse for not raising an error. That is unacceptable. Documenting it just isn't enough.

Re: MySQL - Do Not Pass This Way Again

#44

Post by contingencies »

jacques_chester wrote: post #36
contingencies wrote: post #14

Used MySQL for 10+ years. I found it great for most purposes. I don't feel I left MySQL because of MySQL's failings, but that of all monolithic RDBMS.

Have you tried other RDBMSes?

OK I'll bite: Yes. Ultimately my problem with the old monolithic RDBMS is architectural.

Q: "How do you get 24x7x365 service on a mission-critical system that depends on a huge monolithic RDBMS datastore, preserving the capacity for RDBMS major-version software upgrades (protocol, on-disk format, etc.) and no downtime even between major versions?"

A: "While you could umm and ahh about it for awhile, basically, you don't. It's too big. Decentralization, plan-to-fail, clustering ('private cloud', hah), and commodity hardware are your friends. Embrace them and enjoy lower blood pressure."

If you don't have high availability requirements, RDBMS can still be a great and simple solution. Right tool for the job. (I have simply personally drifted from RDBMS as the requirements of systems I tend to work on has grown. My current rule of thumb is roughly: 'Make all storage backends service-provider abstracted (swappable, easy to benchmark, unit test, etc.). When choosing a backend for a storage implementation - if you can't SQLite it, don't SQL it.')

Re: MySQL - Do Not Pass This Way Again

#45

Post by jacques_chester »

contingencies wrote: post #44
jacques_chester wrote: post #36

Earlier quoted context omitted.

Have you tried other RDBMSes?

OK I'll bite: Yes. Ultimately my problem with the old monolithic RDBMS is architectural. Q: "How do you get 24x7x365 service on a mission-critical system that depends on a huge monolithic RDBMS datastore, preserving the capacity for RDBMS major-version software upgrades (protocol, on-disk format, etc.) and no downtime even between major versions?" A: "While you could umm and ahh about it for awhile, basically, you do…

Any central service is going to be a point of failure. This isn't unique to RBMSes (the other classic SPOF is message buses).

The idea that RDBMSes are unsuitable for HA applications is ... well to be charitable, I'll call it "inaccurate".

Most of the techniques that are used for HA were first invented for conventional databases and/or their best friends, mainframes and midrange systems.

I agree however that it is a question of picking the right tool for the job.

I'm a data-safety bigot. I require a great deal of talking down from my tree. These days I can understand that, yes, OK, a consistent and durable model of Facebook comments probably isn't really that important.

But I will bet folding money that Facebook stores their financial data in a huge monolithic RDBMS datastore, like Amazon, Microsoft, Google and Apple do.

Re: MySQL - Do Not Pass This Way Again

#46

Post by mscarborough »

This person doesn't even offer a solution?

How is it that people who have blogs that take 30 seconds to load continue to give performance advice that gets upvoted?

The funny thing is that this blog's performance is based on some cookie. If I reload in Chrome? 2 sec. If I reload in "Incognito Chrome", it's again really slow.

So seriously, just stop with these authoritative blog posts when you don't even know what you're talking about.

Re: MySQL - Do Not Pass This Way Again

#47

Post by jacques_chester »

taligent wrote: post #30
16s wrote: post #10

MySQL is the visual basic of SQL databases. Anyone can set one up and use it. The problem is that many non-technical people use MySQL and then think they know all about DBs. Ask them what ACID is, or about foreign key constraints. You'll get blank stares. If you know what those things are and value them, you probably don't use MySQL.

I really don't know where to begin with such a stupid comment. How about you start with listing which of these companies needs educating: http://www.mysql.com/customers/ Facebook ? Twitter ? Amazon ? Flickr ?

In some of these cases MySQL is not being used as an RDBMS.

And in most of those cases it looks like path dependency rather than a selection based on the merits of this or that database.

Either way, this is actually an argument from authority ("So and so use Brand X, therefore Brand X has positive qualities Y").

I've sat in on presentations with DB2 engineers who bragged about the data centre IBM runs for UPS. ~12 billion transactions per day (and that was 5 years ago). Does UPS make DB2 better or worse than MySQL?

Answer: it's irrelevant. DB2 and MySQL would need to be picked on their merits, not their users.

Re: MySQL - Do Not Pass This Way Again

#48

Post by fdr »

mscarborough wrote: post #46

This person doesn't even offer a solution? How is it that people who have blogs that take 30 seconds to load continue to give performance advice that gets upvoted? The funny thing is that this blog's performance is based on some cookie. If I reload in Chrome? 2 sec. If I reload in "Incognito Chrome", it's again really slow. So seriously, just stop with these authoritative blog posts when you don't even know what you'…

When criticizing a piece of software as popular as MySQL, offering a solution is an exercise in futility: it only leads people to think that the author is wrong because the alternative posited is wrong for them. See the endless MySQL vs. PostgreSQL discussions (which are not entirely without merit: the two system do have overlap in targeted users).

Instead, this author his simply focused on numerous detailed aspects of MySQL that he/she seems to think would be seen by most people as particularly insane, without need for comparison. I think there's a place for that.

Re: MySQL - Do Not Pass This Way Again

#49

Post by contingencies »

jacques_chester wrote: post #45
contingencies wrote: post #44

Earlier quoted context omitted.

OK I'll bite: Yes. Ultimately my problem with the old monolithic RDBMS is architectural. Q: "How do you get 24x7x365 service on a mission-critical system that depends on a huge monolithic RDBMS datastore, preserving the capacity for RDBMS major-version software upgrades (protocol, on-disk format, etc.) and no downtime even between major versions?" A: "While you could umm and ahh about it for awhile, basically, you do…

Any central service is going to be a point of failure. This isn't unique to RBMSes (the other classic SPOF is message buses). The idea that RDBMSes are unsuitable for HA applications is ... well to be charitable, I'll call it "inaccurate". Most of the techniques that are used for HA were first invented for conventional databases and/or their best friends, mainframes and midrange systems. I agree however that it is a…

I agree with the historical point. But I think you miss mine: show me an open source RDBMS (I don't want to drop $annual_profit_margin on Oracle) with major-version upgrade capable HA while under load. My view: it doesn't exist. You could try to make it happen somehow, but it would be a huge project on its own.

On your somewhat loaded financial example, as someone who is designing some financial systems at the moment, I would instead argue that 'eventual consistency' is actually the de-facto model within the vast majority of business accounting, globally (credit card chargebacks, taxation systems, international (or domestic in the US) bank transfers, invoices/accounts receivable, etc.). Simultaneously truly real time and truly atomic requirements, particularly at scale, are rare.

Re: MySQL - Do Not Pass This Way Again

#50

Post by lucb1e »

jacques_chester wrote: post #38

This is a great article. But I really wish it had sources for each of the claims. I would be interested to read the relevant documentation, because some of these directly describe problems I've had with running a Wordpress installation. And I've been blaming Wordpress for it. There's possibly a big mea culpa brewing; but I'd really like to look at the specifics.

Oh just blame Wordpress. My self-written software has no performance issues on MySQL, but Wordpress loading times are over two seconds with a default installation.
Post reply on HN