While tuning my new server, I came across an article on MySQL tuning that does a really great job of laying out the main options that need to be tweaked, as well as explaining what they do. It's several years old, but still applicable. Also worth mentioning that MySQL ships with four "sample" configuration files for small, medium, large, and huge servers. The compiled-in defaults (at least with RedHat binaries) are somewhere around the "medium" server, which is for "systems up to 128M where MySQL is used together with other programs (such as a web server)". To put that another way, if your hardware was built this century, the defaults are horribly conservative.
In particular, if you're using InnoDB, you simply must set 'innodb_flush_log_at_trx_commit' to either zero or two. The default of one will kill you. I've never picked two (for the data I collect that extra second of safety is irrelevant), but you might want to.
Some thoughts and notes from my own experiences working with MySQL:
#1. The blog you've mentioned is very helpful and their book "High Performance MySQL" is worth purchasing. This is a very good book on databases in general and provides specific information about when to use the different backing engines that MySQL supports.
#2. As of version 5.1, nested subquery performance is exponentially slow. The following query will go exponentially slower as the number of records increases in TableA.
SELECT A.ID
FROM TableA A
WHERE
A.ID IN (
SELECT B.FK_ID
FROM TableB B
WHERE B.OTHER_COLUMN = '[Some Value]'
)
Reference link regarding performance: http://www.selikoff.net/blog/2008/12/10/memo-avoid-nested-queries-in-mysql-at-all-costs/
Note, this is being addressed in 5.4 (see: http://dev.mysql.com/tech-resources/articles/mysql-54.html Subquery Optimizations).
Yes, the query could be rewritten as an inner join (note EXISTS does not improve performance for this on MySQL). But reality is that there is a lot of SQL written this way. SQL that works just fine with SQL Server and Oracle. So caveat emptor.
#3. The slow query log is invaluable for finding said lousy queries (see: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html). What's even nicer is that as of 5.1 it can be turned on and off at runtime (i.e. no server restart required).