Basic MySQL Performance Tuning Practices that Everyone Should Know About | Hacker News Reader

Basic MySQL Performance Tuning Practices that Everyone Should Know About

catonmat.net

50 pointspkrumins10 comments

Oh how many businesses spend countless thousands on incompitent contractors when all they needed was someone to simply run EXPLAIN.

And who's going to interpret that output if all they have is incompetent contractors?

The problem with database optimization isn't that "people don't understand it well", it's that database optimization is inherently obscure and difficult. With most code, performance is a local effect: you can look at the code and see why it's slow. With some harder problems, performance is data-dependent and you need to look very closely.

With databases, it's worse still: performance is a feature of the schema, configuration, and (worse still) the quirks of the software (ex: I never knew MySQL can't optimize count(*) queries!) . They can be useful, but they're hugely complicated. In general, using a database without an expert DBA is a recipe for poor scalability.

For the count(*) queries:

Almost no database optimizes this. What they do is count the rows. MyISAM (one of the storage engines for MySQL) heavily optimizes this by having a row count in the table data.

This turns out not to be that useful because who wants to count the number of rows in a table? You usually want to count a subset of those rows. Like, how many comments are there for post_id 7.

So, MySQL isn't unoptimized for this.

You mean MySQL can give you the answer in constant or log time. But folks like me, who naively use the count() aggregate like we've been taught in books, are going to have scalability problems unless we understand the internals of the database engine.

That was my point: it's not a slam against MySQL per se, it's a slam against the whole development metaphor, which seems to derive unholy pleasure in hiding critically important implementation details from the developer.

Step 1. Install PostgreSQL .

Does PostgreSQL do production-ready replication?

Not to defend the original comment, but yes PostgreSQL does have production quality replication through Slony-I. In fact, that's what is powering reddit right now.

Again, I don't want to defend the original comment because it was just a flame and I think the database-fights are just as stupid as the language-fights.

I wasn't trying to feed the flames of an argument, I was genuinely curious. In my day-job, I administer MySQL databases, and only vaguely handle anything with PostgreSQL. Replication was an issue when we went down the MySQL path, and I haven't kept abreast of PostgreSQL's development.

Both MySQL and PostgreSQL are absolutely fantastic pieces of software. Both have their pros and cons, but in general, they are neck in neck.

There was a nice quote that I forget where I read it, but it goes as such:

"Choose MySQL if you want an open source product, PostgreSQL if you want an open source project."

Actually no. PostgreSQL and MySQL are certainly not neck to neck especially in performance. See http://tweakers.net/reviews/657

Summary: MySQL comes close to beating PostgreSQL (in speed) when there is little contention but gets absolutely clobbered when there are multiple users simultaneously.

My addition: Even with 5.1, MySQL is still severely lacking when it comes to ANSI features as compared to PostgreSQL and other commercial solutions like Oracle and DB2.

Reply on news.ycombinator.com