This is a hack I've heard about a couple times now:

Paul wrote a script that reads from the logfile the queries that are going to be executed moments later. He parses the queries and constructs new select queries that populate the cache with the data that speeds up the upcoming writes. He claims, if I remember correctly, a three to four times speed-increase.

Here's the problem in a nutshell. The master can write transactions in parallel but slaves can only write them in series. [1]

This means you have a lot of optimizations on the master (TCQ and NCQ being examples) that aren't possible on the slave.

What this patch would do is precache the data so it's already available in memory. Since you're pre-reading the binary log you can run SELECTs in parallel on the SLAVEs so that the cache is hot when …

We've been doing a lot of performance analysis of MySQL and SATA disks over the last few days and I wanted to get some thoughts out in the open to see what you guys thought.

Now that Debian Etch is out we're seriously looking at making the jump to a full 64bit OS with 8G of memory on each of our boxes.

This also involves benchmarking SATA and potentially migrating away from MyISAM which, while very trusty, is seriously showing its age for us.

First off is raw IO tuning.

XFS benchmarks show about a 20% performance boost from bonnie++ over ext3. This is pretty obvious. Tuning readahead with 'blockdev' didn't seem to help performance much. SATA seems pretty tuned when compared to hdparm and IDE drives.

After fully tuning it seems we can get about 90MBps out or our disks. Not to shabby. The Linux md driver on RAID 0 didn't seem to boost IO performance much (and I'm pretty disappointed). Even with a 1M chunk …

Baron Schwartz was nice enough to make recordings of about a dozen talks at the recent MySQL conference. The original uploaded files were in ogg format and wouldn't work on an ipod and weren't very portable.

With his help, I uploaded the original MP3 files and made a podcast which contains all the links to the sessions and should work fine with iTunes and an ipod.

Here's the link to the podcast. Just add it to iTunes or your favorite podcasting software and off you go.

We've been working on automatic master promotion for MySQL using lbpool and I'm reminded of a few ideas we had after reading about MMM.

The MySQL Master-Master replication (often in active-passive mode) is popular pattern used by many companies using MySQL for scale out. Most of the companies would have some internal scripts to handle things as automatic fallback and slave cloning but no Open Source solution was made available.

Few months ago we were asked to implement such solution for one of the customers and they kindly agreed to let us release things under GPL2 License, and we gave them reduced rate for being Open Source friendly.

We actually considered a dual master approach but abandoned because it was non ideal. …

OK lazy web.... You have to help me out!

I try to monitor every performance metric I can within my cluster. Memcached efficiency, MySQL key buffer efficiency, etc.

One thing I can't benchmark is the efficiency of the Linux filesystem buffer (buffer cache).

From all the research I have done there's no way to to see the number of hits or total reads done by the file system cache.

Has anyone solved this problem?

I did some comparison of ZFS vs XFS last week to review the current state of the art in filesystems.

Long story short. XFS is still the reigning champion (at least on Linux). XFS beats out most filesystem benchmarks across the board. Reiser does well on directories with lots of small files but not enough to justify not using XFS.

Reiser FS is out of the picture honestly. First, it just doesn't perform very well. Second, Hans Reiser is probably going to prison for murdering his wife and is selling the company to pay off his legal costs.

ZFS would have a shot on …

Some of the common conceptions of data storage seem to have been blown out of the water.

Two things I found interesting:

In their study they found that there was no correlation between disk failure rates and utilization, environmental conditions such as temperature, or age. This means that high disk utilization or age of the disk have no significant impact on the probability that it will fail.

...

They observed that older disks had a much lower failure rates then newer disks, where the newer disks in general were less expensive.

Which makes me think that buying used HDDs off Craigslist might not be a bad idea.

One could buy cheap 15k RPM low latency disks from a few years ago and forget about the storage capacity in exchange for FAST seek time.

Of course it depends on how …

We've been working on the design of a protocol which would enable promotion of a slave to a master in a MySQL replication cluster.

Right now, if a MySQL master fails, most people just deal with a temporary outage. They bring the box back up, run REPAIR TABLEs if necessary, and generally take a few hours of downtime.

Google, Flickr, and Friendster have protocols in place for handling master failure but for the most part these are undocumented.

One solution would be to use a system like DRDB to get a synchronous copy of the data into a backup DB. This would work of course but would require more hardware and a custom kernel.

You could also use a second master in multi-master replication but this would require more hardware as well and complicates matters now that you're using multi-master replication which has a few technical issues.

A simpler approach is to just take a slave and promote it to the master. …

It seems that there's a bug with MyISAM tables in any version of MySQL since 4.1 running fulltext indexes.

If you do a FLUSH TABLES (without replication running) and then copy these tables to another DB server (which is essentially a mysqlhotcopy) replication will eventually break with:


Error 'Incorrect key file for table './blogindex/FOO.MYI'; try to repair it' on query. Default database: 'mydb'. Query: 'DELETE FROM FOO WHERE FOO.DATE_FOUND < '2006-12-12 22:35:33'', Error_code: 126


This works just fine for regular MyISAM tables and only breaks for tables which have fulltext indexes. It seems that maybe the index isn't correctly being written to disk?

MySQL AB has licensed lbpool - a load balancing and fault tolerant JDBC driver written in Java that I developed six months ago for use within Tailrank.

The lbpool project provides a load balancing JDBC driver for use with DB connection pools. It wraps a normal JDBC driver providing reconnect semantics in the event of additional hardware availability, partial system failure, or uneven load distribution. It also evenly distributes all new connections among slave DB servers in a given pool. Each time connect() is called it will attempt to use the best server with the least system load.

I originally designed lbpool based on the difficulty I had at managing MySQL clusters in a former life. Basically, the more clients you have and the more slave servers you have the greater the chance that something will crash and end up ruining …