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

51–60 of 164 posts

Re: MySQL - Do Not Pass This Way Again

#51

Post by oasisbob »

reinhardt wrote: post #13
jes5199 wrote: post #9

Earlier quoted context omitted.

I worked at a large startup that was trying to migrate from MySQL to Postgres because of how long table migrations take on MySQL. There's a tendency for adding a column in MySQL to a table to be stop-the-world for the whole server, and it can take hours. They had some pretty serious workarounds for that, and some of them were "try to never change the database structure". There was some thought that there were other p…

Percona's OSC [1] pretty much fixes the migration pain without locking. [1] http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch...

pt-online-schema change is a great tool, but not a panacea.

I've seen a serious issue where under moderately heavy load, the OSC tool would trigger a deadlock on the final atomic table swap, blocking all transactions against the table, including the final `RENAME TABLE`.

All operations (including reads) would block, waiting on a table metadata lock. Killing the offending RENAME thread was the only way to restore access.

IIRC, the ultimate cause was due to a bug in our code that would occasionally leave stale transactions open in edge cases. Happened a few times, and caused a fairly severe outage the first time we hit it.

Not sure where the blame here lies -- and what can fundamentally be improved -- but OSC techniques and MySQL still leave plenty of room for a moderately savvy user to get into trouble.

Re: MySQL - Do Not Pass This Way Again

#52

Post by jacques_chester »

contingencies wrote: post #49
jacques_chester wrote: post #45

Earlier quoted context omitted.

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…

Accountants invented the concept of a queue of transactions causing predictable updates. They also invented eventual consistency in the form of special and general ledgers.

However, ACID is four requirements and they're all still valuable and useful defaults. Atomicity comes from double-entry bookkeeping, consistency from the idea that data is meaningless without structure, isolation from the demands of consistency and durability because people get grumpy when you tell that that umpteen jillion dollars may ... or may not ... have been recorded.

Like I said, I'm a data-safety bigot. I greatly prefer to start with a safe default and then relax the guarantees. Retrofitting safety is harder, especially when you don't have a uniform statement of what your data is.

For something like a blog, an RDBMS is probably overkill. And MySQL got its start in life because it so thoroughly (silently) relaxed the standard guarantees that it was much faster than anything else.

> major-version upgrade capable HA while under load

The strategies are the same as for NoSQL.

Either you stop the world, or you run versions in parallel and drain traffic from the old versions.

I also find it slightly hilarious that just casually upgrading something without exercising great caution is seen as a good idea.

Re: MySQL - Do Not Pass This Way Again

#53

Post by justinjlynn »

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 ?

The analogy goes like this: If you're very rich, you can do horrible drugs and pay a veritable army of people to keep you presentable and working. Of course, this until one day it doesn't. Being large and successful doesn't mean that there's nothing festering deep within. Companies and people can become so invested in previous solutions that the perceived benefit versus the cost of switching is almost always too low to gather enough political will to execute. MySQL is a drug, when you start it is cheap and available, but as you grow as you hope you will, you start to need more and more. Of course, this exposes many of the flaws and problems of the drugs you were on and if you're in a bad way, still are. Better to go through a bit of pain now and know what you're in for than to just use MySQL because facebook, twitter, amazon, and flickr can pay a huge team of people or large consulting firms to make their technology decisions actually work. If you really must, use MySQL to spike the product but know the side-effects and know when to get the hell off it.

Re: MySQL - Do Not Pass This Way Again

#54

Post by mdellabitta »

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'…

This is an ad hominem argument. His statements about MySQL should be judged on their own merits, regardless of the load times of his blog.

Re: MySQL - Do Not Pass This Way Again

#55

Post by mscarborough »

fdr wrote: post #48
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…

You're right, offering a solution to MySQL problems in a thread is generally useless.

I don't really get what point the author is trying to make.

I also don't take detailed performance advice from people who can't keep a blog up. Yeah, must be the wrong backend.

Re: MySQL - Do Not Pass This Way Again

#56

Post by willlll »

mscarborough wrote: post #55
fdr wrote: post #48

Earlier quoted context omitted.

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…

You're right, offering a solution to MySQL problems in a thread is generally useless. I don't really get what point the author is trying to make. I also don't take detailed performance advice from people who can't keep a blog up. Yeah, must be the wrong backend.

How could you possibly not get the point? It's the first two (2) sentences "Considering MySQL? Use something else."

Re: MySQL - Do Not Pass This Way Again

#57

Post by mscarborough »

mdellabitta wrote: post #54
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'…

This is an ad hominem argument. His statements about MySQL should be judged on their own merits, regardless of the load times of his blog.

No, it's not.

If you're going to be a performance authority about web technology, you should know how to configure the open-source and very well-documented code you are using.

Re: MySQL - Do Not Pass This Way Again

#58

Post by jacques_chester »

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'…

It's not performance advice, though.

It's a list of known problems with MySQL, most of which violate the "Principle of Least Surprise".

Some of the problems have performance implications. The query planner stuff, for example.

> The funny thing is that this blog's performance is based on some cookie. If I reload in Chrome? 2 sec ... just stop ... when you don't even know what you're talking about.

It may interest you to learn that web browsers have local caches. Incognito Mode does not have such a cache, and so must refetch pages from scratch on each view.

Re: MySQL - Do Not Pass This Way Again

#59

Post by jacques_chester »

mscarborough wrote: post #57
mdellabitta wrote: post #54

Earlier quoted context omitted.

This is an ad hominem argument. His statements about MySQL should be judged on their own merits, regardless of the load times of his blog.

No, it's not. If you're going to be a performance authority about web technology, you should know how to configure the open-source and very well-documented code you are using.

I don't see how using Apache, with what I guess are the default settings, has any bearing on remarks about MySQL.

Re: MySQL - Do Not Pass This Way Again

#60

Post by taligent »

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 know Apple uses SAP/Oracle and imagine the others would be similar.

But they use Oracle Cluster i.e. multi-master so it isn't monolithic like say a giant PostgreSQL database would be.

Post reply on HN