This post describes how MySQL Cluster executes queries. First of all, Cluster is a storage engine. It doesn't actually execute queries because it doesn't speak SQL. That is why you use a MySQL server, which parses your queries and sends low-level storage engine API calls to the Cluster data nodes. The data nodes know how to retrieve or store data. Or you can talk to the data nodes directly using the NDB API(s).

MySQL Cluster has various means of executing queries. They boil down to:

1. Primary key lookup
2. Unique key lookup
3. Ordered index scan (i.e., non-unique indexes that use T-trees)
4. Full table scan

Let's say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you've controlled the partitioning using the 5.1 partitioning features). So for …

As my first contribution to the MySQL Performance Blog, I joined Percona at the beginning September, I chose to cover the various high-availability (HA) options available for MySQL.  I have done dozen of MySQL HA related engagements while working for Sun/MySQL over the last couple of years using Heartbeat, DRBD and NDB cluster and I'll probably be doing the same at Percona.  I have built my first DRBD based HA solution nearly 10 years ago.

There is quite a lot of confusion surrounding HA solutions for MySQL, I will try to present them objectively, my goal here been not to sell any specific technology but to help people choose the right one for their needs.  This post is first of a series,  I don't yet know how many I will write in the series.

Before we start, it must be stated that high-availability is not only a matter of technical solutions, good management practices covering monitoring, alerting, security and …

Since I started doing the occasional consulting job for Open Query, I've seen a lot of MySQL servers that have been installed once and then forgotten about. This gave me the idea to do a short presentation about some basic MySQL server configuration. The first go was at DrupalCampMelbourne and I recently tried (and failed) to cram it into a three minute lightning talk slot at the LUV September meeting.

The title of the talk is (now) MySQL > YourSQL. I chose this not because I think that MySQL is better than the $other_database you use or because I may or may not run a newer version of MySQL on better hardware, but because I use InnoDB and …

Since I started doing the occasional consulting job for Open Query, I've seen a lot of MySQL servers that have been installed once and then forgotten about. This gave me the idea to do a short presentation about some basic MySQL server configuration. The first go was at DrupalCampMelbourne and I recently tried (and failed) to cram it into a three minute lightning talk slot at the LUV September meeting.

The title of the talk is (now) MySQL > YourSQL. I chose this not because I think that MySQL is better than the $other_database you use or because I may or may not run a newer version of MySQL on better hardware, but because I use InnoDB …

I like profilers. I use them when trying to make software (such as Drizzle) faster. Many profilers suck – and pretty much all of them are impossible to attach to a running system. Two notable exceptions are oprofile and dtrace (for Linux and Solaris respectively). The downside of oprofile is that it is non trivial to configure and get running and is pretty much all or nothing. Dtrace has the major disadvantage of that it is Solaris specific, so is only available to a minority of our users (and developers).

The new Linux Performance Events interface (perf_event) presents to userspace a nice abstraction of the hardware Performance Monitoring Unit inside the CPU. Typically these are processor specific (i.e. the one in a Core is different than the one in a Core 2) and can only be used by one thing at a time. The perf_events interface lets multiple applications/threads use the PMU (switching state at context switch as needed), even giving us …

I am not a fan of upgrading mySQL unless I need to. I am of the mind if it is not broke don't fix it, but when I do upgrade I follow these general steps.

If I have run into a mySQL bug, I look to see if that bug is fixed by searching the mySQL bug database.

If I've notice a performance bottleneck, I look to see if the performance bottleneck has been fixed by searching the same database.

I will NOT upgrade to the latest and greatest version of mySQL (5.4) I stay within my branch (5.0).

These are my three general motivations that drive my upgrade decisions. Anytime I upgrade I also make a list of things that might affect my environment for the stuff I use.

1. Here are my steps:
   
2. Check the change log
   
3. Ignore all the NDB changes... I don't use it …

Welcome to the 165th edition of Log Buffer, the weekly review of database blogs.

Since they haven’t had any Log Buffer love for a couple weeks, let’s start this one with . . . 

PostgreSQL

Selena Marie Deckelmann was tending the garden and found a Snow Leopard amongst the Macintoshes. The result, her post Snow Leopard and PostgreSQL: installation help links.

Josh Berkus posts a poll on encrypted backup. he writes, …

I have been working with cluster, in test environments, for a long time. Setting up MySQL Cluster for testing or just to mess around with is easy. This post will explain how to set up a MySQL Cluster on one test server or workstation. Please keep in mind that this is a limited installation for testing and NOT for production.

You should always start with the latest GA version of MySQL. For this example I will be using 5.1.39. You should install the max version which includes MySQL Cluster.

After you have MySQL installed don’t start the server! Instead lets get into some details. Create the file, /etc/config.cnf in vi and add the following to the file.

# This configuration should not be used in production!
[NDBD DEFAULT]
DataDir = /var/mysql-cluster
NoOfReplicas = 2
DataMemory = 80M # Default
…

Funding for Engine Yard and DataSync. Red Hat and Microsoft joint support. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

# Engine Yard raised $19m in Series C funding from the likes of Benchmark, DAG, Presidio and Amazon.

# Red Hat and Microsoft are now providing joint support for virtualization interoperability options.

# DataSync raised $1.2m and announced a partnership with SugarCRM.

# Simon Phipps began building a scorecard to …

OpenLDAP includes a driver that allows it to store and access data held in MySQL Cluster. It uses the NDB-API to access the database and so the performance is extremely good. One of the great things about the solution is that it lets you simultaneously access the same data through both LDAP and SQL (or the NDB-API or any of the MySQL connectors). This article gives an example of how this can be done.

Prerequisites:

This article assumes that you’ve already installed MySQL Cluster  – if that isn’t the case then please first refer to “Creating a simple Cluster on a single LINUX host” or “ …