I understand that the new technology can be scaring and this makes entry barriers to try and get familiar with it.
That’s why I created simple set of scripts which setup N nodes cluster on running EC2 instances.

The script assumes that you have running N EC2 instances (in the same availability zone) running RedHat Enterprise Linux 6.2 64bit (I use m1.xlarge size).

You put hostnames of the instances into ec2hosts.txt file and run install.nodes.sh script.
You also should have your private key to access to your instances (test-cluster.pem in my case).

For my box installing and starting 3 nodes took exactly

time install.nodes.sh
...
real    2m51.257s

In fact you can deploy as many nodes as you want, I made also running 20 nodes cluster.

After that you have the cluster deployed and ready to accept queries!

The scripts are …

In this post I want to show new possibilities which open with Percona XtraDB Cluster.
We will create 3 nodes Cluster with nodes on different continents (Europe, USA, Japan) and each node will accept write queries.
Well, you theoretically could create 3 node traditional MySQL ring replication, but this is not what you want to use day-to-day.

To show how it works I will use Amazon m1.xlarge instances, by one in Tokyo, Ireland and North California, running RedHat Entreprise 6.2 64bit.

In fact to create instances is most time consuming task. After that using my script you will have cluster running in 5 min or less.

There however some precautions needed if you run Amazon instances.
First, you need to open ports in the firewall. For the communication the nodes need, …

You probably saw that Vadim blogged about the first alpha release of Percona XtraDB Cluster. Just in time: now I can talk about it at my High Availability talk in Percona Live DC! If you’re coming, be sure to catch us in the hallway and ask those specific questions that won’t be addressed in a general survey talk on HA solutions.

Preparing Choosing Storage Systems for MySQL talk for Percona Live in Washington,DC I ran into great paper called Sane SAN 2010 by James Morle from Scale Abilities – and Oracle consulting company. It is worth to read for variety of reason yet for this post I wanted to mention what James calls “Busy” Oracle database application when it comes to IO consumption:

It is applications either using Over 10.000 IOs Per second (latency sensitive) or using Over 500MB/s bandwidth (bandwidth sensitive). I wonder how many of MySQL Users are running applications of these scale, for single …

I got a question a while ago about how Percona Server handles corrupted data more gracefully than the standard MySQL server from Oracle. The short version is that it won’t crash the whole server.

With standard MySQL from Oracle, if any page of data in InnoDB is found to be corrupt, the entire instance will crash forcefully. This is a good policy if you want to treat your entire data set as a single unit, which is either usable or not. However, this does not reflect reality for many users, who have a lot of data collocated in a single instance. In such cases, it is desirable for the server to continue running, so the corruption in one database does not affect the others.

Percona Server handles corruption more gracefully, if you enable it, by simply marking the single table as corrupt, and not crashing the entire server.

The relevant documentation is …

One thing I noticed during the observation was that there were roughly 2,000 new connections to MySQL per second during peak times. This is a high number by any account.

When a new connection to MySQL is made, it can go into the back_log, which effectively serves as a queue for new connections on operating system size to allow MySQL to handle spikes. Although MySQL connections are quite fast compared to many other databases it can become the bottleneck. For a more in depth discussion of what goes on during a connection and ideas for improvement, see this post by Domas]),

With MySQL 5.5 default back_log of 50 and 2000 connections created per second it will take just 0.025 seconds to fill the queue completely if requests are not …

A lot have been written about Battery Learning Cycle problems and its impact to MySQL Performance. Here are couple of links (1,2). It is good to see though there are some controllers coming out which solve this problem, namely Adaptec 5Z series controllers (Z stands for Zero Maintenance). This is not quite new technology they have appeared on market about 2 years ago but it is just now we can state they have been working well for number of customers.

As Explained in this PDF ZMCP (Zero-Maintenance Cache Protection) does not use battery but instead Capacitor plus flash. Capacitor provides …

I guess I’m first to post in 2012 so Happy New Year all blog readers !
Now back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3.23.44 have not been improved in new releases as much as I’d like. We still get cryptic error messages such as “ERROR 1025 (HY000): Error on rename of ‘./test/child’ to ‘./test/#sql2-496-40a5′ (errno: 152)” in many cases and foreign keys are still handled on storage engine level making them not working for partitioned tables as well as making foreign keys performed row by row which often can be very inefficient.
As results of Foreign Key limitations you might need to get rid of them, yet this leaves you up for a final bite – dropping foreign keys requires table rebuild. Yes you get it right even though Innodb is able to drop indexes without rebuilding table since MySQL 5.1 (Innodb Plugin) …

Overview

Profiling, analyzing and then fixing queries is likely the most oft-repeated part of a job of a DBA and one that keeps evolving, as new features are added to the application new queries pop up that need to be analyzed and fixed. And there are not too many tools out there that can make your life easy. However, there is one such tool, pt-query-digest (from Percona Toolkit) which provides you with all the data points you need to attack the right query in the right way. But vanilla MySQL does have its limitations, it reports only a subset of stats, however if you compare that to Percona server, it reports extra stats such as information about the queries’ execution plan (which includes things like whether Query cache was used or not, if Filesort was used, whether tmp table was created in memory or on disk, if full scan was done, etc) as well as InnoDB …

We will be holding our highly acclaimed MySQL workshops in Frankfurt, Germany the week of February 13th. Early registration is open; enroll today and secure your seat. Detail and enrollment can be found here.