Yesterday, I (Zardosht) posted an entry introducing clustering indexes.  Here, I elaborate on three differences between a clustering index and a covering index:

1. 
   
2. Clustering indexes can create indexes that would otherwise bounce up against the limits on the maximum length and maximum number of columns in a MySQL index.
   
3. Clustering indexes simplify syntax making them easier and more intuitive to use.
   
4. Clustering indexes have smaller key sizes leading to better performance.
   

Expanding MySQL’s Limits

MySQL allows at most 16 columns in an index and at most 3072 bytes per index. For tables that have more than 16 columns or a row size of greater than 3072 bytes, one cannot create a covering index that includes …

So lets test some different configurations and try and build some best practices around Multiple SSD’s:

Which is better? Raid 5 or Raid 10?

As with regular disks, Raid 10 seems to performance better ( accept for pure reads ).  I did get a lot of movement test to test like with the 67% read test -vs- the 75% or 80% tests. But all in all RAID 10 seemed to be the optimal config.

Should you enable the controller cache? One of the things I have found in my single drive tests is that “dumb” controllers tend to give better performance numbers then “smart” controllers. Really expensive controllers tend to have extra logic to compensate for the limitations of traditional disk. So I decided to play with some of the controller options. The obvious one is cache on the controller.

Some tests showed substantially better performance when the disk cache was disabled ( both read & write ).

If better …

Giuseppe Maxia spoke at the May 2009 Boston MySQL User Group about MySQL 5.4 and what it can do, the new performance features, etc.

So in my previous post I showed some benchmarks showing a large drop off in performance when you fill the x-25e. I wanted to followup and say this: even if you do everything correctly ( i.e. leave 50%+ space free, disable controller cache etc ) you may still see a drop in performance if your workload is heavily write skewed.  To show this I ran a 100% random read sysbench fileio test over a 12GB dataset (37.5% full ) , the tests were run back-to-back over a several hours , here is what we see:

*Note the scale is a little skewed here ( i start at 2500 reqs ).

Each data point represents 2 million IO’s, so somewhere after about 6 million IO’s we start to drop.  At the end it looks like we stabilize around2900-3000 requests per second, an overall drop of about 25%.

While preparing the slides, I realized that, for the first time, we have a product that allows a painless performance gain. Unlike all the tuning and technological improvements that you can use to improve …

Everyone loves SSD.  It’s a hot topic all around the MySQL community with vendors lining up all kinds of new solutions to attack the “disk io”  problem that has plagued us all for years and years.  At this year’s user conference I talked about SSD’s and MySQL.   Those who follow my blog know I love IO and I love to benchmark anything that can help overcome IO issues.  One of the most exciting things out their at this point are the Intel x-25e drives.  These bad boys are not only fast but relatively inexpensive.  How fast are they?  Let’s just do a quick bit of review here and peak at the single drive #’s from sysbench.    Here you can see that a single X25-e outperforms all my other single drive test.

Yep you have probably seen this type of chart on other sites…   The great thing about the Intel drives is their performance on writes, this difference gives …

What is the Spider engine, then? In short, it's an extension to the partitioning engine with the ability of connecting to remote servers. Basically, partitions + federated, except that Federated is explicitly removed during the …

While I can not consider myself a member of MySQL's community of developers, I've been watching those developments the same way I follow the development of Linux and many of the Java and Apache projects our own services depend on. It was great to meet many of the core members of the development community and get some insight into their thoughts about the future.

Baron Schwartz called in his Percona Performance Conference keynote on Thursday for a new, active MySQL community to take the driver's seat in the development of the database, not just in the incremental improvements way of bug fixing and performance improvement, but also by setting a vision for the next generation …