This post is the third of a series that started here.

From the previous of this series, we now have two working EC2 instances that are EBS based. The first instance is the monitor, usually an m1.small type instance and the second instance is hamysql, a large instance type. So far, we have configured Heartbeat for communication between the nodes. Now, it is time to configure the HA resources in Pacemaker. Here are the requirements for the HA resources:

On Monitor

• Kill the other instance
• Free the share resources
• Start a new hamysql instance
• Reconfigure the clients
• Give away the resources

On hamysql

• Run MySQL
• Monitor MySQL
• Get out …

This article was initially inspired by Vadim's post on mysqlperformanceblog.com comparing MySQL 5.5.4 and XtraDB under TPCC-like workload. Vadim's post has opened an interesting discussion about InnoDB I/O capacity feature in general, and particularly - tunning / settings regarding 5.5.4 and XtraDB. However, going more in depth I've discovered more interesting things than I've expected initially :-) and here is a kind of summary of my observations..

The following stuff is mainly covering heavy Read+Write workloads as I/O capacity was added to InnoDB to improve page writes rather reading ;-)

First of all - why I/O capacity in InnoDB?..

InnoDB I/O Capacity

Initially InnoDB was designed to flush in background only 100 pages per second. The value of 100 pages was fixed within a code …

One problem with MySQL Cluster we are starting to see quite often is to do with the current generation of Xeon processors.  This post outlines the problem and how to avoid it.

With the Nehalem based Intel Xeons (and also in some older AMD CPUs) they add a technology called NUMA (Non-Uniform Memory Access).  This basically gives each CPU its own bank of memory instead of all CPUs accessing all the memory.  For many applications this means much faster memory access. You should be able to see if NUMA is on by looking for it in dmesg.

So why is this a bad thing?

MySQL Cluster data nodes typically require a large portion of the memory, this means very often that one CPU will need to access the memory from another other CPU.  This in general is quite slow, on a busy cluster we have seen this access take 100ms - 500ms!  MySQL Cluster is real-time and is not a happy bunny when there are things stopping it …

A little while back we did a webinar on using Pacemaker to fully automate MySQL replication. We now have a recording of that webinar available from our web site.

We’re well into summer and almost at our 200th edition of Log Buffer, a blog of blogs about the database world.

Remember if you find a link or interesting blog post that you think Log Buffer should mention, send a note to the editor at Log Buffer and be sure to include the link, and a short note outlining why you think that particular post would be of value to other DBAs, or what you learned from reading it.

Now on to our weekly reading in Log Buffer #194:

Oracle Exadata is a topic that is getting hotter and hotter. Following Pythian’s announcement of Exadata services Oracle’s Greg Rahn …

Recently we had couple posts dedicated to performance monitoring, i.e. Color code your performance numbers, Performance Optimization and Six Sigma, so you may understand we consider stability of performance numbers as one of important area for database management.

That's why we decided to add histogram of queries response times into Percona Server, and our software engineer Oleg Tsarev implemented this feature. The feature adds new INFORMATION_SCHEMA table QUERY_RESPONSE_TIME
( full docs are on page response_time_distribution), which looks like

time count
0.000001 53
…

As some might remember I made a visual representation of the Joomla 1.5 database schema back in 2006. I have now set up an EER representation of the Joomla 1.6 database schema too, that you can download for free. This schema was made after the 1.6 beta 2 release, and it was built using MySQL Workbench, which is both GPL and available as a free download for multiple platforms.

First of all: If you find any errors in this schema, make sure you leave a comment for me at the bottom. Because of the lack of conventions for naming primary key columns, I am left to do a lot of guesswork here when drawing up table relations, so there may be errors. Pretty much the only table …

One of the significant benefits of MySQL is it’s ease of use. Generally already installed on most Linux systems, MySQL can be installed by a single command if not yet present. On Oracle Enterprise Linux 5.4 you can use the following commands to check for MySQL, configure your yum repository and install MySQL.

# Check if already installed
$ rpm -qa | grep -i mysql

# Configure yum repository on new server
$ su -
$ cd /tmp
$ wget http://public-yum.oracle.com/public-yum-el5.repo
$ sed -e "s/enabled=0/enabled=1/" public-yum-el5.repo > /etc/yum.repos.d/public-yum-el5.repo

# Install MySQL
$ yum install -y mysql-server mysql

# Start and test MySQL Instance
$ /etc/init.d/mysqld start
$ mysql -uroot -e "SELECT VERSION"

+-----------+
| VERSION() |
+-----------+
| 5.0.77    |
+-----------+

You can find more …

There’s been a surprising amount of drama (in some circles, at least) about database technology recently.  I shouldn’t be surprised, given the volume of reactions to the I Want a New Datastore post that I wrote. (Hint: I still hear from folks pitching the newest data storage systems.)

The two things that caught my eye recently involve Cassandra and MongoDB (and, indirectly, MySQL). First was what I read as a poorly thought out and whiny critique of MongoDB’s durability model: MongoDB Performance & Durability. Just because something is the default doesn’t mean you have to use it that way. Thankfully there was reasoned discussion and reaction elsewhere, including the …