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

101–110 of 164 posts

Re: MySQL - Do Not Pass This Way Again

#101

Post by sergiosgc »

pippy wrote: post #82

I've been hearing bad things about MySQL, so I've been avoiding it as of late. So far my experience has been subpar. PostgreSQL is pedantic with data insertion, almost to a fault. This costs me development time. (Also I have no idea what my users will do, and I'd rather have faulty data inserted than none at all. If it's for a client asking about a product, this could cost money). Yet purists claim this is a great fe…

I don't know where the "twice as slow" number for postgresql comes from. My the experience is different. Wildly different. For simple "keystore" loads, both are at the same performance class. For anything more complicated, typical relational database scenarios, mysql folds and is one or two orders of magnitude slower.

The query planner's inability to push restrictions down the tree into subqueries alone prevents many typical real world queries.

The way I see it, mysql is trapped between NoSQL and proper relational databases

Re: MySQL - Do Not Pass This Way Again

#102

Post by sergiosgc »

toast0 wrote: post #71
jacques_chester wrote: post #35

Earlier quoted context omitted.

Or "joins are too slow". Well, yes, they can be. But some database systems have smarter plan builders than others. Take, for example, this gem: > Joining and ordering by rows from multiple tables often forces MySQL to dump the whole join to a temporary table, then sort it -- awful, especially if you then use LIMIT BY to paginate the results. This describes just about every standard page-with-comments schema ever devi…

Is it MySQLs fault that wordpress asks it to do something that's hard? LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of…

I know WordPress quite well. Most of these queries can be very easily executed from indexes, pulling from the tables only the data that will be output. It's just that mysql chooses to do it the dumb way.

Re: MySQL - Do Not Pass This Way Again

#103

Post by simonw »

toast0 wrote: post #67
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…

try to never change the database structure works pretty well. For all other cases, the easiest solution requires that you have a good redundancy system in place, and some extra planning. But basically run alter table without replication on your out of rotation slaves and masters, and then swap. Depending on your backup situation, you could probably just run the alter table on your backup systems and then restore from…

This has been one of the biggest changes I've noticed since our team switched from MySQL to PostgreSQL: our database design is now much, much better and we make changes to it much more often.

I has grown used to compromising on database quality to avoid having to make frequent changes to large tables: implementing a new feature as a join table for example rather than adding the columns it needs to a core, multi million row table.

Being able to add a billable column to a large existing table without worrying about downtime frees you up to be much smarter about how you design your application, and much more agile about changes you make to exuding functionality. This is a far bigger benefit than I was expecting from the change.

Re: MySQL - Do Not Pass This Way Again

#104

Post by sergiosgc »

toast0 wrote: post #76
jacques_chester wrote: post #62

Earlier quoted context omitted.

> Oh just blame Wordpress. I usually do. But in this case it looks as though MySQL's underwhelming query planner might be at fault.

MySQL's query planner is actually pretty good, but there are some things that are just not going to be fast; most of which is documented, almost all of which can be seen with describe select ...; if it uses a temporary table, it's probably necessary, and it's definitely going to be slow once you have enough rows. If WordPress uses any of these things, it's not MySQL's fault.

This may have been true ten years ago. Not anymore. Mysql query planner is dumb as a rock. Easy example: instead of referring to a table in FROM, try using a sub query with SELECT * from the same table. It's an easy reproduction of the planner failing to process the query tree. It should result in the same execution tree. It results in a temporary table being created, as an identical copy of the original table.

Before you attack the query itself, and note that it is an example.

Re: MySQL - Do Not Pass This Way Again

#105

Post by lucian1900 »

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

Given such a detailed article, the solution is obviously another DB. Postgres lacks most of the MySQL flaws described.

Re: MySQL - Do Not Pass This Way Again

#106

Post by tene »

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...

We recently spent a few weeks trying to make it work in production, and couldn't. We always ran into deadlocks due to gap locking and auto_increment columns.

After a few weeks of failure, we finally gave up and did it the slow way, unweigh a slave, do the alter offline, catch up in replication, and fail over. Repeat as needed.

