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 extend a NDB cluster

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

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

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

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

For those unfamiliar with SQL Server clustering let me give you a quick blurb.  Microsoft markets SQL Server as having active-active clustering.  When most people hear active/active or clustering in the database they generally immediately think of features like Oracle Rac.  SQL Servers implementation of Active-Acitve clustering ( as of 2005 anyways ) is really a HA setup with one instance active on the A side, and a seperate unrelated instance active on the B Side.  These servers hove different ports, install directories,  and share nothing at the DB level.  A san is used with its disk presented to each node in the cluster.  Microsoft Clustering Services (MCS) is used to manage the IP take over,  handle resource transitions, etc.   The setup is really active-passive, passive-active.  This ends up confusing and even mis-leading people who are both technical and in management positions.

Why Is this