The Future of MySQL at DrupalCon SF 2010

 

Narayan Newton is the lead sysadmin of Drupal.org. He gave a talk called The Future of MySQL at DrupalCon 2010 San Francisco. My notes are below.

What Just Happened?

For a long time, MySQL was a stable project to track. There was one place to get it, there was one vendor, there were no really popular patch sets, and the biggest question was what minor version to run.

It changed about a year ago, when things started getting interesting.

What led to this?

There are lots of theories, here's a few:

  • Long release cycles
  • Poor community integration
  • Management of 5.1

This led to stagnation. The focus was less on what independent developers needed, but what the bigger customers of MySQL AB needed. People who needed a high performance database for web applications were somewhat let down.

However, this has changed a lot. Lots of companies stepped up to the bat:

  • Percona: put features in XtraDB that they needed for their customers, which help for performance consulting
  • Google: huge patch set that impacted the 5.4 release from MySQL AB, and pulled into other builds
  • Ourdelta/Open Query: started aggregating patch sets and doing builds, making them available
  • Facebook: launchpad project patch set for their own uses
  • Monty Program: new company started by a founder of MySQL to support a new storage engine

Oracle

Then, Oracle bought them! A lot of people freaked out. Many emails, quite a few tweets.

Where are we now?

There are quite a few versions to choose from. It's frustrating, exciting and confusing at the same time. What version has which features and what are the risks? Let's dive into them.

MySQL 5.1 "Vanilla"

You can download this from Sun/Oracle/MySQL AB. This has been around for a while and is generally considered stable. There were some performance regressions;

  • The base InnoDB is fine, but I would upgrade to the InnoDB plugin, which is a huge improvement that you will notice immediately.
  • It doesn't scale vertically well. It won't use huge CPUs very effectively.
  • Row level replication. Previously the SQL statements would be transmitted from the master to the slave to keep a cluster in sync; each statement had to be completely repeatable when serialized, which doesn't play well with concurrency. However, the stability of it has not been great.

MySQL 5.1 "XtraDB" by Percona

  • Fork of the InnoDB Plugin
  • InnoDB status improvements
  • Show buffer pool contents
  • Log nnoDB rows statistics
  • Split InnoDB buffer pool mutex
  • Rewrite RW locks: helps for concurrency and throughput
  • Configurable IO thread numbers: instead of one reading thread and one writing thread, you can bump the thread numbers up for huge servers
  • Configurable IO "Capacity": you can tell InnoDB how much IO capacity your system should have, which decides when it will flush to disk
  • Adaptive check pointing
  • Table import/export

MySQL 5.1 MariaDB/MontyProgram

  • Maria storage engine
  • XtraDB
  • PBXT
  • Pool of threads
  • Wrong mutex detector
  • Table elimination: If you have some tables that are in the query but not required, Maria won't use them, and they won't be included in the join.
  • Slow query log extensions
  • Microsecond precision for the query list

Sun/Oracle

MySQL 5.4

  • Join optimizations
  • Stored procedures
  • General scalability improvements

MySQL 5.5

  • SemiSync replication
  • Perf schema
  • MRS
  • InnoDB recovery
  • Delete buffer: Drupal cron can be really nasty on big sites!
  • MDL
  • Split InnoDB buffer pools: The buffer pool has a global mutex at the beginning of it; Sun/Oracle split the mutex split the mutex based on the number of buffer pools, and then there's a hash query that distributes the data amongst the buffer pools
  • InnoDB as default

MariaDB 5.2

  • Varchar/blob for heap
  • Group commit for Maria
  • Pluggable auth
  • Segmented MyISAM key cache
  • Virtual columns: This is views for columns, that is, virtual columns based on other tables. These may be indexable!

Drupal 7 + NoSQL

Now let's talk about Drupal 7 and SQL. Drupal 7 allows us to use MongoDB, Redis or Cassandra in addition to MySQL. We're entering a time when we won't have N nodes talking to failover servers running MySQL. Now we might have a Cassandra server for Voting API, or bootstrapping Drupal. There's a huge number of options, where we didn't have options before.

Did you enjoy this post? Please spread the word.