Percona's OSC is great for the cases where it works, but there are still many cases where it doesn't.

Re: MySQL - Do Not Pass This Way Again

#107

Post by yxhuvud »

waps wrote: post #97
CaptainZapp wrote: post #84

Earlier quoted context omitted.

"I'm going to blame myself only - not fileutils or bash - this has nothing to do with the database" I disagree. UPDATE MyTab SET column = 'value' WHERE otherColumn = 1 is a valid SQL statement, while UPDATE MyTab SET column = 'value' WHERE otherColumn - 1 is not and should throw a syntax error and not perform a mass update due to some bullshit auto conversion. What would rile me most is that even if you're a very car…

This is actually a pretty good summary of the whole article. The main point is that mysql works very well, by slightly modifying what you meant into valid statements and doing the best it can. And because what it does depends on the (hidden) type of the data as well as what you actually ask it to do ... it sometimes does things you didn't intend. It reads a lot like a static typing versus dynamic typing debate. It's…

This is not dynamic vs static. This is automatic type coercion vs explicit type coercion. This is not the same thing. Weak and strong typing are completely orthogonal to if the language is static or dynamic. Weak typing mean values will be automatically coerced into something it isn't, whereas dynamic typing only mean that you will get your error during runtime, not at compile time.

Javascript is dynamic and weakly typed. C is static and weakly typed.

Re: MySQL - Do Not Pass This Way Again

#108

Post by zorlem »

stef25 wrote: post #94
willlll wrote: post #56

Earlier quoted context omitted.

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

I used Postgres on a project and my main concern with it is that whatever GUIs were available were seriously lacking. Phppgadmin is awful. If you're really pushing its capabilities then there may be better solutions than MySQL but for bog standard applications and websites there is no alternative as far as I'm concerned.

You should've used pgAdmin [1], it's much better than PhpPgAdmin [1]. I have not used the MySQL GUI (MySQL Workbench [2])that Oracle/Sun/MySQL sells with the Enterprise license but by its feature list I suspect that it provides comparable functionality to pgAdmin (which is FOSS). I would not put any of Php{Pg,My}Admin interfaces in the same category - they're too lacking IMO.

[1] http://www.pgadmin.org/

[2] http://www.mysql.com/products/workbench/

Re: MySQL - Do Not Pass This Way Again

#109

Post by zorlem »

mscarborough wrote: post #80
jacques_chester wrote: post #68

Earlier quoted context omitted.

> I'm genuinely interested to learn which use cases are more dependent on query planning than caching methodologies. I'll reply to this separately, it's a good discussion to have. My basic problem is that caching comes with a coordination cost and I prefer the originating data source to be as performant as possible. My own use case is a small Wordpress multisite installation. Even with a relatively trivial amount of…

As you described it, the query/caching strategy sounds too complicated. mysql proxy might help you out, or mysql triggers, or plenty of easy indexing strategies. i agree, caching strategies are not easy, but that is not the fault of databases. IMHO keeping your data layers separate is best. if you have a multi-site installation, set some kind of prefix within the app that makes sense to you for each application, befo…

> As you described it, the query/caching strategy sounds too complicated.

But that was his whole point - needlessly complicating an otherwise simple setup to get around limitations in the DB engine.

> mysql proxy might help you out, or mysql triggers, or plenty of easy indexing strategies.

I genuinely fail to see how mysql-proxy [1] (which has a sleuth of long-standing, unfixed, problems on its own), or triggers would (elegantly) help in this situation.

[1] http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html

edit: formatting

Re: MySQL - Do Not Pass This Way Again

#110

Post by LaGrange »

jacques_chester wrote: post #47
taligent wrote: post #30

Earlier quoted context omitted.

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

It does invalidate claims that using product X is going to inevitably cause you huge problems once you "grow enough". I've seen a lot of places grow a lot while using MySQL, and the problems aren't really that different or more serious than in places using Postgres or Oracle.

In fact, I know enough places moving from Postgres to MySQL due to growing pains.

Post reply on HN