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….

Slides (PDF, 402K) These are the slides for my talk "Monitoring MySQL" at the MySQL Users Conference and Expo 2007 from yesterday.

I tried to reproduce my talk as a written text, but what I attach below is somewhat more and at the same time less than what I was actually saying yesterday.


Continue reading "Monitoring MySQL"

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 …

Got a MySQL project that needs a partitioned table. It's the ideal candidate really. Historical data that naturally would be partitioned by date. I need to keep an active window of data and delete old data quickly without doing DML.

Except partitioning is in 5.1 (more on that later) and I'm on 5.0.

One way I would overcome this limitation in Oracle Standard Edition would be to have multiple tables that hold a month worth of data and put a view on top of them. My users might notice a slight performance degradation, but it would be worth it to drop the data quickly.

So, I tried the same experiment in MySQL. I created three tables of this format:

CREATE TABLE `xyz_2007_01` (
`id` bigint(10) NOT NULL default '0',
`report_dt` date default NULL,
`acct_id` varchar(8) default NULL,
`some_text` varchar(222) default NULL,
PRIMARY KEY  (`id`),
KEY `xyz_rdt_acct_2007_01` …