MySQL: The Real Grid Database, Mark Callaghan, Chip Turner

A tremendous amount of work also done by Wei Li and Gene Pang.

Google has a large MySQL deployment, and they enhance it as needed.

MySQL@Google: too many queries, transactions, data, and rapid growth. Real workload with OLTP and reporting. Workload at Google is *critical*.

The well known solution is to deploy a “grid database”:

• use many replicas to scale read performance
• shard your data over many masters to scale write performance (vertical partitioning of data)
• sharding is easy, resharding is hard

Large number of small servers, not much capacity lost when a server fails, support as many servers as possible with a few DBAs.

Manageability is important at Google - make all tasks scriptable. Gives you time to solve more interesting problems, and …

Arguably one of the most interesting keynotes (and technical to boot!), Paul Tuckfield not only entertained us in his 40 minute keynote, he also did so outside when the keynotes ended.

Just the DBA at PayPal, just the DBA at YouTube. Only 3 DBAs at YouTube that make it all happen. Only a MySQLer for ~8 months (Oracle for ~15 years). So guess PayPal is a Oracle shop.

MySQL is one (important) piece of the scalability picture.

Technologies: Python, Memcache, MySQL replication. Praises Python, a lot (its much quicker, than C++, to implement goodness).

Click tracking on a separate MyISAM site. But Read/write on InnoDB, using replication. Far more reads than writes at YouTube

4×2ghz Opteron core, 16GB ram, 12×10krpm scsi - constantly crashing, replication saved them

5.0 “mystery cache hits” - when you export and import (mysqldump and load back into 5.0), you boost your performance, rather than …

Yesterday I released mysqlreport v3.1. No large changes:

• Added support for table_open_cache system var (MySQL 5.1.3+).
• Removed default assignments for $mycnf{’host’}, $mycnf{’port’}, and $mycnf{’socket’}.
• Connecting to MySQL now tries socket if one is given and actually a socket, else TCP/IP host if one is given, else generic “host=localhost” which relies on the MySQL client lib to do something useful.
• Updated help for clarity.
• Fixed bug: spelled “fsync” wrong.

I also updated The Guide To Understanding mysqlreport to include InnoDB reports and the mysqlreport documentation.

MySQL: The Real Grid Database

Introduction

• Can’t work on performance problems until we solve the availability
• We want MySQL to fix our problems first.

The problem

• Deploy a DBMS for a workload with
  • too many queries
  • to many transactions
  • to much data

A well known solution

-use many replicas to scale read performance
-shard your data over many master to scale write performance
-sharding is easy, resharding is hard

- make it easy to run many severs
- unbretable aggregate perfomance

The grid database approach

…

Eric and I gave a 3-hour tutorial on Monday morning at the MySQL Conference and Expo 2007 entitled “Scaling and High Availability Architectures“. The slides aren’t great, and of course you should have been there. Nonetheless many of you will find the slides useful, and we’re keen to provide them, so here they are:

• MySQL Scaling and High Availability Architectures

If you have any questions or need help validating or understanding how our recommendations may fit into your environment, contact us.

Yesterday Phillip Reisner was giving an introduction to DRBD. I won't get into it here, but essentially it's a way to keep blockdevices on two different hosts in sync to get a kind of shared disk. (Go read about it on their site!)

I've used it for a while to get high-availability from two NFS servers and I always recommend it in my scalability talk. It's awesome. (Heartbeat which is the "fail-over" software in the typical configuration isn't so great, more on that another time).

Anyway, being at the MySQL conference the context of course is "how can this be used for MySQL". Usually I prefer a simpler master-master replication setup for redundancy. Later in the afternoon Mats Kindahl (one of the replication …

I think this was a really interesting talk (because of all the contributors talking), and my only minor complaint was that it was up against some really good talks, and we didn’t get more people showing up to a talk that was very largely on the great Architecture of Participation. It also is interesting, as it goes to show that blogging can get you good rewards - most of everyone listed below, is a somewhat active blogger.

Martin Friebe - bug reports, patches
Why? Its just cool to contribute. Improves your knowledge. MySQL rewards you (named on the website, Enterprise, etc.).
How? Write code. Look for limitations. Just use MySQL.

Peter Zaitsev
Hates submitting bugs, but he needs a bug free MySQL for himself and customers. Therefore, report them, and scream loud!
Be an early adopter.
Regular hardware, for storage engine benchmarks. Patches, and other cool bits for …

The MySQL Con has done something amazingly intellegent. Taped down to the floors in all the session rooms and meetings rooms, there are power strips. No matter where you sit, there is a powerstrip within a couple of meters. So instead of spending time looking for power outlets and having clumps of people gathered around the few outlets around the walls, you can sit where you want, and take notes and do research while listening and speaking.

This is something that all other tech conferences would do well to emulate.

“The web is broken you can all go home now.”

Ramus Lerdorf — Father of PHP — MySQL Conference 2007

If you can find me today during the MySQL Conference & Expo, I have a limited amount of DVD’s that contain all 15 podcasts on them. If you have been thinking you wanted to listen to the podcast but haven’t gotten around to downloading the episodes yet, here’s your chance! Just find me — Today I’m in a red top and black skirt….