For years, companies that run high-performance data-intensive web applications (like ours), have focused on putting everything possible in RAM… heavy DB caching, IMAP indexes, Search indexes, etc.  And we have been eagerly awaiting the day when we can affordably put this data on solid state disks.  We’re close now… real close.  You can even get an SSD for your MacBook Air.

I’ve been following Kevin Burton’s dive into MySQL performance on solid state disks.  Here is his final summary.  I recommend reading his entire series of posts on the topic.

There’s a bit of quirkiness when running a database on SSDs, but the results are very promising.  I can’t wait to put some of these in our servers.

Kevin’s tests were all with MySQL InnoDB and MyISAM tables, but he briefly touched on a few buzz words in his closing remarks:

> Also, if you’re using something like Bigtable via Hypertable or Hbase
your performance should be
> stellar since these are append only
databases and SSDs do very well with sequential reads and writes.

In a MySQL master-slave environment, when you do an UPDATE or DELETE with a complex WHERE clause, the complex query gets executed on every slave server, which is horrible if your slaves are already serving a high volume of read requests. But this can be avoided.

MySQL’s replication binlogs only replicate queries that modify data. So you can make the slaves do less work by running a SELECT first with the complex WHERE clause, then a UPDATE or DELETE with a simple WHERE clause. By breaking it up into 2 queries like this, only the simple query gets passed down and executed on the slave servers.

An example of where this might be useful is deleting a set of records that relate to another DELETE operation, and where there is no useful index that can be used to locate the related records. For example email aliases…. If a customer deletes user@example.com we need to also update/delete any aliases that point to user@example.com. If you store alias destinations in an unindexed comma delimited column, a LIKE statement or other complex string parsing statement must used in the WHERE clause to update/delete these related records. In this scenario, it would be much better to first run a SELECT query to find all alias records that you need to update/delete, and then run each update/delete query with a simple WHERE clause that references an indexed column.

Speaking of politics, looks like I’m voting for either Ron Paul or Obama in next week’s primaries:

http://feedblog.org/2008/02/05/obama-runs-linux-clinton-windows-ron-paul-linux/

I just noticed there is a reference manual online now for MySQL 6.0…

http://dev.mysql.com/doc/refman/6.0/en/mysql-nutshell.html

Also, libevent support!…

http://krow.livejournal.com/572937.html