edit: added “Low cardinality isn’t always bad” section after Morgan’s comment

As we’ve seen already column size is important for indexes. Cardinality is really important too, it’s the uniqueness of the values included in the index.

Indexes are used by MySQL (and in any RDBMS) to help find rows quickly. We want to make it as easy as possible for MySQL to find the relevant rows, the more precise or specific we are the less the number of rows MySQL has to fetch.

Example Table

For this post I’ve created a table with some dummy data in it. Here’s the SQL I used to create it (in case you want to) which was generated with this hack script.

The table is fairly …

Yesterday marked my 5th anniversary since I signed on the dotted line, and starting working with MySQL AB! I celebrated yesterday with a BBQ with the family, and a few vodka shots for old times sake. Interestingly, it was also the 5 year anniversary of the first 5.0 RC being announced.

Since that time I’ve transferred through to Sun, and now find my home to be Oracle (funnily, the user community that I left to join MySQL).

I’ve met a huge number of fantastically talented people, made a lot of new friends, seen a lot of new colleagues come on board as we expanded so quickly, and sadly seen a number of those colleagues leave to go onwards (and pretty much in all cases, upwards) within the IT world. Happily, a large number remain, and continue to amaze me with the work they have been doing, and focus that they have.

I’ve …

There has been major changes in mysql and postgres over a couple of years. Mysql has been focusing on improving and optimizing innodb. Postgres on the other hand has been focusing on database replication and hot standby.

Recently postgres came out with version 9.0 which has built-in replication and hot standby - the two most requested feature in postgresql. Earlier people used to shy away from

Oracle OpenWorld 2010 is over!  While I had to leave early Tuesday (the paying job takes precedence over the volunteer job), there are many things to talk about that happened while I was at OpenWorld this year.  I’ll attempt to cover a few of them in this posting.

Saturday Night, the ODTUG Board and YCC staff got together to discuss our plans and obligations for the week.  With Sunday User Forum, MySQL Sunday, and Oracle Develop to attend to during the week, along with many other meetings, this was an important start to the week.  It was nice for everyone to see everyone again, and to recharge and energize for the tasks ahead during the week.  Below is a picture of fellow Board Members Barbara Morris and Monty Latiolais at the dinner at Annabelle’s Bar and Bistro.

Saturday Evening Dinner before OpenWorld 2010

Sunday morning, I had a …

This project was announced a year or so ago by Antony Curtis who used to work for MySQL AB. Having met Antony a few times I was intrigued to see what he was up to. The quote on the OpenQuery website describes it well :
The Open Query GRAPH engine (OQGRAPH) is a computation engine allowing hierarchies and more complex graph structures to be handled in a relational fashion. In a nutshell, tree structures and friend-of-a-friend style searches can now be done using standard SQL syntax, and results joined onto other tables.
That sounds cool, and it's the first time I've heard of a MySQL 'Computation engine' plugin. Delving further into the manual gives some insight, and there's some unexpected twists there :

• OQG is a storage engine, but data stored is not persistent w.r.t. server crashes.
…

One thing that has puzzled me about MySQL Server is that it became famous for sharded scale-out deployments in well known web sites and yet has no visible support for such deployments. The MySQL killer feature for some time has been built-in asynchronous replication and gigabytes of blogs have been written about how to setup, use, debug and optimise replication, but when it comes to 'sharding' there is nothing built in. Perhaps to have attempted to implement something would have artificially constrained user's imaginations, whereas having no support at all has allowed 1,000 solutions to sprout? Perhaps there just wasn't MySQL developer bandwidth available, or perhaps it just wasn't the best use of the available time. In any case, it remains unclaimed territory to this day.

On first hearing of the Federated storage engine some years ago, …

Most of the time we think of SQL queries as being executed at a point in time and generating a single definitive result, but huge efficiency gains are available when data changes are tracked and derived views are partially updated as needed rather than being fully recomputed periodically. MySQL has support for views on tables, but there is currently no support for materialized views. While thinking about this topic I decided to have another look at Justin Swanhart's Flexviews tool and it's definitely a cool MySQL based project.

Flexviews is an open source set of non-intrusive addons to MySQL enabling materialized views to be defined and maintained as the underlying tables are changed. If you're not sure what a materialized view is or why they can be useful then I recommend reading the intro on the Flexviews site. I was particularly impressed by the documented support for …

As you might have guessed from my last couple blog posts, I’ve been experimenting with a few languages and libraries for a new project. I’ve finally gotten things far enough along to the point where I’d like to start getting other developers and potential users involved. I’m introducing SlackDB, an open source project that combines the functionality of relational databases with the ideas behind eventually consistent, shared-nothing data stores to provide a new database to support new and existing web applications in the cloud (enough buzzwords in there?). This is an idea I wrote about a while ago and recently I started putting a lot of night and weekend time into it.

It is still very early on in the development process, but the ideas behind it are starting to solidify and a fair amount of code is already written. It’s not all …

I ran sysbench for a simple read-write workload on a 16-core x86 server using MySQL 5.1 and was disappointed by the results. I then tried 5.5 and was thrilled by the results. I tested MySQL 5.1.50 with the InnoDB plugin and the Facebook patch. That was compared with MySQL 5.5.6-rc with InnoDB.

At first I did not test for 5.1.50 without the Facebook patch. Eventually I tested for 5.1.47 and then 5.1.50 without the patch and results were very different (some better, some worse and the worst-case behavior in the patch is really bad). Finally I tested 5.1.47 with the patch and performance is good there. So something went wrong while porting the patch to 5.1.50 and we must fix it.

The benchmark used 8 sysbench tables with 72M rows each. The primary key indexes for all tables were 120GB and a 4GB InnoDB buffer pool was used. 8 instances of sysbench were run where each instance used between 1 and 128 …

Let’s suppose that your backup process looks like this: you stop a replication slave, shut down MySQL, and copy away the data directory. Assume that the slave is perfect and has the same data as the master. Nothing is broken, nothing is wrong, everything is working fine. In most cases, this should work, right?

Under what kinds of circumstances will you not get all your data back if you restore the file copy and start MySQL?

Related posts:

1. Pop quiz: how can one slave break another slave
2. Progress on High Performance MySQL Backup and Recovery chapter
3. …