At MySQL/Sun we do a lot of high availability setup using Heartbeat but recently I was involved, along with Harold Mayfield (a VCS expert), in project using Symantec VCS on Solaris. Of course, MySQL works perfectly well with VCS, here are a few highlights of the installation.

Apart from the share store resources and the IP resources, MySQL is configured as the following:

        Application insiderDB-app (
                StartProgram = "/san/mysql/bin/mysqld --defaults-file=/etc/my.cnf &"
                StopProgram = "/san/mysql/bin/mysqladmin -u root shutdown"
                MonitorProgram = "/san/vcs_scripts/mysql_monitor"
                )

and the mysql_monitor script is the following:

#!/bin/ksh

STATUS=$(/usr/bin/echo status | /san/mysql/bin/mysql -u root 2>/dev/null |/usr/bin/grep -c Uptime) 

if [ "$STATUS" -eq "1" ]
then
        exit 110
else
        exit 100
fi

In the previous 2 posts of this series, we basically talked about how to execute social networking type queries using SQL IN clause and how handle multiple columns IN clause. In this last post on the topic, I will introduce the notion of NDB API filters, although I don’t consider myself as an NDB API expert. Filters are to NDB API the equivalent WHERE clause in SQL. The point is that the filters can be nested and they are sent to the storage nodes only when the transaction is executed.

As an example, let’s consider the following table:

Create Table: CREATE TABLE `MultiColPK` (
  `region_id` int(11) NOT NULL DEFAULT '0',
  `application_id` int(11) NOT NULL DEFAULT '0',
  `first_name` varchar(30) NOT NULL DEFAULT '',
  `payload` varchar(30) DEFAULT NULL,
  PRIMARY KEY …

If you have been following the development of the NDB Cluster storage engine lately, you are probably as excited as I am. NDB Cluster is becoming a kind of large database killer app. Look at all the nice features that have been added:

• Replication, if you know MySQL you know what I am talking about
• Distribution awareness, optimize query execution based on the distribution, a strong scaling factor
• Disk based data, the possibility of pushing some columns to disk
• Online add index, among the only online DDL I know of in MySQL
• Multi-threading, no more need to configure many data nodes per server
• Realtime, when query execution times matter

and I probably miss some. And now, with version 7 (renamed from 6.4) it is possible to …

Recently, I talked about how to optimize social networking type queries for the NDB storage engine using IN clause statements. In clauses are great but they have one fundamental limitation, they work only on one column (Actually, this is not true, I discovered, thanks to Roland’s comment, that MySQL supports multiple columns IN clause). What if the primary key is a composite of let’s say “region_id”, “application_id” and “user_id”? Recently, while on site with a client, Brian Morin showed me a very clever way of dealing these type of primary keys in an IN clause. The main problem is that you cannot return a binary or varbinary from a function. So the idea was to used the return values of a stored proc. First we need to compose the varbinary from the actual values with this stored proc:
.

delimiter $$
drop procedure if exists compose_user_account_key $$ …

People often wants to use the MySQL memory engine to store web sessions or other similar volatile data.
There are good reasons for that, here are the main ones:

• Data is volatile, it is not the end of the world if it is lost
• Elements are accessed by primary key so hash index are good
• Sessions tables are accessed heavily (reads/writes), using Memory tables save disk IO

Unfortunately, the Memory engine also has some limitations that can prevent its use on a large scale:

• Bound by the memory of one server
• Variable length data types like varchar are expanded
• Bound to the CPU processing of one server
• The Memory engine only supports table level locking, limiting concurrency

Those limitations can be hit fairly rapidly, especially if the session payload data is large. What is less known is that NDB Cluster can creates tables …

NDB Cluster is the only integrated sharding framework that I know of (educate me if I am wrong) but it is known to have issues with large joins. These days, large databases that would benefit from a sharding framework are often for social networking type applications that requires large joins.

Actually it is not NDB that is the root cause of the joins problem, it is the way MySQL executes joins. Instead of asking the cluster for a large number of rows for the secondary table it joins to, the current version of MySQL does ask one row at a time. NDB cluster answers those queries very rapidly but, the time to hop over the network kills performance. The MySQL-6.0 branch will implement the Batch Key Access (BKA) algorithm which will solve that issue and might create database application killer with NDB cluster.

Although right now BKA is not available, there are ways to execute those queries in an efficient way by rewriting them. The …

With the NDB realtime options, you can choose on which CPU the execution thread and the maintenance will be running. The point is, which CPUs to use.

The output of “cat /proc/interrupts” will help you determine which CPU to use. Here is an example of a dual quad-cores box:

Let’s grab your attention with a shameless marketing ploy:  How would you like up to a 14X performance boost in your Mysql database performance? Now your hooked who wouldn’t want a 14x boost?  Well we got that using Dolphin interconnects and Waffle Grid! Read on!

As you know over the last few weeks the good folks over at Dolphin Interconnect Solutions have lent me use of a couple of servers with their interconnects installed ( They also sent me a couple of cards to test with, but I ended up not being able to secure the hardware to put these through their paces at my home office ) .  I ran into several challenges in the testing, and we also found out a lot about Waffle Grid and where we have some open bugs ( its good to test Waffle at the fringe of performance and see what breaks).

The oddest thing I ran into here was the way using the Dolphin cards …

One year after Sun acquired MySQL we see the positive results of this acquisition. Things are going very well in terms of collaboration, the teams are getting closer together and share the experience and the knowledge. If you want to know how the teamwork is seen from the Sun Cluster engineers, Thorsten Frueauf and myself, you should read this.

It was interesting for us to see that this interview was well observed by MySQL customers and community members. Although the interviews were not technical, they lead to technical questions. You can get more information here.

So if you are keen to see what the MySQL community wants to know about Sun Cluster, read the blog entry about MySQL and Sun Cluster.

Detlef …

One year after Sun acquired MySQL we see the positive results of this acquisition. Things are going very well in terms of collaboration, the teams are getting closer together and share the experience and the knowledge. If you want to know how the teamwork is seen from the Sun Cluster engineers, Thorsten Frueauf and myself, you should read this.

It was interesting for us to see that this interview was well observed by MySQL customers and community members. Although the interviews were not technical, they lead to technical questions. You can get more information here.

So if you are keen to see what the MySQL community wants to know about Sun Cluster, read the blog entry about MySQL and Sun Cluster.

Detlef …